s4: 369602864de8 s4-funcs.sh

s4

view s4-funcs.sh @ 758:369602864de8

Clean-up code to remove orphaned blogs and articles

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 06 Jun 2020 16:33:23 +0900
parents	2d45b0478d45
children	3f0ef9bb1776

line source

1 #!/bin/sh

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

3 # $HGid$

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

6 if $isCGI; then

7 case "$0" in

8 *-world-*)

9 S4WORLD=${0#*world-}

10 S4WORLD=${S4WORLD%.*}

11 echo S4WORLD=$S4WORLD >&2

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

13 ;;

14 esac

16 echo worldconf=$worldconf >&2

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

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

19 echo DB=$DB >&2

20 fi

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

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

23 myargs="$@"

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

25 tmpdir=${TMPDIR:-tmp}

26 dbdir=${DBDIR:-db}

27 tmpfiles=""

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

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

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

31 sessdb=$dbdir/sess.sq3

32 userupdateflag=$dbdir/userupdate

33 sesstb=tmp.sess

34 workdb=$dbdir/tmpdata.sq3

35 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

40 templ=${TEMPL:-templ}

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

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

43 imgdir=${IMGDIR:-img}

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

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

46 msgdir=$templ/msg

47 timeout="+2 days"

48 memoplimitdays="7"

49 dumpcollen=22

50 #thumbxy=120x120

51 thumbxy=96x96

52 iconxy_S=80x80

53 iconxy_M=400x400

54 maximagexy=1600x1600

55 ### maximagexy=400x400

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

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

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

59 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"'

61 file_accept_help="

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

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

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

65 "

66 file_warn="$file_accept_help

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

68 blogreadflagrowid=0

69 blogcutoffflagrowid=-1

70 whatsnewdays=${WHATS_NEW_DAYS:-14}

72 tconfs=""

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

74 conftbl=_tblconf

75 nl="

76 "

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

78 iconcachekey="profimgcache_S"

80 # Start debug logging

81 exec 3>> $tmpdir/debug.out

82 err() {

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

84 }

85 case "$HTTP_USER_AGENT" in

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

87 *) touchpanel="" ;;

88 esac

90 # If S4MASTERDB is set, behave in another world

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

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

93 fi

94 v=""

95 for i in $S4WORLDS; do

96 _aname=${i%:*}; _apath=${i##*:}

97 v=${v}"<tr><td>⇒ <a href=\"$_apath\">$_aname</a></td></tr>$nl"

98 done

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

100 err S4MASTERURL=$S4MASTERURL

101 i="<tr><th>World List</th></tr>$nl"

102 if [ -n "$S4MASTERURL" ]; then

103 i="$i<tr><td>⇒ <a href=\"$S4MASTERURL\">Base</a></td></tr>$nl"

104 fi

105 S4WORLDS="${touchpanel:+ : ▼}<div><table>$i$v</table></div>"

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 [ -n "$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 -D_S4WORLDS_="${S4WORLDS}" "$@"

465 }

466 ismember() {

467 # $1=user, $2=group

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

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

470 }

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

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

473 }

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

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

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

477 }

478 isgrpowner() (

479 # $1=user, $2=group

480 gn=`sqlquote "$2"`

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

482 err isgrpowner: $sql

483 test -n "`query $sql`"

484 )

485 isgrpownerbygid() (

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

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

488 err isgrpownerbygid: $sql

489 test -n "`query $sql`"

490 )

491 getgroupadminmails() {

492 # $1=group

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

494 email4group "$1" "$i" ;

495 done

496 }

497 getgroupadmins() { # $1=group

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

499 qgrp=`sqlquote "$1"`

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

501 }

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

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

504 getvalbyid grp $2 \

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

506 }

507 getgroupbyid() {

508 # $1=id|gname

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

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

511 # err ggbyid: `echo $sql`

512 query $sql

513 }

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

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

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

517

518 # Return true when anonymous mode

519 [ "$anonymousmode" ] && return 0

520 # case `getvalbyid blog mode $2` in

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

522 # *closed*)

523 # owner=`getvalbyid blog owner $2`

524 # if isgrp $owner; then

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

526 # elif isuser $owner; then

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

528 # fi

529 # esac

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

531 sql="with getblog as (

532 select key,val from blog_s where id=(

533 select blogid from article where id in

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

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

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

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

538 isgrpadm as (select user from grp_adm where

539 gname=(select val from getowner) and

540 user='$1'),

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

542 select case

543 when (select author from article where

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

545 then 'author'

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

547 then 'open'

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

549 then CASE

550 WHEN (SELECT val FROM isgrp) IS NULL

551 THEN

552 CASE WHEN (SELECT val from getowner)

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

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

555 ELSE ''

556 END

557 WHEN (select user from isgrpadm) IS NOT NULL

558 THEN 'i-am-admin'

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

560 END

561 when (select val from getmode) is null

562 then 'open'

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

564 then (SELECT user FROM isgrpadm)

565 when (select author from article where

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

567 then 'user+author'

568 else '' end;"

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

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

571 # result=`query "$sql"`

572 # err FileAccessibility=$result

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

574 }

575 linkhome() {

576 # $1=UserOrGroup

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

578 if isuser $1; then

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

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

581 else

582 echo -n "grp+$1"

583 fi

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

585 }

586 hreflink() {

587 # s4 specific notation:

588 # ^href=URL

589 # ^iframe=URL

590 # ^video=URL

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

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

593 # OSM umap Wikistyle Notation:

594 # [[URL]] - Simle Link

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

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

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

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

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

600 # Other Style

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

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

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

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

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

606 # SPC+SPC+$ - <br>

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

608 checkboxON="${cb} checked>"

609 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

634

635 }

636 minitbl() {

637 sed -n '

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

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

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

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

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

643 H; # Concat this line to HoldSpace

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

645 $ b cont

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

647 }

648 :cont

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

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

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

652 # . of ^. is workaround for FreeBSD sed

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

654 p; # Print whole "table" element

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

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

657 }

658 x; # Back to the newest line

659 p; # Print rest' | miniul

660 }

661 miniul() {

662 sed -e '

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

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

665 :top

666 s/\n//;

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

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

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

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

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

672 $ b cont

673 N; # 次の行を読む

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

675 /^ /b top

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

677 p; s/.*//;

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

679 /^\* /b top

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

681 }

682

683 :cont

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

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

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

687 H; x

688 }

689 x'

690 }

691 acclog() (

692 # $1=table, $2=rowid

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

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

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

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

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

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

699 fi

700 )

701 gecos() (

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

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

704 )

705 setpar() {

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

707 case "$1" in

708 user) _user="$v" ;;

709 skey) _skey="$v" ;;

710 esac

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

712 }

713 replpar() {

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

715 }

716 getpar() {

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

718 val=""

719 if false; then # 2020/6/3 Temporary disabled for joingrp()

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

721 user) val=$_user ;;

722 skey) val=$_skey ;;

723 esac

724 fi

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

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

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

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

729 fi

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

731 case "$var" in

732 owner)

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

734 echo $user; return

735 elif ismember $user $val; then

736 echo $val; return

737 fi ;;

738 esac

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

740 echo "$val"

741 }

742 setskey() {

743 # For quick response...(?)

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

745 }

746 chkskey() {

747 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

753 return 0

754 fi

755 return 1

756 }

757 resetskey() {

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

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

760 fi

761 }

762 getpartype() {

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

764 }

765 getparcount() {

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

767 }

768 getparfilename() {

769 # null if type of $1 is not file

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

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

772 }

773 sqlquote() {

774 (v="$1"

775 case "$v" in

776 "") return ;; # null

777 "X'"*) # quoted hex string

778 echo $1 ;;

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

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

781 echo "\"$v\""

782 return ;;

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

784 echo "\"$v\""

785 return ;;

786 *)

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

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

789 else

790 echo "\"$v\""

791 fi ;;

792 esac)

793 }

794 sqlquotestr() (

795 case "$1" in

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

797 echo "'$v'" ;;

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

799 esac

800 )

801 mktempd() {

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

803 }

804 getcachedir() { # $1=maintable

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

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

807 else

808 echo $tmpd/$thumbxy

809 fi

810 }

811 getval() {

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

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

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

815 echo "$user" ;;

816 stamp|datetime)

817 date "+%F %T" ;;

818 serial)

819 (s=`getpar $2`

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

821 *)

822 getpar "$2" "$3";;

823 esac

824 }

825

826 getvalquote() {

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

828 (v=`getval "$@"`

829 case "$v" in

830 "") echo NULL ;;

831 *) sqlquote "$v" ;;

832 esac)

833 }

834 getparquote() {

835 sqlquote "`getpar $1`"

836 }

837 getbinbyid() {

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

839

840 }

841 getvalbyid() {

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

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

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

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

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

847

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

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

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

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

852 return

853 fi

854 done

855 rowid=$3

856 pk=`gettblpkey $1`

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

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

859 td=${4:-$tmpd}

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

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

862 for kt in s m; do

863 t=${1}_$kt

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

865 vcount=1 # count(val)

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

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

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

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

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

871 if [ $kt = m ]; then

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

873 # Reset val to store filenames if type is string

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

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

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

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

878 i=0

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

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

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

882 i=$((i+1))

883 fn=$c.$i

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

885 case $type in

886 file:*)

887 #file=$td/$val

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

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

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

891 # FOR SPEED: Skip file generation if imgcache exists

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

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

894 && continue

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

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

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

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

899 select rowid from $cond $slice;

900 .output '$td/$fn'

901 select val from $cond $slice;

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

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

904 .output stdout

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

906 EOF

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

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

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

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

911 ls -lh "$file" |

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

913 case "$type" in

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

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

916 *|*:[Aa]pplication*)

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

918 png:- > "$file"

919 ;;

920 esac

921 ;;

922 *)

923 sq $db<<EOF

924 .output $td/$fn.rowid

925 select rowid from $cond $slice;

926 .output $td/$fn

927 select val from $cond $slice;

928 EOF

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

930 ;;

931 esac

932 done

933 else

934 rm -f $td/$c.count

935 case $type in

936 file:*)

937 echo "$val" \

938 | while read fn; do

939 file=$td/$fn

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

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

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

943 | unhexize > "$file"

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

945 case $type in

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

947 *:[Aa]pplication*)

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

949 png:- > $file ;;

950 esac

951 fi

952 done

953 ;;

954 esac

955 fi

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

957 return

958 fi

959 done

960 done)

961 }

962 getvalbypkey() (

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

964 pk=`gettblpkey $1`

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

966 getvalbyid "$1" "$2" $rowid $4

967 )

968 getvalbycond() {

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

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

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

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

973 getvalbyid "$1" "$2" $rowid "$4"

974 fi

975 }

976 getpwfield() {

977 # getpwfield user column

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

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

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

981 echo "$val"

982 return 0

983 else

984 return 1

985 fi

986 }

987 numericalize() {

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

989 }

990 encode() {

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

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

993 sha1=sha1

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

995 sha1=sha1sum

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

997 sha1=gsha1sum

998 fi

999 fi

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

1001 }

1002 enjpeg() {

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

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

1005 cjpeg="cjpeg"

1006 else

1007 cjpeg="convert - jpeg:-"

1008 fi

1009 fi

1010 $cjpeg "$@"

1011 }

1012 mycrypt() (

1013 key=$1 salt=$2

1014 # err \$2=$2

1015 case $2 in

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

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

1018 esac

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

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

1021 )

1022 hexize() {

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

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

1025 hexize="xxd -p"

1026 else

1027 hexize_hd() {

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

1029 }

1030 hexize="hexize_hd"

1031 fi

1032 fi

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

1034 }

1035 unhexize() {

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

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

1038 unhex="xxd -p -r"

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

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

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

1042 EOF

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

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

1045 # world writable...:)

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

1047 fi

1048 fi

1049 cat "$@" | $unhex

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

1051 }

1052 percenthex() {

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

1054 }

1055 htmlescape() {

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

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

1058 }

1059 enascii() {

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

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

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

1063 else

1064 enascii_now=`date +%FT%T`

1065 enascii_sed() {

1066 nkf -Z0Z1Z2 \

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

1068 }

1069 enascii="enascii_sed"

1070 fi

1071 fi

1072 cat "$@" | $enascii

1073 }

1074 size_h() {

1075 i="$1" oi=$1

1076 set -- B B KB MB GB TB

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

1078 oi=$i

1079 i=$((i/1024))

1080 shift

1081 done

1082 echo ${oi}$1

1083 }

