s4: 56adb6e5ee68 s4-funcs.sh

s4

view s4-funcs.sh @ 797:56adb6e5ee68

Add world-links to Grps entry of topmenu

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 14 Jun 2020 11:15:45 +0900
parents	8646ec4bd6fc
children	91016ff5edb3

line source

1 #!/bin/sh

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

3 # $HGid$

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

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

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

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

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

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, $S4WORLDGRPS

103 # Files created in s4-world.sh

104 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

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 ${S4WORLD:+-D_S4WORLD_="$S4WORLD"} \

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

466 ${S4WORLDGRPS:+-D_S4WORLDGRPS_="$S4WORLDGRPS"} \

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

468 }

469 ismember() {

470 # $1=user, $2=group

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

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

473 }

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

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

476 }

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

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

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

480 }

481 isgrpowner() (

482 # $1=user, $2=group

483 gn=`sqlquote "$2"`

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

485 err isgrpowner: $sql

486 test -n "`query $sql`"

487 )

488 isgrpownerbygid() (

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

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

491 err isgrpownerbygid: $sql

492 test -n "`query $sql`"

493 )

494 getgroupadminmails() {

495 # $1=group

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

497 email4group "$1" "$i" ;

498 done

499 }

500 getgroupadmins() { # $1=group

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

502 qgrp=`sqlquote "$1"`

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

504 }

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

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

507 getvalbyid grp $2 \

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

509 }

510 getgroupbyid() {

511 # $1=id|gname

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

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

514 # err ggbyid: `echo $sql`

515 query $sql

516 }

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

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

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

520

521 # Return true when anonymous mode

522 [ "$anonymousmode" ] && return 0

523 # case `getvalbyid blog mode $2` in

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

525 # *closed*)

526 # owner=`getvalbyid blog owner $2`

527 # if isgrp $owner; then

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

529 # elif isuser $owner; then

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

531 # fi

532 # esac

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

534 sql="with getblog as (

535 select key,val from blog_s where id=(

536 select blogid from article where id in

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

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

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

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

541 isgrpadm as (select user from grp_adm where

542 gname=(select val from getowner) and

543 user='$1'),

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

545 select case

546 when (select author from article where

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

548 then 'author'

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

550 then 'open'

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

552 then CASE

553 WHEN (SELECT val FROM isgrp) IS NULL

554 THEN

555 CASE WHEN (SELECT val from getowner)

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

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

558 ELSE ''

559 END

560 WHEN (select user from isgrpadm) IS NOT NULL

561 THEN 'i-am-admin'

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

563 END

564 when (select val from getmode) is null

565 then 'open'

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

567 then (SELECT user FROM isgrpadm)

568 when (select author from article where

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

570 then 'user+author'

571 else '' end;"

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

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

574 # result=`query "$sql"`

575 # err FileAccessibility=$result

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

577 }

578 linkhome() {

579 # $1=UserOrGroupRowid

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

581 if isuser $1; then

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

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

584 name=`gecos $1|htmlescape`

585 else

586 _grid=`numericalize "$1"`

587 echo -n "grp+$1"

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

589 fi

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

591 }

592 hreflink() {

593 # s4 specific notation:

594 # ^href=URL

595 # ^iframe=URL

596 # ^video=URL

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

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

599 # OSM umap Wikistyle Notation:

600 # [[URL]] - Simle Link

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

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

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

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

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

606 # Other Style

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

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

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

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

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

612 # SPC+SPC+$ - <br>

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

614 checkboxON="${cb} checked>"

615 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

640

641 }

642 minitbl() {

643 sed -n '

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

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

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

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

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

649 H; # Concat this line to HoldSpace

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

651 $ b cont

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

653 }

654 :cont

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

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

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

658 # . of ^. is workaround for FreeBSD sed

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

660 p; # Print whole "table" element

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

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

663 }

664 x; # Back to the newest line

665 p; # Print rest' | miniul

666 }

667 miniul() {

668 sed -e '

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

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

671 :top

672 s/\n//;

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

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

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

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

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

678 $ b cont

679 N; # 次の行を読む

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

681 /^ /b top

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

683 p; s/.*//;

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

685 /^\* /b top

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

687 }

688

689 :cont

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

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

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

693 H; x

694 }

695 x'

696 }

697 acclog() (

698 # $1=table, $2=rowid

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

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

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

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

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

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

705 fi

706 )

707 gecos() (

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

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

710 )

711 setpar() {

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

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

714 case "$1" in

715 user) _user="$v" ;;

716 skey) _skey="$v" ;;

717 esac

718 fi

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

720 }

721 unsetpar() {

722 for i; do

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

724 case "$i" in

725 user|skey) unset _$i ;;

726 esac

727 fi

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

729 done

730 }

731 replpar() {

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

733 }

734 getpar() {

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

736 val=""

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

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

739 user) val=$_user ;;

740 skey) val=$_skey ;;

741 esac

742 fi

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

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

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

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

747 fi

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

749 case "$var" in

750 owner)

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

752 echo $user; return

753 elif ismember $user $val; then

754 echo $val; return

755 fi ;;

756 esac

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

758 echo "$val"

759 }

760 setskey() {

761 # For quick response...(?)

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

763 }

764 chkskey() {

765 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

771 return 0

772 fi

773 return 1

774 }

775 resetskey() {

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

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

778 fi

779 }

780 getpartype() {

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

782 }

783 getparcount() {

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

785 }

786 getparfilename() {

787 # null if type of $1 is not file

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

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

790 }

791 sqlquote() {

792 (v="$1"

793 case "$v" in

794 "") return ;; # null

795 "X'"*) # quoted hex string

796 echo $1 ;;

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

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

799 echo "\"$v\""

800 return ;;

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

802 echo "\"$v\""

803 return ;;

804 *)

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

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

807 else

808 echo "\"$v\""

809 fi ;;

810 esac)

811 }

812 sqlquotestr() (

813 case "$1" in

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

815 echo "'$v'" ;;

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

817 esac

818 )

819 mktempd() {

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

821 }

822 getcachedir() { # $1=maintable

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

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

825 else

826 echo $tmpd/$thumbxy

827 fi

828 }

829 getval() {

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

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

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

833 echo "$user" ;;

834 stamp|datetime)

835 date "+%F %T" ;;

836 serial)

837 (s=`getpar $2`

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

839 *)

840 getpar "$2" "$3";;

841 esac

842 }

843

844 getvalquote() {

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

846 (v=`getval "$@"`

847 case "$v" in

848 "") echo NULL ;;

849 *) sqlquote "$v" ;;

850 esac)

851 }

852 getparquote() {

853 sqlquote "`getpar $1`"

854 }

855 getbinbyid() {

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

857

858 }

859 getvalbyid() {

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

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

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

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

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

865

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

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

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

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

870 return

871 fi

872 done

873 rowid=$3

874 pk=`gettblpkey $1`

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

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

877 td=${4:-$tmpd}

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

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

880 for kt in s m; do

881 t=${1}_$kt

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

883 vcount=1 # count(val)

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

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

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

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

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

889 if [ $kt = m ]; then

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

891 # Reset val to store filenames if type is string

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

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

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

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

896 i=0

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

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

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

900 i=$((i+1))

901 fn=$c.$i

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

903 case $type in

904 file:*)

905 #file=$td/$val

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

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

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

909 # FOR SPEED: Skip file generation if imgcache exists

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

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

912 && continue

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

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

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

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

917 select rowid from $cond $slice;

918 .output '$td/$fn'

919 select val from $cond $slice;

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

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

922 .output stdout

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

924 EOF

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

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

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

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

929 ls -lh "$file" |

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

931 case "$type" in

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

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

934 *|*:[Aa]pplication*)

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

936 png:- > "$file"

937 ;;

938 esac

939 ;;

940 *)

941 sq $db<<EOF

942 .output $td/$fn.rowid

943 select rowid from $cond $slice;

944 .output $td/$fn

945 select val from $cond $slice;

946 EOF

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

948 ;;

949 esac

950 done

951 else

952 rm -f $td/$c.count

953 case $type in

954 file:*)

955 echo "$val" \

956 | while read fn; do

957 file=$td/$fn

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

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

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

961 | unhexize > "$file"

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

963 case $type in

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

965 *:[Aa]pplication*)

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

967 png:- > $file ;;

968 esac

969 fi

970 done

971 ;;

972 esac

973 fi

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

975 return

976 fi

977 done

978 done)

979 }

980 getvalbypkey() (

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

982 pk=`gettblpkey $1`

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

984 getvalbyid "$1" "$2" $rowid $4

985 )

986 getvalbycond() {

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

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

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

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

991 getvalbyid "$1" "$2" $rowid "$4"

992 fi

993 }

994 getpwfield() {

995 # getpwfield user column

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

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

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

999 echo "$val"

1000 return 0

1001 else

1002 return 1

1003 fi

1004 }

1005 numericalize() {

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

1007 }

1008 encode() {

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

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

1011 sha1=sha1

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

1013 sha1=sha1sum

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

1015 sha1=gsha1sum

1016 fi

1017 fi

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

1019 }

1020 enjpeg() {

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

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

1023 cjpeg="cjpeg"

1024 else

1025 cjpeg="convert - jpeg:-"

1026 fi

1027 fi

1028 $cjpeg "$@"

1029 }

1030 mycrypt() (

1031 key=$1 salt=$2

1032 # err \$2=$2

1033 case $2 in

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

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

1036 esac

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

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

1039 )

1040 hexize() {

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

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

1043 hexize="xxd -p"

1044 else

1045 hexize_hd() {

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

1047 }

1048 hexize="hexize_hd"

1049 fi

1050 fi

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

1052 }

1053 unhexize() {

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

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

1056 unhex="xxd -p -r"

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

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

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

1060 EOF

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

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

1063 # world writable...:)

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

1065 fi

1066 fi

1067 cat "$@" | $unhex

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

1069 }

1070 percenthex() {

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

1072 }

1073 htmlescape() {

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

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

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

1077 }

1078 enascii() {

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

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

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

1082 else

1083 enascii_now=`date +%FT%T`

1084 enascii_sed() {

1085 nkf -Z0Z1Z2 \

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

1087 }

1088 enascii="enascii_sed"

1089 fi

1090 fi

1091 cat "$@" | $enascii

1092 }

1093 size_h() {

1094 i="$1" oi=$1

1095 set -- B B KB MB GB TB

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

1097 oi=$i

1098 i=$((i/1024))

1099 shift

1100 done

1101 echo ${oi}$1

1102 }

