s4: 567980314463 s4-funcs.sh

s4

view s4-funcs.sh @ 791:567980314463

World and Migration structures refined

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 13 Jun 2020 17:31:27 +0900
parents	ce8942919edf
children	8646ec4bd6fc

line source

1 #!/bin/sh

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

3 # $HGid$

5 test -n "$HTTP_HOST" && isCGI=true || isCGI=false

6 if $isCGI; then

7 case "$SCRIPT_NAME" in

8 *-world-*)

9 S4WORLD=${SCRIPT_NAME#*world-}

10 S4WORLD=${S4WORLD%.*}

11 echo S4WORLD=$S4WORLD >&2

12 worldconf=s4-config-${S4WORLD}.sh

13 ;;

14 *)

15 worldconf=s4-config.sh

16 ;;

17 esac

19 echo worldconf=$worldconf >&2

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

21 [ -n "$worldconf" -a -e "$worldconf" ] && . ./$worldconf

22 echo DB=$DB >&2

23 fi

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

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

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

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

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 tmpfiles=""

32 querylog=${QUERYLOG:-$tmpdir/query.log}

33 searchlog=${SEARCHLOG:-$tmpdir/search.log}

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

35 sessdb=${SESSDB:-$dbdir/sess.sq3}

36 userupdateflag=$dbdir/userupdate

37 sesstb=tmp.sess

38 workdb=$dbdir/tmpdata.sq3

39 listentlimit=${LISTENTLIMIT:-30}

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

41 noreply=${NOREPLY:-noreply@example.org}

42 noreply_from="${S4NAME:-s4} message notification <$noreply>"

43 invite_policy=${INVITE_POLICY:-"このコミュニティに関りのあるあなたの信頼できる人を招きます。"}

44 templ=${TEMPL:-templ}

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

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

47 imgdir=${IMGDIR:-img}

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

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

50 msgdir=$templ/msg

51 timeout="+2 days"

52 memoplimitdays="7"

53 dumpcollen=22

54 #thumbxy=120x120

55 thumbxy=96x96

56 iconxy_S=80x80

57 iconxy_M=400x400

58 maximagexy=1600x1600

59 ### maximagexy=400x400

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

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

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

63 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

65 file_accept_help="

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

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

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

69 "

70 file_warn="$file_accept_help

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

72 blogreadflagrowid=0

73 blogcutoffflagrowid=-1

74 whatsnewdays=${WHATS_NEW_DAYS:-14}

75 main_session=`date +%F-$$`

76 session=$main_session

78 tconfs=""

79 imgcached=cache/${S4WORLD:+$S4WORLD/}img.`date +%Y/%m`

80 conftbl=_tblconf

81 nl="

82 "

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

84 iconcachekey="profimgcache_S"

86 # Start debug logging

87 exec 3>> $tmpdir/debug.out

88 err() {

89 echo "[`date +%F-%T%z`] $@" 1>&3

90 }

91 case "$HTTP_USER_AGENT" in

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

93 *) touchpanel="" ;;

94 esac

96 # If S4MASTERDB is set, behave in another world

97 ### if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then

98 # If S4WORLDLIST is set, this s4 have world!

99 if [ -n "$S4WORLDLIST" ]; then

100 . ./s4-world.sh 2>> tmp/debug.out

101 # Variables set in s4-world.sh

102 # $S4WORLDS, $S4WROLDNAME

103 # Files created in s4-world.sh

104 # $worldlistfile, $worldoptionfile, $worldnamefile

105 err S4WORLDS set to $S4WORLDS -----

106 fi

107

108

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

110

111 : <<EOF

112

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

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

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

116 レポート提出管理

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

118

119 [2016]

120 7/12 根本への反省

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

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

123

124 [以下2015]

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

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

127 Web

128 締切設定

129

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

131 ○自分の提出物リスト

132

133 7/19 ○設置

134 ○一斉送信

135 ○getparfilename の tmpd の扱い

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

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

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

139 ○添付ファイル回収

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

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

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

143

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

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

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

147 ○レポート提出モード

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

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

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

151

152

153 7/9 ○管理者の追加

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

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

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

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

158

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

160

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

162 ○グループ検索

163

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

165

166

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

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

169

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

171 ○6/1 par2tableを triplex 対応に

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

173 →とすると一気に

174

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

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

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

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

179

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

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

182 ## 1. passwd

183 ## 2. grp

184 ## 3. grp_mem

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

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

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

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

189

190 ## ● listの定義:

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

192 ## ● hashの定義:

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

194

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

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

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

198 ## csv2sq3 で .csv.sq3 の Makefile

199

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

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

202 ## type := root | comment

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

204 ## unique(id, filename)

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

206

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

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

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

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

211 ## できたー!

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

213 ↓

214 ↓以下に変更

215 with recursive allmem as

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

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

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

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

220

221

222 with recursive allmem as

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

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

225 where allmem.val=grp_m.gname)

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

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

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

229

230

231 ## triggerもできた。

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

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

234

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

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

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

238 ## グループ管理

239 ## 個人情報管理

240 ##

241

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

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

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

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

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

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

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

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

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

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

252

253 ## ログテーブル

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

255

256

257

258 ■表設計

259 * 3つの表に分散管理

260 id格納表 + hash表 + list表

261 * *_s *_m

262

263

264

265 user, user_map, user_col

266

267 ■抽象エントリタイプ

268 * user

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

270 * group

271 権限判定に利用

272 * serial

273 自動idとして機能

274 * password

275 入力 type=passwordで入力

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

277 * session

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

279 * text

280 入力 type=text

281 * textarea

282 入力 textarea

283 * image|document

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

285 * owner

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

287 * gowner

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

289 * timestamp

290 datetime()

291 * parent

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

293 * path

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

295

296 格納タイプ

297 * list

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

299 * hash

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

301

302 オブジェクトタイプ

303 * entry

304 id, title, owner

305 * textpart

306 id, parentID, text

307 * binarypart

308 id, parentID, contenttype, filename, content

309 * content

310 hash(textpart), list(binarypart)

311 * topic

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

313 * reply

314 id, parentID, content

315 * blog

316 list(entry)

317 blog = [topic, list(reply)]

318

319

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

321 "text" => "hogehoge ..",

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

323 "author" => "taro",

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

325 "parent" => "/",

326 "path" => "/1",

327 "text" => "blah, blah, ....",

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

329 {"serial" => 2,

330 "author" => "hanako",

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

332 "parent" => "/",

333 "path" => "/2",

334 "text" => "blah, blah, ....",

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

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

337

338

339 user:=

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

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

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

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

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

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

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

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

348

349 変換表

350 /user/email=m

351

352 blog:=

353 シリアル:id:p:serial

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

355 所有者:owner:s:owner:

356 時刻:ctime:s:stamp:

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

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

359

360 article:=

361 シリアル:id:p:serial

362 筆者:author:s:owner

363 時刻:ctime:s:stamp:

364 参照元:parent:s:parent:

365 パス:path:s:path:

366 本文:text:s:textarea:

367 画像:image:m:image:

368

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

370

371

372 EOF

373

374 logstart() {

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

376 }

377 logend() {

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

379 }

380 sqlog() {

381 logstart

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

383 cat >> $querylog

384 else

385 echo "$*" >> $querylog

386 fi

387 logend

388 }

389 sq() {

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

391 logstart

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

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

394 else

395 echo "$@" >> $querylog

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

397 ###sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"

398 fi

399 logend

400 }

401 dbsetup() {

402 pipedir=$tmpdir/pipedir

403 [ -d $pipedir ] || mkdir -p -m 1777 $pipedir

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

405 suf=`date +%s`

406 sqi=$pipedir/sqi-$suf.$$

407 sqo=$pipedir/sqo-$suf.$$

408 mkfifo $sqi $sqo

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

410 sq $db < $sqi &

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

412 if $isCGI; then

413 exec 2>> $tmpdir/error.out

414 fi

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

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

417 rm $sqi

418 # Attach supplemental DB

419 cat >&5 <<-EOF

420 .output /dev/null

421 ATTACH DATABASE "$sessdb" AS tmp;

422 CREATE TABLE IF NOT EXISTS $sesstb(user, skey, expire, UNIQUE(user, skey));

423 DELETE FROM $sesstb WHERE expire < datetime('now', 'localtime');

424 DELETE FROM $sesstb WHERE expire is NULL or expire = '';

425 .output stdout

426 EOF

427 }

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

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

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

431 fi

432 chmod o-r $querylog $db $sessdb # make sure

433 }

434 cleanup() {

435 trap '' INT HUP EXIT TERM PIPE

436 echo .quit >&5

437 kill $sq3pid

438 kill $sq3pid

439 rm -f $sqo $sqi

440 rm -rf $tmpfiles

441 cleanup2

442 }

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

444 # simple redirection for the sake of speed.

445 query() {

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

447 echo ".output $sqo" >&5

448 logstart

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

450 tee -a $querylog

451 else

452 echo "$@" >> $querylog

453 echo "$@"

454 fi >&5

455 echo ".output stdout" >&5

456 cat $sqo

457 rc=$?

458 logend

459 return $rc

460 }

461 _m4() {

462 #S4NAME=f,f,f

463 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} \

464 ${S4WORLDNAME:+-D_S4WORLDNAME_="$S4WORLDNAME"} \

465 ${S4WORLDS:+-D_S4WORLDS_="$S4WORLDS"} "$@"

466 }

467 ismember() {

468 # $1=user, $2=group

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

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

471 }

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

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

474 }

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

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

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

478 }

479 isgrpowner() (

480 # $1=user, $2=group

481 gn=`sqlquote "$2"`

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

483 err isgrpowner: $sql

484 test -n "`query $sql`"

485 )

486 isgrpownerbygid() (

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

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

489 err isgrpownerbygid: $sql

490 test -n "`query $sql`"

491 )

492 getgroupadminmails() {

493 # $1=group

494 for i in $(getgroupadmins "$1"); do

495 email4group "$1" "$i" ;

496 done

497 }

498 getgroupadmins() { # $1=group

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

500 qgrp=`sqlquote "$1"`

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

502 }

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

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

505 getvalbyid grp $2 \

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

507 }

508 getgroupbyid() {

509 # $1=id|gname

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

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

512 # err ggbyid: `echo $sql`

513 query $sql

514 }

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

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

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

518

519 # Return true when anonymous mode

520 [ "$anonymousmode" ] && return 0

521 # case `getvalbyid blog mode $2` in

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

523 # *closed*)

524 # owner=`getvalbyid blog owner $2`

525 # if isgrp $owner; then

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

527 # elif isuser $owner; then

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

529 # fi

530 # esac

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

532 sql="with getblog as (

533 select key,val from blog_s where id=(

534 select blogid from article where id in

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

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

537 getauthor as (select author from article where id=(select id from $2 where rowid=$3)),

538 isgrp as (SELECT val from getowner WHERE val IN (select gname from grp)),

539 isgrpadm as (select user from grp_adm where

540 gname=(select val from getowner) and

541 user='$1'),

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

543 select case

544 when (select author from article where

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

546 then 'author'

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

548 then 'open'

549 when (select val from getmode) in ('quiz', 'enquete')

550 then CASE

551 WHEN (SELECT val FROM isgrp) IS NULL

552 THEN

553 CASE WHEN (SELECT val from getowner)

554 IN ('$user', (SELECT author FROM getauthor))

555 THEN 'owner-or-user-article-is-readable'

556 ELSE ''

557 END

558 WHEN (select user from isgrpadm) IS NOT NULL

559 THEN 'i-am-admin'

560 ELSE (SELECT author from getauthor WHERE author IN (SELECT user FROM grp_adm WHERE gname=(SELECT val FROM getowner)))

561 END

562 when (select val from getmode) is null

563 then 'open'

564 when (select val from getowner) in (select gname from grp)

565 then (SELECT user FROM isgrpadm)

566 when (select author from article where

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

568 then 'user+author'

569 else '' end;"

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

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

572 # result=`query "$sql"`

573 # err FileAccessibility=$result

574 [ -n "`query $sql`" ] || return 2

575 }

576 linkhome() {

577 # $1=UserOrGroupRowid

578 echo -n "<a href=\"$myname?"

579 if isuser $1; then

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

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

582 name=`gecos $1|htmlescape`

583 else

584 _grid=`numericalize "$1"`

585 echo -n "grp+$1"

586 name=`query "SELECT gname FROM grp WHERE rowid=$_grid;"|htmlescape`

587 fi

588 echo "\">$name</a>"

589 }

590 hreflink() {

591 # s4 specific notation:

592 # ^href=URL

593 # ^iframe=URL

594 # ^video=URL

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

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

597 # OSM umap Wikistyle Notation:

598 # [[URL]] - Simle Link

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

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

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

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

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

604 # Other Style

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

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

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

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

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

610 # SPC+SPC+$ - <br>

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

612 checkboxON="${cb} checked>"

613 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

638

639 }

640 minitbl() {

641 sed -n '

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

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

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

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

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

647 H; # Concat this line to HoldSpace

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

649 $ b cont

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

651 }

652 :cont

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

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

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

656 # . of ^. is workaround for FreeBSD sed

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

658 p; # Print whole "table" element

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

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

661 }

662 x; # Back to the newest line

663 p; # Print rest' | miniul

664 }

665 miniul() {

666 sed -e '

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

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

669 :top

670 s/\n//;

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

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

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

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

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

676 $ b cont

677 N; # 次の行を読む

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

679 /^ /b top

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

681 p; s/.*//;

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

683 /^\* /b top

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

685 }

686

687 :cont

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

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

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

691 H; x

692 }

693 x'

694 }

695 acclog() (

696 # $1=table, $2=rowid

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

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

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

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

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

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

703 fi

704 )

705 gecos() (

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

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

708 )

709 setpar() {

710 # 2020/5/14 Add dirty code to cache essential params

711 if [ x"$session" = x"$main_session" ]; then

712 case "$1" in

713 user) _user="$v" ;;

714 skey) _skey="$v" ;;

715 esac

716 fi

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

718 }

719 unsetpar() {

720 for i; do

721 if [ x"$session" = x"$main_session" ]; then

722 case "$i" in

723 user|skey) unset _$i ;;

724 esac

725 fi

726 query "DELETE FROM par WHERE var='$i';"

727 done

728 }

729 replpar() {

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

731 }

732 getpar() {

733 # err GETPAR=$1, _user=$_user

734 val=""

735 if [ x"$session" = x"$main_session" ]; then

736 case "$1" in # Dirty cache mechanism for high-load average

737 user) val=$_user ;;

738 skey) val=$_skey ;;

739 esac

740 fi

741 val=${val:-`query "select val from par where var='$1' and sessid='$session' $2;"`}

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

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

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

745 fi

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

747 case "$var" in

748 owner)

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

750 echo $user; return

751 elif ismember $user $val; then

752 echo $val; return

753 fi ;;

754 esac

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

756 echo "$val"

757 }