1084 gettblconf() {

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

1086 ## tconfs=`sq $db \

1087 tconfs=`query \

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

1089 fi

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

1091 }

1092 gettblkeys() {

1093 # $1=tbl

1094 gettblconf

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

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

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

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

1099 case $type in

1100 *p*)

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

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

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

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

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

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

1107 esac

1108 case $type in

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

1110 esac

1111 done

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

1113 }

1114 gettblpkey() {

1115 # $1=tbl

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

1117 }

1118 gettblfkey() {

1119 (x=`gettblkeys $1`

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

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

1122 }

1123 gettblcols() {

1124 (x=`gettblkeys $1`

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

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

1127 }

1128 gettbl_s_cols() {

1129 (x=`gettblkeys $1`

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

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

1132 }

1133 gettbl_m_cols() {

1134 (x=`gettblkeys $1`

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

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

1137 }

1138 gettbl_h_cols() {

1139 (x=`gettblkeys $1`

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

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

1142 }

1143 gettbl_coltype() (

1144 gettblconf

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

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

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

1148 )

1149 is_hidden() {

1150 # $1=Tbl $2=col

1151 gettblconf

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

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

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

1155 case $x in

1156 *h*) return 0 ;;

1157 *) return 1 ;;

1158 esac

1159 }

1160

1161 dbsetbyid() {

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

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

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

1165 quotedp=$(sqlquotestr "$p")

1166 unset primary update

1167 gettblconf

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

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

1170 #err conf=$conf

1171 case ${conf#*=} in

1172 p*) primary=1 ;;

1173 f*) update=1 ;;

1174 u*) ;;

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

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

1177 esac

1178 #err t=$t

1179 type=string fn=""

1180 case $conf in

1181 */password)

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

1183 ;;

1184 */image*|*/document*)

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

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

1187 ;;

1188 esac

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

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

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

1192 if [ "$primary" ]; then

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

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

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

1196 elif [ "$update" ]; then

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

1198 else

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

1200 fi

1201 )

1202 }

1203 expire() (

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

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

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

1207 )

1208 addsession() {

1209 # expireをセット

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

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

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

1213 # Remove old session parameters

1214 now=`expire now`

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

1216 }

1217 gencookie() (

1218 path=${URL#*:/}

1219 path=${URL%/*}

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

1221 for kv; do

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

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

1224 done

1225 )

1226 contenttype() {

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

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

1229 }

1230 putheader() {

1231

1232 }

1233 putfooter() {

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

1235 }

1236 getcookie() {

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

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

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

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

1241 case "$k" in

1242 user) _user="$v" ;;

1243 skey) _skey="$v" ;;

1244 esac

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

1246 done

1247 }

1248 genrandom() {

1249 # $1=columns (default: 10)

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

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

1252 }

1253 genserial() {

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

1255 }

1256 smail() {

1257 # smail rcpts subj (file)

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

1259 # $MAIL_FROM <- From: header value

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

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

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

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

1264 sender=${SENDER:-$admin}

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

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

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

1268 }

1269 smail_queue_flush() {

1270 # $1=timelimit

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

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

1273 SELECT rowid FROM smailq

1274 GROUP BY rcpts, subj

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

1276 EOF

1277 )

1278 for rid in $rowids; do

1279 sq $workdb \

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

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

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

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

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

1285

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

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

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

1289 .separator "\n" "------------------\n\n"

1290 SELECT time, text FROM smailq

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

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

1293 ORDER by time;

1294 EOF

1295 then

1296 echo rowid=$rid

1297 cat <<-EOF | sq $workdb

1298 DELETE FROM smailq

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

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

1301 EOF

1302 fi

1303 fi

1304 done

1305 }

1306 smail_queue() {

1307 # $1=Rcpts, $@=subj

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

1309 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1318 EOF

1319 )

1320 ## XXX: Adhoc load mitigation

1321 case "$now" in

1322 *[01])

1323 err flush_queue=$mintime

1324 smail_queue_flush ;;

1325 esac

1326 fi

1327 }

1328 setviastring() {

1329 table=par

1330 oifs="$IFS"

1331 IFS="&"

1332 for us in $1; do

1333 k=${us%%=*}

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

1335 setpar "$k" "string" "$v"

1336 done

1337 IFS="$oifs"

1338 }

1339 checkdomain() (

1340 # Check the validity of domain by referring DNS

1341 item=$1

1342 err checkdomain $1

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

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

1345 )

1346 pwcheck() {

1347 # $1=passwd

1348 dbpswd=`getpwfield $user pswd`

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

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

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

1352 }

1353 mypwhash() {

1354 mycrypt "`cat`" `genrandom 5`

1355 }

1356 wasureta() {

1357 user=$1

1358 if ! checkdomain $user; then

1359 contenttype; echo

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

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

1362 putfooter

1363 exit 0

1364 fi

1365 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1367 encpswd=`echo $newpswd|mypwhash`

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

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

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

1371 $msgdir/mail-newaccount.m4 \

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

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

1374 }

1375 checkauth() {

1376 user=`getpar user`

1377 skc=`getpar skey` # from cookie

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

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

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

1381 if chkskey "$skc"; then

1382 return 0

1383 fi

1384 fi

1385 pswd=`getpar pswd`

1386 quser=`sqlquotestr "$user"`

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

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

1389 return 4 # 4=server too heavy

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

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

1392 return 2 # 2=login fail

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

1394 wasureta "$user"

1395 return 1 # wasureta error

1396 fi

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

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

1399 if pwcheck "$pswd"; then

1400 newsession=`genrandom 34`

1401 if setskey "$user" "$newsession" &&

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

1403 gencookie "user=$user" "skey=$newsession"

1404 return 0

1405 else

1406 return 4 # Heavy load??

1407 fi

1408 fi

1409 err "Login failed: [$user]"

1410 return 2 # Password mismatch

1411 }

1412 showlogin() {

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

1414 test -z "$args" && resetskey

1415 s4name=${S4NAME:-s4}

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

1417 cat $layout/login.m4.html

1418 echo '</body></html>'

1419 ) | _m4 \

1420 -D_BODYCLASS_="login" \

1421 -D_TITLE_="$s4name" \

1422 -D_SYSNAME_="Welcome to $s4name" \

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

1424 exit 0

1425 }

1426 dologin() {

1427 checkauth

1428 st=$?

1429 if [ $st != 0 ]; then

1430 contenttype; echo

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

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

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

1434 showlogin # and EXIT

1435 fi

1436 }

1437

1438 # Do instant jobs here

1439 dbsetup

1440 trap cleanup INT HUP EXIT TERM PIPE

1441 # trap cleanup INT HUP

1442

1443

1444 cgiinit() {

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

1446 tmpd=`tmpd=$tmpdir mktempd`

1447 tmpf=$tmpd/stream.$$

1448 tmpfiles=$tmpfiles" $tmpd"

1449 addsession $session

1450 getcookie

1451 case "$REQUEST_METHOD" in

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

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

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

1455 # cat > $tmpf # too much?

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

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

1458 s="`cat $tmpf`"

1459 ;;

1460 esac

1461 case "$CONTENT_TYPE" in

1462 *boundary*)

1463 bndry=${CONTENT_TYPE#*boundary=}

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

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

1466 do

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

1468 #echo u=$us

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

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

1471 # err k=$k v=$v

1472 case "$k" in

1473 *:filename)

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

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

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

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

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

1479 :

1480 fi

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

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

1483 case "$mimetype" in

1484 [Ii]mage/x-xcf)

1485 bzip2 "$tmpd/$v"

1486 v=${v}.bz2

1487 ;;

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

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

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

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

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

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

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

1495 v=$vjpg

1496 else

1497 mimetype="Not supported"

1498 fi

1499 ;;

1500 [Ii]mage/*)

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

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

1503 ;;

1504 esac

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

1506 then

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

1508 continue

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

1510 replpar text string \

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

1512 continue

1513 fi

1514 ;;

1515 *)

1516 type='string'

1517 ;;

1518 esac

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

1520 setpar "$k" "$type" "$v"

1521 done

1522 ;;

1523 *)

1524 setviastring "$s"

1525 ;;

1526 esac

1527 }

1528 email4group() {

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

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

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

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

1533 sql="WITH

1534 grpemails AS (

1535 SELECT gname, user, val email

1536 FROM grp_mem NATURAL JOIN grp_mem_s

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

1538 useremails AS (

1539 SELECT user.name, val email

1540 FROM user

1541 LEFT JOIN user_m

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

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

1544 FROM useremails u LEFT JOIN grpemails g

1545 ON u.name=g.user

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

1547 query "$sql"

1548 }

1549 email4groupbyuid() {

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

1551 g=$1; shift

1552 uids=`echo "$@"`

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

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

1555 email4group "$g" `query "$sql"`

1556 }

1557 myemail4group() {

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

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

1560 }

1561 collectmembersbyid() {

1562 # Collect user names of group specified by grid

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

1564 query "SELECT user FROM grp_mem \

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

1566 }

1567 collectmembersbyid() {

1568 # Collect user names of group name

1569 qgrp=`sqlquote "$1"`

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

1571 }

1572 collectgecosesbyid() {

1573 # Collect user gecoses of group

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

1575 query<<-EOF

1576 SELECT gecos

1577 FROM gecoses

1578 WHERE name IN (SELECT user FROM grp_mem

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

1580 EOF

1581 }

1582 collectemail() (

1583 # Collect email addresses for group $1

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

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

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

1587 for e; do

1588 if isuser "$e"; then

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

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

1591 else

1592 qgrp=`sqlquote "$e"`

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

1594 gmem="grp_mem"

1595 else

1596 tm=`sqlquote "$TEAM"`

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

1598 fi

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

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

1601 $gmem g left join grp_mem_s s

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

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

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

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

1606 query "$sql"

1607 fi

1608 done

1609 )

1610 sendinvitation() (

1611 # $1=email

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

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

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

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

1616 gecos=`gecos`

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

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

1619 _m4 -D_URL_="$urlbase" \

1620 -D_USER_="$name" \

1621 -D_EMAIL_="$1" \

1622 -D_REGIST_="$regist" \

1623 -D_ADMIN_="$admin" \

1624 $msgdir/mail-invite.m4 \

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

1626 return 0

1627 )

1628 emaildomaincheck() {

1629 case "$1" in

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

1631 *@*)

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

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

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

1635 return 2

1636 fi

1637 return 0

1638 ;;

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

1640 esac

1641 }

1642 invite() {

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

1644 case "$email" in

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

1646 *@*)

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

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

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

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

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

1652 elif sendinvitation $email; then

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

1654 else # Cannot be reached here

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

1656 fi ;;

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

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

1659 esac

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

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

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

1663 fi

1664 if [ -f $invite_policy ]; then

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

1666 else

1667 pol="$invite_policy"

1668 fi

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

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

1671 -D_INVITE_POLICY_="$pol" \

1672 $layout/html.m4.html $layout/invite.m4.html

1673 }

1674 regist() {

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

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

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

1678 echo "bye bye" | html p

1679 reutrn

1680 fi

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

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

1683 cat<<EOF

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

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

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

1687 EOF

1688 return

1689 fi

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

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

1692 # Fake login password to wasureta

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

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

1695 wasureta $email

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

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

1698 addsession $1 # for removal after 1 minute

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

1700 $layout/login.m4.html

1701 return

1702 }

1703 group_safename() {

1704 # Convert $1 to safe group name

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

1706 }

1707 groupupdate() {

1708 gname=`getpar gname`

1709 qgname=`sqlquote "$gname"`

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

1711 # See ALSO same job in showgroup()

1712 newgname=`group_safename "$gname"`

1713 err newgname=$newgname

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

1715 err NewGNAME: gname=$newgname

1716 gname=$newgname

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

1718 replpar gname string "$gname"

1719 fi

1720 # Name confliction check

1721 parow=`getpar rowid`

1722 ## err parow=$parow

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

1724 query "BEGIN EXCLUSIVE;"

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

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

1727 if [ $count -gt 0 ]; then

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

1729 query "END;"

1730 return

1731 fi

1732 par2table $formdir/grp.def

1733 query "END TRANSACTION;"

1734 # Remove orphan

1735 : <<EOF

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

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

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

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

1740 EOF

1741 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1745 sql="delete from blog where id in

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

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

1748 query "$sql";

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

1750 return # and return

1751 fi

1752 fi

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

1754 fi

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

1756 grid=$(query $sql)

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

1758 grp $grid

1759 }

1760 groupclone() {

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

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

1763 case "$1" in

1764 */noteam)

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

1766 esac

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

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

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

1770 return

1771 fi

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

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

1774 return

1775 fi

1776 i=0

1777 while true; do

1778 copy="-copy$i"

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

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

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

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

1783 i=$((i++))

1784 continue

1785 fi

1786 break

1787 done

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

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

1790 query<<-EOF

1791 BEGIN;

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

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

1794 SELECT $newqname, key, val

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

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

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

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

1799 -- Copy members and their configuration --

1800 REPLACE INTO grp_mem SELECT $newqname, user

1801 FROM grp_mem WHERE gname=$qgrp;

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

1803 FROM grp_mem_s WHERE gname=$qgrp;

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

1805 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1806 -- Copy administrators --

1807 REPLACE INTO grp_adm SELECT $newqname, user

1808 FROM grp_adm WHERE gname=$qgrp;

1809 COMMIT;

1810 EOF

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

1812 STOPCLONEMSG=1 groupconf "$newrowid"

1813 }

1814 groupman() {

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

1816

1817 GF_STAGE="grpconf"

1818 GF_STAGE=groupupdate

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

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

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

1822 -D_DUMPTABLE_="syscmd(cat)" \

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

1824 }

1825 userconf() {

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

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

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

1829 }

1830 groupconf() {

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

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

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

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

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

1836

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

1838 grp=`getgroupbyid $rowid`

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

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

1841 return

1842 fi

1843

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

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

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

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

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

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

1850 <div>

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

1852 <table>

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

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

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

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

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

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

1859 </table>

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

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

1862 </div>

1863 EOF

1864 fi

1865 }

1866 mems() {

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

1868 kwd=`getpar kwd`

1869 listmember $kwd

1870 }

1871 grps() {

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

1873 kwd=`getpar kwd`

1874 listgroup $kwd \

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

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

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

1878 $layout/form+dump.m4.html

1879 }

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

1881 gpg=`getpar grp`

1882 grid=${1:-$gpg}

1883 grp=`getgroupbyid "$grid"`

1884 ## . ./s4-blog.sh

1885 jg=`getpar joingrp`

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

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

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

1889 fi

1890 htmlheader=$layout/html.m4.html

1891 showgroup "$grid"

1892 }

1893 sql4interestblogs() {

1894 cat<<EOF

1895 CREATE TEMPORARY VIEW interestblogs AS

1896 SELECT blog.rowid rid, id, author

1897 FROM blog

1898 NATURAL JOIN

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

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

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

1902 WHEN (SELECT user FROM grp_mem

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

1904 THEN 0

1905 ELSE 1

1906 END;

1907 EOF

1908 }

1909 listnewblogsql() { # $1=user

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

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

1912 newdays=${newdays:-14}

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

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

1915 WHERE user='$user'

1916 AND tblrowid IN

1917 ($blogreadflagrowid,

1918 $blogcutoffflagrowid)),

1919 $basetime -- "0"

1920 );"`

1921 cat<<EOF

1922 `sql4interestblogs`

1923 WITH article_ctime as (

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

1925 FROM article join article_s s using(id)

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

1927 GROUP BY id

1928 ), blog_title_owner as (

1929 SELECT blg.rid brid, id,

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

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

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

1933 ), blogall as (

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

1935 ), news as (

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

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

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

1939 FROM blogall bl

1940 LEFT JOIN

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

1942 ON bl.brid=al.tblrowid

1943 WHERE atime < bl.ctime

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

1945 LIMIT 10

1946 ) SELECT brid LINK, "新着",

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

1948 ctime, title,

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

1950 FROM news;

1951 EOF

1952 }