1103 gettblconf() {

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

1105 ## tconfs=`sq $db \

1106 tconfs=`query \

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

1108 fi

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

1110 }

1111 gettblkeys() {

1112 # $1=tbl

1113 gettblconf

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

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

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

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

1118 case $type in

1119 *p*)

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

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

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

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

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

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

1126 esac

1127 case $type in

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

1129 esac

1130 done

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

1132 }

1133 gettblpkey() {

1134 # $1=tbl

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

1136 }

1137 gettblfkey() {

1138 (x=`gettblkeys $1`

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

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

1141 }

1142 gettblcols() {

1143 (x=`gettblkeys $1`

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

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

1146 }

1147 gettbl_s_cols() {

1148 (x=`gettblkeys $1`

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

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

1151 }

1152 gettbl_m_cols() {

1153 (x=`gettblkeys $1`

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

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

1156 }

1157 gettbl_h_cols() {

1158 (x=`gettblkeys $1`

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

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

1161 }

1162 gettbl_coltype() (

1163 gettblconf

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

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

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

1167 )

1168 is_hidden() {

1169 # $1=Tbl $2=col

1170 gettblconf

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

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

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

1174 case $x in

1175 *h*) return 0 ;;

1176 *) return 1 ;;

1177 esac

1178 }

1179

1180 dbsetbyid() {

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

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

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

1184 quotedp=$(sqlquotestr "$p")

1185 unset primary update

1186 gettblconf

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

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

1189 #err conf=$conf

1190 case ${conf#*=} in

1191 p*) primary=1 ;;

1192 f*) update=1 ;;

1193 u*) ;;

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

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

1196 esac

1197 #err t=$t

1198 type=string fn=""

1199 case $conf in

1200 */password)

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

1202 ;;

1203 */image*|*/document*)

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

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

1206 ;;

1207 esac

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

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

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

1211 if [ "$primary" ]; then

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

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

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

1215 elif [ "$update" ]; then

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

1217 else

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

1219 fi

1220 )

1221 }

1222 expire() (

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

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

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

1226 )

1227 addsession() {

1228 # expireをセット

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

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

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

1232 # Remove old session parameters

1233 now=`expire now`

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

1235 }

1236 gencookie() (

1237 path=${URL#*:/}

1238 path=${URL%/*}

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

1240 for kv; do

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

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

1243 done

1244 )

1245 contenttype() {

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

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

1248 }

1249 putheader() {

1250

1251 }

1252 putfooter() {

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

1254 }

1255 getcookie() {

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

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

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

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

1260 case "$k" in

1261 user) _user="$v" ;;

1262 skey) _skey="$v" ;;

1263 esac

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

1265 done

1266 }

1267 genrandom() {

1268 # $1=columns (default: 10)

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

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

1271 }

1272 genserial() {

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

1274 }

1275 smail() {

1276 # smail rcpts subj (file)

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

1278 # $MAIL_FROM <- From: header value

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

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

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

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

1283 sender=${SENDER:-$admin}

1284 # Do not call m4 with directly passing text

1285 _r=$tmpd/rcpt

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

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

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

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

1290 }

1291 smail_queue_flush() {

1292 # $1=timelimit

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

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

1295 SELECT rowid FROM smailq

1296 GROUP BY rcpts, subj

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

1298 EOF

1299 )

1300 for rid in $rowids; do

1301 sq $workdb \

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

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

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

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

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

1307

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

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

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

1311 .separator "\n" "------------------\n\n"

1312 SELECT time, text FROM smailq

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

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

1315 ORDER by time;

1316 EOF

1317 then

1318 cat <<-EOF | sq $workdb

1319 DELETE FROM smailq

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

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

1322 EOF

1323 fi

1324 fi

1325 done

1326 }

1327 smail_queue() {

1328 # $1=Rcpts, $@=subj

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

1330 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1339 EOF

1340 )

1341 ## XXX: Adhoc load mitigation

1342 case "$now" in

1343 *[01])

1344 err flush_queue=$mintime

1345 smail_queue_flush ;;

1346 esac

1347 fi

1348 }

1349 setviastring() {

1350 table=par

1351 oifs="$IFS"

1352 IFS="&"

1353 for us in $1; do

1354 k=${us%%=*}

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

1356 setpar "$k" "string" "$v"

1357 done

1358 IFS="$oifs"

1359 }

1360 checkdomain() (

1361 # Check the validity of domain by referring DNS

1362 item=$1

1363 err checkdomain $1

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

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

1366 )

1367 pwcheck() {

1368 # $1=passwd

1369 dbpswd=`getpwfield $user pswd`

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

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

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

1373 }

1374 mypwhash() {

1375 mycrypt "`cat`" `genrandom 5`

1376 }

1377 wasureta() {

1378 user=$1

1379 if ! checkdomain $user; then

1380 contenttype; echo

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

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

1383 putfooter

1384 exit 0

1385 fi

1386 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1388 encpswd=`echo $newpswd|mypwhash`

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

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

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

1392 $msgdir/mail-newaccount.m4 \

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

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

1395 }

1396 checkauth() {

1397 user=`getpar user`

1398 skc=`getpar skey` # from cookie

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

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

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

1402 if chkskey "$skc"; then

1403 return 0

1404 fi

1405 fi

1406 pswd=`getpar pswd`

1407 quser=`sqlquotestr "$user"`

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

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

1410 return 4 # 4=server too heavy

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

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

1413 return 2 # 2=login fail

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

1415 wasureta "$user"

1416 return 1 # wasureta error

1417 fi

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

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

1420 if pwcheck "$pswd"; then

1421 newsession=`genrandom 34`

1422 if setskey "$user" "$newsession" &&

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

1424 gencookie "user=$user" "skey=$newsession"

1425 return 0

1426 else

1427 return 4 # Heavy load??

1428 fi

1429 fi

1430 err "Login failed: [$user]"

1431 return 2 # Password mismatch

1432 }

1433 showlogin() {

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

1435 test -z "$args" && resetskey

1436 s4name=${S4NAME:-s4}

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

1438 cat $layout/login.m4.html

1439 echo '</body></html>'

1440 ) | _m4 \

1441 -D_BODYCLASS_="login" \

1442 -D_TITLE_="$s4name" \

1443 -D_SYSNAME_="Welcome to $s4name" \

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

1445 exit 0

1446 }

1447 dologin() {

1448 checkauth

1449 st=$?

1450 if [ $st != 0 ]; then

1451 contenttype; echo

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

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

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

1455 showlogin # and EXIT

1456 fi

1457 }

1458

1459 # Do instant jobs here

1460 dbsetup

1461 trap cleanup INT HUP EXIT TERM PIPE

1462 # trap cleanup INT HUP

1463

1464

1465 cgiinit() {

1466 tmpd=`tmpd=$tmpdir mktempd`

1467 tmpf=$tmpd/stream.$$

1468 tmpfiles=$tmpfiles" $tmpd"

1469 addsession $session

1470 getcookie

1471 case "$REQUEST_METHOD" in

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

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

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

1475 # cat > $tmpf # too much?

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

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

1478 s="`cat $tmpf`"

1479 ;;

1480 esac

1481 case "$CONTENT_TYPE" in

1482 *boundary*)

1483 bndry=${CONTENT_TYPE#*boundary=}

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

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

1486 do

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

1488 #echo u=$us

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

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

1491 # err k=$k v=$v

1492 case "$k" in

1493 *:filename)

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

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

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

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

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

1499 :

1500 fi

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

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

1503 case "$mimetype" in

1504 [Ii]mage/x-xcf)

1505 bzip2 "$tmpd/$v"

1506 v=${v}.bz2

1507 ;;

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

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

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

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

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

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

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

1515 v=$vjpg

1516 else

1517 mimetype="Not supported"

1518 fi

1519 ;;

1520 [Ii]mage/*)

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

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

1523 ;;

1524 esac

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

1526 then

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

1528 continue

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

1530 replpar text string \

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

1532 continue

1533 fi

1534 ;;

1535 *)

1536 type='string'

1537 ;;

1538 esac

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

1540 setpar "$k" "$type" "$v"

1541 done

1542 ;;

1543 *)

1544 setviastring "$s"

1545 ;;

1546 esac

1547 }

1548 email4group() {

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

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

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

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

1553 sql="WITH

1554 grpemails AS (

1555 SELECT gname, user, val email

1556 FROM grp_mem NATURAL JOIN grp_mem_s

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

1558 useremails AS (

1559 SELECT user.name, val email

1560 FROM user

1561 LEFT JOIN user_m

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

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

1564 FROM useremails u LEFT JOIN grpemails g

1565 ON u.name=g.user

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

1567 query "$sql"

1568 }

1569 email4groupbyuid() {

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

1571 g=$1; shift

1572 uids=`echo "$@"`

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

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

1575 email4group "$g" `query "$sql"`

1576 }

1577 myemail4group() {

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

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

1580 }

1581 collectmembersbyid() {

1582 # Collect user names of group specified by grid

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

1584 query "SELECT user FROM grp_mem \

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

1586 }

1587 collectmembersbyid() {

1588 # Collect user names of group name

1589 qgrp=`sqlquote "$1"`

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

1591 }

1592 collectgecosesbyid() {

1593 # Collect user gecoses of group

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

1595 query<<-EOF

1596 SELECT gecos

1597 FROM gecoses

1598 WHERE name IN (SELECT user FROM grp_mem

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

1600 EOF

1601 }

1602 collectemail() (

1603 # Collect email addresses for group $1

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

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

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

1607 for e; do

1608 if isuser "$e"; then

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

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

1611 else

1612 qgrp=`sqlquote "$e"`

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

1614 gmem="grp_mem"

1615 else

1616 tm=`sqlquote "$TEAM"`

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

1618 fi

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

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

1621 $gmem g left join grp_mem_s s

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

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

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

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

1626 query "$sql"

1627 fi

1628 done

1629 )

1630 sendinvitation() (

1631 # $1=email

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

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

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

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

1636 gecos=`gecos`

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

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

1639 _m4 -D_URL_="$urlbase" \

1640 -D_USER_="$name" \

1641 -D_EMAIL_="$1" \

1642 -D_REGIST_="$regist" \

1643 -D_ADMIN_="$admin" \

1644 $msgdir/mail-invite.m4 \

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

1646 return 0

1647 )

1648 emaildomaincheck() {

1649 case "$1" in

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

1651 *@*)

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

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

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

1655 return 2

1656 fi

1657 return 0

1658 ;;

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

1660 esac

1661 }

1662 invite() {

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

1664 case "$email" in

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

1666 *@*)

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

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

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

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

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

1672 elif sendinvitation $email; then

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

1674 else # Cannot be reached here

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

1676 fi ;;

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

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

1679 esac

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

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

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

1683 fi

1684 if [ -f $invite_policy ]; then

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

1686 else

1687 pol="$invite_policy"

1688 fi

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

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

1691 -D_INVITE_POLICY_="$pol" \

1692 $layout/html.m4.html $layout/invite.m4.html

1693 }

1694 regist() {

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

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

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

1698 echo "bye bye" | html p

1699 reutrn

1700 fi

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

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

1703 cat<<EOF

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

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

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

1707 EOF

1708 return

1709 fi

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

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

1712 # Fake login password to wasureta

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

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

1715 wasureta $email

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

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

1718 addsession $1 # for removal after 1 minute

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

1720 $layout/login.m4.html

1721 return

1722 }

1723 group_safename() {

1724 # Convert $1 to safe group name

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

1726 }

1727 groupupdate() {

1728 gname=`getpar gname`

1729 qgname=`sqlquote "$gname"`

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

1731 # See ALSO same job in showgroup()

1732 newgname=`group_safename "$gname"`

1733 err newgname=$newgname

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

1735 err NewGNAME: gname=$newgname

1736 gname=$newgname

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

1738 replpar gname string "$gname"

1739 fi

1740 # Name confliction check

1741 parow=`getpar rowid`

1742 ## err parow=$parow

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

1744 query "BEGIN EXCLUSIVE;"

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

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

1747 if [ $count -gt 0 ]; then

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

1749 query "END;"

1750 return

1751 fi

1752 par2table $formdir/grp.def

1753 query "END TRANSACTION;"

1754 # Remove orphan

1755 : <<EOF

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

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

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

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

1760 EOF

1761 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1765 sql="delete from blog where id in

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

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

1768 query "$sql";

1769 unsetpar tag kwd

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

1771 return # and return

1772 fi

1773 fi

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

1775 fi

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

1777 grid=$(query $sql)

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

1779 grp $grid

1780 }

1781 groupclone() {

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

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

1784 case "$1" in

1785 */noteam)

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

1787 esac

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

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

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

1791 return

1792 fi

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

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

1795 return

1796 fi

1797 i=0

1798 while true; do

1799 copy="-copy$i"

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

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

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

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

1804 i=$((i++))

1805 continue

1806 fi

1807 break

1808 done

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

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

1811 query<<-EOF

1812 BEGIN;

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

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

1815 SELECT $newqname, key, val

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

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

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

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

1820 -- Copy members and their configuration --

1821 REPLACE INTO grp_mem SELECT $newqname, user

1822 FROM grp_mem WHERE gname=$qgrp;

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

1824 FROM grp_mem_s WHERE gname=$qgrp;

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

1826 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1827 -- Copy administrators --

1828 REPLACE INTO grp_adm SELECT $newqname, user

1829 FROM grp_adm WHERE gname=$qgrp;

1830 COMMIT;

1831 EOF

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

1833 STOPCLONEMSG=1 groupconf "$newrowid"

1834 }

1835 groupman() {

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

1837

1838 GF_STAGE="grpconf"

1839 GF_STAGE=groupupdate

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

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

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

1843 -D_DUMPTABLE_="syscmd(cat)" \

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

1845 }

1846 userconf() {

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

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

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

1850 }

1851 groupconf() {

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

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

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

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

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

1857

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

1859 grp=`getgroupbyid $rowid`

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

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

1862 return

1863 fi

1864

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

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

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

1868 ## Setup migration menu

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

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

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

1872 err v=$v

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

1874 migrate=$(cat<<-EOF

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

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

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

1878 <form action="?migrategrp">

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

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

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

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

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

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

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

1886 `cgi_hidden stage migrategrp`

1887 `cgi_hidden rowid $rowid`

1888 `cgi_submit OK`

1889 `cgi_reset Reset`

1890 </form>

1891 </div>

1892 EOF

1893 )

1894 fi

1895 fi

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

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

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

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

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

1901 <table>

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

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

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

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

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

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

1908 </table>

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

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

1911 </div>

1912 $migrate

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

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

1915 EOF

1916 fi

1917 }

1918 migrategrp() {

1919 rowid=`getpar rowid`

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

1921 grp=`getgroupbyid $rowid`

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

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

1924 return

1925 fi

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

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

1928 grp "$rowid"

1929 return

1930 fi

1931 destconf=`getpar migrateto`

1932 err destconf=$destconf

1933 if [ ! -e $destconf ]; then

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

1935 grp "$rowid"

1936 return

1937 fi

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

1939 srcconf=$worldconf

1940 else

1941 srcconf=s4-config.sh

1942 fi

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

1944 echo "移住操作" | html h1

1945 echo '<pre>'

1946 set -- "$srcconf" "$destconf" "$rowid"

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

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

1949 rc=$?

1950 echo "</pre>"

1951 if [ $rc -eq 0 ]; then

1952 echo "World [$world] への移住完了。" | html p

1953 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

1954 else

1955 echo "移住失敗" | html p

1956 echo "移動先に重複がないか確認して下さい。" | html p

1957 fi

1958 return

1959 }

1960 mems() {

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

1962 kwd=`getpar kwd`

1963 listmember $kwd

1964 }

1965 grps() {

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

1967 kwd=`getpar kwd`

1968 listgroup $kwd \

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

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

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

1972 $layout/form+dump.m4.html

1973 }

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

1975 gpg=`getpar grp`

1976 grid=${1:-$gpg}

1977 grp=`getgroupbyid "$grid"`

1978 ## . ./s4-blog.sh

1979 jg=`getpar joingrp`

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

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

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

1983 fi

1984 htmlheader=$layout/html.m4.html

1985 showgroup "$grid"

1986 }

1987 sql4interestblogs() {

1988 cat<<EOF

1989 CREATE TEMPORARY VIEW interestblogs AS

1990 SELECT blog.rowid rid, id, author

1991 FROM blog

1992 NATURAL JOIN

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

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

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

1996 WHEN (SELECT user FROM grp_mem

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

1998 THEN 0

1999 ELSE 1

2000 END;

2001 EOF

2002 }

2003 listnewblogsql() { # $1=user

2004 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2005 newdays=${newdays%%[!0-9]*}

2006 newdays=${newdays:-14}

2007 basetime="datetime('now', 'localtime', '-${newdays} days')"

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

2009 WHERE user='$user'

2010 AND tblrowid IN

2011 ($blogreadflagrowid,

2012 $blogcutoffflagrowid)),

2013 $basetime -- "0"

2014 );"`

2015 cat<<EOF

2016 `sql4interestblogs`

2017 WITH article_ctime as (

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

2019 FROM article join article_s s using(id)

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

2021 GROUP BY id

2022 ), blog_title_owner as (

2023 SELECT blg.rid brid, id,

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

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

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

2027 ), blogall as (

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

2029 ), news as (

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

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

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

2033 FROM blogall bl

2034 LEFT JOIN

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

2036 ON bl.brid=al.tblrowid

2037 WHERE atime < bl.ctime

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

2039 LIMIT 10

2040 ) SELECT brid LINK, "新着",

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

2042 ctime, title,

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

2044 FROM news;

2045 EOF

2046 }