758 setskey() {

759 # For quick response...(?)

760 query "REPLACE INTO $sesstb VALUES('$1', '$2', datetime('now', 'localtime', '$timeout'));"

761 }

762 chkskey() {

763 # $1=sesskey, $user=LoginUserName

764 test -z "$1" && return 1

765 repl=`query "SELECT rowid,user FROM $sesstb WHERE user='$user' AND skey = '$1';"` || return 2

766 rowid=${repl%%\|*}; repuser=${repl#*\|}

767 if [ -n "$rowid" -a x"$user" = x"$repuser" ]; then

768 query "UPDATE $sesstb SET expire=datetime('now', 'localtime', '$timeout') WHERE rowid=$rowid;" # Errors can be ignored

769 return 0

770 fi

771 return 1

772 }

773 resetskey() {

774 if [ -n "$_user" ]; then

775 query "DELETE FROM $sesstb WHERE user='$_user';"

776 fi

777 }

778 getpartype() {

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

780 }

781 getparcount() {

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

783 }

784 getparfilename() {

785 # null if type of $1 is not file

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

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

788 }

789 sqlquote() {

790 (v="$1"

791 case "$v" in

792 "") return ;; # null

793 "X'"*) # quoted hex string

794 echo $1 ;;

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

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

797 echo "\"$v\""

798 return ;;

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

800 echo "\"$v\""

801 return ;;

802 *)

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

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

805 else

806 echo "\"$v\""

807 fi ;;

808 esac)

809 }

810 sqlquotestr() (

811 case "$1" in

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

813 echo "'$v'" ;;

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

815 esac

816 )

817 mktempd() {

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

819 }

820 getcachedir() { # $1=maintable

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

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

823 else

824 echo $tmpd/$thumbxy

825 fi

826 }

827 getval() {

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

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

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

831 echo "$user" ;;

832 stamp|datetime)

833 date "+%F %T" ;;

834 serial)

835 (s=`getpar $2`

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

837 *)

838 getpar "$2" "$3";;

839 esac

840 }

841

842 getvalquote() {

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

844 (v=`getval "$@"`

845 case "$v" in

846 "") echo NULL ;;

847 *) sqlquote "$v" ;;

848 esac)

849 }

850 getparquote() {

851 sqlquote "`getpar $1`"

852 }

853 getbinbyid() {

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

855

856 }

857 getvalbyid() {

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

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

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

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

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

863

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

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

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

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

868 return

869 fi

870 done

871 rowid=$3

872 pk=`gettblpkey $1`

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

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

875 td=${4:-$tmpd}

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

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

878 for kt in s m; do

879 t=${1}_$kt

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

881 vcount=1 # count(val)

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

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

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

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

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

887 if [ $kt = m ]; then

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

889 # Reset val to store filenames if type is string

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

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

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

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

894 i=0

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

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

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

898 i=$((i+1))

899 fn=$c.$i

900 ## err td=$td, fn=$fn, type=$type, val="[$val]"

901 case $type in

902 file:*)

903 #file=$td/$val

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

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

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

907 # FOR SPEED: Skip file generation if imgcache exists

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

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

910 && continue

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

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

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

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

915 select rowid from $cond $slice;

916 .output '$td/$fn'

917 select val from $cond $slice;

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

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

920 .output stdout

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

922 EOF

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

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

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

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

927 ls -lh "$file" |

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

929 case "$type" in

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

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

932 *|*:[Aa]pplication*)

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

934 png:- > "$file"

935 ;;

936 esac

937 ;;

938 *)

939 sq $db<<EOF

940 .output $td/$fn.rowid

941 select rowid from $cond $slice;

942 .output $td/$fn

943 select val from $cond $slice;

944 EOF

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

946 ;;

947 esac

948 done

949 else

950 rm -f $td/$c.count

951 case $type in

952 file:*)

953 echo "$val" \

954 | while read fn; do

955 file=$td/$fn

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

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

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

959 | unhexize > "$file"

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

961 case $type in

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

963 *:[Aa]pplication*)

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

965 png:- > $file ;;

966 esac

967 fi

968 done

969 ;;

970 esac

971 fi

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

973 return

974 fi

975 done

976 done)

977 }

978 getvalbypkey() (

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

980 pk=`gettblpkey $1`

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

982 getvalbyid "$1" "$2" $rowid $4

983 )

984 getvalbycond() {

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

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

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

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

989 getvalbyid "$1" "$2" $rowid "$4"

990 fi

991 }

992 getpwfield() {

993 # getpwfield user column

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

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

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

997 echo "$val"

998 return 0

999 else

1000 return 1

1001 fi

1002 }

1003 numericalize() {

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

1005 }

1006 encode() {

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

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

1009 sha1=sha1

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

1011 sha1=sha1sum

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

1013 sha1=gsha1sum

1014 fi

1015 fi

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

1017 }

1018 enjpeg() {

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

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

1021 cjpeg="cjpeg"

1022 else

1023 cjpeg="convert - jpeg:-"

1024 fi

1025 fi

1026 $cjpeg "$@"

1027 }

1028 mycrypt() (

1029 key=$1 salt=$2

1030 # err \$2=$2

1031 case $2 in

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

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

1034 esac

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

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

1037 )

1038 hexize() {

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

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

1041 hexize="xxd -p"

1042 else

1043 hexize_hd() {

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

1045 }

1046 hexize="hexize_hd"

1047 fi

1048 fi

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

1050 }

1051 unhexize() {

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

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

1054 unhex="xxd -p -r"

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

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

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

1058 EOF

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

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

1061 # world writable...:)

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

1063 fi

1064 fi

1065 cat "$@" | $unhex

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

1067 }

1068 percenthex() {

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

1070 }

1071 htmlescape() {

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

1073 -e "s/</\</g; s/>/\>/g" -e 's/`/\`/g' -e 's/(/\(/g' \

1074 -e 's/`/\&#96/'

1075 }

1076 enascii() {

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

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

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

1080 else

1081 enascii_now=`date +%FT%T`

1082 enascii_sed() {

1083 nkf -Z0Z1Z2 \

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

1085 }

1086 enascii="enascii_sed"

1087 fi

1088 fi

1089 cat "$@" | $enascii

1090 }

1091 size_h() {

1092 i="$1" oi=$1

1093 set -- B B KB MB GB TB

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

1095 oi=$i

1096 i=$((i/1024))

1097 shift

1098 done

1099 echo ${oi}$1

1100 }

1101 gettblconf() {

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

1103 ## tconfs=`sq $db \

1104 tconfs=`query \

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

1106 fi

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

1108 }

1109 gettblkeys() {

1110 # $1=tbl

1111 gettblconf

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

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

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

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

1116 case $type in

1117 *p*)

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

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

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

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

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

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

1124 esac

1125 case $type in

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

1127 esac

1128 done

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

1130 }

1131 gettblpkey() {

1132 # $1=tbl

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

1134 }

1135 gettblfkey() {

1136 (x=`gettblkeys $1`

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

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

1139 }

1140 gettblcols() {

1141 (x=`gettblkeys $1`

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

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

1144 }

1145 gettbl_s_cols() {

1146 (x=`gettblkeys $1`

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

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

1149 }

1150 gettbl_m_cols() {

1151 (x=`gettblkeys $1`

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

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

1154 }

1155 gettbl_h_cols() {

1156 (x=`gettblkeys $1`

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

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

1159 }

1160 gettbl_coltype() (

1161 gettblconf

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

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

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

1165 )

1166 is_hidden() {

1167 # $1=Tbl $2=col

1168 gettblconf

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

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

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

1172 case $x in

1173 *h*) return 0 ;;

1174 *) return 1 ;;

1175 esac

1176 }

1177

1178 dbsetbyid() {

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

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

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

1182 quotedp=$(sqlquotestr "$p")

1183 unset primary update

1184 gettblconf

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

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

1187 #err conf=$conf

1188 case ${conf#*=} in

1189 p*) primary=1 ;;

1190 f*) update=1 ;;

1191 u*) ;;

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

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

1194 esac

1195 #err t=$t

1196 type=string fn=""

1197 case $conf in

1198 */password)

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

1200 ;;

1201 */image*|*/document*)

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

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

1204 ;;

1205 esac

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

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

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

1209 if [ "$primary" ]; then

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

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

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

1213 elif [ "$update" ]; then

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

1215 else

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

1217 fi

1218 )

1219 }

1220 expire() (

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

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

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

1224 )

1225 addsession() {

1226 # expireをセット

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

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

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

1230 # Remove old session parameters

1231 now=`expire now`

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

1233 }

1234 gencookie() (

1235 path=${URL#*:/}

1236 path=${URL%/*}

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

1238 for kv; do

1239 # echo "Set-Cookie: $kv; expires=$expire; Path=$path"

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

1241 done

1242 )

1243 contenttype() {

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

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

1246 }

1247 putheader() {

1248

1249 }

1250 putfooter() {

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

1252 }

1253 getcookie() {

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

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

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

1257 ## err "GetCookie: $k=[$v]"

1258 case "$k" in

1259 user) _user="$v" ;;

1260 skey) _skey="$v" ;;

1261 esac

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

1263 done

1264 }

1265 genrandom() {

1266 # $1=columns (default: 10)

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

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

1269 }

1270 genserial() {

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

1272 }

1273 smail() {

1274 # smail rcpts subj (file)

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

1276 # $MAIL_FROM <- From: header value

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

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

1279 rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`

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

1281 sender=${SENDER:-$admin}

1282 # Do not call m4 with directly passing text

1283 _r=$tmpd/rcpt

1284 echo -n "${SMAIL_TO:-$rcptheader}" > $_r

1285 replyto=${REPLYTO:+"Reply-to: $REPLYTO$LF"}

1286 (_m4 -D_RCPT_="spaste(\`$_r')" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

1287 cat $3 | nkf -jd ) | sendmail -f $sender $rcpt

1288 }

1289 smail_queue_flush() {

1290 # $1=timelimit

1291 timelimit=`query "SELECT datetime('now', 'localtime', '-6 hours');"`

1292 rowids=$(sq $workdb <<-EOF

1293 SELECT rowid FROM smailq

1294 GROUP BY rcpts, subj

1295 HAVING min(time) < '$timelimit';

1296 EOF

1297 )

1298 for rid in $rowids; do

1299 sq $workdb \

1300 "SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid in ($rid)" \

1301 | if IFS='|' read hexr hexs; then

1302 # err "hexrcpt=[$hexr] hexsubj=[$hexs]"

1303 rcpt=`echo "$hexr"|unhexize`

1304 subj=`echo "$hexs"|unhexize`

1305

1306 # err ROWID==$rowid "sql=<<SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid=$rowid;>>"

1307 # err "rcpt=[$rcpt] subj=[$subj]"

1308 if sq $workdb <<-EOF | smail "$rcpt" "$subj"

1309 .separator "\n" "------------------\n\n"

1310 SELECT time, text FROM smailq

1311 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1312 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid)

1313 ORDER by time;

1314 EOF

1315 then

1316 cat <<-EOF | sq $workdb

1317 DELETE FROM smailq

1318 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1319 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid);

1320 EOF

1321 fi

1322 fi

1323 done

1324 }

1325 smail_queue() {

1326 # $1=Rcpts, $@=subj

1327 now=`query "SELECT datetime('now', 'localtime');"`

1328 if [ $? -eq 0 ]; then

1329 rcpts="X'"`echo "$1"|hexize`"'"

1330 subj="X'"`echo "$2"|hexize`"'"

1331 text="X'"`cat | hexize`"'"

1332 err "smail_queue: rcpts=[$1] s=[$2] t:hex=[$text]"

1333 mintime=$(cat <<-EOF | sq $workdb

1334 CREATE TABLE IF NOT EXISTS smailq(rcpts, subj, text, time);

1335 INSERT INTO smailq VALUES($rcpts, $subj, $text, '$now');

1336 SELECT min(time) FROM smailq WHERE rcpts=$rcpts AND subj=$subj;

1337 EOF

1338 )

1339 ## XXX: Adhoc load mitigation

1340 case "$now" in

1341 *[01])

1342 err flush_queue=$mintime

1343 smail_queue_flush ;;

1344 esac

1345 fi

1346 }

1347 setviastring() {

1348 table=par

1349 oifs="$IFS"

1350 IFS="&"

1351 for us in $1; do

1352 k=${us%%=*}

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

1354 setpar "$k" "string" "$v"

1355 done

1356 IFS="$oifs"

1357 }

1358 checkdomain() (

1359 # Check the validity of domain by referring DNS

1360 item=$1

1361 err checkdomain $1

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

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

1364 )

1365 pwcheck() {

1366 # $1=passwd

1367 dbpswd=`getpwfield $user pswd`

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

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

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

1371 }

1372 mypwhash() {

1373 mycrypt "`cat`" `genrandom 5`

1374 }

1375 wasureta() {

1376 user=$1

1377 if ! checkdomain $user; then

1378 contenttype; echo

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

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

1381 putfooter

1382 exit 0

1383 fi

1384 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1386 encpswd=`echo $newpswd|mypwhash`

1387 dbsetbyid user $user pswd "$encpswd" && touch $userupdateflag

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

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

1390 $msgdir/mail-newaccount.m4 \

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

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

1393 }

1394 checkauth() {

1395 user=`getpar user`

1396 skc=`getpar skey` # from cookie

1397 [ -z "$user" ] && return 2

1398 ##skey="`getpwfield $user skey`"

1399 if [ -n "$skc" ]; then

1400 if chkskey "$skc"; then

1401 return 0

1402 fi

1403 fi

1404 pswd=`getpar pswd`

1405 quser=`sqlquotestr "$user"`

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

1407 if [ $? != 0 ]; then # Maybe DB locked

1408 return 4 # 4=server too heavy

1409 elif [ -z "$dbuser" ]; then

1410 err "Login USER failed: [$user]"

1411 return 2 # 2=login fail

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

1413 wasureta "$user"

1414 return 1 # wasureta error

1415 fi

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

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

1418 if pwcheck "$pswd"; then

1419 newsession=`genrandom 34`

1420 if setskey "$user" "$newsession" &&

1421 dbsetbyid user "$user" login "`date '+%F %T'`"; then

1422 gencookie "user=$user" "skey=$newsession"

1423 return 0

1424 else

1425 return 4 # Heavy load??

1426 fi

1427 fi

1428 err "Login failed: [$user]"

1429 return 2 # Password mismatch

1430 }

1431 showlogin() {

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

1433 test -z "$args" && resetskey

1434 s4name=${S4NAME:-s4}

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

1436 cat $layout/login.m4.html

1437 echo '</body></html>'

1438 ) | _m4 \

1439 -D_BODYCLASS_="login" \

1440 -D_TITLE_="$s4name" \

1441 -D_SYSNAME_="Welcome to $s4name" \

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

1443 exit 0

1444 }

1445 dologin() {

1446 checkauth

1447 st=$?

1448 if [ $st != 0 ]; then

1449 contenttype; echo

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

1451 -D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \

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

1453 showlogin # and EXIT

1454 fi

1455 }