1953

1954 search_form() {

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

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

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

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

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

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

1961 @week → 最近一週間

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

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

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

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

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

1967 auth=""

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

1969 case "$1" in

1970 author=*)

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

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

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

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

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

1976 ;;

1977 grp=*)

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

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

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

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

1982 ;;

1983 esac

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

1985 cat<<-EOF

1986 <div class="right">

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

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

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

1990

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

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

1993

1994 </form>

1995 </div>

1996 EOF

1997 }

1998

1999 imgsrc_cache() (

2000 # $1 = directory for cache'ing

2001 # $2 = table (user_m or grp_m)

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

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

2004 dir="$1" tbl="$2"

2005 keycond="$3"

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

2007 ORDER BY rowid DESC LIMIT 1"

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

2009 tmpf=$tmpd/imgsrc_cache.$$

2010 case "$4" in

2011 [Ss]) size=S ;;

2012 [Oo]) size=O ;;

2013 *) size=M ;;

2014 esac

2015 # ImageCache filename storing schema:

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

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

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

2019 valtype=`query "$sql0"`

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

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

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

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

2024 case "$filetype" in

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

2026 esac

2027 fi

2028 cacheimg_S=$dir/S_$filename

2029 cacheimg_M=$dir/M_$filename

2030 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2038 else

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

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

2041 fifo2=$fifo.2

2042 mkfifo $fifo $fifo2

2043 fmt=${filename##*.}

2044 ## [[ NOTE ]]

2045 ## a. convert oldimage newimage

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

2047 ## b is much smaller than a

2048 cat $tmpf | unhexize \

2049 | tee $fifo \

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

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

2052 | tee $fifo2 \

2053 | convert - "$cacheimg_M" &

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

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

2056 | convert - "$cacheimg_S" &

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

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

2059 echo '">'

2060 echo "$sum" > $sumfile

2061 fi

2062 ## Now preparing cache image, done.

2063 ## Store this information to DB

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

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

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

2067 query <<-EOF

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

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

2070 EOF

2071 )

2072

2073 showhome() {

2074 # $1=userRowIdToShow

2075 err showhome \$1=$1

2076 case "$1" in

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

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

2079 esac

2080 ## err ShowHome: uname=$uname

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

2082 gecos=`gecos "$uname"`

2083 ## err SH:gecos=$gecos

2084 GF_VIEWONLY=1

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

2086 search_form_args=""

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

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

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

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

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

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

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

2094 fi

2095 usermenu="$usermenu

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

2097 # Display folders

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

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

2100 and type like 'file:%';"

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

2102 nfile=`query "$sql"`

2103 # err nfile=$nfile

2104 if [ $nfile -gt 0 ]; then

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

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

2107 fi

2108 else

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

2110 GROUP BY user;"`

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

2112 search_form_args="author=$uname"

2113 fi

2114 . ./s4-blog.sh

2115

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

2117 search_form "$search_form_args" > $sf

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

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

2120 viewtable $formdir/user.def user $1

2121 echo "</div>"

2122 } > $pf

2123

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

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

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

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

2128 if true; then

2129 tbl=user_m

2130 enticond="name='$uname'"

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

2132 else

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

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

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

2136 echo '">'

2137 }

2138 fi > $imf

2139 fi

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

2141 val='$uname';"`

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

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

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

2145 listblog $uname > $bf

2146 ## unset ddd

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

2148

2149 hometail=$tmpd/tail.$$

2150 mkfifo $hometail

2151

2152 #Calling listgroupbytable, originally here

2153

2154 (

2155 # Display Most Recent Entry

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

2157 shortval=${shortval:-val}

2158

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

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

2161 ctime,

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

2163 (SELECT gecos FROM gecoses

2164 WHERE name=(SELECT val FROM blog_s

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

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

2167 FROM blog b

2168 JOIN

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

2170 FROM article a, article_s s

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

2172 GROUP BY blogid ORDER BY val DESC LIMIT 50

2173 ) m

2174 ON b.id=m.blogid;"

2175 # This should be as follows

2176 : <<EOF

2177 WITH arts AS(

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

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

2180 FROM article a NATURAL JOIN article_s s

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

2182 GROUP by s.id

2183 )

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

2185 FROM arts a0

2186 JOIN

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

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

2189 ORDER BY ctime DESC LIMIT 50;

2190 EOF

2191

2192 cat<<-EOF

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

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

2195 <div class="lcto">

2196 `DT_VIEW=replyblog dumptable html blog`

2197 </div>

2198 EOF

2199 unset DT_SQL

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

2201 # Display NEWS

2202 # 2016-06-26

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

2204 acclog blog $blogreadflagrowid

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

2206 fi

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

2208 sql=`listnewblogsql "$user"`

2209 # echo "$sql" > tmp/listnew

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

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

2212 cont=$((cont-1))

2213 err newcount=$cont

2214 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2221 `cgi_submit '確定'`

2222 `cgi_hidden read yes`

2223 EOF

2224 echo "$new10 "

2225 echo "</div>"

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

2227 acclog blog $blogcutoffflagrowid # for speed

2228 fi

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

2230 : # DT_SQL=

2231 fi

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

2233 #

2234 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2240 -D_NBLOG_="$nblog" \

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

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

2243 $layout/html.m4.html $layout/home.m4.html

2244

2245 # Record access log

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

2247 }

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

2249 contenttype; echo

2250 ## err commission: "$@"

2251 gname=`getgroupbyid $1`

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

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

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

2255 grp_reg_adm "$@"

2256 else

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

2258 fi

2259 }

2260 listgroupbytable() {

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

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

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

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

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

2266 NGsql="select distinct tag from\

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

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

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

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

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

2272 for tag in `query "$sql"`

2273 do

2274 ## err ListGrp: tag=$tag

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

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

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

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

2279 as rowid,

2280 gname,

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

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

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

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

2285 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2290 echo '</table>'

2291 done

2292 echo '</div>'

2293 }

2294 iconhref() (

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

2296 data=`percenthex "$1"`

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

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

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

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

2301 )

2302 iconhref2() (

2303 # $1=icon-file, $2=Href $3=title $4...=anchor

2304 src=$1

2305 href=$2; title=$3; shift 3

2306 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2307 )