2047

2048 search_form() {

2049 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

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

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

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

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

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

2055 @week → 最近一週間

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

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

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

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

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

2061 auth=""

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

2063 case "$1" in

2064 author=*)

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

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

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

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

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

2070 ;;

2071 grid=*)

2072 a=`echo "${1#grid=}"`; a=$((0 + $a))

2073 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

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

2075 ;;

2076 esac

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

2078 cat<<-EOF

2079 <div class="right">

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

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

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

2083

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

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

2086

2087 </form>

2088 </div>

2089 EOF

2090 }

2091

2092 imgsrc_cache() (

2093 # $1 = directory for cache'ing

2094 # $2 = table (user_m or grp_m)

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

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

2097 dir="$1" tbl="$2"

2098 keycond="$3"

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

2100 ORDER BY rowid DESC LIMIT 1"

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

2102 tmpf=$tmpd/imgsrc_cache.$$

2103 case "$4" in

2104 [Ss]) size=S ;;

2105 [Oo]) size=O ;;

2106 *) size=M ;;

2107 esac

2108 # ImageCache filename storing schema:

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

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

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

2112 valtype=`query "$sql0"`

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

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

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

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

2117 case "$filetype" in

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

2119 esac

2120 fi

2121 cacheimg_S=$dir/S_$filename

2122 cacheimg_M=$dir/M_$filename

2123 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2131 else

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

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

2134 fifo2=$fifo.2

2135 mkfifo $fifo $fifo2

2136 fmt=${filename##*.}

2137 ## [[ NOTE ]]

2138 ## a. convert oldimage newimage

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

2140 ## b is much smaller than a

2141 cat $tmpf | unhexize \

2142 | tee $fifo \

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

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

2145 | tee $fifo2 \

2146 | convert - "$cacheimg_M" &

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

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

2149 | convert - "$cacheimg_S" &

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

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

2152 echo '">'

2153 echo "$sum" > $sumfile

2154 fi

2155 ## Now preparing cache image, done.

2156 ## Store this information to DB

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

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

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

2160 query <<-EOF

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

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

2163 EOF

2164 )

2165

2166 showhome() {

2167 # $1=userRowIdToShow

2168 err showhome \$1=$1

2169 case "$1" in

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

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

2172 esac

2173 ## err ShowHome: uname=$uname

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

2175 gecos=`gecos "$uname"`

2176 ## err SH:gecos=$gecos

2177 GF_VIEWONLY=1

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

2179 search_form_args=""

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

2181 if [ -z "$S4MASTERDB" ]; then

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

2183 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2184 elif [ -n "$S4MASTERURL" ]; then

2185 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2186 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2187 fi

2188 usermenu="$usermenu

2189 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2190 # Display folders

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

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

2193 and type like 'file:%';"

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

2195 nfile=`query "$sql"`

2196 # err nfile=$nfile

2197 if [ $nfile -gt 0 ]; then

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

2199 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2200 fi

2201 else

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

2203 GROUP BY user;"`

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

2205 search_form_args="author=$uname"

2206 fi

2207 . ./s4-blog.sh

2208

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

2210 search_form "$search_form_args" > $sf

2211 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2212 | htmlescape > $tf

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

2214 viewtable $formdir/user.def user $1

2215 echo "</div>"

2216 } > $pf

2217

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

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

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

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

2222 if true; then

2223 tbl=user_m

2224 enticond="name='$uname'"

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

2226 else

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

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

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

2230 echo '">'

2231 }

2232 fi > $imf

2233 fi

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

2235 val='$uname';"`

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

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

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

2239 listblog $uname > $bf

2240 ## unset ddd

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

2242

2243 hometail=$tmpd/tail.$$

2244 mkfifo $hometail

2245

2246 #Calling listgroupbytable, originally here

2247

2248 (

2249 # Display Most Recent Entry

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

2251 shortval=${shortval:-val}

2252

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

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

2255 ctime,

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

2257 (SELECT gecos FROM gecoses

2258 WHERE name=(SELECT val FROM blog_s

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

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

2261 FROM blog b

2262 JOIN

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

2264 FROM article a, article_s s

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

2266 GROUP BY blogid ORDER BY val DESC LIMIT 50

2267 ) m

2268 ON b.id=m.blogid;"

2269 # This should be as follows

2270 : <<EOF

2271 WITH arts AS(

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

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

2274 FROM article a NATURAL JOIN article_s s

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

2276 GROUP by s.id

2277 )

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

2279 FROM arts a0

2280 JOIN

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

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

2283 ORDER BY ctime DESC LIMIT 50;

2284 EOF

2285

2286 cat<<-EOF

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

2288 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2289 <div class="lcto">

2290 `DT_VIEW=replyblog dumptable html blog`

2291 </div>

2292 EOF

2293 unset DT_SQL

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

2295 # Display NEWS

2296 # 2016-06-26

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

2298 acclog blog $blogreadflagrowid

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

2300 fi

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

2302 sql=`listnewblogsql "$user"`

2303 # echo "$sql" > tmp/listnew

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

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

2306 cont=$((cont-1))

2307 err newcount=$cont

2308 if [ $cont -gt 0 ]; then

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

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

2311 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

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

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

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

2315 `cgi_submit '確定'`

2316 `cgi_hidden read yes`

2317 EOF

2318 echo "$new10 "

2319 echo "</div>"

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

2321 acclog blog $blogcutoffflagrowid # for speed

2322 fi

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

2324 : # DT_SQL=

2325 fi

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

2327 #

2328 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2334 -D_NBLOG_="$nblog" \

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

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

2337 $layout/html.m4.html $layout/home.m4.html

2338

2339 # Record access log

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

2341 }

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

2343 contenttype; echo

2344 ## err commission: "$@"

2345 gname=`getgroupbyid $1`

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

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

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

2349 grp_reg_adm "$@"

2350 else

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

2352 fi

2353 }

2354 listgroupbytable() {

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

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

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

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

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

2360 NGsql="select distinct tag from\

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

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

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

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

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

2366 for tag in `query "$sql"`

2367 do

2368 ## err ListGrp: tag=$tag

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

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

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

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

2373 as rowid,

2374 gname,

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

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

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

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

2379 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2384 echo '</table>'

2385 done

2386 echo '</div>'

2387 }

2388 iconhref() (

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

2390 data=`percenthex "$1"`

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

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

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

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

2395 )

2396 iconhref2() (

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

2398 src=$1

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

2400 anchor=`echo $@|htmlescape`

2401 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2402 )