1456

1457 # Do instant jobs here

1458 dbsetup

1459 trap cleanup INT HUP EXIT TERM PIPE

1460 # trap cleanup INT HUP

1461

1462

1463 cgiinit() {

1464 tmpd=`tmpd=$tmpdir mktempd`

1465 tmpf=$tmpd/stream.$$

1466 tmpfiles=$tmpfiles" $tmpd"

1467 addsession $session

1468 getcookie

1469 case "$REQUEST_METHOD" in

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

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

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

1473 # cat > $tmpf # too much?

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

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

1476 s="`cat $tmpf`"

1477 ;;

1478 esac

1479 case "$CONTENT_TYPE" in

1480 *boundary*)

1481 bndry=${CONTENT_TYPE#*boundary=}

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

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

1484 do

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

1486 #echo u=$us

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

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

1489 # err k=$k v=$v

1490 case "$k" in

1491 *:filename)

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

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

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

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

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

1497 :

1498 fi

1499 err "k=[$k] v=[$v]" # debug@2020-05-31

1500 err "`ls -lF $tmpd/$v` MimeType=$mimetype"

1501 case "$mimetype" in

1502 [Ii]mage/x-xcf)

1503 bzip2 "$tmpd/$v"

1504 v=${v}.bz2

1505 ;;

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

1507 [Ii]mage/[Hh][Ee][Ii][Ff])

1508 if type heif-convert >/dev/null 2>&1; then

1509 vjpg="${v%.heic}.jpg"

1510 err "Conv $v to $vjpg in $tmpd"

1511 convert -quality 75 -resize $maximagexy'>' \

1512 "$tmpd/$v" "$tmpd/$vjpg" >/dev/null 2>&1

1513 v=$vjpg

1514 else

1515 mimetype="Not supported"

1516 fi

1517 ;;

1518 [Ii]mage/*)

1519 mogrify -quality 75 -resize $maximagexy'>' "$tmpd/$v"

1520 err "Mogrified: `ls -lF $tmpd/$v`" # 2020-05-31

1521 ;;

1522 esac

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

1524 then

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

1526 continue

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

1528 replpar text string \

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

1530 continue

1531 fi

1532 ;;

1533 *)

1534 type='string'

1535 ;;

1536 esac

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

1538 setpar "$k" "$type" "$v"

1539 done

1540 ;;

1541 *)

1542 setviastring "$s"

1543 ;;

1544 esac

1545 }

1546 email4group() {

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

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

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

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

1551 sql="WITH

1552 grpemails AS (

1553 SELECT gname, user, val email

1554 FROM grp_mem NATURAL JOIN grp_mem_s

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

1556 useremails AS (

1557 SELECT user.name, val email

1558 FROM user

1559 LEFT JOIN user_m

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

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

1562 FROM useremails u LEFT JOIN grpemails g

1563 ON u.name=g.user

1564 WHERE u.name in ($users);"

1565 query "$sql"

1566 }

1567 email4groupbyuid() {

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

1569 g=$1; shift

1570 uids=`echo "$@"`

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

1572 sql="SELECT DISTINCT name FROM user WHERE rowid IN ($uids);"

1573 email4group "$g" `query "$sql"`

1574 }

1575 myemail4group() {

1576 # Get my email address for $1-specified group

1577 email4group "$1" "$user" | sed -e 1q -e 's/[ ,].*//'

1578 }

1579 collectmembersbyid() {

1580 # Collect user names of group specified by grid

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

1582 query "SELECT user FROM grp_mem \

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

1584 }

1585 collectmembersbyid() {

1586 # Collect user names of group name

1587 qgrp=`sqlquote "$1"`

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

1589 }

1590 collectgecosesbyid() {

1591 # Collect user gecoses of group

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

1593 query<<-EOF

1594 SELECT gecos

1595 FROM gecoses

1596 WHERE name IN (SELECT user FROM grp_mem

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

1598 EOF

1599 }

1600 collectemail() (

1601 # Collect email addresses for group $1

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

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

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

1605 for e; do

1606 if isuser "$e"; then

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

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

1609 else

1610 qgrp=`sqlquote "$e"`

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

1612 gmem="grp_mem"

1613 else

1614 tm=`sqlquote "$TEAM"`

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

1616 fi

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

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

1619 $gmem g left join grp_mem_s s

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

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

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

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

1624 query "$sql"

1625 fi

1626 done

1627 )

1628 sendinvitation() (

1629 # $1=email

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

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

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

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

1634 gecos=`gecos`

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

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

1637 _m4 -D_URL_="$urlbase" \

1638 -D_USER_="$name" \

1639 -D_EMAIL_="$1" \

1640 -D_REGIST_="$regist" \

1641 -D_ADMIN_="$admin" \

1642 $msgdir/mail-invite.m4 \

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

1644 return 0

1645 )

1646 emaildomaincheck() {

1647 case "$1" in

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

1649 *@*)

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

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

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

1653 return 2

1654 fi

1655 return 0

1656 ;;

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

1658 esac

1659 }

1660 invite() {

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

1662 case "$email" in

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

1664 *@*)

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

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

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

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

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

1670 elif sendinvitation $email; then

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

1672 else # Cannot be reached here

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

1674 fi ;;

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

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

1677 esac

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

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

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

1681 fi

1682 if [ -f $invite_policy ]; then

1683 pol="spaste(\`$invite_policy')"

1684 else

1685 pol="$invite_policy"

1686 fi

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

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

1689 -D_INVITE_POLICY_="$pol" \

1690 $layout/html.m4.html $layout/invite.m4.html

1691 }

1692 regist() {

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

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

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

1696 echo "bye bye" | html p

1697 reutrn

1698 fi

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

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

1701 cat<<EOF

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

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

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

1705 EOF

1706 return

1707 fi

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

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

1710 # Fake login password to wasureta

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

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

1713 wasureta $email

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

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

1716 addsession $1 # for removal after 1 minute

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

1718 $layout/login.m4.html

1719 return

1720 }

1721 group_safename() {

1722 # Convert $1 to safe group name

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

1724 }

1725 groupupdate() {

1726 gname=`getpar gname`

1727 qgname=`sqlquote "$gname"`

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

1729 # See ALSO same job in showgroup()

1730 newgname=`group_safename "$gname"`

1731 err newgname=$newgname

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

1733 err NewGNAME: gname=$newgname

1734 gname=$newgname

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

1736 replpar gname string "$gname"

1737 fi

1738 # Name confliction check

1739 parow=`getpar rowid`

1740 ## err parow=$parow

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

1742 query "BEGIN EXCLUSIVE;"

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

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

1745 if [ $count -gt 0 ]; then

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

1747 query "END;"

1748 return

1749 fi

1750 par2table $formdir/grp.def

1751 query "END TRANSACTION;"

1752 # Remove orphan

1753 : <<EOF

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

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

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

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

1758 EOF

1759 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1763 sql="delete from blog where id in

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

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

1766 query "$sql";

1767 unsetpar tag kwd

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

1769 return # and return

1770 fi

1771 fi

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

1773 fi

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

1775 grid=$(query $sql)

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

1777 grp $grid

1778 }

1779 groupclone() {

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

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

1782 case "$1" in

1783 */noteam)

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

1785 esac

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

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

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

1789 return

1790 fi

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

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

1793 return

1794 fi

1795 i=0

1796 while true; do

1797 copy="-copy$i"

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

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

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

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

1802 i=$((i++))

1803 continue

1804 fi

1805 break

1806 done

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

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

1809 query<<-EOF

1810 BEGIN;

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

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

1813 SELECT $newqname, key, val

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

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

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

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

1818 -- Copy members and their configuration --

1819 REPLACE INTO grp_mem SELECT $newqname, user

1820 FROM grp_mem WHERE gname=$qgrp;

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

1822 FROM grp_mem_s WHERE gname=$qgrp;

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

1824 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1825 -- Copy administrators --

1826 REPLACE INTO grp_adm SELECT $newqname, user

1827 FROM grp_adm WHERE gname=$qgrp;

1828 COMMIT;

1829 EOF

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

1831 STOPCLONEMSG=1 groupconf "$newrowid"

1832 }

1833 groupman() {

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

1835

1836 GF_STAGE="grpconf"

1837 GF_STAGE=groupupdate

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

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

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

1841 -D_DUMPTABLE_="syscmd(cat)" \

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

1843 }

1844 userconf() {

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

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

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

1848 }

1849 groupconf() {

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

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

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

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

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

1855

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

1857 grp=`getgroupbyid $rowid`

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

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

1860 return

1861 fi

1862

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

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

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

1866 ## Setup migration menu

1867 height="10em" ## Ugly!!

1868 v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`

1869 err v=$v

1870 if [ -n "$v" ]; then

1871 migrate=$(cat<<-EOF

1872 `cgi_radio grpaction migrate id="migrate"`<label

1873 for="migrate">別Worldへ移住</label>

1874 <div style="height: $height;">

1875 <form action="?migrategrp">

1876 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

1877 <p>グループや掲示板のURLが変わります。

1878 外部からリンクしている場合は飛べなくなります。

1879 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

1880 「グループのクローン」で

1881 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

1882 <p><label>`cgi_checkbox emichk yes`確認</label></p>

1883 `cgi_hidden stage migrategrp`

1884 `cgi_hidden rowid $rowid`

1885 `cgi_submit OK`

1886 `cgi_reset Reset`

1887 </form>

1888 </div>

1889 EOF

1890 )

1891 fi

1892 html div 'class="foldtabs"' <<-EOF

1893 `cgi_radio grpaction clone id="clone"`<label

1894 for="clone">グループのクローン作成</label>

1895 <div style="height: $height;">

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

1897 <table>

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

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

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

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

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

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

1904 </table>

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

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

1907 </div>

1908 $migrate

1909 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

1910 <div style="height: $height; background: transparent;"></div>

1911 EOF

1912 fi

1913 }

1914 migrategrp() {

1915 rowid=`getpar rowid`

1916 rowid=${rowid%%[!0-9]*}

1917 grp=`getgroupbyid $rowid`

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

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

1920 return

1921 fi

1922 if [ x`getpar emichk` != x"yes" ]; then

1923 echo "移住確認未チェックなので中止します。" | html p

1924 grp "$rowid"

1925 return

1926 fi

1927 destconf=`getpar migrateto`

1928 err destconf=$destconf

1929 if [ ! -e $destconf ]; then

1930 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

1931 grp "$rowid"

1932 return

1933 fi

1934 if [ -n "$worldconf" ]; then

1935 srcconf=$worldconf

1936 else

1937 srcconf=s4-config.sh

1938 fi

1939 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

1940 echo "移住操作" | html h1

1941 echo '<pre>'

1942 set -- "$srcconf" "$destconf" "$rowid"

1943 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

1944 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

1945 rc=$?

1946 echo "</pre>"

1947 if [ $rc -eq 0 ]; then

1948 echo "World [$world] への移住完了。" | html p

1949 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

1950 else

1951 echo "移住失敗" | html p

1952 echo "移動先に重複がないか確認して下さい。" | html p

1953 fi

1954 return

1955 }

1956 mems() {

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

1958 kwd=`getpar kwd`

1959 listmember $kwd

1960 }

1961 grps() {

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

1963 kwd=`getpar kwd`

1964 listgroup $kwd \

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

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

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

1968 $layout/form+dump.m4.html

1969 }

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

1971 gpg=`getpar grp`

1972 grid=${1:-$gpg}

1973 grp=`getgroupbyid "$grid"`

1974 ## . ./s4-blog.sh

1975 jg=`getpar joingrp`

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

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

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

1979 fi

1980 htmlheader=$layout/html.m4.html

1981 showgroup "$grid"

1982 }

1983 sql4interestblogs() {

1984 cat<<EOF

1985 CREATE TEMPORARY VIEW interestblogs AS

1986 SELECT blog.rowid rid, id, author

1987 FROM blog

1988 NATURAL JOIN

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

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

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

1992 WHEN (SELECT user FROM grp_mem

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

1994 THEN 0

1995 ELSE 1

1996 END;

1997 EOF

1998 }

1999 listnewblogsql() { # $1=user

2000 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2001 newdays=${newdays%%[!0-9]*}

2002 newdays=${newdays:-14}

2003 basetime="datetime('now', 'localtime', '-${newdays} days')"

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

2005 WHERE user='$user'

2006 AND tblrowid IN

2007 ($blogreadflagrowid,

2008 $blogcutoffflagrowid)),

2009 $basetime -- "0"

2010 );"`

2011 cat<<EOF

2012 `sql4interestblogs`

2013 WITH article_ctime as (

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

2015 FROM article join article_s s using(id)

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

2017 GROUP BY id

2018 ), blog_title_owner as (

2019 SELECT blg.rid brid, id,

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

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

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

2023 ), blogall as (

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

2025 ), news as (

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

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

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

2029 FROM blogall bl

2030 LEFT JOIN

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

2032 ON bl.brid=al.tblrowid

2033 WHERE atime < bl.ctime

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

2035 LIMIT 10

2036 ) SELECT brid LINK, "新着",

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

2038 ctime, title,

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

2040 FROM news;

2041 EOF

2042 }

2043

2044 search_form() {

2045 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

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

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

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

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

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

2051 @week → 最近一週間

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

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

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

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

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

2057 auth=""

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

2059 case "$1" in

2060 author=*)

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

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

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

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

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

2066 ;;

2067 grid=*)

2068 a=`echo "${1#grid=}"`; a=$((0 + $a))

2069 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

2071 ;;

2072 esac

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

2074 cat<<-EOF

2075 <div class="right">

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

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

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

2079

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

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

2082

2083 </form>

2084 </div>

2085 EOF

2086 }

2087

2088 imgsrc_cache() (

2089 # $1 = directory for cache'ing

2090 # $2 = table (user_m or grp_m)

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

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

2093 dir="$1" tbl="$2"

2094 keycond="$3"

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

2096 ORDER BY rowid DESC LIMIT 1"

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

2098 tmpf=$tmpd/imgsrc_cache.$$

2099 case "$4" in

2100 [Ss]) size=S ;;

2101 [Oo]) size=O ;;

2102 *) size=M ;;

2103 esac

2104 # ImageCache filename storing schema:

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

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

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

2108 valtype=`query "$sql0"`

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

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

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

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

2113 case "$filetype" in

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

2115 esac

2116 fi

2117 cacheimg_S=$dir/S_$filename

2118 cacheimg_M=$dir/M_$filename

2119 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2127 else

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

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

2130 fifo2=$fifo.2

2131 mkfifo $fifo $fifo2

2132 fmt=${filename##*.}

2133 ## [[ NOTE ]]

2134 ## a. convert oldimage newimage

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

2136 ## b is much smaller than a

2137 cat $tmpf | unhexize \

2138 | tee $fifo \

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

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

2141 | tee $fifo2 \

2142 | convert - "$cacheimg_M" &

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

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

2145 | convert - "$cacheimg_S" &

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

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

2148 echo '">'

2149 echo "$sum" > $sumfile

2150 fi

2151 ## Now preparing cache image, done.

2152 ## Store this information to DB

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

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

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

2156 query <<-EOF

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

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

2159 EOF

2160 )