2308 listentry() (

2309 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2310 # Referring variable $iamowner=$grp to attach owner-request links

2311 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2312 cond='' hiddens=''

2313 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2314 if [ -z "$offset" ]; then

2315 offset=`getpar start`; offset=${offset%%[!0-9]*}

2316 offset=$((offset-1))

2317 fi

2318 offset=$((offset + 0)) # change to numeric forcibly

2319 [ $offset -lt 0 ] && offset=0

2320 limit=$listentlimit

2321 dir=`getcachedir "$1"`

2322 if [ x"$1" = x"user" ]; then

2323 hrb="$myname?home"

2324 deficon=person-default.png

2325 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2326 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2327 gcs=gecos

2328 else # if group

2329 hrb="$myname?grp"

2330 deficon=group-default.png

2331 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2332 gcs=name

2333 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2334 if [ -n "$tagline" ]; then

2335 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2336 ## err tagconv=$tagconv

2337 fi

2338 fi

2339 if [ ! -d $dir ]; then

2340 mkdir -p $dir

2341 fi

2342 if [ ! -s $dir/$deficon ]; then

2343 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2344 fi

2345 if [ -n "$2" ]; then

2346 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2347 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2348 fi

2349 tag=`getpar tag` tag2=`getpar tag2`

2350 if [ x"$tag" = x"NULL" ]; then

2351 tag="" tag2=""

2352 fi

2353 if [ -n "$tag$tag2" ]; then

2354 tag=${tag:-$tag2}

2355 qtag=`sqlquote "$tag"`

2356 cond2="tag=$qtag"

2357 fi

2358 if [ -n "$cond1$cond2" ]; then

2359 cond="$cond1${cond2:+ AND $cond2}"

2360 cond="WHERE ${cond# AND }"

2361 fi

2362

2363 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2364 # grpは呼出し元の動的スコープ変数でよくないな...

2365 ##qgrp=`sqlquote $grp`

2366 getgrp="(select gname from grp where rowid=${rowid:--1})"

2367 sql="select a.rowid, a.$link,

2368 coalesce(b.$gcs, a.$nm) as nick,

2369 quote(a.$nm) as qname,

2370 (SELECT val FROM ${tbl}_s

2371 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2372 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2373 || case when a.$nm in (select gname from grp)

2374 then printf('(%d名)',

2375 (select count(user) from grp_mem where gname=a.$nm))

2376 else ' <'||a.$nm||'>'

2377 end

2378 as name,

2379 b.tag,

2380 case when a.$nm in (select user from grp_adm

2381 where gname=$getgrp) then '管理者'

2382 when '$user' in (select user from grp_adm where gname=a.$nm)

2383 then 'ADMIN'

2384 when '$user' in (select user from grp_mem where gname=a.$nm)

2385 then 'Member'

2386 when '$iamowner' = '' then ''

2387 else ',not='||a.rowid end as ownerlink,

2388 CASE '$entity'

2389 WHEN 'グループ'

2390 THEN coalesce(

2391 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2392 'open')

2393 ||

2394 CASE WHEN '$user'

2395 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2396 THEN ' ismember'

2397 ELSE ''

2398 END

2399 ELSE 'user'

2400 END regmode

2401 from $tbl a left join

2402 (select $nm as name,

2403 max(case key when 'gecos' then val end) as gecos,

2404 max(case key when 'tag' then val end) as tag,

2405 max(case key when 'mtime' then val end) as mtime,

2406 max(case key when 'wtime' then val end) as wtime,

2407 max(case key when 'login' then val end) as login

2408 from ${tbl}_s group by $nm)

2409 b on a.$nm=b.name $cond $3

2410 order by b.wtime desc, b.login desc,

2411 b.mtime desc, b.tag desc, a.rowid asc"

2412 # Give precedence to newer maintained groups (2016-09-24)

2413 # Note that mtime is stored only in grp_s.

2414 ## err LE:sql.1="$sql"

2415 total=`query "with x as ($sql) select count(*) from x;"`

2416 echo "${entity} 一覧" | html h2

2417 echo '<div class="listentry">' # List-entry div

2418 # Show owner/member filter button

2419 METHOD=GET

2420 hiddens="$hiddens

2421 `cgi_hidden kwd \"$kwd\"`

2422 `cgi_hidden stage \"$stage\"`"

2423 if [ x"$tbl" = x"grp" ]; then

2424 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2425 fh="<select name=\"tag\">$nl"

2426 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2427 for l in $args; do

2428 val=${l#*=} tname=${l%=*}

2429 if [ x"$val" = x"$tag" ]; then

2430 s=" selected"

2431 selectedtags="(種別[${tname}]のみ)"

2432 else

2433 s=""

2434 fi

2435 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2436 done

2437 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2438 cat<<-EOF

2439 <form action="$myname" method="$METHOD">

2440 </form>

2441 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2442 for="ismembtn">参加中以外隠す</label>

2443 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2444 for="isadmbtn">管理者参加以外隠す</label>

2445 EOF

2446 # limit=3

2447 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2448 fi

2449 if [ $total -gt $limit ]; then

2450 echo '<div>'

2451 METHOD=GET cgi_form $stage <<EOF

2452 $form

2453 <label>次の語を含む${entity}で検索:

2454 `cgi_text kwd "$kwd"`</label>

2455 $hiddens

2456 EOF

2457 echo '</div>'

2458 else

2459 echo $selectedtags | html p

2460 fi

2461 cat<<EOF

2462 <form action="$myname" method="$METHOD">

2463 <p>${total}件中の<input class="hidesub" type="text" name="start"

2464 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2465 <input type="submit" value="確定"></p>

2466 </form>

2467 EOF

2468 if [ $((offset+limit)) -lt $total ]; then

2469 nextbtn=$(

2470 cat<<EOF

2471 <div class="right clear"><form action="$myname" method="$METHOD">

2472 `cgi_submit 次の${limit}件`

2473 $hiddens

2474 `cgi_hidden offset $((offset + limit))`</form></div>

2475 EOF

2476 )

2477 fi

2478 if [ $offset -gt 0 ]; then

2479 prevbtn=$(

2480 cat<<EOF

2481 <form action="$myname" method="$METHOD">

2482 `cgi_submit 前の${limit}件`

2483 $hiddens

2484 `cgi_hidden offset $((offset - limit))`</form>

2485 EOF

2486 )

2487 fi

2488 pnbtn="$nextbtn$prevbtn"

2489 echo $pnbtn

2490

2491 ## err ListEntry: `echo "$sql"\;`

2492 # sq $db here??? 2016-11-28

2493 query "$sql limit $limit ${offset:+offset $offset};" \

2494 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2495 # err name=$name owner=$ownerp lnk=$lnk

2496 # err newlnk=$lnk regmode=$regmode

2497 icondir=$dir/$id

2498 # Pick up only last icon

2499 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2500 <p class=\"tag _$tag\">$tag</p>" \

2501 | _m4 $tagconv

2502 if [ -n "$NOSPEEDUP" ]; then

2503 files=`getvalbyid $tbl profimg $id $icondir`

2504 if [ -n "$files" ]; then

2505 icon=`echo "$files"|tail -1`

2506 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2507 else

2508 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2509 fi

2510 elif [ -n "$icon" -a -s "$icon" ]; then

2511 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2512 else

2513 cond="$nm=$qname"

2514 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2515 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2516 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2517 # err "img=[$img]"

2518 if [ -n "$img" ]; then

2519 echo "<a href=\"$hrb+$lnk\">"

2520 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2521 echo "</a>"

2522 else

2523 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2524 fi

2525 fi

2526 echo "<br>$name${ownerp:+<br>($ownerp)}"

2527 echo "</div>"

2528 done

2529 echo "</div>" # End of List-entry div

2530 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2531 )

2532 listmember() {

2533 listentry user "$@"

2534 }

2535 listgroup() {

2536 listentry group "$@"

2537 }

2538 hexteams() { # $1=gname, $2(optional)=user

2539 cond=${2:+" AND user='$2'"}

2540 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2541 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2542 }

2543 showgroup() { # $1=group-rowid

2544 if [ -z "$1" ]; then

2545 grid=`getpar grid`

2546 grid=${grid%%[!0-9]*}

2547 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2548 else

2549 grid=$1

2550 fi

2551 grp=`getgroupbyid $grid`

2552 qgrp=`sqlquote "$grp"`

2553 htmlgrp=`echo "$grp"|htmlescape`

2554 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2555 if isgroup "$grp"; then

2556 tf=$tmpd/title.$$

2557 sf=$tmpd/search.$$

2558 bodyclass=`query "SELECT val FROM grp_s

2559 WHERE gname=$qgrp AND key='regmode';"`

2560 if ismember "$user" "$grp"; then

2561 ismember="ismember"

2562 bodyclass="$bodyclass${bodyclass:+ }ismember"

2563 else

2564 ismember="" # bodyclass="group"

2565 fi

2566 bodyclass="$bodyclass grouphome"

2567 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2568 echo "グループ $htmlgrp" > $tf

2569

2570 showgroupsub $formdir/grp.def "$grid" | \

2571 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2572 -D_FORM_="syscmd(\`cat')" \

2573 -D_BODYCLASS_="$bodyclass" \

2574 -D_DUMPTABLE_="" \

2575 $htmlheader $sf $layout/form+dump.m4.html

2576 # $htmlheader $layout/form+dump.m4.html

2577 # $htmlheader is defined in grp()

2578 else # if $grp is removed at par2table

2579 listgroup

2580 fi

2581 }

2582 showgroupsub() {

2583 # $1=def-file $2=group-rowid

2584 # Using $ismember

2585 rowid=$2

2586 grp=`getgroupbyid $2`

2587 qgrp=`sqlquote "$grp"`

2588 td=`getcachedir grp/"$2"`

2589 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2590 if [ -z "$rowid" ]; then

2591 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2592 #grp=`sq $db "select gname from grp where rowid=$grp"`

2593 echo "showgroupsub: invalid argument($1 $2)" | html p

2594 return

2595 fi

2596 val=`getvalbyid grp profimg $rowid $tmpd`

2597 enticond="gname=$qgrp"

2598 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2599 if [ -n "$img" ]; then

2600 cat<<-EOF

2601 <p class="groupimg">

2602 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2603 EOF

2604 fi

2605 echo "<div class=\"noprofimg\">"

2606 viewtable $1 grp $rowid

2607 echo "</div>"

2608 if isgrpowner "$user" "$grp"; then

2609 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2610 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2611 iamowner=$rowid

2612 colmd=" mode"

2613 fi

2614 if [ -n "$ismember" ]; then

2615 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2616 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2617 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2618 cat<<-EOF

2619 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2620 href="?blog+$rowid">グループの新規話題作成</a>

2621 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2622 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2623 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2624 <div class="fold clear">

2625 `cgi_checkbox send yes id="send"`<label

2626 for="send">グループ全員にメッセージ送信</label>

2627 <div>

2628 `cgi_textarea message "" "cols=60"`

2629 `cgi_submit 送信`

2630 `cgi_reset リセット`

2631 </div>

2632 `cgi_hidden grp $rowid`

2633 </div></form>

2634 EOF

2635 fi

2636 # 加入ボタン + 加入者リスト

2637 if [ -n "$ismember" ]; then

2638 ismem='checked' state="(参加中)"

2639 else

2640 nomem='checked' state="(現在非加入)"

2641 fi

2642 # このグループでの加入アドレス

2643 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2644 and key='email';"`

2645 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2646 ## and key='email';"

2647 ##err email=$eml

2648 cat <<EOF

2649 <div class="fold clear">

2650 `cgi_checkbox reg yes id="reg"`<label

2651 for="reg">自身の加入状態を操作する</label>$state

2652 <div>

2653 EOF

2654 cgi_form grp <<EOF

2655 <p>このグループに</p>

2656 <table class="b">

2657 <tr><th>メンバーとして</th><td>

2658 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2659 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2660 <tr><th>参加する場合のメイルアドレス<br>

2661 <small>(メインのアドレスとは違うものにする場合に記入<br>

2662 同じでよい場合は空欄に)</small></th>

2663 <td>`cgi_text email $eml`</td></tr>

2664 </table>

2665 `cgi_hidden grp $rowid`

2666 EOF

2667 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2668 echo "moderated (承認加入の)グループなので実際に参加できるのは

2669 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2670 fi

2671 echo '</div></div>'

2672 echo '<h2>話題一覧</h2>'

2673 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2674 cat<<-EOF

2675 <form class="summary" action="$myname" title="$thelp">

2676 `cgi_hidden owner "$grp"`

2677 `cgi_hidden kwd "@week"`

2678 `cgi_hidden stage searchart`

2679 `cgi_submit "一週間のまとめ"`

2680 </form>

2681 EOF

2682 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2683 colstate="state:稼動状態:frozen=rowclass=凍結"

2684 DT_CHLD=article:blogid \

2685 DT_QOWNER="$qgrp" \

2686 DT_VIEW=replyblog dumptable html blog \

2687 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2688

2689 getgname="(select gname from grp where rowid=$rowid)"

2690 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2691 cm="?commission+$rowid"

2692 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2693 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2694 # team list

2695 hexteams=`hexteams "$grp"`

2696 if [ -n "$hexteams" ]; then

2697 echo "チーム一覧" | html h2

2698 echo '<div class="dumptable"><table class="b">'

2699 sq $db -html -header<<-EOF

2700 SELECT val TEAM,

2701 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2702 MEMBERS

2703 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2704 EOF

2705 echo '</table></div>'

2706 fi

2707 }

2708 grp_getbodyclass() {

2709 # Get css class name for document.

2710 # `moderated' for moderated groups

2711 # `ismember' for groups where user belongs

2712 # $1=GroupName (w/o quote)

2713 # $user=userNameCurrentlyLogin

2714 ## err grp_getbodyclass: 1="$1"

2715 qgrp=`sqlquote "$1"`

2716 query<<-EOF

2717 SELECT coalesce(

2718 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2719 'open')

2720 ||

2721 CASE WHEN '$user'

2722 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2723 THEN ' ismember'

2724 ELSE ''

2725 END;

2726 EOF

2727 }

2728 grpaction() { # $1=group-rowid

2729 err GRP_ACTION:IN

2730 grid=${1:-`getpar grp`}

2731 grp=`getgroupbyid "$grid"`

2732 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2733 if [ -z "$grp" ]; then

2734 echo "無効な指定です。" | html p; return

2735 fi

2736 if ! ismember $user "$grp"; then

2737 echo "加入者のみに許可された操作です。" | html p; return

2738 fi

2739 echo "グループ $grp 個別選択操作" \

2740 | _m4 -D_TITLE_="syscmd(\`cat')" \

2741 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2742 $layout/html.m4.html

2743

2744 isowner=""

2745 isgrpowner "$user" "$grp" && isowner="yes"

2746 usel=`getpar usel`

2747 if [ -n "$usel" ]; then

2748 uids=$(echo `echo $usel`|tr ' ' ',')

2749 ## err grpaction-1: grp=$grp, `echo $sql`

2750 text=`getpar text|tr -d '\r'`

2751

2752 rm=`getpar rm` cfm=`getpar confirm`

2753 ## err rm=$rm cfm=$cfm

2754 if [ x"$rm" = x"yes" ]; then

2755 if [ "$isowner" ]; then

2756 if [ x"$rm$cfm" = x"yesyes" ]; then

2757 # Eliminate

2758 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2759 for tbl in grp_mem grp_mem_s grp_mem_m; do

2760 sql="delete from $tbl $cond;"

2761 # echo "sql=$sql"

2762 query "$sql"

2763 err rmGRPuser "$sql"

2764 done

2765 num=`query "select count(*) from user where rowid in ($uids);"`

2766 #err num=$num

2767 if [ 0$num -gt 0 ]; then

2768 sql="select coalesce(b.val,a.name) from user a left join \

2769 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2770 # err `echo "$sql"`

2771 html pre<<EOF

2772 以下の${num}名のグループ $grp 登録を解除しました。

2773 `query "$sql"`

2774 EOF

2775 fi

2776 else

2777 echo "確認のチェックがないのでやめておきます。" | html p

2778 return

2779 fi

2780 else # not Group Owner

2781 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2782 return

2783 fi

2784 cat<<EOF

2785

2786 EOF

2787 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2788 if [ -z "$text" ]; then # if msg is empty

2789 echo "なにかメッセージを..." | html p

2790 return 0

2791 fi

2792 gecos=`gecos $user`

2793 safegc=`echo "$gecos" | tr -d '<>@'`

2794 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2795 fromad=`myemail4group "$grp"`

2796 ###mail_from="$safegc <$fromad>"

2797 mail_from="$safegc <$user>" # TEST: 2020/5/13

2798 test -n `getpar sender` &&

2799 export SENDER=$user # TEST: 2020/5/15

2800 replyto=$fromad

2801

2802 ## Start parse of attachment files

2803 if [ -n "`getpar email`" ]; then

2804 ar=`getpar supprcpt`

2805 if [ -n "$ar" ]; then

2806 for a in $ar; do

2807 if checkdomain "$a"; then

2808 supprcpt="$supprcpt $a"

2809 else

2810 err "SupprcptErr=[$a] by $user"

2811 removercpt="$removercpt $a"

2812 fi

2813 done

2814 fi

2815 subj=`getpar subject`

2816 afiles=""

2817 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2818 do

2819 f=$tmpd/$fn

2820 if [ -s $f ]; then

2821 afiles=$afiles"${afiles:+ }$f"

2822 fi

2823 done

2824 else

2825 preface=$(cat <<-EOF

2826 $url

2827 のグループ「$grp」のメンバーである $gecos さんから、

2828 あなた宛のメッセージです。

2829 ----------------------------------------------------------

2830 EOF

2831 )

2832 fi

2833 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2834 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2835 subj="${subj:-$gecos さんからのメッセージ}"

2836 REPLYTO=$replyto

2837 MAIL_FROM=$mail_from

2838 export REPLYTO SMAIL_TO MAIL_FROM

2839 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2840 for r in $rcpts; do

2841 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2842 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2843 else

2844 # Show sender and rcpts address for guest

2845 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2846 fi

2847 if [ -n "$afiles" ];then

2848 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2849 else

2850 smail "$r" "$subj"

2851 fi <<EOF

2852 ${preface:+$preface$nl}$text

2853 EOF

2854 done

2855 if [ $? = 0 ]; then

2856 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2857 sql="select coalesce(b.val, a.name) from

2858 (select name from user where rowid in ($uids)) a

2859 left join user_s b on a.name=b.name and b.key='gecos';"

2860 html pre<<EOF

2861 `query "$sql"`

2862 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2863 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2864 EOF

2865 err SendDone: `echo $sql`

2866 fi

2867 elif [ x"$rm" = x"commission" ]; then

2868 grp_reg_adm $grid $usel

2869 elif [ x"$rm" = x"addteam" ]; then

2870 team=`getpar team|sed "s/'/''/g"` # for single quotation

2871 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2872 if [ x"$team" != x"$newteam" ]; then

2873 echo "チーム名に使えない文字を除去しました" | html p

2874 team=$newteam

2875 fi

2876 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2877 cat<<-EOF | html p

2878 有効なチーム名を入力してください。

2879 カンマだけ、「なし」という名前は使えません。

2880 EOF

2881 echo "有効なチーム名を入力してください。" | html p

2882 else

2883 grp_add_team $grid "$team" $usel

2884 fi

2885 elif [ x"$rm" = x"rmteam" ]; then

2886 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2887 rmteam=`getpar rmteam|sed "s/'/''/g"`

2888 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2889 gname='$grp' AND user='$user' AND key='team'\

2890 AND val='$rmteam';\"`" ]; then

2891 grp_rm_team $grid "$rmteam" $usel

2892 else

2893 echo "所属していないチームの除去操作はできません。"|html p

2894 fi

2895 else

2896 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2897 fi

2898 fi

2899 fi

2900 # POST count summary

2901 from=`getpar from`; to=`getpar to`

2902 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2903 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2904 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2905 # New entry

2906 sql="WITH mems AS (

2907 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2908 ON gm.user=g.name

2909 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2910 ), target_article AS (

2911 SELECT id FROM article_s

2912 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2913 ), posts AS (

2914 SELECT author, count(author) post

2915 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2916 WHERE blogid IN (SELECT id FROM blog_s

2917 WHERE key='owner'

2918 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2919 AND key='text'

2920 GROUP BY author

2921 ), teams AS (

2922 SELECT user, group_concat(val, ', ') team

2923 FROM grp_mem_m

2924 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2925 AND key='team'

2926 GROUP BY user

2927 ), user_post AS (

2928 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2929 FROM mems m LEFT JOIN posts

2930 ON m.name=posts.author

2931 GROUP by m.rowid

2932 )

2933 SELECT

2934 CASE

2935 WHEN (SELECT user FROM grp_adm

2936 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2937 AND user=up.name) IS NOT NULL

2938 then 'k'

2939 ELSE ''

2940 END || rowid || ',' || gecos NAME,

2941 post POST, team _TEAM_

2942 FROM user_post up LEFT JOIN teams t

2943 ON up.name=t.user

2944 ORDER BY gecos;"

2945 ## err grpaction: "`echo \"$sql\"`"

2946 tf=$tmpd/title.$$

2947 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2948 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2949 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2950 for=\"cmadmin\">管理者委任</label>

2951 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2952 </p></div>"

2953 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2954 title=\"Shortcut: g${nl}Remove from the Group\"

2955 for=\"conf\">GRP登録解除</label>

2956 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2957 <p>この操作による通知は本人に行きません。

2958 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2959 </div>"

2960 # Get team list to which current user belongs into $hexteams

2961 allhexteams=$(hexteams "$grp")

2962 if [ -n "$isowner" ]; then

2963 myhexteams="$allhexteams" # admin can remove all teams' attr

2964 else

2965 myhexteams=$(hexteams "$grp" "$user")

2966 fi

2967 if [ -n "$myhexteams" ]; then

2968 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2969 title=\"Shortcut: s${nl}Strip a team tag from\"

2970 for=\"cmrmteam\">チーム属性除去</label>

2971 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2972 を除去します: `cgi_checkbox teamconfirm yes` 確認

2973 <p>この操作による通知は本人に行きません。

2974 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2975 </div>

2976 "

2977 fi

2978 stf=$tmpd/selteam.$$

2979 cgi_select_h selteam "5445414d" $allhexteams > $stf

2980 b1='<label> <input type="checkbox" name="usel" value="'

2981 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2982 br='<span id="reverse" title="Reverse Selection"></span>'

2983 #b2='"> <span>' b3='</span></label>'

2984 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2985 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2986 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2987 cgi_form grpaction<<EOF \

2988 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2989 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2990 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2991 | _m4 -D_TITLE_="spaste(\`$tf')" \

2992 -D_SUBTITLE_="チェック後操作ボタン" \

2993 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2994 $layout/form+dump.m4.html \

2995 | _m4 -D_TEAM_="spaste(\`$stf')"

2996 <p>下でチェックした人を対象として:</p>

2997 <div class="foldtabs">

2998 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2999 title="Shortcut: a${nl}Add a team tag to"

3000 for="cmteam">同じチーム属性を付与</label>

3001 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3002 `cgi_datalist_h teams $allhexteams`

3003 </div>

3004 ${rmteammsg}

3005 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3006 title="Shortcut: d${nl}DirectMail to"

3007 for="sendmsg" title="Direct Message">DM送信</label>

3008 <div>

3009 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3010 title="Using email format">email書式を使う</label>

3011 <div class="folded">

3012 <table>

3013 <tr><td>From: </td><td>$user</td></tr>

3014 <tr><td>このFrom:で送る</td>

3015 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3016 </td></tr>

3017 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3018 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3019 <tr><td>ファイル添付: </td>

3020 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3021 </table>

3022 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3023 </div>

3024 <div>本文:`cgi_textarea text "" cols=72`

3025 </div>

3026 </div>

3027 ${isowner:+$cmmsg$excmsg}

3028 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3029 </div>

3030 <h4>$grp 参加者一覧</h4>$fromtonote

3031 <table class="td2r thl">

3032 `sq $db -header -html "$sql"`

3033 </table>

3034 `cgi_hidden grp $grid`

3035 `cgi_hidden myuid $myuid id="myuid"`

3036 EOF

3037 }

3038 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3039 # Create TEMPORARY VIEW

3040 query<<EOF

3041 CREATE TEMPORARY VIEW writeusers AS

3042 SELECT DISTINCT author FROM article

3043 WHERE id in (

3044 select id from article where blogid=(select id from blog where rowid=$1)

3045 );

3046 CREATE TEMPORARY VIEW movablegroups AS

3047 SELECT g.rowid growid , g.gname

3048 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3049 ON grp.gname=gm.gname -- そのユーザが属している

3050 AND user='$user') g -- グループに絞る