2403 listentry() (

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

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

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

2407 cond='' hiddens=''

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

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

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

2411 offset=$((offset-1))

2412 fi

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

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

2415 limit=$listentlimit

2416 dir=`getcachedir "$1"`

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

2418 hrb="$myname?home"

2419 deficon=person-default.png

2420 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2421 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2422 gcs=gecos

2423 else # if group

2424 hrb="$myname?grp"

2425 deficon=group-default.png

2426 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2427 gcs=name

2428 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2429 if [ -n "$tagline" ]; then

2430 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2431 ## err tagconv=$tagconv

2432 fi

2433 fi

2434 if [ ! -d $dir ]; then

2435 mkdir -p $dir

2436 fi

2437 if [ ! -s $dir/$deficon ]; then

2438 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2439 fi

2440 if [ -n "$2" ]; then

2441 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2442 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2443 fi

2444 tag=`getpar tag` tag2=`getpar tag2`

2445 if [ x"$tag" = x"NULL" ]; then

2446 tag="" tag2=""

2447 fi

2448 if [ -n "$tag$tag2" ]; then

2449 tag=${tag:-$tag2}

2450 qtag=`sqlquote "$tag"`

2451 cond2="tag=$qtag"

2452 fi

2453 if [ -n "$cond1$cond2" ]; then

2454 cond="$cond1${cond2:+ AND $cond2}"

2455 cond="WHERE ${cond# AND }"

2456 fi

2457

2458 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2459 # grpは呼出し元の動的スコープ変数でよくないな...

2460 ##qgrp=`sqlquote $grp`

2461 getgrp="(select gname from grp where rowid=${rowid:--1})"

2462 sql="select a.rowid, a.$link,

2463 coalesce(b.$gcs, a.$nm) as nick,

2464 quote(a.$nm) as qname,

2465 (SELECT val FROM ${tbl}_s

2466 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2467 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2468 || case when a.$nm in (select gname from grp)

2469 then printf('(%d名)',

2470 (select count(user) from grp_mem where gname=a.$nm))

2471 else ' <'||a.$nm||'>'

2472 end

2473 as name,

2474 b.tag,

2475 case when a.$nm in (select user from grp_adm

2476 where gname=$getgrp) then '管理者'

2477 when '$user' in (select user from grp_adm where gname=a.$nm)

2478 then 'ADMIN'

2479 when '$user' in (select user from grp_mem where gname=a.$nm)

2480 then 'Member'

2481 when '$iamowner' = '' then ''

2482 else ',not='||a.rowid end as ownerlink,

2483 CASE '$entity'

2484 WHEN 'グループ'

2485 THEN coalesce(

2486 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2487 'open')

2488 ||

2489 CASE WHEN '$user'

2490 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2491 THEN ' ismember'

2492 ELSE ''

2493 END

2494 ELSE 'user'

2495 END regmode

2496 from $tbl a left join

2497 (select $nm as name,

2498 max(case key when 'gecos' then val end) as gecos,

2499 max(case key when 'tag' then val end) as tag,

2500 max(case key when 'mtime' then val end) as mtime,

2501 max(case key when 'wtime' then val end) as wtime,

2502 max(case key when 'login' then val end) as login

2503 from ${tbl}_s group by $nm)

2504 b on a.$nm=b.name $cond $3

2505 order by b.wtime desc, b.login desc,

2506 b.mtime desc, b.tag desc, a.rowid asc"

2507 # Give precedence to newer maintained groups (2016-09-24)

2508 # Note that mtime is stored only in grp_s.

2509 ## err LE:sql.1="$sql"

2510 total=`query "with x as ($sql) select count(*) from x;"`

2511 echo "${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2512 echo '<div class="listentry">' # List-entry div

2513 # Show owner/member filter button

2514 METHOD=GET

2515 hiddens="$hiddens

2516 `cgi_hidden kwd \"$kwd\"`

2517 `cgi_hidden stage \"$stage\"`"

2518 if [ x"$tbl" = x"grp" ]; then

2519 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2520 fh="<select name=\"tag\">$nl"

2521 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2522 for l in $args; do

2523 val=${l#*=} tname=${l%=*}

2524 if [ x"$val" = x"$tag" ]; then

2525 s=" selected"

2526 selectedtags="(種別[${tname}]のみ)"

2527 else

2528 s=""

2529 fi

2530 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2531 done

2532 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2533 cat<<-EOF

2534 <form action="$myname" method="$METHOD">

2535 </form>

2536 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2537 for="ismembtn">参加中以外隠す</label>

2538 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2539 for="isadmbtn">管理者参加以外隠す</label>

2540 EOF

2541 # limit=3

2542 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2543 fi

2544 if [ $total -gt $limit ]; then

2545 echo '<div>'

2546 METHOD=GET cgi_form $stage <<EOF

2547 $form

2548 <label>次の語を含む${entity}で検索:

2549 `cgi_text kwd "$kwd"`</label>

2550 $hiddens

2551 EOF

2552 echo '</div>'

2553 else

2554 echo $selectedtags | html p

2555 fi

2556 cat<<EOF

2557 <form action="$myname" method="$METHOD">

2558 <p>${total}件中の<input class="hidesub" type="text" name="start"

2559 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2560 <input type="submit" value="確定"></p>

2561 </form>

2562 EOF

2563 if [ $((offset+limit)) -lt $total ]; then

2564 nextbtn=$(

2565 cat<<EOF

2566 <div class="right clear"><form action="$myname" method="$METHOD">

2567 `cgi_submit 次の${limit}件`

2568 $hiddens

2569 `cgi_hidden offset $((offset + limit))`</form></div>

2570 EOF

2571 )

2572 fi

2573 if [ $offset -gt 0 ]; then

2574 prevbtn=$(

2575 cat<<EOF

2576 <form action="$myname" method="$METHOD">

2577 `cgi_submit 前の${limit}件`

2578 $hiddens

2579 `cgi_hidden offset $((offset - limit))`</form>

2580 EOF

2581 )

2582 fi

2583 pnbtn="$nextbtn$prevbtn"

2584 echo $pnbtn

2585

2586 ## err ListEntry: `echo "$sql"\;`

2587 # sq $db here??? 2016-11-28

2588 query "$sql limit $limit ${offset:+offset $offset};" \

2589 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2590 # err name=$name owner=$ownerp lnk=$lnk

2591 # err newlnk=$lnk regmode=$regmode

2592 icondir=$dir/$id

2593 # Pick up only last icon

2594 htmlname=`echo $name|htmlescape`

2595 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2596 <p class=\"tag _$tag\">$tag</p>" \

2597 | _m4 $tagconv

2598 if [ -n "$NOSPEEDUP" ]; then

2599 files=`getvalbyid $tbl profimg $id $icondir`

2600 if [ -n "$files" ]; then

2601 icon=`echo "$files"|tail -1`

2602 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2603 else

2604 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2605 fi

2606 elif [ -n "$icon" -a -s "$icon" ]; then

2607 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2608 else

2609 cond="$nm=$qname"

2610 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2611 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2612 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2613 # err "img=[$img]"

2614 if [ -n "$img" ]; then

2615 echo "<a href=\"$hrb+$lnk\">"

2616 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2617 echo "</a>"

2618 else

2619 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2620 fi

2621 fi

2622 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2623 echo "</div>"

2624 done

2625 echo "</div>" # End of List-entry div

2626 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2627 )

2628 listmember() {

2629 listentry user "$@"

2630 }

2631 listgroup() {

2632 listentry group "$@"

2633 }

2634 hexteams() { # $1=gname, $2(optional)=user

2635 cond=${2:+" AND user='$2'"}

2636 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2637 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2638 }

2639 showgroup() { # $1=group-rowid

2640 if [ -z "$1" ]; then

2641 grid=`getpar grid`

2642 grid=${grid%%[!0-9]*}

2643 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2644 else

2645 grid=$1

2646 fi

2647 grp=`getgroupbyid $grid`

2648 qgrp=`sqlquote "$grp"`

2649 htmlgrp=`echo "$grp"|htmlescape`

2650 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2651 if isgroup "$grp"; then

2652 tf=$tmpd/title.$$

2653 sf=$tmpd/search.$$

2654 bodyclass=`query "SELECT val FROM grp_s

2655 WHERE gname=$qgrp AND key='regmode';"`

2656 if ismember "$user" "$grp"; then

2657 ismember="ismember"

2658 bodyclass="$bodyclass${bodyclass:+ }ismember"

2659 else

2660 ismember="" # bodyclass="group"

2661 fi

2662 bodyclass="$bodyclass grouphome"

2663 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2664 echo "グループ $htmlgrp" > $tf

2665

2666 showgroupsub $formdir/grp.def "$grid" | \

2667 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2668 -D_FORM_="syscmd(\`cat')" \

2669 -D_BODYCLASS_="$bodyclass" \

2670 -D_DUMPTABLE_="" \

2671 $htmlheader $sf $layout/form+dump.m4.html

2672 # $htmlheader $layout/form+dump.m4.html

2673 # $htmlheader is defined in grp()

2674 else # if $grp is removed at par2table

2675 listgroup

2676 fi

2677 }

2678 showgroupsub() {

2679 # $1=def-file $2=group-rowid

2680 # Using $ismember

2681 rowid=$2

2682 grp=`getgroupbyid $2`

2683 qgrp=`sqlquote "$grp"`

2684 td=`getcachedir grp/"$2"`

2685 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2686 if [ -z "$rowid" ]; then

2687 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2688 #grp=`sq $db "select gname from grp where rowid=$grp"`

2689 echo "showgroupsub: invalid argument($1 $2)" | html p

2690 return

2691 fi

2692 val=`getvalbyid grp profimg $rowid $tmpd`

2693 enticond="gname=$qgrp"

2694 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2695 if [ -n "$img" ]; then

2696 cat<<-EOF

2697 <p class="groupimg">

2698 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2699 EOF

2700 fi

2701 echo "<div class=\"noprofimg\">"

2702 viewtable $1 grp $rowid

2703 echo "</div>"

2704 if isgrpowner "$user" "$grp"; then

2705 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2706 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2707 iamowner=$rowid

2708 colmd=" mode"

2709 fi

2710 if [ -n "$ismember" ]; then

2711 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2712 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2713 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2714 cat<<-EOF

2715 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2716 href="?blog+$rowid">グループの新規話題作成</a>

2717 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2718 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2719 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2720 <div class="fold clear">

2721 `cgi_checkbox send yes id="send"`<label

2722 for="send">グループ全員にメッセージ送信</label>

2723 <div>

2724 `cgi_textarea message "" "cols=60"`

2725 `cgi_submit 送信`

2726 `cgi_reset リセット`

2727 </div>

2728 `cgi_hidden grp $rowid`

2729 </div></form>

2730 EOF

2731 fi

2732 # 加入ボタン + 加入者リスト

2733 if [ -n "$ismember" ]; then

2734 ismem='checked' state="(参加中)"

2735 else

2736 nomem='checked' state="(現在非加入)"

2737 fi

2738 # このグループでの加入アドレス

2739 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2740 and key='email';"`

2741 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2742 ## and key='email';"

2743 ##err email=$eml

2744 cat <<EOF

2745 <div class="fold clear">

2746 `cgi_checkbox reg yes id="reg"`<label

2747 for="reg">自身の加入状態を操作する</label>$state

2748 <div>

2749 EOF

2750 cgi_form grp <<EOF

2751 <p>このグループに</p>

2752 <table class="b">

2753 <tr><th>メンバーとして</th><td>

2754 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2755 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2756 <tr><th>参加する場合のメイルアドレス<br>

2757 <small>(メインのアドレスとは違うものにする場合に記入<br>

2758 同じでよい場合は空欄に)</small></th>

2759 <td>`cgi_text email $eml`</td></tr>

2760 </table>

2761 `cgi_hidden grp $rowid`

2762 EOF

2763 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2764 echo "moderated (承認加入の)グループなので実際に参加できるのは

2765 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2766 fi

2767 echo '</div></div>'

2768 echo '<h2>話題一覧</h2>'

2769 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2770 cat<<-EOF

2771 <form class="summary" action="$myname" title="$thelp">

2772 `cgi_hidden owner "$grp"`

2773 `cgi_hidden kwd "@week"`

2774 `cgi_hidden stage searchart`

2775 `cgi_submit "一週間のまとめ"`

2776 </form>

2777 EOF

2778 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2779 colstate="state:稼動状態:frozen=rowclass=凍結"

2780 DT_CHLD=article:blogid \

2781 DT_QOWNER="$qgrp" \

2782 DT_VIEW=replyblog dumptable html blog \

2783 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2784

2785 getgname="(select gname from grp where rowid=$rowid)"

2786 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2787 cm="?commission+$rowid"

2788 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2789 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2790 # team list

2791 hexteams=`hexteams "$grp"`

2792 if [ -n "$hexteams" ]; then

2793 echo "チーム一覧" | html h2

2794 echo '<div class="dumptable"><table class="b">'

2795 sq $db -html -header<<-EOF

2796 SELECT val TEAM,

2797 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2798 MEMBERS

2799 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2800 EOF

2801 echo '</table></div>'

2802 fi

2803 }

2804 grp_getbodyclass() {

2805 # Get css class name for document.

2806 # `moderated' for moderated groups

2807 # `ismember' for groups where user belongs

2808 # $1=GroupName (w/o quote)

2809 # $user=userNameCurrentlyLogin

2810 ## err grp_getbodyclass: 1="$1"

2811 qgrp=`sqlquote "$1"`

2812 query<<-EOF

2813 SELECT coalesce(

2814 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2815 'open')

2816 ||

2817 CASE WHEN '$user'

2818 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2819 THEN ' ismember'

2820 ELSE ''

2821 END;

2822 EOF

2823 }

2824 grpaction() { # $1=group-rowid

2825 err GRP_ACTION:IN

2826 grid=${1:-`getpar grp`}

2827 grp=`getgroupbyid "$grid"`

2828 htmlgrp=`echo "$grp" | htmlescape`

2829 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2830 if [ -z "$grp" ]; then

2831 echo "無効な指定です。" | html p; return

2832 fi

2833 if ! ismember $user "$grp"; then

2834 echo "加入者のみに許可された操作です。" | html p; return

2835 fi

2836 echo "グループ $grp 個別選択操作" \

2837 | _m4 -D_TITLE_="syscmd(\`cat')" \

2838 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2839 $layout/html.m4.html

2840

2841 isowner=""

2842 isgrpowner "$user" "$grp" && isowner="yes"

2843 usel=`getpar usel`

2844 if [ -n "$usel" ]; then

2845 uids=$(echo `echo $usel`|tr ' ' ',')

2846 ## err grpaction-1: grp=$grp, `echo $sql`

2847 text=`getpar text|tr -d '\r'`

2848

2849 rm=`getpar rm` cfm=`getpar confirm`

2850 ## err rm=$rm cfm=$cfm

2851 if [ x"$rm" = x"yes" ]; then

2852 if [ "$isowner" ]; then

2853 if [ x"$rm$cfm" = x"yesyes" ]; then

2854 # Eliminate

2855 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2856 for tbl in grp_mem grp_mem_s grp_mem_m; do

2857 sql="delete from $tbl $cond;"

2858 # echo "sql=$sql"

2859 query "$sql"

2860 err rmGRPuser "$sql"

2861 done

2862 num=`query "select count(*) from user where rowid in ($uids);"`

2863 #err num=$num

2864 if [ 0$num -gt 0 ]; then

2865 sql="select coalesce(b.val,a.name) from user a left join \

2866 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2867 # err `echo "$sql"`

2868 html pre<<EOF

2869 以下の${num}名のグループ $grp 登録を解除しました。

2870 `query "$sql"`

2871 EOF

2872 fi

2873 else

2874 echo "確認のチェックがないのでやめておきます。" | html p

2875 return

2876 fi

2877 else # not Group Owner

2878 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2879 return

2880 fi

2881 cat<<EOF

2882

2883 EOF

2884 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2885 if [ -z "$text" ]; then # if msg is empty

2886 echo "なにかメッセージを..." | html p

2887 return 0

2888 fi

2889 gecos=`gecos $user`

2890 safegc=`echo "$gecos" | tr -d '<>@'`

2891 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2892 fromad=`myemail4group "$grp"`

2893 ###mail_from="$safegc <$fromad>"

2894 mail_from="$safegc <$user>" # TEST: 2020/5/13

2895 test -n `getpar sender` &&

2896 export SENDER=$user # TEST: 2020/5/15

2897 replyto=$fromad

2898

2899 ## Start parse of attachment files

2900 if [ -n "`getpar email`" ]; then

2901 ar=`getpar supprcpt`

2902 if [ -n "$ar" ]; then

2903 for a in $ar; do

2904 if checkdomain "$a"; then

2905 supprcpt="$supprcpt $a"

2906 else

2907 err "SupprcptErr=[$a] by $user"

2908 removercpt="$removercpt $a"

2909 fi

2910 done

2911 fi

2912 subj=`getpar subject`

2913 afiles=""

2914 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2915 do

2916 f=$tmpd/$fn

2917 if [ -s $f ]; then

2918 afiles=$afiles"${afiles:+ }$f"

2919 fi

2920 done

2921 else

2922 preface=$(cat <<-EOF

2923 $url

2924 のグループ「$grp」のメンバーである $gecos さんから、

2925 あなた宛のメッセージです。

2926 ----------------------------------------------------------

2927 EOF

2928 )

2929 fi

2930 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2931 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2932 subj="${subj:-$gecos さんからのメッセージ}"

2933 REPLYTO=$replyto

2934 MAIL_FROM=$mail_from

2935 export REPLYTO SMAIL_TO MAIL_FROM

2936 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2937 for r in $rcpts; do

2938 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2939 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2940 else

2941 # Show sender and rcpts address for guest

2942 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2943 fi

2944 if [ -n "$afiles" ];then

2945 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2946 else

2947 smail "$r" "$subj"

2948 fi <<EOF

2949 ${preface:+$preface$nl}$text

2950 EOF

2951 done

2952 if [ $? = 0 ]; then

2953 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2954 sql="select coalesce(b.val, a.name) from

2955 (select name from user where rowid in ($uids)) a

2956 left join user_s b on a.name=b.name and b.key='gecos';"

2957 html pre<<EOF

2958 `query "$sql"`

2959 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2960 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2961 EOF

2962 err SendDone: `echo $sql`

2963 fi

2964 elif [ x"$rm" = x"commission" ]; then

2965 grp_reg_adm $grid $usel

2966 elif [ x"$rm" = x"addteam" ]; then

2967 team=`getpar team|sed "s/'/''/g"` # for single quotation

2968 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2969 if [ x"$team" != x"$newteam" ]; then

2970 echo "チーム名に使えない文字を除去しました" | html p

2971 team=$newteam

2972 fi

2973 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2974 cat<<-EOF | html p

2975 有効なチーム名を入力してください。

2976 カンマだけ、「なし」という名前は使えません。

2977 EOF

2978 echo "有効なチーム名を入力してください。" | html p

2979 else

2980 grp_add_team $grid "$team" $usel

2981 fi

2982 elif [ x"$rm" = x"rmteam" ]; then

2983 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2984 rmteam=`getpar rmteam|sed "s/'/''/g"`

2985 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2986 gname='$grp' AND user='$user' AND key='team'\

2987 AND val='$rmteam';\"`" ]; then

2988 grp_rm_team $grid "$rmteam" $usel

2989 else

2990 echo "所属していないチームの除去操作はできません。"|html p

2991 fi

2992 else

2993 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2994 fi

2995 fi

2996 fi

2997 # POST count summary

2998 from=`getpar from`; to=`getpar to`

2999 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3000 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3001 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

3002 # New entry

3003 sql="WITH mems AS (

3004 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3005 ON gm.user=g.name

3006 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3007 ), target_article AS (

3008 SELECT id FROM article_s

3009 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3010 ), posts AS (

3011 SELECT author, count(author) post

3012 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3013 WHERE blogid IN (SELECT id FROM blog_s

3014 WHERE key='owner'

3015 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3016 AND key='text'

3017 GROUP BY author

3018 ), teams AS (

3019 SELECT user, group_concat(val, ', ') team

3020 FROM grp_mem_m

3021 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3022 AND key='team'

3023 GROUP BY user

3024 ), user_post AS (

3025 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3026 FROM mems m LEFT JOIN posts

3027 ON m.name=posts.author

3028 GROUP by m.rowid

3029 )

3030 SELECT

3031 CASE

3032 WHEN (SELECT user FROM grp_adm

3033 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3034 AND user=up.name) IS NOT NULL

3035 then 'k'

3036 ELSE ''

3037 END || rowid || ',' || gecos NAME,

3038 post POST, team _TEAM_

3039 FROM user_post up LEFT JOIN teams t

3040 ON up.name=t.user

3041 ORDER BY gecos;"

3042 ## err grpaction: "`echo \"$sql\"`"

3043 tf=$tmpd/title.$$

3044 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3045 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3046 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3047 for=\"cmadmin\">管理者委任</label>

3048 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3049 </p></div>"

3050 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3051 title=\"Shortcut: g${nl}Remove from the Group\"

3052 for=\"conf\">GRP登録解除</label>

3053 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3054 <p>この操作による通知は本人に行きません。

3055 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3056 </div>"

3057 # Get team list to which current user belongs into $hexteams

3058 allhexteams=$(hexteams "$grp")

3059 if [ -n "$isowner" ]; then

3060 myhexteams="$allhexteams" # admin can remove all teams' attr

3061 else

3062 myhexteams=$(hexteams "$grp" "$user")

3063 fi

3064 if [ -n "$myhexteams" ]; then

3065 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3066 title=\"Shortcut: s${nl}Strip a team tag from\"

3067 for=\"cmrmteam\">チーム属性除去</label>

3068 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3069 を除去します: `cgi_checkbox teamconfirm yes` 確認

3070 <p>この操作による通知は本人に行きません。

3071 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3072 </div>

3073 "

3074 fi

3075 stf=$tmpd/selteam.$$

3076 cgi_select_h selteam "5445414d" $allhexteams > $stf

3077 b1='<label> <input type="checkbox" name="usel" value="'

3078 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3079 br='<span id="reverse" title="Reverse Selection"></span>'

3080 #b2='"> <span>' b3='</span></label>'

3081 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3082 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3083 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

3084 cgi_form grpaction<<EOF \

3085 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3086 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

3087 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

3088 | _m4 -D_TITLE_="spaste(\`$tf')" \

3089 -D_SUBTITLE_="チェック後操作ボタン" \

3090 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3091 $layout/form+dump.m4.html \

3092 | _m4 -D_TEAM_="spaste(\`$stf')"

3093 <p>下でチェックした人を対象として:</p>

3094 <div class="foldtabs">

3095 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3096 title="Shortcut: a${nl}Add a team tag to"

3097 for="cmteam">同じチーム属性を付与</label>

3098 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3099 `cgi_datalist_h teams $allhexteams`

3100 </div>

3101 ${rmteammsg}

3102 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3103 title="Shortcut: d${nl}DirectMail to"

3104 for="sendmsg" title="Direct Message">DM送信</label>

3105 <div>

3106 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3107 title="Using email format">email書式を使う</label>

3108 <div class="folded">

3109 <table>

3110 <tr><td>From: </td><td>$user</td></tr>

3111 <tr><td>このFrom:で送る</td>

3112 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3113 </td></tr>

3114 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3115 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3116 <tr><td>ファイル添付: </td>

3117 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3118 </table>

3119 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3120 </div>

3121 <div>本文:`cgi_textarea text "" cols=72`

3122 </div>

3123 </div>

3124 ${isowner:+$cmmsg$excmsg}

3125 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3126 </div>

3127 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3128 <table class="td2r thl">

3129 `sq $db -header -html "$sql"`

3130 </table>

3131 `cgi_hidden grp $grid`

3132 `cgi_hidden myuid $myuid id="myuid"`

3133 EOF

3134 }

3135 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3136 # Create TEMPORARY VIEW

3137 query<<EOF

3138 CREATE TEMPORARY VIEW writeusers AS

3139 SELECT DISTINCT author FROM article

3140 WHERE id in (

3141 select id from article where blogid=(select id from blog where rowid=$1)

3142 );

3143 CREATE TEMPORARY VIEW movablegroups AS

3144 SELECT g.rowid growid , g.gname

3145 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3146 ON grp.gname=gm.gname -- そのユーザが属している

3147 AND user='$user') g -- グループに絞る