2161

2162 showhome() {

2163 # $1=userRowIdToShow

2164 err showhome \$1=$1

2165 case "$1" in

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

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

2168 esac

2169 ## err ShowHome: uname=$uname

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

2171 gecos=`gecos "$uname"`

2172 ## err SH:gecos=$gecos

2173 GF_VIEWONLY=1

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

2175 search_form_args=""

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

2177 if [ -z "$S4MASTERDB" ]; then

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

2179 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2180 elif [ -n "$S4MASTERURL" ]; then

2181 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2182 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2183 fi

2184 usermenu="$usermenu

2185 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2186 # Display folders

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

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

2189 and type like 'file:%';"

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

2191 nfile=`query "$sql"`

2192 # err nfile=$nfile

2193 if [ $nfile -gt 0 ]; then

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

2195 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2196 fi

2197 else

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

2199 GROUP BY user;"`

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

2201 search_form_args="author=$uname"

2202 fi

2203 . ./s4-blog.sh

2204

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

2206 search_form "$search_form_args" > $sf

2207 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2208 | htmlescape > $tf

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

2210 viewtable $formdir/user.def user $1

2211 echo "</div>"

2212 } > $pf

2213

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

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

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

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

2218 if true; then

2219 tbl=user_m

2220 enticond="name='$uname'"

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

2222 else

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

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

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

2226 echo '">'

2227 }

2228 fi > $imf

2229 fi

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

2231 val='$uname';"`

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

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

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

2235 listblog $uname > $bf

2236 ## unset ddd

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

2238

2239 hometail=$tmpd/tail.$$

2240 mkfifo $hometail

2241

2242 #Calling listgroupbytable, originally here

2243

2244 (

2245 # Display Most Recent Entry

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

2247 shortval=${shortval:-val}

2248

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

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

2251 ctime,

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

2253 (SELECT gecos FROM gecoses

2254 WHERE name=(SELECT val FROM blog_s

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

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

2257 FROM blog b

2258 JOIN

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

2260 FROM article a, article_s s

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

2262 GROUP BY blogid ORDER BY val DESC LIMIT 50

2263 ) m

2264 ON b.id=m.blogid;"

2265 # This should be as follows

2266 : <<EOF

2267 WITH arts AS(

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

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

2270 FROM article a NATURAL JOIN article_s s

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

2272 GROUP by s.id

2273 )

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

2275 FROM arts a0

2276 JOIN

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

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

2279 ORDER BY ctime DESC LIMIT 50;

2280 EOF

2281

2282 cat<<-EOF

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

2284 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2285 <div class="lcto">

2286 `DT_VIEW=replyblog dumptable html blog`

2287 </div>

2288 EOF

2289 unset DT_SQL

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

2291 # Display NEWS

2292 # 2016-06-26

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

2294 acclog blog $blogreadflagrowid

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

2296 fi

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

2298 sql=`listnewblogsql "$user"`

2299 # echo "$sql" > tmp/listnew

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

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

2302 cont=$((cont-1))

2303 err newcount=$cont

2304 if [ $cont -gt 0 ]; then

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

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

2307 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

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

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

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

2311 `cgi_submit '確定'`

2312 `cgi_hidden read yes`

2313 EOF

2314 echo "$new10 "

2315 echo "</div>"

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

2317 acclog blog $blogcutoffflagrowid # for speed

2318 fi

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

2320 : # DT_SQL=

2321 fi

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

2323 #

2324 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2330 -D_NBLOG_="$nblog" \

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

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

2333 $layout/html.m4.html $layout/home.m4.html

2334

2335 # Record access log

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

2337 }

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

2339 contenttype; echo

2340 ## err commission: "$@"

2341 gname=`getgroupbyid $1`

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

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

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

2345 grp_reg_adm "$@"

2346 else

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

2348 fi

2349 }