3051 WHERE (SELECT author FROM writeusers

3052 EXCEPT

3053 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3054 IS NULL;

3055 $2

3056 EOF

3057 }

3058 sql4readableblogs() {

3059 # Create view of blogs that can be readable to $user

3060 # Blog is readable when:

3061 # 1: blog owner is an user

3062 # 2: else, 2.1: owner-group where the $user belongs

3063 # 2.2: else, owner-group is not moderated

3064 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3065 cat<<EOF ## | tee tmp/sql.out

3066 CREATE TEMPORARY VIEW readableblogs AS

3067 SELECT blog.rowid rid, id, author

3068 FROM blog

3069 NATURAL JOIN

3070 (SELECT id,

3071 max(CASE key WHEN 'owner' THEN val END) owner,

3072 max(CASE key WHEN 'mode' THEN val END) mode

3073 FROM blog_s GROUP by id) bs

3074 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3075 THEN 1 -- blog owner is an user, READABLE

3076 WHEN (SELECT val FROM grp_s

3077 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3078 AND

3079 (SELECT user FROM grp_mem

3080 WHERE gname=bs.owner AND user='$user') IS NULL

3081 THEN 0

3082 WHEN mode IN ('quiz', 'enquete')

3083 THEN 0 -- "quiz" mode blog cannot be searched

3084 ELSE 1

3085 END;

3086 EOF

3087 }

3088 editheading() { # $1=rowid-of-heading

3089 rowid=${1%%[!A-Z0-9a-z_]*}

3090 if [ -z "$rowid" ]; then

3091 echo "話題番号が未指定です。" | html p

3092 return

3093 fi

3094 owner=`getvalbyid blog owner $rowid`

3095 title=`getvalbyid blog title $rowid`

3096 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3097 | _m4 -D_TITLE_="修正" \

3098 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3099 -D_BLOGS_="" -D_DUMPTABLE_="" \

3100 -D_FORM_="syscmd(\`cat')" \

3101 $layout/html.m4.html $layout/form+dump.m4.html

3102 # Move to group

3103 if isuser "$owner"; then

3104 crview4article $rowid

3105 n=`query "SELECT count(*) FROM writeusers;"`

3106 ## err N=$n

3107 if [ $((n)) -gt 0 ]; then

3108 ## err ROWID=$rowid

3109 sql="SELECT growid || ':' || gname FROM movablegroups;"

3110 cat<<-EOF

3111 <div class="fold">

3112 `cgi_checkbox mv send id="mv"`<label

3113 for="mv">この話題をグループ所有に移動する</label>

3114 <div>

3115 <form action="?mvart" method="POST" enctype="multipart/form-data">

3116 移動先グループ:

3117 <select name="mv2grp">

3118 EOF

3119 query ".mode html"

3120 query<<-EOF |

3121 $sql

3122 .mode list

3123 EOF

3124 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3125 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3126 cat<<-EOF

3127 </select>

3128 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3129 そのグループに加入しているものに限られます)</p>

3130 <p>`cgi_checkbox cfm yes`<label>確認

3131 (この操作は元に戻すことができません)</label></p>

3132 `cgi_hidden blogrowid $rowid`

3133 `cgi_submit 移動`

3134 `cgi_reset Reset`

3135 </form>

3136 </div>

3137 </div>

3138 EOF

3139 fi

3140 # end of isuser "$owner"

3141 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3142 [ -n "$hexteams" ];}; then

3143 none="`echo なし|hexize`"

3144 cat<<-EOF

3145 <div class="fold">

3146 `cgi_checkbox mv2team send id="mv2team"`<label

3147 for="mv2team">この話題を以下のチームのものにする</label>

3148 <div><p>現在の所属チーム設定:

3149 `query "SELECT

3150 coalesce((SELECT val FROM blog_s

3151 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3152 AND key='team'),

3153 ':なし');"`</p>

3154 <form action="?mvart" method="POST" enctype="multipart/form-data">

3155 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3156 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3157 `cgi_hidden blogrowid $rowid`<br>

3158 `cgi_submit 移動`

3159 `cgi_reset Reset`

3160 </form></div></div>

3161 EOF

3162 fi

3163 }

3164 mvart() { # move diary to some group or team

3165 # or move blog of group to team which belong to the group

3166 blogrowid=`getpar blogrowid`

3167 cfm=`getpar cfm`

3168 ##### echo move blog:$blogrowid to $mv2grp | html p

3169 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3170 . ./s4-blog.sh

3171 if [ -z "$blogrowid" ]; then

3172 echo "無効な指定です(mvart)。" | html p

3173 return

3174 elif [ x"$cfm" != x"yes" ]; then

3175 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3176 elif { mv2grp=`getpar mv2grp`

3177 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3178 [ -n "$mv2grp" ]; }; then

3179 crview4article $blogrowid

3180 ########## TRANSACTION BEGIN

3181 query "BEGIN;"

3182 n=`query "SELECT count(*) FROM writeusers;"`

3183 ## err Nwriteuser=$n

3184 if [ $((n)) -gt 0 ]; then

3185 query<<-EOF

3186 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3187 WHERE key='owner'

3188 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3189 AND $mv2grp IN (SELECT growid FROM movablegroups);

3190 EOF

3191 fi

3192 query "END;"

3193 ########## TRANSACTION END

3194 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3195 [ -n "$mv2team" ];}; then

3196 # blog owner can move it to ANY team

3197 case "$mv2team" in

3198 'なし')