3148 WHERE (SELECT author FROM writeusers

3149 EXCEPT

3150 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3151 IS NULL;

3152 $2

3153 EOF

3154 }

3155 sql4readableblogs() {

3156 # Create view of blogs that can be readable to $user

3157 # Blog is readable when:

3158 # 1: blog owner is an user

3159 # 2: else, 2.1: owner-group where the $user belongs

3160 # 2.2: else, owner-group is not moderated

3161 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3162 cat<<EOF ## | tee tmp/sql.out

3163 CREATE TEMPORARY VIEW readableblogs AS

3164 SELECT blog.rowid rid, id, author

3165 FROM blog

3166 NATURAL JOIN

3167 (SELECT id,

3168 max(CASE key WHEN 'owner' THEN val END) owner,

3169 max(CASE key WHEN 'mode' THEN val END) mode

3170 FROM blog_s GROUP by id) bs

3171 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3172 THEN 1 -- blog owner is an user, READABLE

3173 WHEN (SELECT val FROM grp_s

3174 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3175 AND

3176 (SELECT user FROM grp_mem

3177 WHERE gname=bs.owner AND user='$user') IS NULL

3178 THEN 0

3179 WHEN mode IN ('quiz', 'enquete')

3180 THEN 0 -- "quiz" mode blog cannot be searched

3181 ELSE 1

3182 END;

3183 EOF

3184 }

3185 editheading() { # $1=rowid-of-heading

3186 rowid=${1%%[!A-Z0-9a-z_]*}

3187 if [ -z "$rowid" ]; then

3188 echo "話題番号が未指定です。" | html p

3189 return

3190 fi

3191 owner=`getvalbyid blog owner $rowid`

3192 title=`getvalbyid blog title $rowid`

3193 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3194 | _m4 -D_TITLE_="修正" \

3195 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3196 -D_BLOGS_="" -D_DUMPTABLE_="" \

3197 -D_FORM_="syscmd(\`cat')" \

3198 $layout/html.m4.html $layout/form+dump.m4.html

3199 # Move to group

3200 if isuser "$owner"; then

3201 crview4article $rowid

3202 n=`query "SELECT count(*) FROM writeusers;"`

3203 ## err N=$n

3204 if [ $((n)) -gt 0 ]; then

3205 ## err ROWID=$rowid

3206 sql="SELECT growid || ':' || gname FROM movablegroups;"

3207 cat<<-EOF

3208 <div class="fold">

3209 `cgi_checkbox mv send id="mv"`<label

3210 for="mv">この話題をグループ所有に移動する</label>

3211 <div>

3212 <form action="?mvart" method="POST" enctype="multipart/form-data">

3213 移動先グループ:

3214 <select name="mv2grp">

3215 EOF

3216 query ".mode html"

3217 query<<-EOF |

3218 $sql

3219 .mode list

3220 EOF

3221 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3222 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3223 cat<<-EOF

3224 </select>

3225 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3226 そのグループに加入しているものに限られます)</p>

3227 <p>`cgi_checkbox cfm yes`<label>確認

3228 (この操作は元に戻すことができません)</label></p>

3229 `cgi_hidden blogrowid $rowid`

3230 `cgi_submit 移動`

3231 `cgi_reset Reset`

3232 </form>

3233 </div>

3234 </div>

3235 EOF

3236 fi

3237 # end of isuser "$owner"

3238 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3239 [ -n "$hexteams" ];}; then

3240 none="`echo なし|hexize`"

3241 cat<<-EOF

3242 <div class="fold">

3243 `cgi_checkbox mv2team send id="mv2team"`<label

3244 for="mv2team">この話題を以下のチームのものにする</label>

3245 <div><p>現在の所属チーム設定:

3246 `query "SELECT

3247 coalesce((SELECT val FROM blog_s

3248 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3249 AND key='team'),

3250 ':なし');"`</p>

3251 <form action="?mvart" method="POST" enctype="multipart/form-data">

3252 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3253 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3254 `cgi_hidden blogrowid $rowid`<br>

3255 `cgi_submit 移動`

3256 `cgi_reset Reset`

3257 </form></div></div>

3258 EOF

3259 fi

3260 }

3261 mvart() { # move diary to some group or team

3262 # or move blog of group to team which belong to the group

3263 blogrowid=`getpar blogrowid`

3264 cfm=`getpar cfm`

3265 ##### echo move blog:$blogrowid to $mv2grp | html p

3266 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3267 . ./s4-blog.sh

3268 if [ -z "$blogrowid" ]; then

3269 echo "無効な指定です(mvart)。" | html p

3270 return

3271 elif [ x"$cfm" != x"yes" ]; then

3272 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3273 elif { mv2grp=`getpar mv2grp`

3274 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3275 [ -n "$mv2grp" ]; }; then

3276 crview4article $blogrowid

3277 ########## TRANSACTION BEGIN

3278 query "BEGIN;"

3279 n=`query "SELECT count(*) FROM writeusers;"`

3280 ## err Nwriteuser=$n

3281 if [ $((n)) -gt 0 ]; then

3282 query<<-EOF

3283 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3284 WHERE key='owner'

3285 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3286 AND $mv2grp IN (SELECT growid FROM movablegroups);

3287 EOF

3288 fi

3289 query "END;"

3290 ########## TRANSACTION END

3291 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3292 [ -n "$mv2team" ];}; then

3293 # blog owner can move it to ANY team

3294 case "$mv2team" in

3295 'なし')

3296 cat<<-EOF

3297 DELETE FROM blog_s

3298 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3299 AND key='team';

3300 EOF

3301 ;;

3302 "") ;;

3303 *)cat<<-EOF

3304 BEGIN;