2350 listgroupbytable() {

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

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

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

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

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

2356 NGsql="select distinct tag from\

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

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

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

2360 sql="select val from grp_s where key='tag' $and$* group by val;"

2361 ## err ListGRP: query sql="$sql"

2362 for tag in `query "$sql"`

2363 do

2364 ## err ListGrp: tag=$tag

2365 tn=${tagline%%=${tag}*}

2366 tn=${tn##*[ :]}

2367 sql="select rowid||':'||gname as 'グループ名',説明 from

2368 (select (select rowid from grp g where g.gname=grp_s.gname)

2369 as rowid,

2370 gname,

2371 max(case key when 'gecos' then val end) as '説明',

2372 max(case key when 'tag' then val end) as 'tag',

2373 max(case key when 'mtime' then val end) as mtime from grp_s

2374 $where$* group by gname having tag='$tag' order by mtime desc);"

2375 ## err PersonalGroupList= `echo $sql`

2376 echo "<h2>$tn</h2>"

2377 echo '<table class="b listgroup">'

2378 sq -header -html $db "$sql" \

2379 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2380 echo '</table>'

2381 done

2382 echo '</div>'

2383 }

2384 iconhref() (

2385 # $1=icon-file, $2=Href $3=title $4...=anchor

2386 data=`percenthex "$1"`

2387 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2388 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2389 href=$2; title=$3; shift 3

2390 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2391 )

2392 iconhref2() (

2393 # $1=icon-file, $2=Href $3=title $4...=anchor

2394 src=$1

2395 href=$2; title=$3; shift 3

2396 anchor=`echo $@|htmlescape`

2397 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2398 )

2399 listentry() (

2400 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2401 # Referring variable $iamowner=$grp to attach owner-request links

2402 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2403 cond='' hiddens=''

2404 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2405 if [ -z "$offset" ]; then

2406 offset=`getpar start`; offset=${offset%%[!0-9]*}

2407 offset=$((offset-1))

2408 fi

2409 offset=$((offset + 0)) # change to numeric forcibly

2410 [ $offset -lt 0 ] && offset=0

2411 limit=$listentlimit

2412 dir=`getcachedir "$1"`

2413 if [ x"$1" = x"user" ]; then

2414 hrb="$myname?home"

2415 deficon=person-default.png

2416 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2417 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2418 gcs=gecos

2419 else # if group

2420 hrb="$myname?grp"

2421 deficon=group-default.png

2422 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2423 gcs=name

2424 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2425 if [ -n "$tagline" ]; then

2426 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2427 ## err tagconv=$tagconv

2428 fi

2429 fi

2430 if [ ! -d $dir ]; then

2431 mkdir -p $dir

2432 fi

2433 if [ ! -s $dir/$deficon ]; then

2434 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2435 fi

2436 if [ -n "$2" ]; then

2437 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2438 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2439 fi

2440 tag=`getpar tag` tag2=`getpar tag2`

2441 if [ x"$tag" = x"NULL" ]; then

2442 tag="" tag2=""

2443 fi

2444 if [ -n "$tag$tag2" ]; then

2445 tag=${tag:-$tag2}

2446 qtag=`sqlquote "$tag"`

2447 cond2="tag=$qtag"

2448 fi

2449 if [ -n "$cond1$cond2" ]; then

2450 cond="$cond1${cond2:+ AND $cond2}"

2451 cond="WHERE ${cond# AND }"

2452 fi

2453

2454 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2455 # grpは呼出し元の動的スコープ変数でよくないな...

2456 ##qgrp=`sqlquote $grp`

2457 getgrp="(select gname from grp where rowid=${rowid:--1})"

2458 sql="select a.rowid, a.$link,

2459 coalesce(b.$gcs, a.$nm) as nick,

2460 quote(a.$nm) as qname,

2461 (SELECT val FROM ${tbl}_s

2462 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2463 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2464 || case when a.$nm in (select gname from grp)

2465 then printf('(%d名)',

2466 (select count(user) from grp_mem where gname=a.$nm))

2467 else ' <'||a.$nm||'>'

2468 end

2469 as name,

2470 b.tag,

2471 case when a.$nm in (select user from grp_adm

2472 where gname=$getgrp) then '管理者'

2473 when '$user' in (select user from grp_adm where gname=a.$nm)

2474 then 'ADMIN'

2475 when '$user' in (select user from grp_mem where gname=a.$nm)

2476 then 'Member'

2477 when '$iamowner' = '' then ''

2478 else ',not='||a.rowid end as ownerlink,

2479 CASE '$entity'

2480 WHEN 'グループ'

2481 THEN coalesce(

2482 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2483 'open')

2484 ||

2485 CASE WHEN '$user'

2486 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2487 THEN ' ismember'

2488 ELSE ''

2489 END

2490 ELSE 'user'

2491 END regmode

2492 from $tbl a left join

2493 (select $nm as name,

2494 max(case key when 'gecos' then val end) as gecos,

2495 max(case key when 'tag' then val end) as tag,

2496 max(case key when 'mtime' then val end) as mtime,

2497 max(case key when 'wtime' then val end) as wtime,

2498 max(case key when 'login' then val end) as login

2499 from ${tbl}_s group by $nm)

2500 b on a.$nm=b.name $cond $3

2501 order by b.wtime desc, b.login desc,

2502 b.mtime desc, b.tag desc, a.rowid asc"

2503 # Give precedence to newer maintained groups (2016-09-24)

2504 # Note that mtime is stored only in grp_s.

2505 ## err LE:sql.1="$sql"

2506 total=`query "with x as ($sql) select count(*) from x;"`

2507 echo "${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2508 echo '<div class="listentry">' # List-entry div

2509 # Show owner/member filter button

2510 METHOD=GET

2511 hiddens="$hiddens

2512 `cgi_hidden kwd \"$kwd\"`

2513 `cgi_hidden stage \"$stage\"`"

2514 if [ x"$tbl" = x"grp" ]; then

2515 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2516 fh="<select name=\"tag\">$nl"

2517 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2518 for l in $args; do

2519 val=${l#*=} tname=${l%=*}

2520 if [ x"$val" = x"$tag" ]; then

2521 s=" selected"

2522 selectedtags="(種別[${tname}]のみ)"

2523 else

2524 s=""

2525 fi

2526 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2527 done

2528 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2529 cat<<-EOF

2530 <form action="$myname" method="$METHOD">

2531 </form>

2532 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2533 for="ismembtn">参加中以外隠す</label>

2534 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2535 for="isadmbtn">管理者参加以外隠す</label>

2536 EOF

2537 # limit=3

2538 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2539 fi

2540 if [ $total -gt $limit ]; then

2541 echo '<div>'

2542 METHOD=GET cgi_form $stage <<EOF

2543 $form

2544 <label>次の語を含む${entity}で検索:

2545 `cgi_text kwd "$kwd"`</label>

2546 $hiddens

2547 EOF

2548 echo '</div>'

2549 else

2550 echo $selectedtags | html p

2551 fi

2552 cat<<EOF

2553 <form action="$myname" method="$METHOD">

2554 <p>${total}件中の<input class="hidesub" type="text" name="start"

2555 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2556 <input type="submit" value="確定"></p>

2557 </form>

2558 EOF

2559 if [ $((offset+limit)) -lt $total ]; then

2560 nextbtn=$(

2561 cat<<EOF

2562 <div class="right clear"><form action="$myname" method="$METHOD">

2563 `cgi_submit 次の${limit}件`

2564 $hiddens

2565 `cgi_hidden offset $((offset + limit))`</form></div>

2566 EOF

2567 )

2568 fi

2569 if [ $offset -gt 0 ]; then

2570 prevbtn=$(

2571 cat<<EOF

2572 <form action="$myname" method="$METHOD">

2573 `cgi_submit 前の${limit}件`

2574 $hiddens

2575 `cgi_hidden offset $((offset - limit))`</form>

2576 EOF

2577 )

2578 fi

2579 pnbtn="$nextbtn$prevbtn"

2580 echo $pnbtn

2581

2582 ## err ListEntry: `echo "$sql"\;`

2583 # sq $db here??? 2016-11-28

2584 query "$sql limit $limit ${offset:+offset $offset};" \

2585 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2586 # err name=$name owner=$ownerp lnk=$lnk

2587 # err newlnk=$lnk regmode=$regmode

2588 icondir=$dir/$id

2589 # Pick up only last icon

2590 htmlname=`echo $name|htmlescape`

2591 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2592 <p class=\"tag _$tag\">$tag</p>" \

2593 | _m4 $tagconv

2594 if [ -n "$NOSPEEDUP" ]; then

2595 files=`getvalbyid $tbl profimg $id $icondir`

2596 if [ -n "$files" ]; then

2597 icon=`echo "$files"|tail -1`

2598 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2599 else

2600 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2601 fi

2602 elif [ -n "$icon" -a -s "$icon" ]; then

2603 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2604 else

2605 cond="$nm=$qname"

2606 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2607 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2608 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2609 # err "img=[$img]"

2610 if [ -n "$img" ]; then

2611 echo "<a href=\"$hrb+$lnk\">"

2612 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2613 echo "</a>"

2614 else

2615 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2616 fi

2617 fi

2618 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2619 echo "</div>"

2620 done

2621 echo "</div>" # End of List-entry div

2622 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2623 )

2624 listmember() {

2625 listentry user "$@"

2626 }

2627 listgroup() {

2628 listentry group "$@"

2629 }

2630 hexteams() { # $1=gname, $2(optional)=user

2631 cond=${2:+" AND user='$2'"}

2632 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2633 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2634 }

2635 showgroup() { # $1=group-rowid

2636 if [ -z "$1" ]; then

2637 grid=`getpar grid`

2638 grid=${grid%%[!0-9]*}

2639 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2640 else

2641 grid=$1

2642 fi

2643 grp=`getgroupbyid $grid`

2644 qgrp=`sqlquote "$grp"`

2645 htmlgrp=`echo "$grp"|htmlescape`

2646 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2647 if isgroup "$grp"; then

2648 tf=$tmpd/title.$$

2649 sf=$tmpd/search.$$

2650 bodyclass=`query "SELECT val FROM grp_s

2651 WHERE gname=$qgrp AND key='regmode';"`

2652 if ismember "$user" "$grp"; then

2653 ismember="ismember"

2654 bodyclass="$bodyclass${bodyclass:+ }ismember"

2655 else

2656 ismember="" # bodyclass="group"

2657 fi

2658 bodyclass="$bodyclass grouphome"

2659 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2660 echo "グループ $htmlgrp" > $tf

2661

2662 showgroupsub $formdir/grp.def "$grid" | \

2663 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2664 -D_FORM_="syscmd(\`cat')" \

2665 -D_BODYCLASS_="$bodyclass" \

2666 -D_DUMPTABLE_="" \

2667 $htmlheader $sf $layout/form+dump.m4.html

2668 # $htmlheader $layout/form+dump.m4.html

2669 # $htmlheader is defined in grp()

2670 else # if $grp is removed at par2table

2671 listgroup

2672 fi

2673 }

2674 showgroupsub() {

2675 # $1=def-file $2=group-rowid

2676 # Using $ismember

2677 rowid=$2

2678 grp=`getgroupbyid $2`

2679 qgrp=`sqlquote "$grp"`

2680 td=`getcachedir grp/"$2"`

2681 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2682 if [ -z "$rowid" ]; then

2683 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2684 #grp=`sq $db "select gname from grp where rowid=$grp"`

2685 echo "showgroupsub: invalid argument($1 $2)" | html p

2686 return

2687 fi

2688 val=`getvalbyid grp profimg $rowid $tmpd`

2689 enticond="gname=$qgrp"

2690 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2691 if [ -n "$img" ]; then

2692 cat<<-EOF

2693 <p class="groupimg">

2694 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2695 EOF

2696 fi

2697 echo "<div class=\"noprofimg\">"

2698 viewtable $1 grp $rowid

2699 echo "</div>"

2700 if isgrpowner "$user" "$grp"; then

2701 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2702 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2703 iamowner=$rowid

2704 colmd=" mode"

2705 fi

2706 if [ -n "$ismember" ]; then

2707 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2708 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2709 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2710 cat<<-EOF

2711 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2712 href="?blog+$rowid">グループの新規話題作成</a>

2713 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2714 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2715 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2716 <div class="fold clear">

2717 `cgi_checkbox send yes id="send"`<label

2718 for="send">グループ全員にメッセージ送信</label>

2719 <div>

2720 `cgi_textarea message "" "cols=60"`

2721 `cgi_submit 送信`

2722 `cgi_reset リセット`

2723 </div>

2724 `cgi_hidden grp $rowid`

2725 </div></form>

2726 EOF

2727 fi

2728 # 加入ボタン + 加入者リスト

2729 if [ -n "$ismember" ]; then

2730 ismem='checked' state="(参加中)"

2731 else

2732 nomem='checked' state="(現在非加入)"

2733 fi

2734 # このグループでの加入アドレス

2735 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2736 and key='email';"`

2737 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2738 ## and key='email';"

2739 ##err email=$eml

2740 cat <<EOF

2741 <div class="fold clear">

2742 `cgi_checkbox reg yes id="reg"`<label

2743 for="reg">自身の加入状態を操作する</label>$state

2744 <div>

2745 EOF

2746 cgi_form grp <<EOF

2747 <p>このグループに</p>

2748 <table class="b">

2749 <tr><th>メンバーとして</th><td>

2750 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2751 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2752 <tr><th>参加する場合のメイルアドレス<br>

2753 <small>(メインのアドレスとは違うものにする場合に記入<br>

2754 同じでよい場合は空欄に)</small></th>

2755 <td>`cgi_text email $eml`</td></tr>

2756 </table>

2757 `cgi_hidden grp $rowid`

2758 EOF

2759 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2760 echo "moderated (承認加入の)グループなので実際に参加できるのは

2761 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2762 fi

2763 echo '</div></div>'

2764 echo '<h2>話題一覧</h2>'

2765 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2766 cat<<-EOF

2767 <form class="summary" action="$myname" title="$thelp">

2768 `cgi_hidden owner "$grp"`

2769 `cgi_hidden kwd "@week"`

2770 `cgi_hidden stage searchart`

2771 `cgi_submit "一週間のまとめ"`

2772 </form>

2773 EOF

2774 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2775 colstate="state:稼動状態:frozen=rowclass=凍結"

2776 DT_CHLD=article:blogid \

2777 DT_QOWNER="$qgrp" \

2778 DT_VIEW=replyblog dumptable html blog \

2779 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2780

2781 getgname="(select gname from grp where rowid=$rowid)"

2782 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2783 cm="?commission+$rowid"

2784 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2785 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2786 # team list

2787 hexteams=`hexteams "$grp"`

2788 if [ -n "$hexteams" ]; then

2789 echo "チーム一覧" | html h2

2790 echo '<div class="dumptable"><table class="b">'

2791 sq $db -html -header<<-EOF

2792 SELECT val TEAM,

2793 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2794 MEMBERS

2795 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2796 EOF

2797 echo '</table></div>'

2798 fi

2799 }

2800 grp_getbodyclass() {

2801 # Get css class name for document.

2802 # `moderated' for moderated groups

2803 # `ismember' for groups where user belongs

2804 # $1=GroupName (w/o quote)

2805 # $user=userNameCurrentlyLogin

2806 ## err grp_getbodyclass: 1="$1"

2807 qgrp=`sqlquote "$1"`

2808 query<<-EOF

2809 SELECT coalesce(

2810 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2811 'open')

2812 ||

2813 CASE WHEN '$user'

2814 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2815 THEN ' ismember'

2816 ELSE ''

2817 END;

2818 EOF

2819 }

2820 grpaction() { # $1=group-rowid

2821 err GRP_ACTION:IN

2822 grid=${1:-`getpar grp`}

2823 grp=`getgroupbyid "$grid"`

2824 htmlgrp=`echo "$grp" | htmlescape`

2825 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2826 if [ -z "$grp" ]; then

2827 echo "無効な指定です。" | html p; return

2828 fi

2829 if ! ismember $user "$grp"; then

2830 echo "加入者のみに許可された操作です。" | html p; return

2831 fi

2832 echo "グループ $grp 個別選択操作" \

2833 | _m4 -D_TITLE_="syscmd(\`cat')" \

2834 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2835 $layout/html.m4.html

2836

2837 isowner=""

2838 isgrpowner "$user" "$grp" && isowner="yes"

2839 usel=`getpar usel`

2840 if [ -n "$usel" ]; then

2841 uids=$(echo `echo $usel`|tr ' ' ',')

2842 ## err grpaction-1: grp=$grp, `echo $sql`

2843 text=`getpar text|tr -d '\r'`

2844

2845 rm=`getpar rm` cfm=`getpar confirm`

2846 ## err rm=$rm cfm=$cfm

2847 if [ x"$rm" = x"yes" ]; then

2848 if [ "$isowner" ]; then

2849 if [ x"$rm$cfm" = x"yesyes" ]; then

2850 # Eliminate

2851 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2852 for tbl in grp_mem grp_mem_s grp_mem_m; do

2853 sql="delete from $tbl $cond;"

2854 # echo "sql=$sql"

2855 query "$sql"

2856 err rmGRPuser "$sql"

2857 done

2858 num=`query "select count(*) from user where rowid in ($uids);"`

2859 #err num=$num

2860 if [ 0$num -gt 0 ]; then

2861 sql="select coalesce(b.val,a.name) from user a left join \

2862 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2863 # err `echo "$sql"`

2864 html pre<<EOF

2865 以下の${num}名のグループ $grp 登録を解除しました。

2866 `query "$sql"`

2867 EOF

2868 fi

2869 else

2870 echo "確認のチェックがないのでやめておきます。" | html p

2871 return

2872 fi

2873 else # not Group Owner

2874 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2875 return

2876 fi

2877 cat<<EOF

2878

2879 EOF

2880 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2881 if [ -z "$text" ]; then # if msg is empty

2882 echo "なにかメッセージを..." | html p

2883 return 0

2884 fi

2885 gecos=`gecos $user`

2886 safegc=`echo "$gecos" | tr -d '<>@'`

2887 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2888 fromad=`myemail4group "$grp"`

2889 ###mail_from="$safegc <$fromad>"

2890 mail_from="$safegc <$user>" # TEST: 2020/5/13

2891 test -n `getpar sender` &&

2892 export SENDER=$user # TEST: 2020/5/15

2893 replyto=$fromad

2894

2895 ## Start parse of attachment files

2896 if [ -n "`getpar email`" ]; then

2897 ar=`getpar supprcpt`

2898 if [ -n "$ar" ]; then

2899 for a in $ar; do

2900 if checkdomain "$a"; then

2901 supprcpt="$supprcpt $a"

2902 else

2903 err "SupprcptErr=[$a] by $user"

2904 removercpt="$removercpt $a"

2905 fi

2906 done

2907 fi

2908 subj=`getpar subject`

2909 afiles=""

2910 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2911 do

2912 f=$tmpd/$fn

2913 if [ -s $f ]; then

2914 afiles=$afiles"${afiles:+ }$f"

2915 fi

2916 done

2917 else

2918 preface=$(cat <<-EOF

2919 $url

2920 のグループ「$grp」のメンバーである $gecos さんから、

2921 あなた宛のメッセージです。

2922 ----------------------------------------------------------

2923 EOF

2924 )

2925 fi

2926 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2927 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2928 subj="${subj:-$gecos さんからのメッセージ}"

2929 REPLYTO=$replyto

2930 MAIL_FROM=$mail_from

2931 export REPLYTO SMAIL_TO MAIL_FROM

2932 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2933 for r in $rcpts; do

2934 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2935 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2936 else

2937 # Show sender and rcpts address for guest

2938 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2939 fi

2940 if [ -n "$afiles" ];then

2941 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2942 else

2943 smail "$r" "$subj"

2944 fi <<EOF

2945 ${preface:+$preface$nl}$text

2946 EOF

2947 done

2948 if [ $? = 0 ]; then

2949 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2950 sql="select coalesce(b.val, a.name) from

2951 (select name from user where rowid in ($uids)) a

2952 left join user_s b on a.name=b.name and b.key='gecos';"

2953 html pre<<EOF

2954 `query "$sql"`

2955 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2956 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2957 EOF

2958 err SendDone: `echo $sql`

2959 fi

2960 elif [ x"$rm" = x"commission" ]; then

2961 grp_reg_adm $grid $usel

2962 elif [ x"$rm" = x"addteam" ]; then

2963 team=`getpar team|sed "s/'/''/g"` # for single quotation

2964 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2965 if [ x"$team" != x"$newteam" ]; then

2966 echo "チーム名に使えない文字を除去しました" | html p

2967 team=$newteam

2968 fi

2969 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2970 cat<<-EOF | html p

2971 有効なチーム名を入力してください。

2972 カンマだけ、「なし」という名前は使えません。

2973 EOF

2974 echo "有効なチーム名を入力してください。" | html p

2975 else

2976 grp_add_team $grid "$team" $usel

2977 fi

2978 elif [ x"$rm" = x"rmteam" ]; then

2979 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2980 rmteam=`getpar rmteam|sed "s/'/''/g"`

2981 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2982 gname='$grp' AND user='$user' AND key='team'\

2983 AND val='$rmteam';\"`" ]; then

2984 grp_rm_team $grid "$rmteam" $usel

2985 else

2986 echo "所属していないチームの除去操作はできません。"|html p

2987 fi

2988 else

2989 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2990 fi

2991 fi

2992 fi

2993 # POST count summary

2994 from=`getpar from`; to=`getpar to`

2995 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2996 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2997 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2998 # New entry

2999 sql="WITH mems AS (

3000 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3001 ON gm.user=g.name

3002 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3003 ), target_article AS (

3004 SELECT id FROM article_s

3005 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3006 ), posts AS (

3007 SELECT author, count(author) post

3008 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3009 WHERE blogid IN (SELECT id FROM blog_s

3010 WHERE key='owner'

3011 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3012 AND key='text'

3013 GROUP BY author

3014 ), teams AS (

3015 SELECT user, group_concat(val, ', ') team

3016 FROM grp_mem_m

3017 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3018 AND key='team'

3019 GROUP BY user

3020 ), user_post AS (

3021 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3022 FROM mems m LEFT JOIN posts

3023 ON m.name=posts.author

3024 GROUP by m.rowid

3025 )

3026 SELECT

3027 CASE

3028 WHEN (SELECT user FROM grp_adm

3029 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3030 AND user=up.name) IS NOT NULL

3031 then 'k'

3032 ELSE ''

3033 END || rowid || ',' || gecos NAME,

3034 post POST, team _TEAM_

3035 FROM user_post up LEFT JOIN teams t

3036 ON up.name=t.user

3037 ORDER BY gecos;"

3038 ## err grpaction: "`echo \"$sql\"`"

3039 tf=$tmpd/title.$$

3040 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3041 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3042 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3043 for=\"cmadmin\">管理者委任</label>

3044 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3045 </p></div>"

3046 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3047 title=\"Shortcut: g${nl}Remove from the Group\"

3048 for=\"conf\">GRP登録解除</label>

3049 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3050 <p>この操作による通知は本人に行きません。

3051 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3052 </div>"

3053 # Get team list to which current user belongs into $hexteams

3054 allhexteams=$(hexteams "$grp")

3055 if [ -n "$isowner" ]; then

3056 myhexteams="$allhexteams" # admin can remove all teams' attr

3057 else

3058 myhexteams=$(hexteams "$grp" "$user")

3059 fi

3060 if [ -n "$myhexteams" ]; then

3061 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3062 title=\"Shortcut: s${nl}Strip a team tag from\"

3063 for=\"cmrmteam\">チーム属性除去</label>

3064 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3065 を除去します: `cgi_checkbox teamconfirm yes` 確認

3066 <p>この操作による通知は本人に行きません。

3067 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3068 </div>

3069 "

3070 fi

3071 stf=$tmpd/selteam.$$

3072 cgi_select_h selteam "5445414d" $allhexteams > $stf

3073 b1='<label> <input type="checkbox" name="usel" value="'

3074 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3075 br='<span id="reverse" title="Reverse Selection"></span>'

3076 #b2='"> <span>' b3='</span></label>'

3077 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3078 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3079 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

3080 cgi_form grpaction<<EOF \

3081 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3082 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

3083 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

3084 | _m4 -D_TITLE_="spaste(\`$tf')" \

3085 -D_SUBTITLE_="チェック後操作ボタン" \

3086 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3087 $layout/form+dump.m4.html \

3088 | _m4 -D_TEAM_="spaste(\`$stf')"

3089 <p>下でチェックした人を対象として:</p>

3090 <div class="foldtabs">

3091 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3092 title="Shortcut: a${nl}Add a team tag to"

3093 for="cmteam">同じチーム属性を付与</label>

3094 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3095 `cgi_datalist_h teams $allhexteams`

3096 </div>

3097 ${rmteammsg}

3098 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3099 title="Shortcut: d${nl}DirectMail to"

3100 for="sendmsg" title="Direct Message">DM送信</label>

3101 <div>

3102 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3103 title="Using email format">email書式を使う</label>

3104 <div class="folded">

3105 <table>

3106 <tr><td>From: </td><td>$user</td></tr>

3107 <tr><td>このFrom:で送る</td>

3108 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3109 </td></tr>

3110 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3111 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3112 <tr><td>ファイル添付: </td>

3113 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3114 </table>

3115 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3116 </div>

3117 <div>本文:`cgi_textarea text "" cols=72`

3118 </div>

3119 </div>

3120 ${isowner:+$cmmsg$excmsg}

3121 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3122 </div>

3123 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3124 <table class="td2r thl">

3125 `sq $db -header -html "$sql"`

3126 </table>

3127 `cgi_hidden grp $grid`

3128 `cgi_hidden myuid $myuid id="myuid"`

3129 EOF

3130 }

3131 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3132 # Create TEMPORARY VIEW

3133 query<<EOF

3134 CREATE TEMPORARY VIEW writeusers AS

3135 SELECT DISTINCT author FROM article

3136 WHERE id in (

3137 select id from article where blogid=(select id from blog where rowid=$1)

3138 );

3139 CREATE TEMPORARY VIEW movablegroups AS

3140 SELECT g.rowid growid , g.gname

3141 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3142 ON grp.gname=gm.gname -- そのユーザが属している

3143 AND user='$user') g -- グループに絞る

3144 WHERE (SELECT author FROM writeusers

3145 EXCEPT

3146 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3147 IS NULL;

3148 $2

3149 EOF

3150 }

3151 sql4readableblogs() {

3152 # Create view of blogs that can be readable to $user

3153 # Blog is readable when:

3154 # 1: blog owner is an user

3155 # 2: else, 2.1: owner-group where the $user belongs

3156 # 2.2: else, owner-group is not moderated

3157 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3158 cat<<EOF ## | tee tmp/sql.out

3159 CREATE TEMPORARY VIEW readableblogs AS

3160 SELECT blog.rowid rid, id, author

3161 FROM blog

3162 NATURAL JOIN

3163 (SELECT id,

3164 max(CASE key WHEN 'owner' THEN val END) owner,

3165 max(CASE key WHEN 'mode' THEN val END) mode

3166 FROM blog_s GROUP by id) bs

3167 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3168 THEN 1 -- blog owner is an user, READABLE

3169 WHEN (SELECT val FROM grp_s

3170 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3171 AND

3172 (SELECT user FROM grp_mem

3173 WHERE gname=bs.owner AND user='$user') IS NULL

3174 THEN 0

3175 WHEN mode IN ('quiz', 'enquete')

3176 THEN 0 -- "quiz" mode blog cannot be searched

3177 ELSE 1

3178 END;

3179 EOF

3180 }

3181 editheading() { # $1=rowid-of-heading

3182 rowid=${1%%[!A-Z0-9a-z_]*}

3183 if [ -z "$rowid" ]; then

3184 echo "話題番号が未指定です。" | html p

3185 return

3186 fi

3187 owner=`getvalbyid blog owner $rowid`

3188 title=`getvalbyid blog title $rowid`

3189 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3190 | _m4 -D_TITLE_="修正" \

3191 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3192 -D_BLOGS_="" -D_DUMPTABLE_="" \

3193 -D_FORM_="syscmd(\`cat')" \

3194 $layout/html.m4.html $layout/form+dump.m4.html

3195 # Move to group

3196 if isuser "$owner"; then

3197 crview4article $rowid

3198 n=`query "SELECT count(*) FROM writeusers;"`

3199 ## err N=$n

3200 if [ $((n)) -gt 0 ]; then

3201 ## err ROWID=$rowid

3202 sql="SELECT growid || ':' || gname FROM movablegroups;"

3203 cat<<-EOF

3204 <div class="fold">

3205 `cgi_checkbox mv send id="mv"`<label

3206 for="mv">この話題をグループ所有に移動する</label>

3207 <div>

3208 <form action="?mvart" method="POST" enctype="multipart/form-data">

3209 移動先グループ:

3210 <select name="mv2grp">

3211 EOF

3212 query ".mode html"

3213 query<<-EOF |

3214 $sql

3215 .mode list

3216 EOF

3217 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3218 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3219 cat<<-EOF

3220 </select>

3221 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3222 そのグループに加入しているものに限られます)</p>

3223 <p>`cgi_checkbox cfm yes`<label>確認

3224 (この操作は元に戻すことができません)</label></p>

3225 `cgi_hidden blogrowid $rowid`

3226 `cgi_submit 移動`

3227 `cgi_reset Reset`

3228 </form>

3229 </div>

3230 </div>

3231 EOF

3232 fi

3233 # end of isuser "$owner"

3234 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3235 [ -n "$hexteams" ];}; then

3236 none="`echo なし|hexize`"

3237 cat<<-EOF

3238 <div class="fold">

3239 `cgi_checkbox mv2team send id="mv2team"`<label

3240 for="mv2team">この話題を以下のチームのものにする</label>

3241 <div><p>現在の所属チーム設定:

3242 `query "SELECT

3243 coalesce((SELECT val FROM blog_s

3244 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3245 AND key='team'),

3246 ':なし');"`</p>

3247 <form action="?mvart" method="POST" enctype="multipart/form-data">

3248 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3249 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3250 `cgi_hidden blogrowid $rowid`<br>

3251 `cgi_submit 移動`

3252 `cgi_reset Reset`

3253 </form></div></div>

3254 EOF

3255 fi

3256 }