3199 cat<<-EOF

3200 DELETE FROM blog_s

3201 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3202 AND key='team';

3203 EOF

3204 ;;

3205 "") ;;

3206 *)cat<<-EOF

3207 BEGIN;

3208 REPLACE INTO blog_s(id, key, val)

3209 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3210 'team', '$mv2team');

3211 REPLACE INTO blog_s(id, key, val)

3212 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3213 'notify', 'all'); -- Change notify to all

3214 END;

3215 EOF

3216 esac | query

3217 fi

3218 blog_reply $blogrowid

3219 echo yes | html p

3220 }

3221 editart() { # $1=article-rowid $2=blogrowid

3222 rowid=${1%%[!A-Z0-9a-z_]*}

3223 blogrowid=${2%%[!A-Z0-9a-z_]*}

3224 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3225 echo "表示する記事番号が未指定です。" | html p

3226 return

3227 fi

3228 owner=`getvalbyid blog owner $blogrowid`

3229 title=`getvalbyid blog title $blogrowid`

3230 author=`getvalbyid article author $rowid`

3231 ## err EDITart: owner=$owner, author=$author

3232 if isgrpowner "$user" "$owner"; then

3233 : EDIT OK

3234 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3235 echo "本人か所有者しか編集できません." | html p

3236 return

3237 fi

3238 aid=`query "select id from article where rowid=$rowid;"`

3239 tmpout=$tmpd/editart.$$.out

3240 GF_ACTION="?replyblog+$blogrowid#$aid" \

3241 edittable $formdir/article.def article $rowid \

3242 > $tmpout

3243 rm -f /tmp/editart.out

3244 # Cannot use pipelining to m4 with genform() because of stdin stack

3245 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3246 -D_FORM_="syscmd(cat $tmpout)" \

3247 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3248 -D_BLOGS_= -D_DUMPTABLE_= \

3249 $layout/html.m4.html $layout/form+dump.m4.html

3250 }

3251 send2mem() {

3252 rowid=`getpar grp`

3253 rowid=${rowid%%[!0-9]*} # Cleaning

3254 if [ -z "$rowid" ]; then

3255 echo "グループが未指定です。" | html p

3256 return

3257 fi

3258 message=`getpar message`

3259 if [ -z "$message" ]; then

3260 echo "文章を入れてください。" | html p

3261 return

3262 fi

3263 grp=`getgroupbyid $rowid`

3264 members=`collectemail $grp`

3265 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3266 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3267 mailfrom="`gecos "$user"` <$mailfrom>"

3268 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3269 msg=$(cat<<-EOF

3270 $urlbase?grp+$rowid

3271 グループ $grp に所属する

3272 `gecos $user` さんよりメッセージ:

3273

3274 $message

3275 EOF

3276 )

3277 # smail rcpt subj (file)

3278 for m in $members; do

3279 echo "$msg" |

3280 MAIL_FROM=$mailfrom \

3281 SENDER=$noreply \

3282 REPLYTO=$mailfrom \

3283 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3284 smail "$m" "$sj"

3285 done

3286 cat<<EOF

3287 <p>以下のユーザに送信しました。</p>

3288 <pre>

3289 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3290 </pre>

3291 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3292 EOF

3293 }

3294 joingrpadmit() {

3295 # $1=yes/no $2=session-key

3296 if [ -z "$2" ]; then

3297 echo "bye bye" | html p; return

3298 fi

3299 t_usr=`session=$2 getpar user`

3300 t_grp=`session=$2 getpar group`

3301 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3302 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3303 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3304 echo "無効な加入依頼です。" | html p

3305 echo "有効期限が切れたか、

3306 他の管理者がいる場合は処理済みの可能性があります。" | html p

3307 return

3308 fi

3309 if ! isgrpowner "$user" "$t_grp"; then

3310 echo "グループ管理者のみの機能です。" | html p; return

3311 fi

3312 case $1 in

3313 yes) joingrp "$t_grp" "$t_usr" yes ;;

3314 no) joingrp "$t_grp" "$t_usr" no ;;

3315 *)

3316 echo "無効な指定です($1)。" | html p

3317 return ;;

3318 esac

3319 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3320 rcpts="`getgroupadminmails $t_grp` $user"

3321 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3322 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3323 に

3324 $t_usr

3325 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3326 しました。"

3327 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3328 query "delete from session where id='$2';"

3329 echo "$body" | html p

3330 }

3331

3332 joingrprequest() {

3333 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3334 jss="joingrp-`date +%s`-`genrandom 12`"

3335 addsession $jss +${memoplimitdays}days

3336 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3337 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3338 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3339 $url

3340 $user さんから

3341 グループ $1

3342 に加入依頼がありました。

3343

3344 承認する:

3345 $urlbase?joingrpadmit+yes+$jss

3346

3347 白紙に戻す:

3348 $urlbase?joingrpadmit+no+$jss

3349 EOF

3350 echo "管理者に加入依頼を出しました。

3351 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3352 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3353 }

3354 joingrp() {

3355 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3356 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3357 if isgrpowner "$user" "$1"; then

3358 isowner="yes"

3359 elif [ -n "$5" ]; then

3360 isowner="yes"

3361 else

3362 isowner=""

3363 fi

3364 ## err jg:isgrpowner: isowner="$isowner"

3365 if [ -n "$isowner" ]; then

3366 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3367 elif [ x"$2" != x"$user" ]; then # if user is not login user

3368 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3369 return

3370 elif [ x"$3" = x"no" ]; then

3371 : # Do not pursue those who leave

3372 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3373 : # Member can change own email address for the joining moderated group

3374 else # adding user is $user itself

3375 case `getgroupattr $1 regmode` in

3376 moderated)

3377 joingrprequest "$@" # Request only

3378 return

3379 ;;

3380 *)

3381 ;;

3382 esac

3383 fi

3384 qgname=`sqlquote "$1"`

3385 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3386 cond="where gname=$qgname and user='$2'"

3387 if [ x"$3" = x"yes" ]; then

3388 query "replace into grp_mem values($qgname, '$2');"

3389 # Notify joingrp to admin

3390 action="に加入しました。"

3391 if [ -n "$4" ]; then

3392 if msg=`emaildomaincheck "$4"`; then

3393 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3394 'string', '$4', NULL);"

3395 else

3396 echo $msg

3397 fi

3398 else

3399 query "delete from grp_mem_s $cond and key='email';"

3400 fi

3401 if [ -n "$5" ]; then # as ADMIN

3402 # Coming here means newly created group

3403 sql="select case\

3404 when (select count(*) from grp_mem where gname=$qgname)=1\

3405 then (select user from grp_mem\

3406 where gname=$qgname and user='$user')\

3407 else '' end; "

3408 err NewGrpChk: $sql

3409 if [ -n "`query \"$sql\"`" ]; then

3410 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3411 query "replace into grp_adm values($qgname, '$user');"

3412 fi

3413 fi

3414 else

3415 query "begin;

3416 delete from grp_mem $cond;

3417 delete from grp_mem_s $cond;

3418 delete from grp_mem_m $cond;

3419 delete from grp_adm $cond;

3420 delete from grp_adm_s $cond;

3421 delete from grp_adm_m $cond;

3422 end;"

3423 action="から脱退しました。"

3424 fi

3425 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3426 $url?grp+$grid

3427 $user (`gecos $user`)さんが

3428 グループ $1

3429 $action

3430 EOF

3431 }

3432 grp_add_team() (

3433 # $1=grp-rowid $2=team $3...=user-rowid(s)

3434 grp=`getgroupbyid $1`

3435 team=$2; shift; shift

3436 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3437 { echo "BEGIN;"

3438 for user; do

3439 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3440 '$grp',\

3441 (SELECT name FROM user WHERE rowid=$user),\

3442 'team', 'string', '$team');"

3443 done

3444 echo "END;"

3445 } | query

3446 )

3447 grp_rm_team() (

3448 # $1=grp-rowid $2=team $3...=user-rowid(s)

3449 grid=$1

3450 qgrp=$(sqlquote "`getgroupbyid $grid`")

3451 team=$2; shift; shift

3452 [ -z "$grid" -o -z "$team" ] && return

3453 { echo "BEGIN;"

3454 for user; do

3455 echo "DELETE FROM grp_mem_m\

3456 WHERE gname=$qgrp \

3457 AND user=(SELECT name FROM user WHERE rowid=$user)\

3458 AND key='team' AND val='$team';"

3459 done

3460 cat<<-EOF

3461 DELETE FROM blog_s

3462 WHERE rowid=(

3463 SELECT rowid

3464 FROM blog_s a

3465 WHERE key='team'

3466 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3467 AND NOT EXISTS (SELECT * FROM grp_mem_m

3468 WHERE key='team' AND val=a.val -- a.val=team

3469 AND gname = (SELECT val FROM blog_s b

3470 WHERE a.id=b.id AND key='owner')

3471 ));

3472 EOF

3473

3474 echo "END;"

3475 } | query

3476 )

3477 grp_reg_adm() {

3478 # $1=grp-rowid $2...=user-rowid

3479 grid=$1

3480 grp=`getgroupbyid "$1"`

3481 if [ -z "$grp" ]; then

3482 echo "無効なグループIDです" | html p; return

3483 fi

3484 if ! isgrpowner "$user" "$grp"; then

3485 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3486 fi

3487 shift

3488 for urid; do

3489 newadm=`query "select name from user where rowid=$urid;"`

3490 if [ -z "$newadm" ]; then

3491 echo "指定ユーザIDがおかしいようです。" | html p; return

3492 fi

3493 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3494 err ismember $newadm $grp

3495 if ismember $newadm "$grp"; then

3496 # OK, go ahead

3497 getgname="(select gname from grp where rowid=$grid)"

3498 query "replace into grp_adm values($getgname, '$newadm');"

3499 # confirm insertion

3500 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3501 if [ -n "`query \"$sql;\"`" ]; then

3502 echo "追加完了: $newadm" | html p

3503 else

3504 echo "追加失敗($1 $urid)" | html p

3505 fi

3506 fi

3507 showgroup $grid

3508 done

3509 }

3510 dt_rowhack() {

3511 # From: <TR>

3512 # ....

3513 # <TD>rowclass=foo</TD>

3514 # </TR>

3515 # To: <TR class="foo">....<TD>foo</TD></TR>

3516 sed -e '

3517 /^<TR>/ {

3518 :loop

3519 s/\n//

3520 N

3521 /<\/TR>/ {

3522 s/\n//

3523 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3524 n

3525 }

3526 $q

3527 b loop

3528 }'

3529 }

3530 dumptable() {

3531 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3532 # textのフィールドだけ全てダンプにしたほうがいいか

3533 # $DT_VIEW sets link

3534 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3535 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3536 VIEW=${DT_VIEW-replyblog}

3537 if [ -n "$VIEW" ]; then

3538 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3539 fi

3540 sqlfile=$tmpd/dump.sql

3541 : > $sqlfile # ensure to be empty

3542 printf '.mode html\n.header 1\n' > $sqlfile

3543 # $DT_CHLD=ChildTable:BindColumn

3544 if [ -n "$DT_CHLD" ]; then

3545 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3546 cat<<-EOF >> $sqlfile

3547 -- presql

3548 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3549 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3550 EOF

3551 # Speed up counting of new articles

3552 cat<<-EOF >> $sqlfile

3553 -- presql2

3554 DROP TABLE IF EXISTS _counts;

3555 CREATE TEMPORARY TABLE _counts AS

3556 SELECT $_i, count($_i) cnt

3557 FROM $_t GROUP BY $_i;

3558 /* Prepare NEW count table */

3559 CREATE TEMPORARY TABLE _target AS

3560 SELECT b.rowid trowid, b.id

3561 FROM "$2" b JOIN "$2_s" s

3562 ON b.id=s.id AND s.key='owner'

3563 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3564

3565 DROP TABLE IF EXISTS _children;

3566 CREATE TEMPORARY TABLE _children AS

3567 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3568 FROM (SELECT t.trowid, t.id $_i, a.id

3569 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3570 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3571

3572 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3573 DROP TABLE IF EXISTS _news;

3574 DROP VIEW IF EXISTS _news;

3575

3576 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3577 -- INSERT INTO _news

3578 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3579 CREATE TEMPORARY VIEW _news AS

3580 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3581 FROM (SELECT DISTINCT id FROM _target)

3582 a LEFT JOIN

3583 (SELECT $_i, count(ctime) newcnt

3584 FROM _children x

3585 WHERE ctime > coalesce((SELECT time from myacclog

3586 WHERE tblrowid=x.trowid),

3587 '1970-01-01')

3588 GROUP BY $_i) b

3589 ON a.id=b.$_i;

3590 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3591 EOF

3592 # REMOVE next line until 2019/5/1

3593 cntall="(select count($_i) from $_t where $_i=a.id)"

3594 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3595 # REMOVE next assignment until 2019/5/1

3596 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3597 and id in (select id from $_t where $_i=a.id) \

3598 and val > coalesce((select time from myacclog where \

3599 tblrowid=a.rowid),\

3600 '1970-01-01'))"

3601 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3602 cnt="$cntnew as '新着', $cntall as '総数',"

3603 dt_class=" td2r td3r dumpblogs"

3604 fi

3605 # Construct join expression

3606 eav="" scols=""

3607 pk=`gettblpkey $2`

3608 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3609 substr=${substr:-%s}

3610 for col in ${3:-`gettbl_s_cols $2`}; do

3611 valvar=val

3612 case $col in

3613 gecos) scols="$scols${scols:+, }${col#}"