3305 REPLACE INTO blog_s(id, key, val)

3306 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3307 'team', '$mv2team');

3308 REPLACE INTO blog_s(id, key, val)

3309 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3310 'notify', 'all'); -- Change notify to all

3311 END;

3312 EOF

3313 esac | query

3314 fi

3315 blog_reply $blogrowid

3316 echo yes | html p

3317 }

3318 editart() { # $1=article-rowid $2=blogrowid

3319 rowid=${1%%[!A-Z0-9a-z_]*}

3320 blogrowid=${2%%[!A-Z0-9a-z_]*}

3321 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3322 echo "表示する記事番号が未指定です。" | html p

3323 return

3324 fi

3325 owner=`getvalbyid blog owner $blogrowid`

3326 title=`getvalbyid blog title $blogrowid`

3327 author=`getvalbyid article author $rowid`

3328 ## err EDITart: owner=$owner, author=$author

3329 if isgrpowner "$user" "$owner"; then

3330 : EDIT OK

3331 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3332 echo "本人か所有者しか編集できません." | html p

3333 return

3334 fi

3335 aid=`query "select id from article where rowid=$rowid;"`

3336 tmpout=$tmpd/editart.$$.out

3337 GF_ACTION="?replyblog+$blogrowid#$aid" \

3338 edittable $formdir/article.def article $rowid \

3339 > $tmpout

3340 rm -f /tmp/editart.out

3341 # Cannot use pipelining to m4 with genform() because of stdin stack

3342 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3343 -D_FORM_="syscmd(cat $tmpout)" \

3344 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3345 -D_BLOGS_= -D_DUMPTABLE_= \

3346 $layout/html.m4.html $layout/form+dump.m4.html

3347 }

3348 send2mem() {

3349 rowid=`getpar grp`

3350 rowid=${rowid%%[!0-9]*} # Cleaning

3351 if [ -z "$rowid" ]; then

3352 echo "グループが未指定です。" | html p

3353 return

3354 fi

3355 message=`getpar message`

3356 if [ -z "$message" ]; then

3357 echo "文章を入れてください。" | html p

3358 return

3359 fi

3360 grp=`getgroupbyid $rowid`

3361 members=`collectemail "$grp"`

3362 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3363 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3364 mailfrom="`gecos "$user"` <$mailfrom>"

3365 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3366 msg=$(cat<<-EOF

3367 $urlbase?grp+$rowid

3368 グループ $grp に所属する

3369 `gecos $user` さんよりメッセージ:

3370

3371 $message

3372 EOF

3373 )

3374 # smail rcpt subj (file)

3375 for m in $members; do

3376 echo "$msg" |

3377 MAIL_FROM=$mailfrom \

3378 SENDER=$noreply \

3379 REPLYTO=$mailfrom \

3380 smail "$m" "$sj"

3381 done

3382 cat<<EOF

3383 <p>以下のユーザに送信しました。</p>

3384 <pre>

3385 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3386 </pre>

3387 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3388 EOF

3389 }

3390 joingrpadmit() {

3391 # $1=yes/no $2=session-key

3392 if [ -z "$2" ]; then

3393 echo "bye bye" | html p; return

3394 fi

3395 t_usr=`session=$2 getpar adduser`

3396 t_grp=`session=$2 getpar group`

3397 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3398 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3399 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3400 echo "無効な加入依頼です。" | html p

3401 echo "有効期限が切れたか、

3402 他の管理者がいる場合は処理済みの可能性があります。" | html p

3403 return

3404 fi

3405 if ! isgrpowner "$user" "$t_grp"; then

3406 echo "グループ管理者のみの機能です。" | html p; return

3407 fi

3408 case $1 in

3409 yes) joingrp "$t_grp" "$t_usr" yes ;;

3410 no) joingrp "$t_grp" "$t_usr" no ;;

3411 *)

3412 echo "無効な指定です($1)。" | html p

3413 return ;;

3414 esac

3415 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3416 rcpts="`getgroupadminmails "$t_grp"` $user"

3417 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3418 body="に

3419 $t_usr

3420 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3421 しました。"

3422 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3423 query "delete from session where id='$2';"

3424 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3425 }

3426

3427 joingrprequest() {

3428 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3429 jss="joingrp-`date +%s`-`genrandom 12`"

3430 addsession $jss +${memoplimitdays}days

3431 grpadmins=`getgroupadmins "$1"`

3432 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3433 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3434 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3435 $url

3436 $user さんから

3437 グループ $1

3438 に加入依頼がありました。

3439

3440 承認する:

3441 $urlbase?joingrpadmit+yes+$jss

3442

3443 白紙に戻す:

3444 $urlbase?joingrpadmit+no+$jss

3445 EOF

3446 echo "管理者に加入依頼を出しました。

3447 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3448 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3449 }

3450 joingrp() {

3451 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3452 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3453 if isgrpowner "$user" "$1"; then

3454 isowner="yes"

3455 elif [ -n "$5" ]; then

3456 isowner="yes"

3457 else

3458 isowner=""

3459 fi

3460 ## err jg:isgrpowner: isowner="$isowner"

3461 if [ -n "$isowner" ]; then

3462 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3463 elif [ x"$2" != x"$user" ]; then # if user is not login user

3464 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3465 return

3466 elif [ x"$3" = x"no" ]; then

3467 : # Do not pursue those who leave

3468 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3469 : # Member can change own email address for the joining moderated group

3470 else # adding user is $user itself

3471 case `getgroupattr "$1" regmode` in

3472 moderated)

3473 joingrprequest "$@" # Request only

3474 return

3475 ;;

3476 *)

3477 ;;

3478 esac

3479 fi

3480 qgname=`sqlquote "$1"`

3481 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3482 cond="where gname=$qgname and user='$2'"

3483 if [ x"$3" = x"yes" ]; then

3484 query "replace into grp_mem values($qgname, '$2');"

3485 # Notify joingrp to admin

3486 action="に加入しました。"

3487 if [ -n "$4" ]; then

3488 if msg=`emaildomaincheck "$4"`; then

3489 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3490 'string', '$4', NULL);"

3491 else

3492 echo $msg

3493 fi

3494 else

3495 query "delete from grp_mem_s $cond and key='email';"

3496 fi

3497 if [ -n "$5" ]; then # as ADMIN

3498 # Coming here means newly created group

3499 sql="select case\

3500 when (select count(*) from grp_mem where gname=$qgname)=1\

3501 then (select user from grp_mem\

3502 where gname=$qgname and user='$user')\

3503 else '' end; "

3504 err NewGrpChk: $sql

3505 if [ -n "`query \"$sql\"`" ]; then

3506 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3507 query "replace into grp_adm values($qgname, '$user');"

3508 fi

3509 fi

3510 else

3511 query "begin;

3512 delete from grp_mem $cond;

3513 delete from grp_mem_s $cond;

3514 delete from grp_mem_m $cond;

3515 delete from grp_adm $cond;

3516 delete from grp_adm_s $cond;

3517 delete from grp_adm_m $cond;

3518 end;"

3519 action="から脱退しました。"

3520 fi

3521 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3522 $url?grp+$grid

3523 $user (`gecos $user`)さんが

3524 グループ $1

3525 $action

3526 EOF

3527 }

3528 grp_add_team() (

3529 # $1=grp-rowid $2=team $3...=user-rowid(s)

3530 grp=`getgroupbyid $1`

3531 team=$2; shift; shift

3532 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3533 { echo "BEGIN;"

3534 for user; do

3535 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3536 '$grp',\

3537 (SELECT name FROM user WHERE rowid=$user),\

3538 'team', 'string', '$team');"

3539 done

3540 echo "END;"

3541 } | query

3542 )

3543 grp_rm_team() (

3544 # $1=grp-rowid $2=team $3...=user-rowid(s)

3545 grid=$1

3546 qgrp=$(sqlquote "`getgroupbyid $grid`")

3547 team=$2; shift; shift

3548 [ -z "$grid" -o -z "$team" ] && return

3549 { echo "BEGIN;"

3550 for user; do

3551 echo "DELETE FROM grp_mem_m\

3552 WHERE gname=$qgrp \

3553 AND user=(SELECT name FROM user WHERE rowid=$user)\

3554 AND key='team' AND val='$team';"

3555 done

3556 cat<<-EOF

3557 DELETE FROM blog_s

3558 WHERE rowid=(

3559 SELECT rowid

3560 FROM blog_s a

3561 WHERE key='team'

3562 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3563 AND NOT EXISTS (SELECT * FROM grp_mem_m

3564 WHERE key='team' AND val=a.val -- a.val=team

3565 AND gname = (SELECT val FROM blog_s b

3566 WHERE a.id=b.id AND key='owner')

3567 ));

3568 EOF

3569

3570 echo "END;"

3571 } | query

3572 )

3573 grp_reg_adm() {

3574 # $1=grp-rowid $2...=user-rowid

3575 grid=$1

3576 grp=`getgroupbyid "$1"`

3577 if [ -z "$grp" ]; then

3578 echo "無効なグループIDです" | html p; return

3579 fi

3580 if ! isgrpowner "$user" "$grp"; then

3581 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3582 fi

3583 shift

3584 for urid; do

3585 newadm=`query "select name from user where rowid=$urid;"`

3586 if [ -z "$newadm" ]; then

3587 echo "指定ユーザIDがおかしいようです。" | html p; return

3588 fi

3589 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3590 err ismember $newadm $grp

3591 if ismember $newadm "$grp"; then

3592 # OK, go ahead

3593 getgname="(select gname from grp where rowid=$grid)"

3594 query "replace into grp_adm values($getgname, '$newadm');"

3595 # confirm insertion

3596 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3597 if [ -n "`query \"$sql;\"`" ]; then

3598 echo "追加完了: $newadm" | html p

3599 else

3600 echo "追加失敗($1 $urid)" | html p

3601 fi

3602 fi

3603 showgroup $grid

3604 done

3605 }

3606 dt_rowhack() {

3607 # From: <TR>

3608 # ....

3609 # <TD>rowclass=foo</TD>

3610 # </TR>

3611 # To: <TR class="foo">....<TD>foo</TD></TR>

3612 sed -e '

3613 /^<TR>/ {

3614 :loop

3615 s/\n//

3616 N

3617 /<\/TR>/ {

3618 s/\n//

3619 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3620 n

3621 }

3622 $q

3623 b loop

3624 }'

3625 }

3626 dumptable() {

3627 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3628 # textのフィールドだけ全てダンプにしたほうがいいか

3629 # $DT_VIEW sets link

3630 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3631 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3632 VIEW=${DT_VIEW-replyblog}

3633 if [ -n "$VIEW" ]; then

3634 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3635 fi

3636 sqlfile=$tmpd/dump.sql

3637 : > $sqlfile # ensure to be empty

3638 printf '.mode html\n.header 1\n' > $sqlfile

3639 # $DT_CHLD=ChildTable:BindColumn

3640 if [ -n "$DT_CHLD" ]; then

3641 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3642 cat<<-EOF >> $sqlfile

3643 -- presql

3644 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3645 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3646 EOF

3647 # Speed up counting of new articles

3648 cat<<-EOF >> $sqlfile

3649 -- presql2

3650 DROP TABLE IF EXISTS _counts;

3651 CREATE TEMPORARY TABLE _counts AS

3652 SELECT $_i, count($_i) cnt

3653 FROM $_t GROUP BY $_i;

3654 /* Prepare NEW count table */

3655 CREATE TEMPORARY TABLE _target AS

3656 SELECT b.rowid trowid, b.id

3657 FROM "$2" b JOIN "$2_s" s

3658 ON b.id=s.id AND s.key='owner'

3659 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3660

3661 DROP TABLE IF EXISTS _children;

3662 CREATE TEMPORARY TABLE _children AS

3663 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3664 FROM (SELECT t.trowid, t.id $_i, a.id

3665 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3666 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3667

3668 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3669 DROP TABLE IF EXISTS _news;

3670 DROP VIEW IF EXISTS _news;

3671

3672 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3673 -- INSERT INTO _news

3674 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3675 CREATE TEMPORARY VIEW _news AS

3676 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3677 FROM (SELECT DISTINCT id FROM _target)

3678 a LEFT JOIN

3679 (SELECT $_i, count(ctime) newcnt

3680 FROM _children x

3681 WHERE ctime > coalesce((SELECT time from myacclog

3682 WHERE tblrowid=x.trowid),

3683 '1970-01-01')

3684 GROUP BY $_i) b

3685 ON a.id=b.$_i;

3686 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3687 EOF

3688 # REMOVE next line until 2019/5/1

3689 cntall="(select count($_i) from $_t where $_i=a.id)"

3690 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3691 # REMOVE next assignment until 2019/5/1

3692 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3693 and id in (select id from $_t where $_i=a.id) \

3694 and val > coalesce((select time from myacclog where \

3695 tblrowid=a.rowid),\

3696 '1970-01-01'))"

3697 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3698 cnt="$cntnew as '新着', $cntall as '総数',"

3699 dt_class=" td2r td3r dumpblogs"

3700 fi

3701 # Construct join expression

3702 eav="" scols=""

3703 pk=`gettblpkey $2`

3704 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3705 substr=${substr:-%s}

3706 for col in ${3:-`gettbl_s_cols $2`}; do

3707 valvar=val

3708 case $col in

3709 gecos) scols="$scols${scols:+, }${col#}"