3257 mvart() { # move diary to some group or team

3258 # or move blog of group to team which belong to the group

3259 blogrowid=`getpar blogrowid`

3260 cfm=`getpar cfm`

3261 ##### echo move blog:$blogrowid to $mv2grp | html p

3262 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3263 . ./s4-blog.sh

3264 if [ -z "$blogrowid" ]; then

3265 echo "無効な指定です(mvart)。" | html p

3266 return

3267 elif [ x"$cfm" != x"yes" ]; then

3268 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3269 elif { mv2grp=`getpar mv2grp`

3270 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3271 [ -n "$mv2grp" ]; }; then

3272 crview4article $blogrowid

3273 ########## TRANSACTION BEGIN

3274 query "BEGIN;"

3275 n=`query "SELECT count(*) FROM writeusers;"`

3276 ## err Nwriteuser=$n

3277 if [ $((n)) -gt 0 ]; then

3278 query<<-EOF

3279 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3280 WHERE key='owner'

3281 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3282 AND $mv2grp IN (SELECT growid FROM movablegroups);

3283 EOF

3284 fi

3285 query "END;"

3286 ########## TRANSACTION END

3287 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3288 [ -n "$mv2team" ];}; then

3289 # blog owner can move it to ANY team

3290 case "$mv2team" in

3291 'なし')

3292 cat<<-EOF

3293 DELETE FROM blog_s

3294 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3295 AND key='team';

3296 EOF

3297 ;;

3298 "") ;;

3299 *)cat<<-EOF

3300 BEGIN;

3301 REPLACE INTO blog_s(id, key, val)

3302 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3303 'team', '$mv2team');

3304 REPLACE INTO blog_s(id, key, val)

3305 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3306 'notify', 'all'); -- Change notify to all

3307 END;

3308 EOF

3309 esac | query

3310 fi

3311 blog_reply $blogrowid

3312 echo yes | html p

3313 }

3314 editart() { # $1=article-rowid $2=blogrowid

3315 rowid=${1%%[!A-Z0-9a-z_]*}

3316 blogrowid=${2%%[!A-Z0-9a-z_]*}

3317 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3318 echo "表示する記事番号が未指定です。" | html p

3319 return

3320 fi

3321 owner=`getvalbyid blog owner $blogrowid`

3322 title=`getvalbyid blog title $blogrowid`

3323 author=`getvalbyid article author $rowid`

3324 ## err EDITart: owner=$owner, author=$author

3325 if isgrpowner "$user" "$owner"; then

3326 : EDIT OK

3327 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3328 echo "本人か所有者しか編集できません." | html p

3329 return

3330 fi

3331 aid=`query "select id from article where rowid=$rowid;"`

3332 tmpout=$tmpd/editart.$$.out

3333 GF_ACTION="?replyblog+$blogrowid#$aid" \

3334 edittable $formdir/article.def article $rowid \

3335 > $tmpout

3336 rm -f /tmp/editart.out

3337 # Cannot use pipelining to m4 with genform() because of stdin stack

3338 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3339 -D_FORM_="syscmd(cat $tmpout)" \

3340 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3341 -D_BLOGS_= -D_DUMPTABLE_= \

3342 $layout/html.m4.html $layout/form+dump.m4.html

3343 }

3344 send2mem() {

3345 rowid=`getpar grp`

3346 rowid=${rowid%%[!0-9]*} # Cleaning

3347 if [ -z "$rowid" ]; then

3348 echo "グループが未指定です。" | html p

3349 return

3350 fi

3351 message=`getpar message`

3352 if [ -z "$message" ]; then

3353 echo "文章を入れてください。" | html p

3354 return

3355 fi

3356 grp=`getgroupbyid $rowid`

3357 members=`collectemail "$grp"`

3358 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3359 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3360 mailfrom="`gecos "$user"` <$mailfrom>"

3361 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3362 msg=$(cat<<-EOF

3363 $urlbase?grp+$rowid

3364 グループ $grp に所属する

3365 `gecos $user` さんよりメッセージ:

3366

3367 $message

3368 EOF

3369 )

3370 # smail rcpt subj (file)

3371 for m in $members; do

3372 echo "$msg" |

3373 MAIL_FROM=$mailfrom \

3374 SENDER=$noreply \

3375 REPLYTO=$mailfrom \

3376 smail "$m" "$sj"

3377 done

3378 cat<<EOF

3379 <p>以下のユーザに送信しました。</p>

3380 <pre>

3381 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3382 </pre>

3383 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3384 EOF

3385 }

3386 joingrpadmit() {

3387 # $1=yes/no $2=session-key

3388 if [ -z "$2" ]; then

3389 echo "bye bye" | html p; return

3390 fi

3391 t_usr=`session=$2 getpar adduser`

3392 t_grp=`session=$2 getpar group`

3393 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3394 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3395 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3396 echo "無効な加入依頼です。" | html p

3397 echo "有効期限が切れたか、

3398 他の管理者がいる場合は処理済みの可能性があります。" | html p

3399 return

3400 fi

3401 if ! isgrpowner "$user" "$t_grp"; then

3402 echo "グループ管理者のみの機能です。" | html p; return

3403 fi

3404 case $1 in

3405 yes) joingrp "$t_grp" "$t_usr" yes ;;

3406 no) joingrp "$t_grp" "$t_usr" no ;;

3407 *)

3408 echo "無効な指定です($1)。" | html p

3409 return ;;

3410 esac

3411 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3412 rcpts="`getgroupadminmails "$t_grp"` $user"

3413 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3414 body="に

3415 $t_usr

3416 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3417 しました。"

3418 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3419 query "delete from session where id='$2';"

3420 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3421 }

3422

3423 joingrprequest() {

3424 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3425 jss="joingrp-`date +%s`-`genrandom 12`"

3426 addsession $jss +${memoplimitdays}days

3427 grpadmins=`getgroupadmins "$1"`

3428 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3429 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3430 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3431 $url

3432 $user さんから

3433 グループ $1

3434 に加入依頼がありました。

3435

3436 承認する:

3437 $urlbase?joingrpadmit+yes+$jss

3438

3439 白紙に戻す:

3440 $urlbase?joingrpadmit+no+$jss

3441 EOF

3442 echo "管理者に加入依頼を出しました。

3443 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3444 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3445 }

3446 joingrp() {

3447 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3448 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3449 if isgrpowner "$user" "$1"; then

3450 isowner="yes"

3451 elif [ -n "$5" ]; then

3452 isowner="yes"

3453 else

3454 isowner=""

3455 fi

3456 ## err jg:isgrpowner: isowner="$isowner"

3457 if [ -n "$isowner" ]; then

3458 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3459 elif [ x"$2" != x"$user" ]; then # if user is not login user

3460 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3461 return

3462 elif [ x"$3" = x"no" ]; then

3463 : # Do not pursue those who leave

3464 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3465 : # Member can change own email address for the joining moderated group

3466 else # adding user is $user itself

3467 case `getgroupattr "$1" regmode` in

3468 moderated)

3469 joingrprequest "$@" # Request only

3470 return

3471 ;;

3472 *)

3473 ;;

3474 esac

3475 fi

3476 qgname=`sqlquote "$1"`

3477 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3478 cond="where gname=$qgname and user='$2'"

3479 if [ x"$3" = x"yes" ]; then

3480 query "replace into grp_mem values($qgname, '$2');"

3481 # Notify joingrp to admin

3482 action="に加入しました。"

3483 if [ -n "$4" ]; then

3484 if msg=`emaildomaincheck "$4"`; then

3485 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3486 'string', '$4', NULL);"

3487 else

3488 echo $msg

3489 fi

3490 else

3491 query "delete from grp_mem_s $cond and key='email';"

3492 fi

3493 if [ -n "$5" ]; then # as ADMIN

3494 # Coming here means newly created group

3495 sql="select case\

3496 when (select count(*) from grp_mem where gname=$qgname)=1\

3497 then (select user from grp_mem\

3498 where gname=$qgname and user='$user')\

3499 else '' end; "

3500 err NewGrpChk: $sql

3501 if [ -n "`query \"$sql\"`" ]; then

3502 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3503 query "replace into grp_adm values($qgname, '$user');"

3504 fi

3505 fi

3506 else

3507 query "begin;

3508 delete from grp_mem $cond;

3509 delete from grp_mem_s $cond;

3510 delete from grp_mem_m $cond;

3511 delete from grp_adm $cond;

3512 delete from grp_adm_s $cond;

3513 delete from grp_adm_m $cond;

3514 end;"

3515 action="から脱退しました。"

3516 fi

3517 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3518 $url?grp+$grid

3519 $user (`gecos $user`)さんが

3520 グループ $1

3521 $action

3522 EOF

3523 }

3524 grp_add_team() (

3525 # $1=grp-rowid $2=team $3...=user-rowid(s)

3526 grp=`getgroupbyid $1`

3527 team=$2; shift; shift

3528 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3529 { echo "BEGIN;"

3530 for user; do

3531 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3532 '$grp',\

3533 (SELECT name FROM user WHERE rowid=$user),\

3534 'team', 'string', '$team');"

3535 done

3536 echo "END;"

3537 } | query

3538 )

3539 grp_rm_team() (

3540 # $1=grp-rowid $2=team $3...=user-rowid(s)

3541 grid=$1

3542 qgrp=$(sqlquote "`getgroupbyid $grid`")

3543 team=$2; shift; shift

3544 [ -z "$grid" -o -z "$team" ] && return

3545 { echo "BEGIN;"

3546 for user; do

3547 echo "DELETE FROM grp_mem_m\

3548 WHERE gname=$qgrp \

3549 AND user=(SELECT name FROM user WHERE rowid=$user)\

3550 AND key='team' AND val='$team';"

3551 done

3552 cat<<-EOF

3553 DELETE FROM blog_s

3554 WHERE rowid=(

3555 SELECT rowid

3556 FROM blog_s a

3557 WHERE key='team'

3558 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3559 AND NOT EXISTS (SELECT * FROM grp_mem_m

3560 WHERE key='team' AND val=a.val -- a.val=team

3561 AND gname = (SELECT val FROM blog_s b

3562 WHERE a.id=b.id AND key='owner')

3563 ));

3564 EOF

3565

3566 echo "END;"

3567 } | query

3568 )