3614 continue ;; # built-in column name

3615 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3616 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3617 case "$as" in

3618 *:*=*) cnd=${as#*:}

3619 h=${cnd%%=*} v=${cnd#*=}

3620 h=`sqlquotestr "$h"`

3621 v=`sqlquotestr "$v"`

3622 valvar="CASE val WHEN $h THEN $v END"

3623 as=${as%%:*} ;;

3624 esac

3625 ;;

3626 *) as=${col} ;;

3627 esac

3628 ss=`printf "$substr" "$valvar"`

3629 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3630 scols="$scols${scols:+, }b.$as"

3631 done

3632 #case author when '$user' then a.rowid else '---' end as ID,

3633 if [ -n "$DT_SQL" ]; then

3634 echo "$DT_SQL"

3635 else

3636 cat<<-EOF

3637 SELECT a.rowid as LINK, $cnt $scols

3638 FROM $2 a LEFT JOIN

3639 (SELECT $pk,$eav,

3640 max(CASE key

3641 WHEN 'owner'

3642 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3643 as gecos

3644 FROM ${2}_s c GROUP BY $pk)

3645 b ON a.$pk=b.$pk $4;

3646 EOF

3647 fi >> $sqlfile

3648 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3649 sqlog<<-EOF

3650 *** SQL-file: $sqlfile ***

3651 `cat $sqlfile`

3652 EOF

3653 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3654 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3655 cat $sqlfile >> tmp/sql

3656 # query ".read $sqlfile" > $tmpd/foo

3657 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3658 cp $tmpd/foo tmp/

3659 err "----- `gdate +%FT%T.%3N` ------------555555"

3660 ## $ddd LINE exists at the end of this function

3661 fi

3662 printf '.mode list\n.header 0\n' >> $sqlfile

3663 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3664 <div>

3665 <div class="dumptable">

3666 <table class="b$dt_class">

3667 `query ".read $sqlfile"`

3668 </table>

3669 </div>

3670 </div>

3671 EOF

3672 ### `query ".read $sqlfile"`

3673 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3674

3675 ## REMOVE THIS!

3676 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3677 }

3678

3679 clean-orphaned() {

3680 # This shoud be done by foreign_key rules, but some db lack them

3681 query<<-EOF

3682 -- Find blogs that have no parent

3683 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS

3684 SELECT blog.id,val FROM blog JOIN blog_s bs

3685 ON blog.id=bs.id AND key='owner'

3686 WHERE val NOT IN (SELECT gname FROM grp)

3687 AND val NOT IN (SELECT name FROM user);

3688 -- Remove them

3689 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3690

3691 -- Find articles that have no parent blog

3692 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS

3693 SELECT id FROM article

3694 WHERE blogid NOT IN (SELECT id FROM blog);

3695

3696 -- Remove them

3697 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3698 EOF

3699 }

3700 par2table() (

3701 # copy current parameters of par into destination table

3702 # $1=definition-file

3703 # Using $user and $session

3704 # Return value:

3705 # 0: Stored successfully

3706 # 1: Insufficient fillings

3707 # 2: No permission to modify the record

3708 # 3: Invalid rowid

3709 # 4: SUCCESS to delete

3710 # 5: Stop deletion for lack of confirm check

3711 # 6: Password length too short

3712 # 7: Password mismatch

3713 # 8: Old password incorrect

3714 # 9: Duplicated post

3715 rowid=`getpar rowid`

3716 if [ ! -e $1 ]; then

3717 echo "テーブル定義ファイルが見付かりません" | html p

3718 exit 1

3719 fi

3720 tbl=${1%.def}

3721 tbl=${tbl##*/}

3722 if [ -n "$rowid" ]; then # Modify existing entry

3723 if [ x"$tbl" = x"user" ]; then

3724 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3725 elif [ x"$tbl" = x"grp" ]; then

3726 sql="select gname from $tbl where rowid=$rowid;"

3727 ##err p2t:grp:q $sql

3728 isgrpowner "$user" "`query $sql`" && rowowner=$user

3729 elif [ x"$tbl" = x"blog" ]; then

3730 # Check if owner in blog_s

3731 blogowner=`getvalbyid blog owner "$rowid"`

3732 if isgrpowner "$user" "$blogowner"; then

3733 rowowner=$user

3734 else

3735 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3736 fi

3737 else

3738 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3739 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3740 fi

3741 ### err rowowner=$rowowner

3742 if [ x"$user" != x"$rowowner" ]; then

3743 echo "他人のレコードはいじれないの" | html p

3744 return 2

3745 elif [ -z "$rowowner" ]; then

3746 echo "指定したレコードはないみたい" | html p

3747 return 3

3748 fi

3749 rm=`getpar rm` cfm=`getpar confirm`

3750 # Editing existent entry

3751 if [ x"$rm" = x"yes" ]; then

3752 if [ x"$rm$cfm" = x"yesyes" ]; then

3753 query "delete from $tbl where rowid=$rowid;"

3754 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3755 clean-orphaned

3756 fi

3757 return 4

3758 else

3759 echo "消去確認のチェックがないので消さなかったの..." | html p

3760 return 5

3761 fi

3762 fi

3763 fi

3764

3765 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3766 if [ -n "$rowid" ]; then

3767 # Update of existing record

3768 for col in `gettblcols $tbl`; do

3769 val=`getparquote $col`

3770 [ -z "$val" ] && continue

3771 ## err query "update $tbl set $col=$val where rowid=$rowid"

3772 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3773 sql="update $tbl set $col=$val where rowid=$rowid;"

3774 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3775 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3776 ## User name cannot be changed with interface provided with this

3777 ## script. But we offer the trigger to change owner user

3778 ## of blog_s table.

3779 #err "select quote($col) from $tbl where rowid=$rowid;"

3780 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3781 cat<<-EOF | query

3782 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3783 -- should use EXCLUSIVE transaction outside of this.

3784 SAVEPOINT par2table;

3785 $sql

3786 update blog_s set val=$val

3787 where key='owner' and val=$old;

3788 RELEASE SAVEPOINT par2table;

3789 EOF

3790 ## XX: DIRTY Hack Ends here

3791 ## We should keep blog's owner as a single column which has

3792 ## foreign key constraint with primary key of grp/user.

3793 else

3794 query "$sql"

3795 fi

3796 done

3797 # Then, set up $pval for further insertion of tbl_s and tbl_m

3798 for col in `gettblpkey $tbl`; do

3799 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3800 pval="$pval${pval:+, }\"$val\""

3801 done

3802 else

3803 # New entry

3804 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3805 # Avoid empty repost of article.

3806 if [ x"$tbl" = x"article" ]; then

3807 # If rowid is empty and ID exists in article-table, that is REPOST!

3808 aid=`getpar id`

3809 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3810 if [ -n "$xaid" ]; then

3811 # REPOST of article

3812 html p <<-EOF

3813 書き込み直後のリロードなので上書きを回避します。

3814 最新記事は末尾の「再読み込み」ボタンから見てください。

3815 EOF

3816 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3817 return 9 # STOP Duplicated posting

3818 fi

3819 fi

3820 # Generate values() for primary keys

3821 for col in `gettblpkey $tbl`; do

3822 # Genuine primary keys for _m and _s

3823 val=`getvalquote $tbl $col`

3824 [ -z "$val" ] && continue

3825 pval="$pval${pval:+, }$val"

3826 done

3827 ##err pval=$pval

3828 for col in `gettblfkey $tbl`; do

3829 # args for values() to insertion into master table

3830 val=`getvalquote $tbl $col`

3831 [ -z "$val" ] && continue

3832 formaster=$formaster"${formaster:+, }$val"

3833 done

3834 formaster="$pval${formaster:+, }$formaster"

3835 ## err formaster=$formaster

3836 if [ -z "$formaster" ]; then

3837 echo "項目を全て埋めてください" | html pre

3838 return 1

3839 fi

3840 ## err "replace into $tbl values($formaster);"

3841 query "replace into $tbl values($formaster);"

3842 ## Insertion to master table, done

3843 fi

3844

3845 transaction=$tmpd/sqlfile.sql; touch $transaction

3846 for kt in s m; do

3847 tb2=${tbl}_$kt

3848 for col in `gettbl_${kt}_cols $tbl`; do

3849 ptype=`getpartype $col "limit 1"`

3850

3851 # First, check update of existing entries in _m

3852 if [ $kt = m ]; then

3853 # sessID|address.1.22|string|Somewhere-x.y.z

3854 sql=""

3855 ##err dots from query "select var from par where var like '$col.%';"

3856 for v in `query "select var from par where var like '$col.%';"`; do

3857 # v=address.1.22

3858 st_rowid=${v##*.}

3859 origcol=${v%%.*} # original column derived from

3860 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3861 ##case `getpartype $v` in

3862 ## err CASE `gettbl_coltype $tbl/$origcol` in

3863 ## err edit flag = `getpar action.$v`

3864 case `getpar action.$v` in

3865 rm)

3866 if [ x`getpar confirm.$v` = x"yes" ]; then

3867 newsql="delete from $tb2"

3868 else

3869 echo "削除確認未チェック" | html p

3870 fi ;;

3871 edit)

3872 case `gettbl_coltype $tbl/$origcol` in

3873 image|document|binary)

3874 file=$tmpd/`getparfilename $v`

3875 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3876 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3877 continue

3878 fi

3879 ## err type=file=$file

3880 [ -z "$file" ] && continue

3881 bn=`sqlquotestr "${file##*/}"`

3882 bin="X'"$(hexize "$file")"'"

3883 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3884 type=\"file:$ct\"

3885 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3886 cachedir=`getcachedir "$tbl/$rowid"`

3887 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3888 rm -rf $cachedir

3889 ;;

3890 *)

3891 newsql="update $tb2 set val=(select val from par where var \

3892 like '$col.%.$st_rowid')"

3893 ;;

3894 esac

3895 ;;

3896 *) # maybe "keep", do not modify value

3897 continue

3898 ;;

3899 esac

3900 # err newsql=$newsql

3901 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3902 done

3903

3904 if [ x"$bin" = x"NULL" ]; then

3905 ## err repl:normal sql=`echo $sql`

3906 if [ -n "$transaction" ]; then

3907 cat<<-EOF >> $transaction

3908 $sql

3909 DELETE FROM $tb2 WHERE type='string' AND val='';

3910 EOF

3911 else

3912 query "$sql

3913 delete from $tb2 where type='string' and val='';"

3914 ## err repl:normal done

3915 fi

3916 else

3917 # Binary update line is TOO LONG to pipelining

3918 sqlfile="$tmpd/sqlf.$$"

3919 if [ -n "$transaction" ]; then

3920 echo "$sql" >> $transaction

3921 else

3922 echo "$sql" > $sqlfile

3923 query ".read $sqlfile"

3924 fi

3925 fi

3926 # Rest of kt==m: set multiple mode

3927 nr=`getparcount $col`

3928 else

3929 nr=1 # for kt==s, number of records is 1

3930 fi

3931

3932 i=0

3933 while [ $i -lt $nr ]; do

3934 limit="limit 1 offset $i"

3935 i=$((i+1)) # increase beforehand against continue

3936 val=`getvalquote $tbl $col "$limit"`

3937 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3938 ## err $col=$val

3939 bin=NULL

3940 ## err partype$col=`getpartype $col "$limit"`

3941 case $ptype in

3942 file) file=$tmpd/`getparfilename $col "$limit"`

3943 ## err parfile-$col=$file

3944 [ -z "$file" ] && continue

3945 bin="X'"$(hexize "$file")"'"

3946 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3947 type=\"file:$ct\" ;;

3948 "*"*) continue ;; # foreign table

3949 *) type=\"string\" ;;

3950 esac

3951 case `gettbl_coltype $tbl/$col` in

3952 password) # special care for password

3953 # name={password,pswd1,pswd2}

3954 p1=`getpar pswd1 "$limit"`

3955 if [ -z "$p1" ]; then

3956 continue # SKIP password setting, if p1 is empty

3957 else

3958 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3959 ## err pswd=$pswd

3960 if pwcheck "$pswd"; then

3961 if [ x"$p1" = x"$p2" ]; then

3962 case "$p1" in

3963 ??????????*) ;;

3964 *) echo "パスワードは10字以上にしてください。" | html p

3965 return 6;;

3966 esac

3967 val="\"`echo $p1|mypwhash`\""

3968 else

3969 echo "2つの新パスワード不一致" | html p

3970 return 7

3971 fi