3710 continue ;; # built-in column name

3711 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3712 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3713 case "$as" in

3714 *:*=*) cnd=${as#*:}

3715 h=${cnd%%=*} v=${cnd#*=}

3716 h=`sqlquotestr "$h"`

3717 v=`sqlquotestr "$v"`

3718 valvar="CASE val WHEN $h THEN $v END"

3719 as=${as%%:*} ;;

3720 esac

3721 ;;

3722 *) as=${col} ;;

3723 esac

3724 ss=`printf "$substr" "$valvar"`

3725 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3726 scols="$scols${scols:+, }b.$as"

3727 done

3728 #case author when '$user' then a.rowid else '---' end as ID,

3729 if [ -n "$DT_SQL" ]; then

3730 echo "$DT_SQL"

3731 else

3732 cat<<-EOF

3733 SELECT a.rowid as LINK, $cnt $scols

3734 FROM $2 a LEFT JOIN

3735 (SELECT $pk,$eav,

3736 max(CASE key

3737 WHEN 'owner'

3738 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3739 as gecos

3740 FROM ${2}_s c GROUP BY $pk)

3741 b ON a.$pk=b.$pk $4;

3742 EOF

3743 fi >> $sqlfile

3744 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3745 sqlog<<-EOF

3746 *** SQL-file: $sqlfile ***

3747 `cat $sqlfile`

3748 EOF

3749 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3750 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3751 cat $sqlfile >> tmp/sql

3752 # query ".read $sqlfile" > $tmpd/foo

3753 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3754 cp $tmpd/foo tmp/

3755 err "----- `gdate +%FT%T.%3N` ------------555555"

3756 ## $ddd LINE exists at the end of this function

3757 fi

3758 printf '.mode list\n.header 0\n' >> $sqlfile

3759 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3760 <div>

3761 <div class="dumptable">

3762 <table class="b$dt_class">

3763 `query ".read $sqlfile"`

3764 </table>

3765 </div>

3766 </div>

3767 EOF

3768 ### `query ".read $sqlfile"`

3769 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3770

3771 ## REMOVE THIS!

3772 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3773 }

3774

3775 clean-orphaned() {

3776 # This shoud be done by foreign_key rules, but some db lack them

3777 query<<-EOF

3778 -- Find blogs that have no parent

3779 WITH orphanedblog AS (

3780 SELECT blog.id,val FROM blog JOIN blog_s bs

3781 ON blog.id=bs.id AND key='owner'

3782 WHERE val NOT IN (SELECT gname FROM grp)

3783 AND val NOT IN (SELECT name FROM user);

3784 ) -- Remove them

3785 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3786

3787 -- Find articles that have no parent blog

3788 WITH orphanedarticle AS (

3789 SELECT id FROM article

3790 WHERE blogid NOT IN (SELECT id FROM blog);

3791 ) -- Remove them

3792 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3793 EOF

3794 }

3795 par2table() (

3796 # copy current parameters of par into destination table

3797 # $1=definition-file

3798 # Using $user and $session

3799 # Return value:

3800 # 0: Stored successfully

3801 # 1: Insufficient fillings

3802 # 2: No permission to modify the record

3803 # 3: Invalid rowid

3804 # 4: SUCCESS to delete

3805 # 5: Stop deletion for lack of confirm check

3806 # 6: Password length too short

3807 # 7: Password mismatch

3808 # 8: Old password incorrect

3809 # 9: Duplicated post

3810 rowid=`getpar rowid`

3811 if [ ! -e $1 ]; then

3812 echo "テーブル定義ファイルが見付かりません" | html p

3813 exit 1

3814 fi

3815 tbl=${1%.def}

3816 tbl=${tbl##*/}

3817 if [ -n "$rowid" ]; then # Modify existing entry

3818 if [ x"$tbl" = x"user" ]; then

3819 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3820 elif [ x"$tbl" = x"grp" ]; then

3821 sql="select gname from $tbl where rowid=$rowid;"

3822 ##err p2t:grp:q $sql

3823 isgrpowner "$user" "`query $sql`" && rowowner=$user

3824 elif [ x"$tbl" = x"blog" ]; then

3825 # Check if owner in blog_s

3826 blogowner=`getvalbyid blog owner "$rowid"`

3827 if isgrpowner "$user" "$blogowner"; then

3828 rowowner=$user

3829 else

3830 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3831 fi

3832 else

3833 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3834 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3835 fi

3836 ### err rowowner=$rowowner

3837 if [ x"$user" != x"$rowowner" ]; then

3838 echo "他人のレコードはいじれないの" | html p

3839 return 2

3840 elif [ -z "$rowowner" ]; then

3841 echo "指定したレコードはないみたい" | html p

3842 return 3

3843 fi

3844 rm=`getpar rm` cfm=`getpar confirm`

3845 # Editing existent entry

3846 if [ x"$rm" = x"yes" ]; then

3847 if [ x"$rm$cfm" = x"yesyes" ]; then

3848 query "delete from $tbl where rowid=$rowid;"

3849 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3850 clean-orphaned

3851 fi

3852 return 4

3853 else

3854 echo "消去確認のチェックがないので消さなかったの..." | html p

3855 return 5

3856 fi

3857 fi

3858 fi

3859

3860 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3861 if [ -n "$rowid" ]; then

3862 # Update of existing record

3863 for col in `gettblcols $tbl`; do

3864 val=`getparquote $col`

3865 [ -z "$val" ] && continue

3866 ## err query "update $tbl set $col=$val where rowid=$rowid"

3867 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3868 sql="update $tbl set $col=$val where rowid=$rowid;"

3869 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3870 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3871 ## User name cannot be changed with interface provided with this

3872 ## script. But we offer the trigger to change owner user

3873 ## of blog_s table.

3874 #err "select quote($col) from $tbl where rowid=$rowid;"

3875 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3876 cat<<-EOF | query

3877 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3878 -- should use EXCLUSIVE transaction outside of this.

3879 SAVEPOINT par2table;

3880 $sql

3881 update blog_s set val=$val

3882 where key='owner' and val=$old;

3883 RELEASE SAVEPOINT par2table;

3884 EOF

3885 ## XX: DIRTY Hack Ends here

3886 ## We should keep blog's owner as a single column which has

3887 ## foreign key constraint with primary key of grp/user.

3888 else

3889 query "$sql"

3890 fi

3891 done

3892 # Then, set up $pval for further insertion of tbl_s and tbl_m

3893 for col in `gettblpkey $tbl`; do

3894 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3895 pval="$pval${pval:+, }\"$val\""

3896 done

3897 else

3898 # New entry

3899 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3900 # Avoid empty repost of article.

3901 if [ x"$tbl" = x"article" ]; then

3902 # If rowid is empty and ID exists in article-table, that is REPOST!

3903 aid=`getpar id`

3904 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3905 if [ -n "$xaid" ]; then

3906 # REPOST of article

3907 html p <<-EOF

3908 書き込み直後のリロードなので上書きを回避します。

3909 最新記事は末尾の「再読み込み」ボタンから見てください。

3910 EOF

3911 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3912 return 9 # STOP Duplicated posting

3913 fi

3914 fi

3915 # Generate values() for primary keys

3916 for col in `gettblpkey $tbl`; do

3917 # Genuine primary keys for _m and _s

3918 val=`getvalquote $tbl $col`

3919 [ -z "$val" ] && continue

3920 pval="$pval${pval:+, }$val"

3921 done

3922 ##err pval=$pval

3923 for col in `gettblfkey $tbl`; do

3924 # args for values() to insertion into master table

3925 val=`getvalquote $tbl $col`

3926 [ -z "$val" ] && continue

3927 formaster=$formaster"${formaster:+, }$val"

3928 done

3929 formaster="$pval${formaster:+, }$formaster"

3930 ## err formaster=$formaster

3931 if [ -z "$formaster" ]; then

3932 echo "項目を全て埋めてください" | html pre

3933 return 1

3934 fi

3935 ## err "replace into $tbl values($formaster);"

3936 query "replace into $tbl values($formaster);"

3937 ## Insertion to master table, done

3938 fi

3939

3940 transaction=$tmpd/sqlfile.sql; touch $transaction

3941 for kt in s m; do

3942 tb2=${tbl}_$kt

3943 for col in `gettbl_${kt}_cols $tbl`; do

3944 ptype=`getpartype $col "limit 1"`

3945

3946 # First, check update of existing entries in _m

3947 if [ $kt = m ]; then

3948 # sessID|address.1.22|string|Somewhere-x.y.z

3949 sql=""

3950 ##err dots from query "select var from par where var like '$col.%';"

3951 for v in `query "select var from par where var like '$col.%';"`; do

3952 # v=address.1.22

3953 st_rowid=${v##*.}

3954 origcol=${v%%.*} # original column derived from

3955 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3956 ##case `getpartype $v` in

3957 ## err CASE `gettbl_coltype $tbl/$origcol` in

3958 ## err edit flag = `getpar action.$v`

3959 case `getpar action.$v` in

3960 rm)

3961 if [ x`getpar confirm.$v` = x"yes" ]; then

3962 newsql="delete from $tb2"

3963 else

3964 echo "削除確認未チェック" | html p

3965 fi ;;

3966 edit)

3967 case `gettbl_coltype $tbl/$origcol` in

3968 image|document|binary)

3969 file=$tmpd/`getparfilename $v`

3970 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3971 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3972 continue

3973 fi

3974 ## err type=file=$file

3975 [ -z "$file" ] && continue

3976 bn=`sqlquotestr "${file##*/}"`

3977 bin="X'"$(hexize "$file")"'"

3978 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3979 type=\"file:$ct\"

3980 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3981 cachedir=`getcachedir "$tbl/$rowid"`

3982 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3983 rm -rf $cachedir

3984 ;;

3985 *)

3986 newsql="update $tb2 set val=(select val from par where var \

3987 like '$col.%.$st_rowid')"

3988 ;;

3989 esac

3990 ;;

3991 *) # maybe "keep", do not modify value

3992 continue

3993 ;;

3994 esac

3995 # err newsql=$newsql

3996 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3997 done

3998

3999 if [ x"$bin" = x"NULL" ]; then

4000 ## err repl:normal sql=`echo $sql`

4001 if [ -n "$transaction" ]; then

4002 cat<<-EOF >> $transaction

4003 $sql

4004 DELETE FROM $tb2 WHERE type='string' AND val='';

4005 EOF

4006 else

4007 query "$sql

4008 delete from $tb2 where type='string' and val='';"

4009 ## err repl:normal done

4010 fi

4011 else

4012 # Binary update line is TOO LONG to pipelining

4013 sqlfile="$tmpd/sqlf.$$"

4014 if [ -n "$transaction" ]; then

4015 echo "$sql" >> $transaction

4016 else

4017 echo "$sql" > $sqlfile

4018 query ".read $sqlfile"

4019 fi

4020 fi

4021 # Rest of kt==m: set multiple mode

4022 nr=`getparcount $col`

4023 else

4024 nr=1 # for kt==s, number of records is 1

4025 fi

4026

4027 i=0

4028 while [ $i -lt $nr ]; do

4029 limit="limit 1 offset $i"

4030 i=$((i+1)) # increase beforehand against continue

4031 val=`getvalquote $tbl $col "$limit"`

4032 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4033 ## err $col=$val

4034 bin=NULL

4035 ## err partype$col=`getpartype $col "$limit"`

4036 case $ptype in

4037 file) file=$tmpd/`getparfilename $col "$limit"`

4038 ## err parfile-$col=$file

4039 [ -z "$file" ] && continue

4040 bin="X'"$(hexize "$file")"'"

4041 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4042 type=\"file:$ct\" ;;

4043 "*"*) continue ;; # foreign table

4044 *) type=\"string\" ;;

4045 esac

4046 case `gettbl_coltype $tbl/$col` in

4047 password) # special care for password

4048 # name={password,pswd1,pswd2}

4049 p1=`getpar pswd1 "$limit"`

4050 if [ -z "$p1" ]; then

4051 continue # SKIP password setting, if p1 is empty

4052 else

4053 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4054 ## err pswd=$pswd

4055 if pwcheck "$pswd"; then

4056 if [ x"$p1" = x"$p2" ]; then

4057 case "$p1" in

4058 ??????????*) ;;

4059 *) echo "パスワードは10字以上にしてください。" | html p

4060 return 6;;

4061 esac

4062 val="\"`echo $p1|mypwhash`\""

4063 else

4064 echo "2つの新パスワード不一致" | html p

4065 return 7

4066 fi

4067 else

4068 echo "旧パスワード違います" | html p