3569 grp_reg_adm() {

3570 # $1=grp-rowid $2...=user-rowid

3571 grid=$1

3572 grp=`getgroupbyid "$1"`

3573 if [ -z "$grp" ]; then

3574 echo "無効なグループIDです" | html p; return

3575 fi

3576 if ! isgrpowner "$user" "$grp"; then

3577 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3578 fi

3579 shift

3580 for urid; do

3581 newadm=`query "select name from user where rowid=$urid;"`

3582 if [ -z "$newadm" ]; then

3583 echo "指定ユーザIDがおかしいようです。" | html p; return

3584 fi

3585 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3586 err ismember $newadm $grp

3587 if ismember $newadm "$grp"; then

3588 # OK, go ahead

3589 getgname="(select gname from grp where rowid=$grid)"

3590 query "replace into grp_adm values($getgname, '$newadm');"

3591 # confirm insertion

3592 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3593 if [ -n "`query \"$sql;\"`" ]; then

3594 echo "追加完了: $newadm" | html p

3595 else

3596 echo "追加失敗($1 $urid)" | html p

3597 fi

3598 fi

3599 showgroup $grid

3600 done

3601 }

3602 dt_rowhack() {

3603 # From: <TR>

3604 # ....

3605 # <TD>rowclass=foo</TD>

3606 # </TR>

3607 # To: <TR class="foo">....<TD>foo</TD></TR>

3608 sed -e '

3609 /^<TR>/ {

3610 :loop

3611 s/\n//

3612 N

3613 /<\/TR>/ {

3614 s/\n//

3615 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3616 n

3617 }

3618 $q

3619 b loop

3620 }'

3621 }

3622 dumptable() {

3623 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3624 # textのフィールドだけ全てダンプにしたほうがいいか

3625 # $DT_VIEW sets link

3626 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3627 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3628 VIEW=${DT_VIEW-replyblog}

3629 if [ -n "$VIEW" ]; then

3630 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3631 fi

3632 sqlfile=$tmpd/dump.sql

3633 : > $sqlfile # ensure to be empty

3634 printf '.mode html\n.header 1\n' > $sqlfile

3635 # $DT_CHLD=ChildTable:BindColumn

3636 if [ -n "$DT_CHLD" ]; then

3637 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3638 cat<<-EOF >> $sqlfile

3639 -- presql

3640 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3641 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3642 EOF

3643 # Speed up counting of new articles

3644 cat<<-EOF >> $sqlfile

3645 -- presql2

3646 DROP TABLE IF EXISTS _counts;

3647 CREATE TEMPORARY TABLE _counts AS

3648 SELECT $_i, count($_i) cnt

3649 FROM $_t GROUP BY $_i;

3650 /* Prepare NEW count table */

3651 CREATE TEMPORARY TABLE _target AS

3652 SELECT b.rowid trowid, b.id

3653 FROM "$2" b JOIN "$2_s" s

3654 ON b.id=s.id AND s.key='owner'

3655 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3656

3657 DROP TABLE IF EXISTS _children;

3658 CREATE TEMPORARY TABLE _children AS

3659 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3660 FROM (SELECT t.trowid, t.id $_i, a.id

3661 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3662 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3663

3664 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3665 DROP TABLE IF EXISTS _news;

3666 DROP VIEW IF EXISTS _news;

3667

3668 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3669 -- INSERT INTO _news

3670 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3671 CREATE TEMPORARY VIEW _news AS

3672 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3673 FROM (SELECT DISTINCT id FROM _target)

3674 a LEFT JOIN

3675 (SELECT $_i, count(ctime) newcnt

3676 FROM _children x

3677 WHERE ctime > coalesce((SELECT time from myacclog

3678 WHERE tblrowid=x.trowid),

3679 '1970-01-01')

3680 GROUP BY $_i) b

3681 ON a.id=b.$_i;

3682 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3683 EOF

3684 # REMOVE next line until 2019/5/1

3685 cntall="(select count($_i) from $_t where $_i=a.id)"

3686 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3687 # REMOVE next assignment until 2019/5/1

3688 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3689 and id in (select id from $_t where $_i=a.id) \

3690 and val > coalesce((select time from myacclog where \

3691 tblrowid=a.rowid),\

3692 '1970-01-01'))"

3693 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3694 cnt="$cntnew as '新着', $cntall as '総数',"

3695 dt_class=" td2r td3r dumpblogs"

3696 fi

3697 # Construct join expression

3698 eav="" scols=""

3699 pk=`gettblpkey $2`

3700 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3701 substr=${substr:-%s}

3702 for col in ${3:-`gettbl_s_cols $2`}; do

3703 valvar=val

3704 case $col in

3705 gecos) scols="$scols${scols:+, }${col#}"

3706 continue ;; # built-in column name

3707 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3708 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3709 case "$as" in

3710 *:*=*) cnd=${as#*:}

3711 h=${cnd%%=*} v=${cnd#*=}

3712 h=`sqlquotestr "$h"`

3713 v=`sqlquotestr "$v"`

3714 valvar="CASE val WHEN $h THEN $v END"

3715 as=${as%%:*} ;;

3716 esac

3717 ;;

3718 *) as=${col} ;;

3719 esac

3720 ss=`printf "$substr" "$valvar"`

3721 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3722 scols="$scols${scols:+, }b.$as"

3723 done

3724 #case author when '$user' then a.rowid else '---' end as ID,

3725 if [ -n "$DT_SQL" ]; then

3726 echo "$DT_SQL"

3727 else

3728 cat<<-EOF

3729 SELECT a.rowid as LINK, $cnt $scols

3730 FROM $2 a LEFT JOIN

3731 (SELECT $pk,$eav,

3732 max(CASE key

3733 WHEN 'owner'

3734 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3735 as gecos

3736 FROM ${2}_s c GROUP BY $pk)

3737 b ON a.$pk=b.$pk $4;

3738 EOF

3739 fi >> $sqlfile

3740 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3741 sqlog<<-EOF

3742 *** SQL-file: $sqlfile ***

3743 `cat $sqlfile`

3744 EOF

3745 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3746 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3747 cat $sqlfile >> tmp/sql

3748 # query ".read $sqlfile" > $tmpd/foo

3749 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3750 cp $tmpd/foo tmp/

3751 err "----- `gdate +%FT%T.%3N` ------------555555"

3752 ## $ddd LINE exists at the end of this function

3753 fi

3754 printf '.mode list\n.header 0\n' >> $sqlfile

3755 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3756 <div>

3757 <div class="dumptable">

3758 <table class="b$dt_class">

3759 `query ".read $sqlfile"`

3760 </table>

3761 </div>

3762 </div>

3763 EOF

3764 ### `query ".read $sqlfile"`

3765 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3766

3767 ## REMOVE THIS!

3768 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3769 }

3770

3771 clean-orphaned() {

3772 # This shoud be done by foreign_key rules, but some db lack them

3773 query<<-EOF

3774 -- Find blogs that have no parent

3775 WITH orphanedblog AS (

3776 SELECT blog.id,val FROM blog JOIN blog_s bs

3777 ON blog.id=bs.id AND key='owner'

3778 WHERE val NOT IN (SELECT gname FROM grp)

3779 AND val NOT IN (SELECT name FROM user);

3780 ) -- Remove them

3781 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3782

3783 -- Find articles that have no parent blog

3784 WITH orphanedarticle AS (

3785 SELECT id FROM article

3786 WHERE blogid NOT IN (SELECT id FROM blog);

3787 ) -- Remove them

3788 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3789 EOF

3790 }

3791 par2table() (

3792 # copy current parameters of par into destination table

3793 # $1=definition-file

3794 # Using $user and $session

3795 # Return value:

3796 # 0: Stored successfully

3797 # 1: Insufficient fillings

3798 # 2: No permission to modify the record

3799 # 3: Invalid rowid

3800 # 4: SUCCESS to delete

3801 # 5: Stop deletion for lack of confirm check

3802 # 6: Password length too short

3803 # 7: Password mismatch

3804 # 8: Old password incorrect

3805 # 9: Duplicated post

3806 rowid=`getpar rowid`

3807 if [ ! -e $1 ]; then

3808 echo "テーブル定義ファイルが見付かりません" | html p

3809 exit 1

3810 fi

3811 tbl=${1%.def}

3812 tbl=${tbl##*/}

3813 if [ -n "$rowid" ]; then # Modify existing entry

3814 if [ x"$tbl" = x"user" ]; then

3815 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3816 elif [ x"$tbl" = x"grp" ]; then

3817 sql="select gname from $tbl where rowid=$rowid;"

3818 ##err p2t:grp:q $sql

3819 isgrpowner "$user" "`query $sql`" && rowowner=$user

3820 elif [ x"$tbl" = x"blog" ]; then

3821 # Check if owner in blog_s

3822 blogowner=`getvalbyid blog owner "$rowid"`

3823 if isgrpowner "$user" "$blogowner"; then

3824 rowowner=$user

3825 else

3826 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3827 fi

3828 else

3829 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3830 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3831 fi

3832 ### err rowowner=$rowowner

3833 if [ x"$user" != x"$rowowner" ]; then

3834 echo "他人のレコードはいじれないの" | html p

3835 return 2

3836 elif [ -z "$rowowner" ]; then

3837 echo "指定したレコードはないみたい" | html p

3838 return 3

3839 fi

3840 rm=`getpar rm` cfm=`getpar confirm`

3841 # Editing existent entry

3842 if [ x"$rm" = x"yes" ]; then

3843 if [ x"$rm$cfm" = x"yesyes" ]; then

3844 query "delete from $tbl where rowid=$rowid;"

3845 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3846 clean-orphaned

3847 fi

3848 return 4

3849 else

3850 echo "消去確認のチェックがないので消さなかったの..." | html p

3851 return 5

3852 fi

3853 fi

3854 fi

3855

3856 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3857 if [ -n "$rowid" ]; then

3858 # Update of existing record

3859 for col in `gettblcols $tbl`; do

3860 val=`getparquote $col`

3861 [ -z "$val" ] && continue

3862 ## err query "update $tbl set $col=$val where rowid=$rowid"

3863 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3864 sql="update $tbl set $col=$val where rowid=$rowid;"

3865 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3866 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3867 ## User name cannot be changed with interface provided with this

3868 ## script. But we offer the trigger to change owner user

3869 ## of blog_s table.

3870 #err "select quote($col) from $tbl where rowid=$rowid;"

3871 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3872 cat<<-EOF | query

3873 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3874 -- should use EXCLUSIVE transaction outside of this.

3875 SAVEPOINT par2table;

3876 $sql

3877 update blog_s set val=$val

3878 where key='owner' and val=$old;

3879 RELEASE SAVEPOINT par2table;

3880 EOF

3881 ## XX: DIRTY Hack Ends here

3882 ## We should keep blog's owner as a single column which has

3883 ## foreign key constraint with primary key of grp/user.

3884 else

3885 query "$sql"

3886 fi

3887 done

3888 # Then, set up $pval for further insertion of tbl_s and tbl_m

3889 for col in `gettblpkey $tbl`; do

3890 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3891 pval="$pval${pval:+, }\"$val\""

3892 done

3893 else

3894 # New entry

3895 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3896 # Avoid empty repost of article.

3897 if [ x"$tbl" = x"article" ]; then

3898 # If rowid is empty and ID exists in article-table, that is REPOST!

3899 aid=`getpar id`

3900 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3901 if [ -n "$xaid" ]; then

3902 # REPOST of article

3903 html p <<-EOF

3904 書き込み直後のリロードなので上書きを回避します。

3905 最新記事は末尾の「再読み込み」ボタンから見てください。

3906 EOF

3907 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3908 return 9 # STOP Duplicated posting

3909 fi

3910 fi

3911 # Generate values() for primary keys

3912 for col in `gettblpkey $tbl`; do

3913 # Genuine primary keys for _m and _s

3914 val=`getvalquote $tbl $col`

3915 [ -z "$val" ] && continue

3916 pval="$pval${pval:+, }$val"

3917 done

3918 ##err pval=$pval

3919 for col in `gettblfkey $tbl`; do

3920 # args for values() to insertion into master table

3921 val=`getvalquote $tbl $col`

3922 [ -z "$val" ] && continue

3923 formaster=$formaster"${formaster:+, }$val"

3924 done

3925 formaster="$pval${formaster:+, }$formaster"

3926 ## err formaster=$formaster

3927 if [ -z "$formaster" ]; then

3928 echo "項目を全て埋めてください" | html pre

3929 return 1

3930 fi

3931 ## err "replace into $tbl values($formaster);"

3932 query "replace into $tbl values($formaster);"

3933 ## Insertion to master table, done

3934 fi

3935

3936 transaction=$tmpd/sqlfile.sql; touch $transaction

3937 for kt in s m; do

3938 tb2=${tbl}_$kt

3939 for col in `gettbl_${kt}_cols $tbl`; do

3940 ptype=`getpartype $col "limit 1"`

3941

3942 # First, check update of existing entries in _m

3943 if [ $kt = m ]; then

3944 # sessID|address.1.22|string|Somewhere-x.y.z

3945 sql=""

3946 ##err dots from query "select var from par where var like '$col.%';"

3947 for v in `query "select var from par where var like '$col.%';"`; do

3948 # v=address.1.22

3949 st_rowid=${v##*.}

3950 origcol=${v%%.*} # original column derived from

3951 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3952 ##case `getpartype $v` in

3953 ## err CASE `gettbl_coltype $tbl/$origcol` in

3954 ## err edit flag = `getpar action.$v`

3955 case `getpar action.$v` in

3956 rm)

3957 if [ x`getpar confirm.$v` = x"yes" ]; then

3958 newsql="delete from $tb2"

3959 else

3960 echo "削除確認未チェック" | html p

3961 fi ;;

3962 edit)

3963 case `gettbl_coltype $tbl/$origcol` in

3964 image|document|binary)

3965 file=$tmpd/`getparfilename $v`

3966 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3967 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3968 continue

3969 fi

3970 ## err type=file=$file

3971 [ -z "$file" ] && continue

3972 bn=`sqlquotestr "${file##*/}"`

3973 bin="X'"$(hexize "$file")"'"

3974 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3975 type=\"file:$ct\"

3976 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3977 cachedir=`getcachedir "$tbl/$rowid"`

3978 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3979 rm -rf $cachedir

3980 ;;

3981 *)

3982 newsql="update $tb2 set val=(select val from par where var \

3983 like '$col.%.$st_rowid')"

3984 ;;

3985 esac

3986 ;;

3987 *) # maybe "keep", do not modify value

3988 continue

3989 ;;

3990 esac

3991 # err newsql=$newsql

3992 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3993 done

3994

3995 if [ x"$bin" = x"NULL" ]; then

3996 ## err repl:normal sql=`echo $sql`

3997 if [ -n "$transaction" ]; then

3998 cat<<-EOF >> $transaction

3999 $sql

4000 DELETE FROM $tb2 WHERE type='string' AND val='';

4001 EOF

4002 else

4003 query "$sql

4004 delete from $tb2 where type='string' and val='';"

4005 ## err repl:normal done

4006 fi

4007 else