3972 else

3973 echo "旧パスワード違います" | html p

3974 return 8

3975 fi

3976 fi

3977 ;;

3978 esac

3979 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3980 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3981 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3982 if [ x"$bin" = x"NULL" ]; then

3983 ## err Normal-query: `echo $sql`

3984 if [ -n "$transaction" ]; then

3985 echo "$sql" >> $transaction

3986 else

3987 query "$sql"

3988 fi

3989 else

3990 sqlfile="$tmpd/query.$$"

3991 ## err sqlfile=`ls -lF $sqlfile`

3992 if [ -n "$transaction" ]; then

3993 echo "$sql" >> $transaction

3994 else

3995 echo "$sql" > $sqlfile

3996 query ".read $sqlfile"

3997 fi

3998 fi

3999 ## err p2t done

4000 done

4001 done

4002 done

4003 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4004 -- We cannot use transaction here, because groupupdate may use it.

4005 SAVEPOINT pa2table_insert;

4006 .read $transaction

4007 RELEASE SAVEPOINT pa2table_insert;

4008 EOF

4009 rc=$?

4010 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4011 ## err "Table:$tbl update done "

4012 return $rc

4013 )

4014 genform() {

4015 # $1 = form definition file

4016 # $2, $3 (optional)= table name and ROWID

4017 # If $GF_VIEWONLY set and nonNull, output values without form

4018 # If $GF_ARGS set, use it as content-strings in the form

4019 # If $GF_OWNER set, use it as value of name="owner"

4020 # If $GF_STAGE set, use it as value of name="stage"

4021 forms="" hiddens="" rowid=$3

4022 if [ ! -e "$1" ]; then

4023 echo "そのようなデータベースはないようです($2)。" | html p

4024 return

4025 elif [ -n "$2" ]; then

4026 rec=`query "select * from $2 where rowid='$rowid';"`

4027 if [ -z "$rec" ]; then

4028 pk=`gettblpkey $2`

4029 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4030 rec=`query "select rowid from $2 where $pk='$rowid';"`

4031 rowid=$rec

4032 rec=$3

4033 fi

4034 if [ -z "$rec" ]; then

4035 echo "そんなレコードはないみたいね..." | html p

4036 return

4037 fi

4038 fi

4039 if [ -z "$GF_VIEWONLY" ]; then

4040 rm='<input id="rm" name="rm" type="checkbox"

4041 value="yes"><label for="rm">このエントリの削除</label>

4042 <span>ほんとうに消しますよ(確認)!

4043 <input name="confirm" type=checkbox value="yes">はい</span>'

4044 fi

4045 # Image Cache dir

4046 ## err genform: getcache=$2/$rowid

4047 td=`getcachedir "$2/$rowid"`

4048 while IFS=: read prompt name keytype type args; do

4049 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4050 sp="${args:+ }"

4051 form="" val=""

4052 if [ -n "$rowid" ]; then

4053 # err genform2a: Seeking for "$2.$name, type=$type"

4054 rawval=`getvalbyid $2 $name $rowid $td`

4055 val=`echo "$rawval"|htmlescape`

4056 ## err genform3a: getvalbyid $2 $name $rowid $td

4057 ## err genform3b: val="[$val]" type="$type"

4058 fi

4059 if [ -n "$GF_VIEWONLY" ]; then

4060 is_hidden "$2" "$name" && continue

4061 fi

4062 case "$type" in

4063 text*)

4064 cgiform=cgi_multi_$type

4065 if [ -s $td/$name.count -a -n "$val" ]; then

4066 form=`$cgiform $name $td`

4067 val=$(echo "$val"|

4068 while read fn; do

4069 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4070 </td></tr>$nl"

4071 done)

4072 val="<table>$nl$val$nl</table>"

4073 else

4074 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4075 form=`cgi_$type $name "$rawval" "$args"`

4076 fi

4077 ;;

4078 [Rr][Aa][Dd][Ii][Oo])

4079 fh="<label><input type=\"radio\" name=\"$name\""

4080 form="`echo $args|sed -e \

4081 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4082 ;;

4083 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4084 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4085 ;;

4086 [Ss][Ee][Ll][Ee][Cc][Tt])

4087 fh="<select name=\"$name\">$nl"

4088 form=$(for l in $args; do

4089 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4090 done)

4091 if [ -n "$val" ]; then

4092 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4093 fi

4094 form="$fh$form</select>"

4095 ;;

4096 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4097 if [ -s $td/$name.count ]; then

4098 form=`cgi_multi_file $name $td "$args"`

4099 if [ -n "$val" ]; then

4100 hrfb="$myname?showattc+$2_m"

4101 val=$(echo "$rawval" \

4102 | while read fn; do

4103 data=`percenthex "$td/$fn"`

4104 #ct=`cat $td/$fn.content-type`

4105 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4106 ri=`cat "$td/$fn.rowid"`

4107 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4108 #imgsrc="<img src=\"data:$ct,$data\">"

4109 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4110 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4111 done)

4112 fi

4113 else

4114 form="<input type=\"file\" name=\"$name\" $args>"

4115 if [ -n "$val" ]; then

4116 imgs=$(echo "$rawval"\

4117 |while read fn;do

4118 data=`percenthex "$td/$fn"`

4119 echo "<img src=\"data:image/png,$data\">$fn<br>"

4120 done)

4121 form=$form"<br>$imgs"

4122 val=$imgs # 2015-06-15

4123 else

4124 form="<input type=\"file\" name=\"$name\" $args>"

4125 fi

4126 fi

4127 ;;

4128 [Hh][Ii][Dd][Dd][Ee][Nn])

4129 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4130 args="value=\"$GF_STAGE\""

4131 fi

4132 form="<input type=\"hidden\" name=\"$name\" $args>"

4133 prompt='' # Remove prompt

4134 ;;

4135 [Aa][Uu][Tt][Hh][Oo][Rr])

4136 [ -n "$GF_VIEWONLY" ] && continue

4137 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4138 prompt="" ;;

4139 [Oo][Ww][Nn][Ee][Rr])

4140 [ -n "$GF_VIEWONLY" ] && continue

4141 val=${GF_OWNER:-$val}

4142 val=${val:-$user}

4143 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4144 prompt="" ;;

4145 [Uu][Ss][Ee][Rr])

4146 # XXX: is null $user ok?

4147 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4148 [ -n "$GF_VIEWONLY" ] && continue

4149 form="$user"

4150 ;;

4151 [Pp]assword)

4152 [ -n "$GF_VIEWONLY" ] && continue

4153 form="`cgi_passwd`"

4154 val=""

4155 ;;

4156 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4157 [ -n "$GF_VIEWONLY" ] && continue

4158 if [ -z "$rowid" ]; then

4159 val=`genserial`

4160 fi

4161 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4162 prompt="" ;;

4163 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4164 prompt=""

4165 ;;

4166 parent|path|blog*)

4167 prompt=""

4168 ;;

4169 "*"*)

4170 tail=$tail"``"

4171 continue ;;

4172 esac

4173 if [ -n "$prompt" ]; then

4174 if [ -n "${GF_VIEWONLY}" ]; then

4175 form=$val

4176 else

4177 :

4178 fi

4179 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4180 else

4181 hiddens=$hiddens$nl"$form"

4182 fi

4183 done < $1

4184 # enctype="multipart/form-data"

4185 cat<<EOF

4186 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4187 ${rowid:+$rm}

4188 <table class="b $2">

4189 $forms

4190 </table>$hiddens

4191 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4192 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4193 EOF

4194 if [ -z $GF_VIEWONLY ]; then

4195 cat<<EOF

4196 <input type="submit" name="sub" value="OK">

4197 <input type="reset" name="res" value="Reset">

4198 EOF

4199 fi

4200 cat<<EOF

4201 $GF_ARGS</form>

4202 $tail

4203 EOF

4204 }

4205 edittable() {

4206 # $1=form-def $2=table $3 rowid

4207 genform "$@"

4208 }

4209 viewtable() {

4210 GF_VIEWONLY=1 genform "$@"

4211 }

4212 showattc() {

4213 # $1=table_m $2=rowid &optional $3=RawFlag

4214 ## err \$1=$1 \$2=$2 \$3=$3

4215 if ! isfilereadable $user $1 $2; then

4216 contenttype; echo

4217 echo "このファイルは管理者のみしか見られません" | html p

4218 putfooter; exit

4219 fi

4220 idir=`umask 002; mktempd` || exit 1

4221 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4222 bin=$idir/$myname-$$.bin

4223 sql="select quote(bin) from $1 where rowid='$2';"

4224 ## err showattc: sql: $sql

4225 sq $db "$sql" | unhexize > $bin

4226 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4227 type=${tv%//*} fn=${tv#*//}

4228 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4229 ct=${type#file:}

4230 case $ct in # all text/* changed to text/plain

4231 text/*)

4232 charset=`nkf -g $bin|cut -d' ' -f1`

4233 case $charset in

4234 ASCII*) charset="" ;;

4235 esac

4236 if [ -z "$3" ]; then

4237 ct="text/html${charset:+; charset=$charset}"

4238 link="?showattc+$1+$2+raw"

4239 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4240 | sed 's,^,<span></span>,' \

4241 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4242 -D_LINK_="$link" \

4243 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4244 exit $?

4245 fi

4246 ct="text/plain${charset:+; charset=$charset}"

4247 ;;

4248 esac

4249 contenttype "$ct"

4250 echo "Content-Disposition: filename=\"$fn\""

4251 echo "Content-Length: " `cat $bin | wc -c`; echo

4252 #echo "Content-Type: " ${type#file:}; echo

4253 cat $bin

4254 }

4255 #

4256 # Some default stupid handler on CGI values

4257 #

4258 default_storedb() {

4259 # ARG: $1=table-def-file

4260 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4261 tbl=`basename $1`

4262 tbl=${tbl%.def}

4263 cols="`grep :text $1|cut -d: -f2`"

4264 col=`echo "$cols"|head -1`

4265 vcol=`getpar $col`

4266 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4267 if [ -n "$vcol" ]; then

4268 par2table $1

4269 else

4270 return 2 # No insertion occurred

4271 fi

4272 }

4273

4274 default_view() { # $1=def-file

4275 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4276 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4277 default_storedb "$@"

4278 query "select rowid from $tbl order by rowid desc;" \

4279 | while read rowid; do

4280 viewtable $1 $tbl $rowid

4281 done | _m4 -D_TITLE_="$tbl" \

4282 -D_FORM_="`genform $1`" \

4283 -D_DUMPTABLE_="syscmd(cat)" \

4284 $layout/html.m4.html $layout/form+dump.m4.html

4285 }

4286 default_viewtext() { # $1=def-file

4287 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4288 default_storedb "$@"

4289 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4290 | _m4 -D_TITLE_="$tbl" \

4291 -D_FORM_="`genform $1`" \

4292 -D_DUMPTABLE_="syscmd(cat)" \

4293 $layout/html.m4.html $layout/form+dump.m4.html

4294 }

4295 default_smail() {

4296 default_storedb "$@"

4297 if [ $? -eq 2 ]; then

4298 _m4 -D_TITLE_="入力" \

4299 -D_FORM_="`genform $1`" \

4300 -D_DUMPTABLE_="" \

4301 $layout/html.m4.html $layout/form+dump.m4.html

4302 return

4303 fi

4304 cond=""

4305 for pk in `gettblpkey $tbl`; do

4306 pv=$(sqlquote "$(getpar $pk)")

4307 cond="$cond${cond:+ and }$pk=$pv"

4308 done

4309 sql="select rowid from $tbl where $cond;"

4310 rowid=`query "$sql"`

4311 ## err smail1 - "$sql" "-> rowid=$rowid"

4312

4313 while IFS=: read prompt name keytype type args; do # Read from $1

4314 val=`getpar $name`

4315 if [ -n "$val" ]; then

4316 text="$text

4317 $prompt

4318 $name=$val

4319 ---------------------------------------------------------"

4320 fi

4321 case "$type" in

4322 image|document|file)

4323 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4324 fns=$(echo "$fn"|while read fn; do

4325 err mv $tmpd/$fn.orig $tmpd/$fn

4326 mv $tmpd/$fn.orig $tmpd/$fn

4327 rm $tmpd/$fn.rowid # Remove cache flag

4328 ## err "`ls $tmpd/$fn`"

4329 echo $fn

4330 done)

4331 files="$files $fns"

4332 ;;

4333 esac

4334 done < $1

4335 ## err FILES=$files "`ls -lF $tmpd`"

4336 subj="from ${REMOTE_ADDR}"

4337 (echo "$url"

4338 echo "への書き込みがありました。"

4339 echo "------"

4340 echo "$text"

4341 ) | (cd $tmpd &&

4342 err LS="`ls -lF`" &&

4343 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4344 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4345 echo "以下の内容で送信しました。" | html p

4346 viewtable $1 $tbl \

4347 `query "select rowid from $tbl order by rowid desc limit 1;"`

4348 echo "戻る" | html a "href=\"?\""

4349 }