4069 return 8

4070 fi

4071 fi

4072 ;;

4073 esac

4074 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4075 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4076 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4077 if [ x"$bin" = x"NULL" ]; then

4078 ## err Normal-query: `echo $sql`

4079 if [ -n "$transaction" ]; then

4080 echo "$sql" >> $transaction

4081 else

4082 query "$sql"

4083 fi

4084 else

4085 sqlfile="$tmpd/query.$$"

4086 ## err sqlfile=`ls -lF $sqlfile`

4087 if [ -n "$transaction" ]; then

4088 echo "$sql" >> $transaction

4089 else

4090 echo "$sql" > $sqlfile

4091 query ".read $sqlfile"

4092 fi

4093 fi

4094 ## err p2t done

4095 done

4096 done

4097 done

4098 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4099 -- We cannot use transaction here, because groupupdate may use it.

4100 SAVEPOINT pa2table_insert;

4101 .read $transaction

4102 RELEASE SAVEPOINT pa2table_insert;

4103 EOF

4104 rc=$?

4105 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4106 ## err "Table:$tbl update done "

4107 return $rc

4108 )

4109 genform() {

4110 # $1 = form definition file

4111 # $2, $3 (optional)= table name and ROWID

4112 # If $GF_VIEWONLY set and nonNull, output values without form

4113 # If $GF_ARGS set, use it as content-strings in the form

4114 # If $GF_OWNER set, use it as value of name="owner"

4115 # If $GF_STAGE set, use it as value of name="stage"

4116 forms="" hiddens="" rowid=$3

4117 if [ ! -e "$1" ]; then

4118 echo "そのようなデータベースはないようです($2)。" | html p

4119 return

4120 elif [ -n "$2" ]; then

4121 rec=`query "select * from $2 where rowid='$rowid';"`

4122 if [ -z "$rec" ]; then

4123 pk=`gettblpkey $2`

4124 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4125 rec=`query "select rowid from $2 where $pk='$rowid';"`

4126 rowid=$rec

4127 rec=$3

4128 fi

4129 if [ -z "$rec" ]; then

4130 echo "そんなレコードはないみたいね..." | html p

4131 return

4132 fi

4133 fi

4134 if [ -z "$GF_VIEWONLY" ]; then

4135 rm='<input id="rm" name="rm" type="checkbox"

4136 value="yes"><label for="rm">このエントリの削除</label>

4137 <span>ほんとうに消しますよ(確認)!

4138 <input name="confirm" type=checkbox value="yes">はい</span>'

4139 fi

4140 # Image Cache dir

4141 ## err genform: getcache=$2/$rowid

4142 td=`getcachedir "$2/$rowid"`

4143 while IFS=: read prompt name keytype type args; do

4144 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4145 sp="${args:+ }"

4146 form="" val=""

4147 if [ -n "$rowid" ]; then

4148 # err genform2a: Seeking for "$2.$name, type=$type"

4149 rawval=`getvalbyid $2 $name $rowid $td`

4150 val=`echo "$rawval"|htmlescape`

4151 ## err genform3a: getvalbyid $2 $name $rowid $td

4152 ## err genform3b: val="[$val]" type="$type"

4153 fi

4154 if [ -n "$GF_VIEWONLY" ]; then

4155 is_hidden "$2" "$name" && continue

4156 fi

4157 case "$type" in

4158 text*)

4159 cgiform=cgi_multi_$type

4160 if [ -s $td/$name.count -a -n "$val" ]; then

4161 form=`$cgiform $name $td`

4162 val=$(echo "$val"|

4163 while read fn; do

4164 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4165 </td></tr>$nl"

4166 done)

4167 val="<table>$nl$val$nl</table>"

4168 else

4169 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4170 form=`cgi_$type $name "$rawval" "$args"`

4171 fi

4172 ;;

4173 [Rr][Aa][Dd][Ii][Oo])

4174 fh="<label><input type=\"radio\" name=\"$name\""

4175 form="`echo $args|sed -e \

4176 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4177 ;;

4178 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4179 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4180 ;;

4181 [Ss][Ee][Ll][Ee][Cc][Tt])

4182 fh="<select name=\"$name\">$nl"

4183 form=$(for l in $args; do

4184 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4185 done)

4186 if [ -n "$val" ]; then

4187 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4188 fi

4189 form="$fh$form</select>"

4190 ;;

4191 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4192 if [ -s $td/$name.count ]; then

4193 form=`cgi_multi_file $name $td "$args"`

4194 if [ -n "$val" ]; then

4195 hrfb="$myname?showattc+$2_m"

4196 val=$(echo "$rawval" \

4197 | while read fn; do

4198 data=`percenthex "$td/$fn"`

4199 #ct=`cat $td/$fn.content-type`

4200 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4201 ri=`cat "$td/$fn.rowid"`

4202 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4203 #imgsrc="<img src=\"data:$ct,$data\">"

4204 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4205 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4206 done)

4207 fi

4208 else

4209 form="<input type=\"file\" name=\"$name\" $args>"

4210 if [ -n "$val" ]; then

4211 imgs=$(echo "$rawval"\

4212 |while read fn;do

4213 data=`percenthex "$td/$fn"`

4214 echo "<img src=\"data:image/png,$data\">$fn<br>"

4215 done)

4216 form=$form"<br>$imgs"

4217 val=$imgs # 2015-06-15

4218 else

4219 form="<input type=\"file\" name=\"$name\" $args>"

4220 fi

4221 fi

4222 ;;

4223 [Hh][Ii][Dd][Dd][Ee][Nn])

4224 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4225 args="value=\"$GF_STAGE\""

4226 fi

4227 form="<input type=\"hidden\" name=\"$name\" $args>"

4228 prompt='' # Remove prompt

4229 ;;

4230 [Aa][Uu][Tt][Hh][Oo][Rr])

4231 [ -n "$GF_VIEWONLY" ] && continue

4232 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4233 prompt="" ;;

4234 [Oo][Ww][Nn][Ee][Rr])

4235 [ -n "$GF_VIEWONLY" ] && continue

4236 val=${GF_OWNER:-$val}

4237 val=${val:-$user}

4238 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4239 prompt="" ;;

4240 [Uu][Ss][Ee][Rr])

4241 # XXX: is null $user ok?

4242 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4243 [ -n "$GF_VIEWONLY" ] && continue

4244 form="$user"

4245 ;;

4246 [Pp]assword)

4247 [ -n "$GF_VIEWONLY" ] && continue

4248 form="`cgi_passwd`"

4249 val=""

4250 ;;

4251 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4252 [ -n "$GF_VIEWONLY" ] && continue

4253 if [ -z "$rowid" ]; then

4254 val=`genserial`

4255 fi

4256 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4257 prompt="" ;;

4258 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4259 prompt=""

4260 ;;

4261 parent|path|blog*)

4262 prompt=""

4263 ;;

4264 "*"*)

4265 tail=$tail"``"

4266 continue ;;

4267 esac

4268 if [ -n "$prompt" ]; then

4269 if [ -n "${GF_VIEWONLY}" ]; then

4270 form=$val

4271 else

4272 :

4273 fi

4274 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4275 else

4276 hiddens=$hiddens$nl"$form"

4277 fi

4278 done < $1

4279 # enctype="multipart/form-data"

4280 cat<<EOF

4281 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4282 ${rowid:+$rm}

4283 <table class="b $2">

4284 $forms

4285 </table>$hiddens

4286 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4287 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4288 EOF

4289 if [ -z $GF_VIEWONLY ]; then

4290 cat<<EOF

4291 <input type="submit" name="sub" value="OK">

4292 <input type="reset" name="res" value="Reset">

4293 EOF

4294 fi

4295 cat<<EOF

4296 $GF_ARGS</form>

4297 $tail

4298 EOF

4299 }

4300 edittable() {

4301 # $1=form-def $2=table $3 rowid

4302 genform "$@"

4303 }

4304 viewtable() {

4305 GF_VIEWONLY=1 genform "$@"

4306 }

4307 showattc() {

4308 # $1=table_m $2=rowid &optional $3=RawFlag

4309 ## err \$1=$1 \$2=$2 \$3=$3

4310 if ! isfilereadable $user $1 $2; then

4311 contenttype; echo

4312 echo "このファイルは管理者のみしか見られません" | html p

4313 putfooter; exit

4314 fi

4315 idir=`umask 002; mktempd` || exit 1

4316 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4317 bin=$idir/$myname-$$.bin

4318 sql="select quote(bin) from $1 where rowid='$2';"

4319 ## err showattc: sql: $sql

4320 sq $db "$sql" | unhexize > $bin

4321 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4322 type=${tv%//*} fn=${tv#*//}

4323 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4324 ct=${type#file:}

4325 case $ct in # all text/* changed to text/plain

4326 text/*)

4327 charset=`nkf -g $bin|cut -d' ' -f1`

4328 case $charset in

4329 ASCII*) charset="" ;;

4330 esac

4331 if [ -z "$3" ]; then

4332 ct="text/html${charset:+; charset=$charset}"

4333 link="?showattc+$1+$2+raw"

4334 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4335 | sed 's,^,<span></span>,' \

4336 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4337 -D_LINK_="$link" \

4338 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4339 exit $?

4340 fi

4341 ct="text/plain${charset:+; charset=$charset}"

4342 ;;

4343 esac

4344 contenttype "$ct"

4345 echo "Content-Disposition: filename=\"$fn\""

4346 echo "Content-Length: " `cat $bin | wc -c`; echo

4347 #echo "Content-Type: " ${type#file:}; echo

4348 cat $bin

4349 }

4350 #

4351 # Some default stupid handler on CGI values

4352 #

4353 default_storedb() {

4354 # ARG: $1=table-def-file

4355 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4356 tbl=`basename $1`

4357 tbl=${tbl%.def}

4358 cols="`grep :text $1|cut -d: -f2`"

4359 col=`echo "$cols"|head -1`

4360 vcol=`getpar $col`

4361 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4362 if [ -n "$vcol" ]; then

4363 par2table $1

4364 else

4365 return 2 # No insertion occurred

4366 fi

4367 }

4368

4369 default_view() { # $1=def-file

4370 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4371 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4372 default_storedb "$@"

4373 query "select rowid from $tbl order by rowid desc;" \

4374 | while read rowid; do

4375 viewtable $1 $tbl $rowid

4376 done | _m4 -D_TITLE_="$tbl" \

4377 -D_FORM_="`genform $1`" \

4378 -D_DUMPTABLE_="syscmd(cat)" \

4379 $layout/html.m4.html $layout/form+dump.m4.html

4380 }

4381 default_viewtext() { # $1=def-file

4382 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4383 default_storedb "$@"

4384 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4385 | _m4 -D_TITLE_="$tbl" \

4386 -D_FORM_="`genform $1`" \

4387 -D_DUMPTABLE_="syscmd(cat)" \

4388 $layout/html.m4.html $layout/form+dump.m4.html

4389 }

4390 default_smail() {

4391 default_storedb "$@"

4392 if [ $? -eq 2 ]; then

4393 _m4 -D_TITLE_="入力" \

4394 -D_FORM_="`genform $1`" \

4395 -D_DUMPTABLE_="" \

4396 $layout/html.m4.html $layout/form+dump.m4.html

4397 return

4398 fi

4399 cond=""

4400 for pk in `gettblpkey $tbl`; do

4401 pv=$(sqlquote "$(getpar $pk)")

4402 cond="$cond${cond:+ and }$pk=$pv"

4403 done

4404 sql="select rowid from $tbl where $cond;"

4405 rowid=`query "$sql"`

4406 ## err smail1 - "$sql" "-> rowid=$rowid"

4407

4408 while IFS=: read prompt name keytype type args; do # Read from $1

4409 val=`getpar $name`

4410 if [ -n "$val" ]; then

4411 text="$text

4412 $prompt

4413 $name=$val

4414 ---------------------------------------------------------"

4415 fi

4416 case "$type" in

4417 image|document|file)

4418 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4419 fns=$(echo "$fn"|while read fn; do

4420 err mv $tmpd/$fn.orig $tmpd/$fn

4421 mv $tmpd/$fn.orig $tmpd/$fn

4422 rm $tmpd/$fn.rowid # Remove cache flag

4423 ## err "`ls $tmpd/$fn`"

4424 echo $fn

4425 done)

4426 files="$files $fns"

4427 ;;

4428 esac

4429 done < $1

4430 ## err FILES=$files "`ls -lF $tmpd`"

4431 subj="from ${REMOTE_ADDR}"

4432 (echo "$url"

4433 echo "への書き込みがありました。"

4434 echo "------"

4435 echo "$text"

4436 ) | (cd $tmpd &&

4437 err LS="`ls -lF`" &&

4438 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4439 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4440 echo "以下の内容で送信しました。" | html p

4441 viewtable $1 $tbl \

4442 `query "select rowid from $tbl order by rowid desc limit 1;"`

4443 echo "戻る" | html a "href=\"?\""

4444 }