4008 # Binary update line is TOO LONG to pipelining

4009 sqlfile="$tmpd/sqlf.$$"

4010 if [ -n "$transaction" ]; then

4011 echo "$sql" >> $transaction

4012 else

4013 echo "$sql" > $sqlfile

4014 query ".read $sqlfile"

4015 fi

4016 fi

4017 # Rest of kt==m: set multiple mode

4018 nr=`getparcount $col`

4019 else

4020 nr=1 # for kt==s, number of records is 1

4021 fi

4022

4023 i=0

4024 while [ $i -lt $nr ]; do

4025 limit="limit 1 offset $i"

4026 i=$((i+1)) # increase beforehand against continue

4027 val=`getvalquote $tbl $col "$limit"`

4028 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4029 ## err $col=$val

4030 bin=NULL

4031 ## err partype$col=`getpartype $col "$limit"`

4032 case $ptype in

4033 file) file=$tmpd/`getparfilename $col "$limit"`

4034 ## err parfile-$col=$file

4035 [ -z "$file" ] && continue

4036 bin="X'"$(hexize "$file")"'"

4037 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4038 type=\"file:$ct\" ;;

4039 "*"*) continue ;; # foreign table

4040 *) type=\"string\" ;;

4041 esac

4042 case `gettbl_coltype $tbl/$col` in

4043 password) # special care for password

4044 # name={password,pswd1,pswd2}

4045 p1=`getpar pswd1 "$limit"`

4046 if [ -z "$p1" ]; then

4047 continue # SKIP password setting, if p1 is empty

4048 else

4049 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4050 ## err pswd=$pswd

4051 if pwcheck "$pswd"; then

4052 if [ x"$p1" = x"$p2" ]; then

4053 case "$p1" in

4054 ??????????*) ;;

4055 *) echo "パスワードは10字以上にしてください。" | html p

4056 return 6;;

4057 esac

4058 val="\"`echo $p1|mypwhash`\""

4059 else

4060 echo "2つの新パスワード不一致" | html p

4061 return 7

4062 fi

4063 else

4064 echo "旧パスワード違います" | html p

4065 return 8

4066 fi

4067 fi

4068 ;;

4069 esac

4070 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4071 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4072 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4073 if [ x"$bin" = x"NULL" ]; then

4074 ## err Normal-query: `echo $sql`

4075 if [ -n "$transaction" ]; then

4076 echo "$sql" >> $transaction

4077 else

4078 query "$sql"

4079 fi

4080 else

4081 sqlfile="$tmpd/query.$$"

4082 ## err sqlfile=`ls -lF $sqlfile`

4083 if [ -n "$transaction" ]; then

4084 echo "$sql" >> $transaction

4085 else

4086 echo "$sql" > $sqlfile

4087 query ".read $sqlfile"

4088 fi

4089 fi

4090 ## err p2t done

4091 done

4092 done

4093 done

4094 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4095 -- We cannot use transaction here, because groupupdate may use it.

4096 SAVEPOINT pa2table_insert;

4097 .read $transaction

4098 RELEASE SAVEPOINT pa2table_insert;

4099 EOF

4100 rc=$?

4101 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4102 ## err "Table:$tbl update done "

4103 return $rc

4104 )

4105 genform() {

4106 # $1 = form definition file

4107 # $2, $3 (optional)= table name and ROWID

4108 # If $GF_VIEWONLY set and nonNull, output values without form

4109 # If $GF_ARGS set, use it as content-strings in the form

4110 # If $GF_OWNER set, use it as value of name="owner"

4111 # If $GF_STAGE set, use it as value of name="stage"

4112 forms="" hiddens="" rowid=$3

4113 if [ ! -e "$1" ]; then

4114 echo "そのようなデータベースはないようです($2)。" | html p

4115 return

4116 elif [ -n "$2" ]; then

4117 rec=`query "select * from $2 where rowid='$rowid';"`

4118 if [ -z "$rec" ]; then

4119 pk=`gettblpkey $2`

4120 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4121 rec=`query "select rowid from $2 where $pk='$rowid';"`

4122 rowid=$rec

4123 rec=$3

4124 fi

4125 if [ -z "$rec" ]; then

4126 echo "そんなレコードはないみたいね..." | html p

4127 return

4128 fi

4129 fi

4130 if [ -z "$GF_VIEWONLY" ]; then

4131 rm='<input id="rm" name="rm" type="checkbox"

4132 value="yes"><label for="rm">このエントリの削除</label>

4133 <span>ほんとうに消しますよ(確認)!

4134 <input name="confirm" type=checkbox value="yes">はい</span>'

4135 fi

4136 # Image Cache dir

4137 ## err genform: getcache=$2/$rowid

4138 td=`getcachedir "$2/$rowid"`

4139 while IFS=: read prompt name keytype type args; do

4140 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4141 sp="${args:+ }"

4142 form="" val=""

4143 if [ -n "$rowid" ]; then

4144 # err genform2a: Seeking for "$2.$name, type=$type"

4145 rawval=`getvalbyid $2 $name $rowid $td`

4146 val=`echo "$rawval"|htmlescape`

4147 ## err genform3a: getvalbyid $2 $name $rowid $td

4148 ## err genform3b: val="[$val]" type="$type"

4149 fi

4150 if [ -n "$GF_VIEWONLY" ]; then

4151 is_hidden "$2" "$name" && continue

4152 fi

4153 case "$type" in

4154 text*)

4155 cgiform=cgi_multi_$type

4156 if [ -s $td/$name.count -a -n "$val" ]; then

4157 form=`$cgiform $name $td`

4158 val=$(echo "$val"|

4159 while read fn; do

4160 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4161 </td></tr>$nl"

4162 done)

4163 val="<table>$nl$val$nl</table>"

4164 else

4165 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4166 form=`cgi_$type $name "$rawval" "$args"`

4167 fi

4168 ;;

4169 [Rr][Aa][Dd][Ii][Oo])

4170 fh="<label><input type=\"radio\" name=\"$name\""

4171 form="`echo $args|sed -e \

4172 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4173 ;;

4174 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4175 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4176 ;;

4177 [Ss][Ee][Ll][Ee][Cc][Tt])

4178 fh="<select name=\"$name\">$nl"

4179 form=$(for l in $args; do

4180 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4181 done)

4182 if [ -n "$val" ]; then

4183 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4184 fi

4185 form="$fh$form</select>"

4186 ;;

4187 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4188 if [ -s $td/$name.count ]; then

4189 form=`cgi_multi_file $name $td "$args"`

4190 if [ -n "$val" ]; then

4191 hrfb="$myname?showattc+$2_m"

4192 val=$(echo "$rawval" \

4193 | while read fn; do

4194 data=`percenthex "$td/$fn"`

4195 #ct=`cat $td/$fn.content-type`

4196 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4197 ri=`cat "$td/$fn.rowid"`

4198 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4199 #imgsrc="<img src=\"data:$ct,$data\">"

4200 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4201 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4202 done)

4203 fi

4204 else

4205 form="<input type=\"file\" name=\"$name\" $args>"

4206 if [ -n "$val" ]; then

4207 imgs=$(echo "$rawval"\

4208 |while read fn;do

4209 data=`percenthex "$td/$fn"`

4210 echo "<img src=\"data:image/png,$data\">$fn<br>"

4211 done)

4212 form=$form"<br>$imgs"

4213 val=$imgs # 2015-06-15

4214 else

4215 form="<input type=\"file\" name=\"$name\" $args>"

4216 fi

4217 fi

4218 ;;

4219 [Hh][Ii][Dd][Dd][Ee][Nn])

4220 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4221 args="value=\"$GF_STAGE\""

4222 fi

4223 form="<input type=\"hidden\" name=\"$name\" $args>"

4224 prompt='' # Remove prompt

4225 ;;

4226 [Aa][Uu][Tt][Hh][Oo][Rr])

4227 [ -n "$GF_VIEWONLY" ] && continue

4228 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4229 prompt="" ;;

4230 [Oo][Ww][Nn][Ee][Rr])

4231 [ -n "$GF_VIEWONLY" ] && continue

4232 val=${GF_OWNER:-$val}

4233 val=${val:-$user}

4234 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4235 prompt="" ;;

4236 [Uu][Ss][Ee][Rr])

4237 # XXX: is null $user ok?

4238 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4239 [ -n "$GF_VIEWONLY" ] && continue

4240 form="$user"

4241 ;;

4242 [Pp]assword)

4243 [ -n "$GF_VIEWONLY" ] && continue

4244 form="`cgi_passwd`"

4245 val=""

4246 ;;

4247 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4248 [ -n "$GF_VIEWONLY" ] && continue

4249 if [ -z "$rowid" ]; then

4250 val=`genserial`

4251 fi

4252 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4253 prompt="" ;;

4254 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4255 prompt=""

4256 ;;

4257 parent|path|blog*)

4258 prompt=""

4259 ;;

4260 "*"*)

4261 tail=$tail"``"

4262 continue ;;

4263 esac

4264 if [ -n "$prompt" ]; then

4265 if [ -n "${GF_VIEWONLY}" ]; then

4266 form=$val

4267 else

4268 :

4269 fi

4270 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4271 else

4272 hiddens=$hiddens$nl"$form"

4273 fi

4274 done < $1

4275 # enctype="multipart/form-data"

4276 cat<<EOF

4277 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4278 ${rowid:+$rm}

4279 <table class="b $2">

4280 $forms

4281 </table>$hiddens

4282 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4283 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4284 EOF

4285 if [ -z $GF_VIEWONLY ]; then

4286 cat<<EOF

4287 <input type="submit" name="sub" value="OK">

4288 <input type="reset" name="res" value="Reset">

4289 EOF

4290 fi

4291 cat<<EOF

4292 $GF_ARGS</form>

4293 $tail

4294 EOF

4295 }

4296 edittable() {

4297 # $1=form-def $2=table $3 rowid

4298 genform "$@"

4299 }

4300 viewtable() {

4301 GF_VIEWONLY=1 genform "$@"

4302 }

4303 showattc() {

4304 # $1=table_m $2=rowid &optional $3=RawFlag

4305 ## err \$1=$1 \$2=$2 \$3=$3

4306 if ! isfilereadable $user $1 $2; then

4307 contenttype; echo

4308 echo "このファイルは管理者のみしか見られません" | html p

4309 putfooter; exit

4310 fi

4311 idir=`umask 002; mktempd` || exit 1

4312 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4313 bin=$idir/$myname-$$.bin

4314 sql="select quote(bin) from $1 where rowid='$2';"

4315 ## err showattc: sql: $sql

4316 sq $db "$sql" | unhexize > $bin

4317 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4318 type=${tv%//*} fn=${tv#*//}

4319 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4320 ct=${type#file:}

4321 case $ct in # all text/* changed to text/plain

4322 text/*)

4323 charset=`nkf -g $bin|cut -d' ' -f1`

4324 case $charset in

4325 ASCII*) charset="" ;;

4326 esac

4327 if [ -z "$3" ]; then

4328 ct="text/html${charset:+; charset=$charset}"

4329 link="?showattc+$1+$2+raw"

4330 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4331 | sed 's,^,<span></span>,' \

4332 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4333 -D_LINK_="$link" \

4334 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4335 exit $?

4336 fi

4337 ct="text/plain${charset:+; charset=$charset}"

4338 ;;

4339 esac

4340 contenttype "$ct"

4341 echo "Content-Disposition: filename=\"$fn\""

4342 echo "Content-Length: " `cat $bin | wc -c`; echo

4343 #echo "Content-Type: " ${type#file:}; echo

4344 cat $bin

4345 }

4346 #

4347 # Some default stupid handler on CGI values

4348 #

4349 default_storedb() {

4350 # ARG: $1=table-def-file

4351 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4352 tbl=`basename $1`

4353 tbl=${tbl%.def}

4354 cols="`grep :text $1|cut -d: -f2`"

4355 col=`echo "$cols"|head -1`

4356 vcol=`getpar $col`

4357 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4358 if [ -n "$vcol" ]; then

4359 par2table $1

4360 else

4361 return 2 # No insertion occurred

4362 fi

4363 }

4364

4365 default_view() { # $1=def-file

4366 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4367 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4368 default_storedb "$@"

4369 query "select rowid from $tbl order by rowid desc;" \

4370 | while read rowid; do

4371 viewtable $1 $tbl $rowid

4372 done | _m4 -D_TITLE_="$tbl" \

4373 -D_FORM_="`genform $1`" \

4374 -D_DUMPTABLE_="syscmd(cat)" \

4375 $layout/html.m4.html $layout/form+dump.m4.html

4376 }

4377 default_viewtext() { # $1=def-file

4378 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4379 default_storedb "$@"

4380 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4381 | _m4 -D_TITLE_="$tbl" \

4382 -D_FORM_="`genform $1`" \

4383 -D_DUMPTABLE_="syscmd(cat)" \

4384 $layout/html.m4.html $layout/form+dump.m4.html

4385 }

4386 default_smail() {

4387 default_storedb "$@"

4388 if [ $? -eq 2 ]; then

4389 _m4 -D_TITLE_="入力" \

4390 -D_FORM_="`genform $1`" \

4391 -D_DUMPTABLE_="" \

4392 $layout/html.m4.html $layout/form+dump.m4.html

4393 return

4394 fi

4395 cond=""

4396 for pk in `gettblpkey $tbl`; do

4397 pv=$(sqlquote "$(getpar $pk)")

4398 cond="$cond${cond:+ and }$pk=$pv"

4399 done

4400 sql="select rowid from $tbl where $cond;"

4401 rowid=`query "$sql"`

4402 ## err smail1 - "$sql" "-> rowid=$rowid"

4403

4404 while IFS=: read prompt name keytype type args; do # Read from $1

4405 val=`getpar $name`

4406 if [ -n "$val" ]; then

4407 text="$text

4408 $prompt

4409 $name=$val

4410 ---------------------------------------------------------"

4411 fi

4412 case "$type" in

4413 image|document|file)

4414 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4415 fns=$(echo "$fn"|while read fn; do

4416 err mv $tmpd/$fn.orig $tmpd/$fn

4417 mv $tmpd/$fn.orig $tmpd/$fn

4418 rm $tmpd/$fn.rowid # Remove cache flag

4419 ## err "`ls $tmpd/$fn`"

4420 echo $fn

4421 done)

4422 files="$files $fns"

4423 ;;

4424 esac

4425 done < $1

4426 ## err FILES=$files "`ls -lF $tmpd`"

4427 subj="from ${REMOTE_ADDR}"

4428 (echo "$url"

4429 echo "への書き込みがありました。"

4430 echo "------"

4431 echo "$text"

4432 ) | (cd $tmpd &&

4433 err LS="`ls -lF`" &&

4434 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4435 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4436 echo "以下の内容で送信しました。" | html p

4437 viewtable $1 $tbl \

4438 `query "select rowid from $tbl order by rowid desc limit 1;"`

4439 echo "戻る" | html a "href=\"?\""

4440 }