s4: d1f60cdc3e1a s4-funcs.sh

s4

view s4-funcs.sh @ 764:d1f60cdc3e1a

Migration support added

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sun, 07 Jun 2020 19:27:46 +0900
parents	3f0ef9bb1776
children	f36f1f148a4f

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}

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

72 session=$main_session

74 tconfs=""

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

76 conftbl=_tblconf

77 nl="

78 "

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

80 iconcachekey="profimgcache_S"

82 # Start debug logging

83 exec 3>> $tmpdir/debug.out

84 err() {

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

86 }

87 case "$HTTP_USER_AGENT" in

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

89 *) touchpanel="" ;;

90 esac

92 # If S4MASTERDB is set, behave in another world

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

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

95 fi

96 v=""

97 for i in $S4WORLDLIST; do

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

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

100 done

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

102 err S4MASTERURL=$S4MASTERURL

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

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

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

106 fi

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

108 fi

109

110

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

112

113 : <<EOF

114

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

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

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

118 レポート提出管理

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

120

121 [2016]

122 7/12 根本への反省

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

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

125

126 [以下2015]

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

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

129 Web

130 締切設定

131

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

133 ○自分の提出物リスト

134

135 7/19 ○設置

136 ○一斉送信

137 ○getparfilename の tmpd の扱い

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

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

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

141 ○添付ファイル回収

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

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

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

145

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

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

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

149 ○レポート提出モード

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

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

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

153

154

155 7/9 ○管理者の追加

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

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

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

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

160

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

162

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

164 ○グループ検索

165

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

167

168

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

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

171

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

173 ○6/1 par2tableを triplex 対応に

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

175 →とすると一気に

176

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

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

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

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

181

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

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

184 ## 1. passwd

185 ## 2. grp

186 ## 3. grp_mem

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

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

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

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

191

192 ## ● listの定義:

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

194 ## ● hashの定義:

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

196

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

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

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

200 ## csv2sq3 で .csv.sq3 の Makefile

201

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

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

204 ## type := root | comment

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

206 ## unique(id, filename)

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

208

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

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

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

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

213 ## できたー!

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

215 ↓

216 ↓以下に変更

217 with recursive allmem as

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

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

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

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

222

223

224 with recursive allmem as

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

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

227 where allmem.val=grp_m.gname)

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

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

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

231

232

233 ## triggerもできた。

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

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

236

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

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

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

240 ## グループ管理

241 ## 個人情報管理

242 ##

243

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

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

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

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

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

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

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

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

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

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

254

255 ## ログテーブル

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

257

258

259

260 ■表設計

261 * 3つの表に分散管理

262 id格納表 + hash表 + list表

263 * *_s *_m

264

265

266

267 user, user_map, user_col

268

269 ■抽象エントリタイプ

270 * user

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

272 * group

273 権限判定に利用

274 * serial

275 自動idとして機能

276 * password

277 入力 type=passwordで入力

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

279 * session

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

281 * text

282 入力 type=text

283 * textarea

284 入力 textarea

285 * image|document

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

287 * owner

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

289 * gowner

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

291 * timestamp

292 datetime()

293 * parent

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

295 * path

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

297

298 格納タイプ

299 * list

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

301 * hash

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

303

304 オブジェクトタイプ

305 * entry

306 id, title, owner

307 * textpart

308 id, parentID, text

309 * binarypart

310 id, parentID, contenttype, filename, content

311 * content

312 hash(textpart), list(binarypart)

313 * topic

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

315 * reply

316 id, parentID, content

317 * blog

318 list(entry)

319 blog = [topic, list(reply)]

320

321

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

323 "text" => "hogehoge ..",

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

325 "author" => "taro",

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

327 "parent" => "/",

328 "path" => "/1",

329 "text" => "blah, blah, ....",

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

331 {"serial" => 2,

332 "author" => "hanako",

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

334 "parent" => "/",

335 "path" => "/2",

336 "text" => "blah, blah, ....",

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

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

339

340

341 user:=

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

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

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

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

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

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

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

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

350

351 変換表

352 /user/email=m

353

354 blog:=

355 シリアル:id:p:serial

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

357 所有者:owner:s:owner:

358 時刻:ctime:s:stamp:

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

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

361

362 article:=

363 シリアル:id:p:serial

364 筆者:author:s:owner

365 時刻:ctime:s:stamp:

366 参照元:parent:s:parent:

367 パス:path:s:path:

368 本文:text:s:textarea:

369 画像:image:m:image:

370

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

372

373

374 EOF

375

376 logstart() {

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

378 }

379 logend() {

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

381 }

382 sqlog() {

383 logstart

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

385 cat >> $querylog

386 else

387 echo "$*" >> $querylog

388 fi

389 logend

390 }

391 sq() {

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

393 logstart

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

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

396 else

397 echo "$@" >> $querylog

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

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

400 fi

401 logend

402 }

403 dbsetup() {

404 pipedir=$tmpdir/pipedir

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

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

407 suf=`date +%s`

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

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

410 mkfifo $sqi $sqo

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

412 sq $db < $sqi &

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

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

415 exec 2>> $tmpdir/error.out

416 fi

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

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

419 rm $sqi

420 # Attach supplemental DB

421 cat >&5 <<-EOF

422 .output /dev/null

423 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

427 .output stdout

428 EOF

429 }

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

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

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

433 fi

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

435 }

436 cleanup() {

437 trap '' INT HUP EXIT TERM PIPE

438 echo .quit >&5

439 kill $sq3pid

440 kill $sq3pid

441 rm -f $sqo $sqi

442 rm -rf $tmpfiles

443 cleanup2

444 }

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

446 # simple redirection for the sake of speed.

447 query() {

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

449 echo ".output $sqo" >&5

450 logstart

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

452 tee -a $querylog

453 else

454 echo "$@" >> $querylog

455 echo "$@"

456 fi >&5

457 echo ".output stdout" >&5

458 cat $sqo

459 rc=$?

460 logend

461 return $rc

462 }

463 _m4() {

464 #S4NAME=f,f,f

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

466 -D_S4WORLDS_="${S4WORLDS}" "$@"

467 }

468 ismember() {

469 # $1=user, $2=group

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

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

472 }

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

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

475 }

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

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

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

479 }

480 isgrpowner() (

481 # $1=user, $2=group

482 gn=`sqlquote "$2"`

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

484 err isgrpowner: $sql

485 test -n "`query $sql`"

486 )

487 isgrpownerbygid() (

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

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

490 err isgrpownerbygid: $sql

491 test -n "`query $sql`"

492 )

493 getgroupadminmails() {

494 # $1=group

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

496 email4group "$1" "$i" ;

497 done

498 }

499 getgroupadmins() { # $1=group

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

501 qgrp=`sqlquote "$1"`

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

503 }

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

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

506 getvalbyid grp $2 \

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

508 }

509 getgroupbyid() {

510 # $1=id|gname

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

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

513 # err ggbyid: `echo $sql`

514 query $sql

515 }

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

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

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

519

520 # Return true when anonymous mode

521 [ "$anonymousmode" ] && return 0

522 # case `getvalbyid blog mode $2` in

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

524 # *closed*)

525 # owner=`getvalbyid blog owner $2`

526 # if isgrp $owner; then

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

528 # elif isuser $owner; then

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

530 # fi

531 # esac

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

533 sql="with getblog as (

534 select key,val from blog_s where id=(

535 select blogid from article where id in

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

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

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

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

540 isgrpadm as (select user from grp_adm where

541 gname=(select val from getowner) and

542 user='$1'),

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

544 select case

545 when (select author from article where

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

547 then 'author'

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

549 then 'open'

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

551 then CASE

552 WHEN (SELECT val FROM isgrp) IS NULL

553 THEN

554 CASE WHEN (SELECT val from getowner)

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

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

557 ELSE ''

558 END

559 WHEN (select user from isgrpadm) IS NOT NULL

560 THEN 'i-am-admin'

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

562 END

563 when (select val from getmode) is null

564 then 'open'

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

566 then (SELECT user FROM isgrpadm)

567 when (select author from article where

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

569 then 'user+author'

570 else '' end;"

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

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

573 # result=`query "$sql"`

574 # err FileAccessibility=$result

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

576 }

577 linkhome() {

578 # $1=UserOrGroupRowid

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

580 if isuser $1; then

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

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

583 name=`gecos $1|htmlescape`

584 else

585 _grid=`numericalize "$1"`

586 echo -n "grp+$1"

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

588 fi

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

590 }

591 hreflink() {

592 # s4 specific notation:

593 # ^href=URL

594 # ^iframe=URL

595 # ^video=URL

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

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

598 # OSM umap Wikistyle Notation:

599 # [[URL]] - Simle Link

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

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

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

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

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

605 # Other Style

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

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

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

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

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

611 # SPC+SPC+$ - <br>

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

613 checkboxON="${cb} checked>"

614 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

639

640 }

641 minitbl() {

642 sed -n '

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

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

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

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

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

648 H; # Concat this line to HoldSpace

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

650 $ b cont

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

652 }

653 :cont

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

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

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

657 # . of ^. is workaround for FreeBSD sed

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

659 p; # Print whole "table" element

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

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

662 }

663 x; # Back to the newest line

664 p; # Print rest' | miniul

665 }

666 miniul() {

667 sed -e '

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

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

670 :top

671 s/\n//;

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

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

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

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

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

677 $ b cont

678 N; # 次の行を読む

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

680 /^ /b top

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

682 p; s/.*//;

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

684 /^\* /b top

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

686 }

687

688 :cont

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

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

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

692 H; x

693 }

694 x'

695 }

696 acclog() (

697 # $1=table, $2=rowid

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

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

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

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

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

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

704 fi

705 )

706 gecos() (

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

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

709 )

710 setpar() {

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

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

713 case "$1" in

714 user) _user="$v" ;;

715 skey) _skey="$v" ;;

716 esac

717 fi

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

719 }

720 replpar() {

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

722 }

723 getpar() {

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

725 val=""

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

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

728 user) val=$_user ;;

729 skey) val=$_skey ;;

730 esac

731 fi

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

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

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

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

736 fi

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

738 case "$var" in

739 owner)

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

741 echo $user; return

742 elif ismember $user $val; then

743 echo $val; return

744 fi ;;

745 esac

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

747 echo "$val"

748 }

749 setskey() {

750 # For quick response...(?)

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

752 }

753 chkskey() {

754 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

760 return 0

761 fi

762 return 1

763 }

764 resetskey() {

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

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

767 fi

768 }

769 getpartype() {

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

771 }

772 getparcount() {

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

774 }

775 getparfilename() {

776 # null if type of $1 is not file

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

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

779 }

780 sqlquote() {

781 (v="$1"

782 case "$v" in

783 "") return ;; # null

784 "X'"*) # quoted hex string

785 echo $1 ;;

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

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

788 echo "\"$v\""

789 return ;;

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

791 echo "\"$v\""

792 return ;;

793 *)

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

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

796 else

797 echo "\"$v\""

798 fi ;;

799 esac)

800 }

801 sqlquotestr() (

802 case "$1" in

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

804 echo "'$v'" ;;

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

806 esac

807 )

808 mktempd() {

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

810 }

811 getcachedir() { # $1=maintable

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

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

814 else

815 echo $tmpd/$thumbxy

816 fi

817 }

818 getval() {

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

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

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

822 echo "$user" ;;

823 stamp|datetime)

824 date "+%F %T" ;;

825 serial)

826 (s=`getpar $2`

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

828 *)

829 getpar "$2" "$3";;

830 esac

831 }

832

833 getvalquote() {

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

835 (v=`getval "$@"`

836 case "$v" in

837 "") echo NULL ;;

838 *) sqlquote "$v" ;;

839 esac)

840 }

841 getparquote() {

842 sqlquote "`getpar $1`"

843 }

844 getbinbyid() {

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

846

847 }

848 getvalbyid() {

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

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

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

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

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

854

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

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

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

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

859 return

860 fi

861 done

862 rowid=$3

863 pk=`gettblpkey $1`

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

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

866 td=${4:-$tmpd}

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

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

869 for kt in s m; do

870 t=${1}_$kt

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

872 vcount=1 # count(val)

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

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

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

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

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

878 if [ $kt = m ]; then

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

880 # Reset val to store filenames if type is string

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

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

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

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

885 i=0

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

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

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

889 i=$((i+1))

890 fn=$c.$i

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

892 case $type in

893 file:*)

894 #file=$td/$val

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

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

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

898 # FOR SPEED: Skip file generation if imgcache exists

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

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

901 && continue

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

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

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

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

906 select rowid from $cond $slice;

907 .output '$td/$fn'

908 select val from $cond $slice;

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

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

911 .output stdout

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

913 EOF

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

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

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

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

918 ls -lh "$file" |

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

920 case "$type" in

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

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

923 *|*:[Aa]pplication*)

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

925 png:- > "$file"

926 ;;

927 esac

928 ;;

929 *)

930 sq $db<<EOF

931 .output $td/$fn.rowid

932 select rowid from $cond $slice;

933 .output $td/$fn

934 select val from $cond $slice;

935 EOF

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

937 ;;

938 esac

939 done

940 else

941 rm -f $td/$c.count

942 case $type in

943 file:*)

944 echo "$val" \

945 | while read fn; do

946 file=$td/$fn

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

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

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

950 | unhexize > "$file"

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

952 case $type in

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

954 *:[Aa]pplication*)

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

956 png:- > $file ;;

957 esac

958 fi

959 done

960 ;;

961 esac

962 fi

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

964 return

965 fi

966 done

967 done)

968 }

969 getvalbypkey() (

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

971 pk=`gettblpkey $1`

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

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

974 )

975 getvalbycond() {

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

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

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

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

980 getvalbyid "$1" "$2" $rowid "$4"

981 fi

982 }

983 getpwfield() {

984 # getpwfield user column

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

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

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

988 echo "$val"

989 return 0

990 else

991 return 1

992 fi

993 }

994 numericalize() {

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

996 }

997 encode() {

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

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

1000 sha1=sha1

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

1002 sha1=sha1sum

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

1004 sha1=gsha1sum

1005 fi

1006 fi

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

1008 }

1009 enjpeg() {

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

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

1012 cjpeg="cjpeg"

1013 else

1014 cjpeg="convert - jpeg:-"

1015 fi

1016 fi

1017 $cjpeg "$@"

1018 }

1019 mycrypt() (

1020 key=$1 salt=$2

1021 # err \$2=$2

1022 case $2 in

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

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

1025 esac

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

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

1028 )

1029 hexize() {

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

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

1032 hexize="xxd -p"

1033 else

1034 hexize_hd() {

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

1036 }

1037 hexize="hexize_hd"

1038 fi

1039 fi

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

1041 }

1042 unhexize() {

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

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

1045 unhex="xxd -p -r"

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

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

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

1049 EOF

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

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

1052 # world writable...:)

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

1054 fi

1055 fi

1056 cat "$@" | $unhex

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

1058 }

1059 percenthex() {

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

1061 }

1062 htmlescape() {

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

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

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

1066 }

1067 enascii() {

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

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

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

1071 else

1072 enascii_now=`date +%FT%T`

1073 enascii_sed() {

1074 nkf -Z0Z1Z2 \

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

1076 }

1077 enascii="enascii_sed"

1078 fi

1079 fi

1080 cat "$@" | $enascii

1081 }

1082 size_h() {

1083 i="$1" oi=$1

1084 set -- B B KB MB GB TB

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

1086 oi=$i

1087 i=$((i/1024))

1088 shift

1089 done

1090 echo ${oi}$1

1091 }

1092 gettblconf() {

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

1094 ## tconfs=`sq $db \

1095 tconfs=`query \

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

1097 fi

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

1099 }

1100 gettblkeys() {

1101 # $1=tbl

1102 gettblconf

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

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

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

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

1107 case $type in

1108 *p*)

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

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

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

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

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

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

1115 esac

1116 case $type in

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

1118 esac

1119 done

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

1121 }

1122 gettblpkey() {

1123 # $1=tbl

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

1125 }

1126 gettblfkey() {

1127 (x=`gettblkeys $1`

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

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

1130 }

1131 gettblcols() {

1132 (x=`gettblkeys $1`

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

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

1135 }

1136 gettbl_s_cols() {

1137 (x=`gettblkeys $1`

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

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

1140 }

1141 gettbl_m_cols() {

1142 (x=`gettblkeys $1`

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

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

1145 }

1146 gettbl_h_cols() {

1147 (x=`gettblkeys $1`

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

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

1150 }

1151 gettbl_coltype() (

1152 gettblconf

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

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

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

1156 )

1157 is_hidden() {

1158 # $1=Tbl $2=col

1159 gettblconf

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

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

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

1163 case $x in

1164 *h*) return 0 ;;

1165 *) return 1 ;;

1166 esac

1167 }

1168

1169 dbsetbyid() {

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

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

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

1173 quotedp=$(sqlquotestr "$p")

1174 unset primary update

1175 gettblconf

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

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

1178 #err conf=$conf

1179 case ${conf#*=} in

1180 p*) primary=1 ;;

1181 f*) update=1 ;;

1182 u*) ;;

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

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

1185 esac

1186 #err t=$t

1187 type=string fn=""

1188 case $conf in

1189 */password)

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

1191 ;;

1192 */image*|*/document*)

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

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

1195 ;;

1196 esac

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

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

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

1200 if [ "$primary" ]; then

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

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

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

1204 elif [ "$update" ]; then

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

1206 else

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

1208 fi

1209 )

1210 }

1211 expire() (

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

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

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

1215 )

1216 addsession() {

1217 # expireをセット

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

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

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

1221 # Remove old session parameters

1222 now=`expire now`

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

1224 }

1225 gencookie() (

1226 path=${URL#*:/}

1227 path=${URL%/*}

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

1229 for kv; do

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

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

1232 done

1233 )

1234 contenttype() {

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

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

1237 }

1238 putheader() {

1239

1240 }

1241 putfooter() {

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

1243 }

1244 getcookie() {

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

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

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

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

1249 case "$k" in

1250 user) _user="$v" ;;

1251 skey) _skey="$v" ;;

1252 esac

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

1254 done

1255 }

1256 genrandom() {

1257 # $1=columns (default: 10)

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

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

1260 }

1261 genserial() {

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

1263 }

1264 smail() {

1265 # smail rcpts subj (file)

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

1267 # $MAIL_FROM <- From: header value

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

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

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

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

1272 sender=${SENDER:-$admin}

1273 # Do not call m4 with directly passing text

1274 _r=$tmpd/rcpt

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

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

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

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

1279 }

1280 smail_queue_flush() {

1281 # $1=timelimit

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

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

1284 SELECT rowid FROM smailq

1285 GROUP BY rcpts, subj

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

1287 EOF

1288 )

1289 for rid in $rowids; do

1290 sq $workdb \

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

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

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

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

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

1296

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

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

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

1300 .separator "\n" "------------------\n\n"

1301 SELECT time, text FROM smailq

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

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

1304 ORDER by time;

1305 EOF

1306 then

1307 cat <<-EOF | sq $workdb

1308 DELETE FROM smailq

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

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

1311 EOF

1312 fi

1313 fi

1314 done

1315 }

1316 smail_queue() {

1317 # $1=Rcpts, $@=subj

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

1319 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1328 EOF

1329 )

1330 ## XXX: Adhoc load mitigation

1331 case "$now" in

1332 *[01])

1333 err flush_queue=$mintime

1334 smail_queue_flush ;;

1335 esac

1336 fi

1337 }

1338 setviastring() {

1339 table=par

1340 oifs="$IFS"

1341 IFS="&"

1342 for us in $1; do

1343 k=${us%%=*}

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

1345 setpar "$k" "string" "$v"

1346 done

1347 IFS="$oifs"

1348 }

1349 checkdomain() (

1350 # Check the validity of domain by referring DNS

1351 item=$1

1352 err checkdomain $1

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

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

1355 )

1356 pwcheck() {

1357 # $1=passwd

1358 dbpswd=`getpwfield $user pswd`

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

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

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

1362 }

1363 mypwhash() {

1364 mycrypt "`cat`" `genrandom 5`

1365 }

1366 wasureta() {

1367 user=$1

1368 if ! checkdomain $user; then

1369 contenttype; echo

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

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

1372 putfooter

1373 exit 0

1374 fi

1375 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1377 encpswd=`echo $newpswd|mypwhash`

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

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

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

1381 $msgdir/mail-newaccount.m4 \

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

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

1384 }

1385 checkauth() {

1386 user=`getpar user`

1387 skc=`getpar skey` # from cookie

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

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

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

1391 if chkskey "$skc"; then

1392 return 0

1393 fi

1394 fi

1395 pswd=`getpar pswd`

1396 quser=`sqlquotestr "$user"`

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

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

1399 return 4 # 4=server too heavy

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

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

1402 return 2 # 2=login fail

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

1404 wasureta "$user"

1405 return 1 # wasureta error

1406 fi

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

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

1409 if pwcheck "$pswd"; then

1410 newsession=`genrandom 34`

1411 if setskey "$user" "$newsession" &&

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

1413 gencookie "user=$user" "skey=$newsession"

1414 return 0

1415 else

1416 return 4 # Heavy load??

1417 fi

1418 fi

1419 err "Login failed: [$user]"

1420 return 2 # Password mismatch

1421 }

1422 showlogin() {

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

1424 test -z "$args" && resetskey

1425 s4name=${S4NAME:-s4}

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

1427 cat $layout/login.m4.html

1428 echo '</body></html>'

1429 ) | _m4 \

1430 -D_BODYCLASS_="login" \

1431 -D_TITLE_="$s4name" \

1432 -D_SYSNAME_="Welcome to $s4name" \

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

1434 exit 0

1435 }

1436 dologin() {

1437 checkauth

1438 st=$?

1439 if [ $st != 0 ]; then

1440 contenttype; echo

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

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

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

1444 showlogin # and EXIT

1445 fi

1446 }

1447

1448 # Do instant jobs here

1449 dbsetup

1450 trap cleanup INT HUP EXIT TERM PIPE

1451 # trap cleanup INT HUP

1452

1453

1454 cgiinit() {

1455 tmpd=`tmpd=$tmpdir mktempd`

1456 tmpf=$tmpd/stream.$$

1457 tmpfiles=$tmpfiles" $tmpd"

1458 addsession $session

1459 getcookie

1460 case "$REQUEST_METHOD" in

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

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

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

1464 # cat > $tmpf # too much?

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

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

1467 s="`cat $tmpf`"

1468 ;;

1469 esac

1470 case "$CONTENT_TYPE" in

1471 *boundary*)

1472 bndry=${CONTENT_TYPE#*boundary=}

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

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

1475 do

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

1477 #echo u=$us

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

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

1480 # err k=$k v=$v

1481 case "$k" in

1482 *:filename)

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

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

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

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

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

1488 :

1489 fi

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

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

1492 case "$mimetype" in

1493 [Ii]mage/x-xcf)

1494 bzip2 "$tmpd/$v"

1495 v=${v}.bz2

1496 ;;

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

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

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

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

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

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

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

1504 v=$vjpg

1505 else

1506 mimetype="Not supported"

1507 fi

1508 ;;

1509 [Ii]mage/*)

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

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

1512 ;;

1513 esac

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

1515 then

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

1517 continue

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

1519 replpar text string \

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

1521 continue

1522 fi

1523 ;;

1524 *)

1525 type='string'

1526 ;;

1527 esac

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

1529 setpar "$k" "$type" "$v"

1530 done

1531 ;;

1532 *)

1533 setviastring "$s"

1534 ;;

1535 esac

1536 }

1537 email4group() {

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

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

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

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

1542 sql="WITH

1543 grpemails AS (

1544 SELECT gname, user, val email

1545 FROM grp_mem NATURAL JOIN grp_mem_s

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

1547 useremails AS (

1548 SELECT user.name, val email

1549 FROM user

1550 LEFT JOIN user_m

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

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

1553 FROM useremails u LEFT JOIN grpemails g

1554 ON u.name=g.user

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

1556 query "$sql"

1557 }

1558 email4groupbyuid() {

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

1560 g=$1; shift

1561 uids=`echo "$@"`

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

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

1564 email4group "$g" `query "$sql"`

1565 }

1566 myemail4group() {

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

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

1569 }

1570 collectmembersbyid() {

1571 # Collect user names of group specified by grid

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

1573 query "SELECT user FROM grp_mem \

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

1575 }

1576 collectmembersbyid() {

1577 # Collect user names of group name

1578 qgrp=`sqlquote "$1"`

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

1580 }

1581 collectgecosesbyid() {

1582 # Collect user gecoses of group

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

1584 query<<-EOF

1585 SELECT gecos

1586 FROM gecoses

1587 WHERE name IN (SELECT user FROM grp_mem

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

1589 EOF

1590 }

1591 collectemail() (

1592 # Collect email addresses for group $1

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

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

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

1596 for e; do

1597 if isuser "$e"; then

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

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

1600 else

1601 qgrp=`sqlquote "$e"`

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

1603 gmem="grp_mem"

1604 else

1605 tm=`sqlquote "$TEAM"`

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

1607 fi

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

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

1610 $gmem g left join grp_mem_s s

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

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

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

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

1615 query "$sql"

1616 fi

1617 done

1618 )

1619 sendinvitation() (

1620 # $1=email

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

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

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

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

1625 gecos=`gecos`

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

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

1628 _m4 -D_URL_="$urlbase" \

1629 -D_USER_="$name" \

1630 -D_EMAIL_="$1" \

1631 -D_REGIST_="$regist" \

1632 -D_ADMIN_="$admin" \

1633 $msgdir/mail-invite.m4 \

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

1635 return 0

1636 )

1637 emaildomaincheck() {

1638 case "$1" in

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

1640 *@*)

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

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

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

1644 return 2

1645 fi

1646 return 0

1647 ;;

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

1649 esac

1650 }

1651 invite() {

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

1653 case "$email" in

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

1655 *@*)

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

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

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

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

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

1661 elif sendinvitation $email; then

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

1663 else # Cannot be reached here

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

1665 fi ;;

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

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

1668 esac

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

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

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

1672 fi

1673 if [ -f $invite_policy ]; then

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

1675 else

1676 pol="$invite_policy"

1677 fi

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

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

1680 -D_INVITE_POLICY_="$pol" \

1681 $layout/html.m4.html $layout/invite.m4.html

1682 }

1683 regist() {

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

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

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

1687 echo "bye bye" | html p

1688 reutrn

1689 fi

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

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

1692 cat<<EOF

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

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

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

1696 EOF

1697 return

1698 fi

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

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

1701 # Fake login password to wasureta

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

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

1704 wasureta $email

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

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

1707 addsession $1 # for removal after 1 minute

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

1709 $layout/login.m4.html

1710 return

1711 }

1712 group_safename() {

1713 # Convert $1 to safe group name

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

1715 }

1716 groupupdate() {

1717 gname=`getpar gname`

1718 qgname=`sqlquote "$gname"`

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

1720 # See ALSO same job in showgroup()

1721 newgname=`group_safename "$gname"`

1722 err newgname=$newgname

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

1724 err NewGNAME: gname=$newgname

1725 gname=$newgname

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

1727 replpar gname string "$gname"

1728 fi

1729 # Name confliction check

1730 parow=`getpar rowid`

1731 ## err parow=$parow

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

1733 query "BEGIN EXCLUSIVE;"

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

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

1736 if [ $count -gt 0 ]; then

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

1738 query "END;"

1739 return

1740 fi

1741 par2table $formdir/grp.def

1742 query "END TRANSACTION;"

1743 # Remove orphan

1744 : <<EOF

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

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

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

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

1749 EOF

1750 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1754 sql="delete from blog where id in

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

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

1757 query "$sql";

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

1759 return # and return

1760 fi

1761 fi

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

1763 fi

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

1765 grid=$(query $sql)

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

1767 grp $grid

1768 }

1769 groupclone() {

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

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

1772 case "$1" in

1773 */noteam)

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

1775 esac

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

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

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

1779 return

1780 fi

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

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

1783 return

1784 fi

1785 i=0

1786 while true; do

1787 copy="-copy$i"

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

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

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

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

1792 i=$((i++))

1793 continue

1794 fi

1795 break

1796 done

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

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

1799 query<<-EOF

1800 BEGIN;

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

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

1803 SELECT $newqname, key, val

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

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

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

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

1808 -- Copy members and their configuration --

1809 REPLACE INTO grp_mem SELECT $newqname, user

1810 FROM grp_mem WHERE gname=$qgrp;

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

1812 FROM grp_mem_s WHERE gname=$qgrp;

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

1814 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1815 -- Copy administrators --

1816 REPLACE INTO grp_adm SELECT $newqname, user

1817 FROM grp_adm WHERE gname=$qgrp;

1818 COMMIT;

1819 EOF

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

1821 STOPCLONEMSG=1 groupconf "$newrowid"

1822 }

1823 groupman() {

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

1825

1826 GF_STAGE="grpconf"

1827 GF_STAGE=groupupdate

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

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

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

1831 -D_DUMPTABLE_="syscmd(cat)" \

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

1833 }

1834 userconf() {

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

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

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

1838 }

1839 groupconf() {

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

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

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

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

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

1845

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

1847 grp=`getgroupbyid $rowid`

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

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

1850 return

1851 fi

1852

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

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

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

1856 ## Setup migration menu

1857 v=""

1858 for i in $S4WORLDLIST; do

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

1860 conf=`echo $_apath | sed -e 's/-world-/-config-/;s/\..*$/.sh/'`

1861 if [ x"$myname" != x"$_apath" ]; then

1862 v=${v}"<option value=\"$conf\">$_aname</option>$nl"

1863 fi

1864 done

1865 if [ -n "$S4MASTERDB" ]; then

1866 v=${v}"<option value=\"s4-config.sh\">Base</option>$nl"

1867 fi

1868 err v=$v

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

1870 migrate=$(cat<<-EOF

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

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

1873 <div>

1874 <form action="?migrategrp">

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

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

1877 外部からリンクしている場合は飛べなくなります。</p>

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

1879 `cgi_hidden stage migrategrp`

1880 `cgi_hidden rowid $rowid`

1881 `cgi_submit OK`

1882 `cgi_reset Reset`

1883 </form>

1884 </div>

1885 EOF

1886 )

1887 fi

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

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

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

1891 <div>

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

1893 <table>

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

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

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

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

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

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

1900 </table>

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

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

1903 </div>

1904 $migrate

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

1906 <div style="height: 5em; background: transparent;"></div>

1907 EOF

1908 fi

1909 }

1910 migrategrp() {

1911 rowid=`getpar rowid`

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

1913 grp=`getgroupbyid $rowid`

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

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

1916 return

1917 fi

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

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

1920 grp "$rowid"

1921 return

1922 fi

1923 destconf=`getpar migrateto`

1924 err destconf=$destconf

1925 if [ ! -e $destconf ]; then

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

1927 grp "$rowid"

1928 return

1929 fi

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

1931 srcconf=$worldconf

1932 else

1933 srcconf=s4-config.sh

1934 fi

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

1936 echo "移住操作" | html h1

1937 echo '<pre>'

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

1939 . ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

1940 rc=$?

1941 echo "</pre>"

1942 if [ $rc -eq 0 ]; then

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

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

1945 else

1946 echo "移住失敗" | html p

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

1948 fi

1949 return

1950 }

1951 mems() {

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

1953 kwd=`getpar kwd`

1954 listmember $kwd

1955 }

1956 grps() {

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

1958 kwd=`getpar kwd`

1959 listgroup $kwd \

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

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

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

1963 $layout/form+dump.m4.html

1964 }

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

1966 gpg=`getpar grp`

1967 grid=${1:-$gpg}

1968 grp=`getgroupbyid "$grid"`

1969 ## . ./s4-blog.sh

1970 jg=`getpar joingrp`

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

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

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

1974 fi

1975 htmlheader=$layout/html.m4.html

1976 showgroup "$grid"

1977 }

1978 sql4interestblogs() {

1979 cat<<EOF

1980 CREATE TEMPORARY VIEW interestblogs AS

1981 SELECT blog.rowid rid, id, author

1982 FROM blog

1983 NATURAL JOIN

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

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

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

1987 WHEN (SELECT user FROM grp_mem

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

1989 THEN 0

1990 ELSE 1

1991 END;

1992 EOF

1993 }

1994 listnewblogsql() { # $1=user

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

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

1997 newdays=${newdays:-14}

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

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

2000 WHERE user='$user'

2001 AND tblrowid IN

2002 ($blogreadflagrowid,

2003 $blogcutoffflagrowid)),

2004 $basetime -- "0"

2005 );"`

2006 cat<<EOF

2007 `sql4interestblogs`

2008 WITH article_ctime as (

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

2010 FROM article join article_s s using(id)

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

2012 GROUP BY id

2013 ), blog_title_owner as (

2014 SELECT blg.rid brid, id,

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

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

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

2018 ), blogall as (

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

2020 ), news as (

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

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

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

2024 FROM blogall bl

2025 LEFT JOIN

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

2027 ON bl.brid=al.tblrowid

2028 WHERE atime < bl.ctime

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

2030 LIMIT 10

2031 ) SELECT brid LINK, "新着",

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

2033 ctime, title,

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

2035 FROM news;

2036 EOF

2037 }

2038

2039 search_form() {

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

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

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

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

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

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

2046 @week → 最近一週間

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

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

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

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

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

2052 auth=""

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

2054 case "$1" in

2055 author=*)

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

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

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

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

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

2061 ;;

2062 grid=*)

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

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

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

2066 ;;

2067 esac

2068 inikwd="$2" # no need to htmlescape

2069 cat<<-EOF

2070 <div class="right">

2071 <form action="$myname">$auth

2072 <input type="text" name="kwd" value="$inikwd" title="$help"

2073 placeholder=" $placeholder " width="10" accesskey="k">

2074

2075 ${touchpanel:+<p class="help">$help</p>}

2076 <input type="hidden" name="stage" value="searchart">

2077

2078 </form>

2079 </div>

2080 EOF

2081 }

2082

2083 imgsrc_cache() (

2084 # $1 = directory for cache'ing

2085 # $2 = table (user_m or grp_m)

2086 # $3 = keycond (was: condition for choosingowner)

2087 # $4 = size : S = Small, M = Medium, O = Original

2088 dir="$1" tbl="$2"

2089 keycond="$3"

2090 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2091 ORDER BY rowid DESC LIMIT 1"

2092 [ -d "$dir" ] || mkdir -p "$dir"

2093 tmpf=$tmpd/imgsrc_cache.$$

2094 case "$4" in

2095 [Ss]) size=S ;;

2096 [Oo]) size=O ;;

2097 *) size=M ;;

2098 esac

2099 # ImageCache filename storing schema:

2100 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2101 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2102 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2103 valtype=`query "$sql0"`

2104 filename=${valtype%%//*}

2105 filetype=${valtype##*//file:}

2106 if [ x"$filename" = x"${filename%.*}" ]; then

2107 # If nor filename extension found, set it to image type

2108 case "$filetype" in

2109 image/*) filename=$filename.${filetype#image/} ;;

2110 esac

2111 fi

2112 cacheimg_S=$dir/S_$filename

2113 cacheimg_M=$dir/M_$filename

2114 cacheimg_O=$dir/$filename

2115 cacheimg=$dir/${size}_$filename

2116 sumfile="$dir/$filename.sum"

2117 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2118 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2119 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2120 # if cache is fresh and has the same checksum,

2121 echo "<img src=\"$cacheimg\">"

2122 else

2123 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2124 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2125 fifo2=$fifo.2

2126 mkfifo $fifo $fifo2

2127 fmt=${filename##*.}

2128 ## [[ NOTE ]]

2129 ## a. convert oldimage newimage

2130 ## b. convert oldimage fmt:- | convert - newimage

2131 ## b is much smaller than a

2132 cat $tmpf | unhexize \

2133 | tee $fifo \

2134 | convert -define ${fmt}:size=${iconxy_M} \

2135 -resize ${iconxy_M}'>' - ${fmt}:- \

2136 | tee $fifo2 \

2137 | convert - "$cacheimg_M" &

2138 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2139 -resize ${iconxy_S}'>' - ${fmt}:- \

2140 | convert - "$cacheimg_S" &

2141 printf '%s' "<img src=\"data:${filetype},"

2142 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2143 echo '">'

2144 echo "$sum" > $sumfile

2145 fi

2146 ## Now preparing cache image, done.

2147 ## Store this information to DB

2148 stbl=${tbl%_m}_s # user_s or grp_s

2149 pkey=${keycond%%=*} # Primary Key name

2150 pval=${keycond#*=} # Primary Key value

2151 query <<-EOF

2152 REPLACE INTO $stbl($pkey, key, type, val)

2153 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2154 EOF

2155 )

2156

2157 showhome() {

2158 # $1=userRowIdToShow

2159 err showhome \$1=$1

2160 case "$1" in

2161 *@*) uname=`getvalbypkey user name "$1"` ;;

2162 *) uname=`getvalbyid user name $1` ;;

2163 esac

2164 ## err ShowHome: uname=$uname

2165 td=`getcachedir home/"$1"`

2166 gecos=`gecos "$uname"`

2167 ## err SH:gecos=$gecos

2168 GF_VIEWONLY=1

2169 cond="gname in (select gname from grp_mem where user='$uname')"

2170 search_form_args=""

2171 if [ x"$user" = x"$uname" ]; then

2172 if [ -z "$S4MASTERDB" ]; then

2173 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2174 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2175 elif [ -n "$S4MASTERURL" ]; then

2176 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2177 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2178 fi

2179 usermenu="$usermenu

2180 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2181 # Display folders

2182 sql="select count(id) from article_m where id

2183 in (select id from article where author='$user')

2184 and type like 'file:%';"

2185 ## err nfile-sql=`echo "$sql"`

2186 nfile=`query "$sql"`

2187 # err nfile=$nfile

2188 if [ $nfile -gt 0 ]; then

2189 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2190 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2191 fi

2192 else

2193 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2194 GROUP BY user;"`

2195 usermenu="<p>Last seen on $latestlog</p>"

2196 search_form_args="author=$uname"

2197 fi

2198 . ./s4-blog.sh

2199

2200 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2201 search_form "$search_form_args" > $sf

2202 printf "%s さん" "$gecos" > $tf

2203 { echo "<div class=\"noprofimg\">"

2204 viewtable $formdir/user.def user $1

2205 echo "</div>"

2206 } > $pf

2207

2208 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2209 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2210 imf=$tmpd/profimg.$$; touch $imf

2211 if [ -n "$img" ]; then

2212 if true; then

2213 tbl=user_m

2214 enticond="name='$uname'"

2215 imgsrc_cache "$td/main" user_m "$enticond" M

2216 else

2217 { printf '%s' "<IMG src=\"data:${img#file:},"

2218 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2219 | sed 's/$..$/%\1/g'

2220 echo '">'

2221 }

2222 fi > $imf

2223 fi

2224 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2225 val='$uname';"`

2226 ## REMOVE This comment block until 2019/7/1

2227 ## err "----- `gdate +%FT%T.%3N` ------------C"

2228 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2229 listblog $uname > $bf

2230 ## unset ddd

2231 ## err "----- `gdate +%FT%T.%3N` ------------D"

2232

2233 hometail=$tmpd/tail.$$

2234 mkfifo $hometail

2235

2236 #Calling listgroupbytable, originally here

2237

2238 (

2239 # Display Most Recent Entry

2240 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2241 shortval=${shortval:-val}

2242

2243 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2244 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2245 ctime,

2246 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2247 (SELECT gecos FROM gecoses

2248 WHERE name=(SELECT val FROM blog_s

2249 WHERE key='owner' AND id=b.id)) owner,

2250 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2251 FROM blog b

2252 JOIN

2253 (SELECT distinct blogid, a.id aid, max(val) ctime

2254 FROM article a, article_s s

2255 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2256 GROUP BY blogid ORDER BY val DESC LIMIT 50

2257 ) m

2258 ON b.id=m.blogid;"

2259 # This should be as follows

2260 : <<EOF

2261 WITH arts AS(

2262 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2263 a.blogid, a.id id, s.val ctime

2264 FROM article a NATURAL JOIN article_s s

2265 WHERE s.key = 'ctime' AND a.author='$user'

2266 GROUP by s.id

2267 )

2268 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2269 FROM arts a0

2270 JOIN

2271 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2272 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2273 ORDER BY ctime DESC LIMIT 50;

2274 EOF

2275

2276 cat<<-EOF

2277 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2278 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2279 <div class="lcto">

2280 `DT_VIEW=replyblog dumptable html blog`

2281 </div>

2282 EOF

2283 unset DT_SQL

2284 if [ x"$user" = x"$uname" ]; then

2285 # Display NEWS

2286 # 2016-06-26

2287 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2288 acclog blog $blogreadflagrowid

2289 # echo "全部既読にしました" | html p

2290 fi

2291 # 2016-02-19 Counting NEWS without using dumptable.

2292 sql=`listnewblogsql "$user"`

2293 # echo "$sql" > tmp/listnew

2294 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2295 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2296 cont=$((cont-1))

2297 err newcount=$cont

2298 if [ $cont -gt 0 ]; then

2299 #echo "全体の新着記事${cont}傑" | html h2

2300 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2301 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2302 cat<<-EOF | html form 'action="?home"'

2303 `cgi_checkbox readchk yes 'id="read"'`<label

2304 for="read">新着ふくめて全部読んだことにする</label>

2305 `cgi_submit '確定'`

2306 `cgi_hidden read yes`

2307 EOF

2308 echo "$new10 "

2309 echo "</div>"

2310 else # If news is 0, set log cut off flag

2311 acclog blog $blogcutoffflagrowid # for speed

2312 fi

2313 else # Not My Home ($user != $uname)

2314 : # DT_SQL=

2315 fi

2316 ) > $hometail & # Is background call safe to m4??

2317 #

2318 listgroupbytable $formdir/grp.def $cond |

2319 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2320 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2321 -D_PROFIMG_="spaste(\`$imf')" \

2322 -D_BLOGS_="spaste(\`$bf')" \

2323 -D_SEARCH_="spaste(\`$sf')" \

2324 -D_NBLOG_="$nblog" \

2325 -D_GROUPS_="syscmd(\`cat')" \

2326 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2327 $layout/html.m4.html $layout/home.m4.html

2328

2329 # Record access log

2330 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2331 }

2332 commission() { # $1=grp-rowid $2=user-rowid

2333 contenttype; echo

2334 ## err commission: "$@"

2335 gname=`getgroupbyid $1`

2336 echo "グループ $gname 管理者委任" \

2337 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2338 if [ -n "$2" ]; then

2339 grp_reg_adm "$@"

2340 else

2341 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2342 fi

2343 }

2344 listgroupbytable() {

2345 # $1=deffile $2...=condition

2346 tagline=`grep :tag: $1`; shift

2347 and="${1:+and }" where=${1:+where }

2348 href="<a href=\"$myname?grp+"

2349 echo '<div class="listgroup">'

2350 NGsql="select distinct tag from\

2351 (select gname, max(case key when 'tag' then val end) as tag, \

2352 max(case key when 'ctime' then val end) as ctime\

2353 from grp_s group by gname order by ctime);"

2354 sql="select val from grp_s where key='tag' $and$* group by val;"

2355 ## err ListGRP: query sql="$sql"

2356 for tag in `query "$sql"`

2357 do

2358 ## err ListGrp: tag=$tag

2359 tn=${tagline%%=${tag}*}

2360 tn=${tn##*[ :]}

2361 sql="select rowid||':'||gname as 'グループ名',説明 from

2362 (select (select rowid from grp g where g.gname=grp_s.gname)

2363 as rowid,

2364 gname,

2365 max(case key when 'gecos' then val end) as '説明',

2366 max(case key when 'tag' then val end) as 'tag',

2367 max(case key when 'mtime' then val end) as mtime from grp_s

2368 $where$* group by gname having tag='$tag' order by mtime desc);"

2369 ## err PersonalGroupList= `echo $sql`

2370 echo "<h2>$tn</h2>"

2371 echo '<table class="b listgroup">'

2372 sq -header -html $db "$sql" \

2373 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2374 echo '</table>'

2375 done

2376 echo '</div>'

2377 }

2378 iconhref() (

2379 # $1=icon-file, $2=Href $3=title $4...=anchor

2380 data=`percenthex "$1"`

2381 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2382 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2383 href=$2; title=$3; shift 3

2384 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2385 )

2386 iconhref2() (

2387 # $1=icon-file, $2=Href $3=title $4...=anchor

2388 src=$1

2389 href=$2; title=$3; shift 3

2390 anchor=`echo $@|htmlescape`

2391 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2392 )

2393 listentry() (

2394 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2395 # Referring variable $iamowner=$grp to attach owner-request links

2396 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2397 cond='' hiddens=''

2398 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2399 if [ -z "$offset" ]; then

2400 offset=`getpar start`; offset=${offset%%[!0-9]*}

2401 offset=$((offset-1))

2402 fi

2403 offset=$((offset + 0)) # change to numeric forcibly

2404 [ $offset -lt 0 ] && offset=0

2405 limit=$listentlimit

2406 dir=`getcachedir "$1"`

2407 if [ x"$1" = x"user" ]; then

2408 hrb="$myname?home"

2409 deficon=person-default.png

2410 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2411 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2412 gcs=gecos

2413 else # if group

2414 hrb="$myname?grp"

2415 deficon=group-default.png

2416 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2417 gcs=name

2418 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2419 if [ -n "$tagline" ]; then

2420 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2421 ## err tagconv=$tagconv

2422 fi

2423 fi

2424 if [ ! -d $dir ]; then

2425 mkdir -p $dir

2426 fi

2427 if [ ! -s $dir/$deficon ]; then

2428 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2429 fi

2430 if [ -n "$2" ]; then

2431 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2432 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2433 fi

2434 tag=`getpar tag` tag2=`getpar tag2`

2435 if [ x"$tag" = x"NULL" ]; then

2436 tag="" tag2=""

2437 fi

2438 if [ -n "$tag$tag2" ]; then

2439 tag=${tag:-$tag2}

2440 qtag=`sqlquote "$tag"`

2441 cond2="tag=$qtag"

2442 fi

2443 if [ -n "$cond1$cond2" ]; then

2444 cond="$cond1${cond2:+ AND $cond2}"

2445 cond="WHERE ${cond# AND }"

2446 fi

2447

2448 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2449 # grpは呼出し元の動的スコープ変数でよくないな...

2450 ##qgrp=`sqlquote $grp`

2451 getgrp="(select gname from grp where rowid=${rowid:--1})"

2452 sql="select a.rowid, a.$link,

2453 coalesce(b.$gcs, a.$nm) as nick,

2454 quote(a.$nm) as qname,

2455 (SELECT val FROM ${tbl}_s

2456 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2457 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2458 || case when a.$nm in (select gname from grp)

2459 then printf('(%d名)',

2460 (select count(user) from grp_mem where gname=a.$nm))

2461 else ' <'||a.$nm||'>'

2462 end

2463 as name,

2464 b.tag,

2465 case when a.$nm in (select user from grp_adm

2466 where gname=$getgrp) then '管理者'

2467 when '$user' in (select user from grp_adm where gname=a.$nm)

2468 then 'ADMIN'

2469 when '$user' in (select user from grp_mem where gname=a.$nm)

2470 then 'Member'

2471 when '$iamowner' = '' then ''

2472 else ',not='||a.rowid end as ownerlink,

2473 CASE '$entity'

2474 WHEN 'グループ'

2475 THEN coalesce(

2476 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2477 'open')

2478 ||

2479 CASE WHEN '$user'

2480 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2481 THEN ' ismember'

2482 ELSE ''

2483 END

2484 ELSE 'user'

2485 END regmode

2486 from $tbl a left join

2487 (select $nm as name,

2488 max(case key when 'gecos' then val end) as gecos,

2489 max(case key when 'tag' then val end) as tag,

2490 max(case key when 'mtime' then val end) as mtime,

2491 max(case key when 'wtime' then val end) as wtime,

2492 max(case key when 'login' then val end) as login

2493 from ${tbl}_s group by $nm)

2494 b on a.$nm=b.name $cond $3

2495 order by b.wtime desc, b.login desc,

2496 b.mtime desc, b.tag desc, a.rowid asc"

2497 # Give precedence to newer maintained groups (2016-09-24)

2498 # Note that mtime is stored only in grp_s.

2499 ## err LE:sql.1="$sql"

2500 total=`query "with x as ($sql) select count(*) from x;"`

2501 echo "${entity} 一覧" | html h2

2502 echo '<div class="listentry">' # List-entry div

2503 # Show owner/member filter button

2504 METHOD=GET

2505 hiddens="$hiddens

2506 `cgi_hidden kwd \"$kwd\"`

2507 `cgi_hidden stage \"$stage\"`"

2508 if [ x"$tbl" = x"grp" ]; then

2509 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2510 fh="<select name=\"tag\">$nl"

2511 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2512 for l in $args; do

2513 val=${l#*=} tname=${l%=*}

2514 if [ x"$val" = x"$tag" ]; then

2515 s=" selected"

2516 selectedtags="(種別[${tname}]のみ)"

2517 else

2518 s=""

2519 fi

2520 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2521 done

2522 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2523 cat<<-EOF

2524 <form action="$myname" method="$METHOD">

2525 </form>

2526 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2527 for="ismembtn">参加中以外隠す</label>

2528 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2529 for="isadmbtn">管理者参加以外隠す</label>

2530 EOF

2531 # limit=3

2532 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2533 fi

2534 if [ $total -gt $limit ]; then

2535 echo '<div>'

2536 METHOD=GET cgi_form $stage <<EOF

2537 $form

2538 <label>次の語を含む${entity}で検索:

2539 `cgi_text kwd "$kwd"`</label>

2540 $hiddens

2541 EOF

2542 echo '</div>'

2543 else

2544 echo $selectedtags | html p

2545 fi

2546 cat<<EOF

2547 <form action="$myname" method="$METHOD">

2548 <p>${total}件中の<input class="hidesub" type="text" name="start"

2549 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2550 <input type="submit" value="確定"></p>

2551 </form>

2552 EOF

2553 if [ $((offset+limit)) -lt $total ]; then

2554 nextbtn=$(

2555 cat<<EOF

2556 <div class="right clear"><form action="$myname" method="$METHOD">

2557 `cgi_submit 次の${limit}件`

2558 $hiddens

2559 `cgi_hidden offset $((offset + limit))`</form></div>

2560 EOF

2561 )

2562 fi

2563 if [ $offset -gt 0 ]; then

2564 prevbtn=$(

2565 cat<<EOF

2566 <form action="$myname" method="$METHOD">

2567 `cgi_submit 前の${limit}件`

2568 $hiddens

2569 `cgi_hidden offset $((offset - limit))`</form>

2570 EOF

2571 )

2572 fi

2573 pnbtn="$nextbtn$prevbtn"

2574 echo $pnbtn

2575

2576 ## err ListEntry: `echo "$sql"\;`

2577 # sq $db here??? 2016-11-28

2578 query "$sql limit $limit ${offset:+offset $offset};" \

2579 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2580 # err name=$name owner=$ownerp lnk=$lnk

2581 # err newlnk=$lnk regmode=$regmode

2582 icondir=$dir/$id

2583 # Pick up only last icon

2584 htmlname=`echo $name|htmlescape`

2585 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2586 <p class=\"tag _$tag\">$tag</p>" \

2587 | _m4 $tagconv

2588 if [ -n "$NOSPEEDUP" ]; then

2589 files=`getvalbyid $tbl profimg $id $icondir`

2590 if [ -n "$files" ]; then

2591 icon=`echo "$files"|tail -1`

2592 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2593 else

2594 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2595 fi

2596 elif [ -n "$icon" -a -s "$icon" ]; then

2597 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2598 else

2599 cond="$nm=$qname"

2600 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2601 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2602 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2603 # err "img=[$img]"

2604 if [ -n "$img" ]; then

2605 echo "<a href=\"$hrb+$lnk\">"

2606 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2607 echo "</a>"

2608 else

2609 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2610 fi

2611 fi

2612 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2613 echo "</div>"

2614 done

2615 echo "</div>" # End of List-entry div

2616 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2617 )

2618 listmember() {

2619 listentry user "$@"

2620 }

2621 listgroup() {

2622 listentry group "$@"

2623 }

2624 hexteams() { # $1=gname, $2(optional)=user

2625 cond=${2:+" AND user='$2'"}

2626 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2627 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2628 }

2629 showgroup() { # $1=group-rowid

2630 if [ -z "$1" ]; then

2631 grid=`getpar grid`

2632 grid=${grid%%[!0-9]*}

2633 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2634 else

2635 grid=$1

2636 fi

2637 grp=`getgroupbyid $grid`

2638 qgrp=`sqlquote "$grp"`

2639 htmlgrp=`echo "$grp"|htmlescape`

2640 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2641 if isgroup "$grp"; then

2642 tf=$tmpd/title.$$

2643 sf=$tmpd/search.$$

2644 bodyclass=`query "SELECT val FROM grp_s

2645 WHERE gname=$qgrp AND key='regmode';"`

2646 if ismember "$user" "$grp"; then

2647 ismember="ismember"

2648 bodyclass="$bodyclass${bodyclass:+ }ismember"

2649 else

2650 ismember="" # bodyclass="group"

2651 fi

2652 bodyclass="$bodyclass grouphome"

2653 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2654 echo "グループ $htmlgrp" > $tf

2655

2656 showgroupsub $formdir/grp.def "$grid" | \

2657 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2658 -D_FORM_="syscmd(\`cat')" \

2659 -D_BODYCLASS_="$bodyclass" \

2660 -D_DUMPTABLE_="" \

2661 $htmlheader $sf $layout/form+dump.m4.html

2662 # $htmlheader $layout/form+dump.m4.html

2663 # $htmlheader is defined in grp()

2664 else # if $grp is removed at par2table

2665 listgroup

2666 fi

2667 }

2668 showgroupsub() {

2669 # $1=def-file $2=group-rowid

2670 # Using $ismember

2671 rowid=$2

2672 grp=`getgroupbyid $2`

2673 qgrp=`sqlquote "$grp"`

2674 td=`getcachedir grp/"$2"`

2675 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2676 if [ -z "$rowid" ]; then

2677 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2678 #grp=`sq $db "select gname from grp where rowid=$grp"`

2679 echo "showgroupsub: invalid argument($1 $2)" | html p

2680 return

2681 fi

2682 val=`getvalbyid grp profimg $rowid $tmpd`

2683 enticond="gname=$qgrp"

2684 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2685 if [ -n "$img" ]; then

2686 cat<<-EOF

2687 <p class="groupimg">

2688 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2689 EOF

2690 fi

2691 echo "<div class=\"noprofimg\">"

2692 viewtable $1 grp $rowid

2693 echo "</div>"

2694 if isgrpowner "$user" "$grp"; then

2695 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2696 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2697 iamowner=$rowid

2698 colmd=" mode"

2699 fi

2700 if [ -n "$ismember" ]; then

2701 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2702 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2703 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2704 cat<<-EOF

2705 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2706 href="?blog+$rowid">グループの新規話題作成</a>

2707 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2708 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2709 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2710 <div class="fold clear">

2711 `cgi_checkbox send yes id="send"`<label

2712 for="send">グループ全員にメッセージ送信</label>

2713 <div>

2714 `cgi_textarea message "" "cols=60"`

2715 `cgi_submit 送信`

2716 `cgi_reset リセット`

2717 </div>

2718 `cgi_hidden grp $rowid`

2719 </div></form>

2720 EOF

2721 fi

2722 # 加入ボタン + 加入者リスト

2723 if [ -n "$ismember" ]; then

2724 ismem='checked' state="(参加中)"

2725 else

2726 nomem='checked' state="(現在非加入)"

2727 fi

2728 # このグループでの加入アドレス

2729 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2730 and key='email';"`

2731 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2732 ## and key='email';"

2733 ##err email=$eml

2734 cat <<EOF

2735 <div class="fold clear">

2736 `cgi_checkbox reg yes id="reg"`<label

2737 for="reg">自身の加入状態を操作する</label>$state

2738 <div>

2739 EOF

2740 cgi_form grp <<EOF

2741 <p>このグループに</p>

2742 <table class="b">

2743 <tr><th>メンバーとして</th><td>

2744 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2745 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2746 <tr><th>参加する場合のメイルアドレス<br>

2747 <small>(メインのアドレスとは違うものにする場合に記入<br>

2748 同じでよい場合は空欄に)</small></th>

2749 <td>`cgi_text email $eml`</td></tr>

2750 </table>

2751 `cgi_hidden grp $rowid`

2752 EOF

2753 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2754 echo "moderated (承認加入の)グループなので実際に参加できるのは

2755 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2756 fi

2757 echo '</div></div>'

2758 echo '<h2>話題一覧</h2>'

2759 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2760 cat<<-EOF

2761 <form class="summary" action="$myname" title="$thelp">

2762 `cgi_hidden owner "$grp"`

2763 `cgi_hidden kwd "@week"`

2764 `cgi_hidden stage searchart`

2765 `cgi_submit "一週間のまとめ"`

2766 </form>

2767 EOF

2768 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2769 colstate="state:稼動状態:frozen=rowclass=凍結"

2770 DT_CHLD=article:blogid \

2771 DT_QOWNER="$qgrp" \

2772 DT_VIEW=replyblog dumptable html blog \

2773 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2774

2775 getgname="(select gname from grp where rowid=$rowid)"

2776 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2777 cm="?commission+$rowid"

2778 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2779 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2780 # team list

2781 hexteams=`hexteams "$grp"`

2782 if [ -n "$hexteams" ]; then

2783 echo "チーム一覧" | html h2

2784 echo '<div class="dumptable"><table class="b">'

2785 sq $db -html -header<<-EOF

2786 SELECT val TEAM,

2787 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2788 MEMBERS

2789 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2790 EOF

2791 echo '</table></div>'

2792 fi

2793 }

2794 grp_getbodyclass() {

2795 # Get css class name for document.

2796 # `moderated' for moderated groups

2797 # `ismember' for groups where user belongs

2798 # $1=GroupName (w/o quote)

2799 # $user=userNameCurrentlyLogin

2800 ## err grp_getbodyclass: 1="$1"

2801 qgrp=`sqlquote "$1"`

2802 query<<-EOF

2803 SELECT coalesce(

2804 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2805 'open')

2806 ||

2807 CASE WHEN '$user'

2808 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2809 THEN ' ismember'

2810 ELSE ''

2811 END;

2812 EOF

2813 }

2814 grpaction() { # $1=group-rowid

2815 err GRP_ACTION:IN

2816 grid=${1:-`getpar grp`}

2817 grp=`getgroupbyid "$grid"`

2818 htmlgrp=`echo "$grp" | htmlescape`

2819 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2820 if [ -z "$grp" ]; then

2821 echo "無効な指定です。" | html p; return

2822 fi

2823 if ! ismember $user "$grp"; then

2824 echo "加入者のみに許可された操作です。" | html p; return

2825 fi

2826 echo "グループ $grp 個別選択操作" \

2827 | _m4 -D_TITLE_="syscmd(\`cat')" \

2828 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2829 $layout/html.m4.html

2830

2831 isowner=""

2832 isgrpowner "$user" "$grp" && isowner="yes"

2833 usel=`getpar usel`

2834 if [ -n "$usel" ]; then

2835 uids=$(echo `echo $usel`|tr ' ' ',')

2836 ## err grpaction-1: grp=$grp, `echo $sql`

2837 text=`getpar text|tr -d '\r'`

2838

2839 rm=`getpar rm` cfm=`getpar confirm`

2840 ## err rm=$rm cfm=$cfm

2841 if [ x"$rm" = x"yes" ]; then

2842 if [ "$isowner" ]; then

2843 if [ x"$rm$cfm" = x"yesyes" ]; then

2844 # Eliminate

2845 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2846 for tbl in grp_mem grp_mem_s grp_mem_m; do

2847 sql="delete from $tbl $cond;"

2848 # echo "sql=$sql"

2849 query "$sql"

2850 err rmGRPuser "$sql"

2851 done

2852 num=`query "select count(*) from user where rowid in ($uids);"`

2853 #err num=$num

2854 if [ 0$num -gt 0 ]; then

2855 sql="select coalesce(b.val,a.name) from user a left join \

2856 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2857 # err `echo "$sql"`

2858 html pre<<EOF

2859 以下の${num}名のグループ $grp 登録を解除しました。

2860 `query "$sql"`

2861 EOF

2862 fi

2863 else

2864 echo "確認のチェックがないのでやめておきます。" | html p

2865 return

2866 fi

2867 else # not Group Owner

2868 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2869 return

2870 fi

2871 cat<<EOF

2872

2873 EOF

2874 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2875 if [ -z "$text" ]; then # if msg is empty

2876 echo "なにかメッセージを..." | html p

2877 return 0

2878 fi

2879 gecos=`gecos $user`

2880 safegc=`echo "$gecos" | tr -d '<>@'`

2881 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2882 fromad=`myemail4group "$grp"`

2883 ###mail_from="$safegc <$fromad>"

2884 mail_from="$safegc <$user>" # TEST: 2020/5/13

2885 test -n `getpar sender` &&

2886 export SENDER=$user # TEST: 2020/5/15

2887 replyto=$fromad

2888

2889 ## Start parse of attachment files

2890 if [ -n "`getpar email`" ]; then

2891 ar=`getpar supprcpt`

2892 if [ -n "$ar" ]; then

2893 for a in $ar; do

2894 if checkdomain "$a"; then

2895 supprcpt="$supprcpt $a"

2896 else

2897 err "SupprcptErr=[$a] by $user"

2898 removercpt="$removercpt $a"

2899 fi

2900 done

2901 fi

2902 subj=`getpar subject`

2903 afiles=""

2904 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2905 do

2906 f=$tmpd/$fn

2907 if [ -s $f ]; then

2908 afiles=$afiles"${afiles:+ }$f"

2909 fi

2910 done

2911 else

2912 preface=$(cat <<-EOF

2913 $url

2914 のグループ「$grp」のメンバーである $gecos さんから、

2915 あなた宛のメッセージです。

2916 ----------------------------------------------------------

2917 EOF

2918 )

2919 fi

2920 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2921 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2922 subj="${subj:-$gecos さんからのメッセージ}"

2923 REPLYTO=$replyto

2924 MAIL_FROM=$mail_from

2925 export REPLYTO SMAIL_TO MAIL_FROM

2926 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2927 for r in $rcpts; do

2928 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2929 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2930 else

2931 # Show sender and rcpts address for guest

2932 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2933 fi

2934 if [ -n "$afiles" ];then

2935 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2936 else

2937 smail "$r" "$subj"

2938 fi <<EOF

2939 ${preface:+$preface$nl}$text

2940 EOF

2941 done

2942 if [ $? = 0 ]; then

2943 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2944 sql="select coalesce(b.val, a.name) from

2945 (select name from user where rowid in ($uids)) a

2946 left join user_s b on a.name=b.name and b.key='gecos';"

2947 html pre<<EOF

2948 `query "$sql"`

2949 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2950 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2951 EOF

2952 err SendDone: `echo $sql`

2953 fi

2954 elif [ x"$rm" = x"commission" ]; then

2955 grp_reg_adm $grid $usel

2956 elif [ x"$rm" = x"addteam" ]; then

2957 team=`getpar team|sed "s/'/''/g"` # for single quotation

2958 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2959 if [ x"$team" != x"$newteam" ]; then

2960 echo "チーム名に使えない文字を除去しました" | html p

2961 team=$newteam

2962 fi

2963 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2964 cat<<-EOF | html p

2965 有効なチーム名を入力してください。

2966 カンマだけ、「なし」という名前は使えません。

2967 EOF

2968 echo "有効なチーム名を入力してください。" | html p

2969 else

2970 grp_add_team $grid "$team" $usel

2971 fi

2972 elif [ x"$rm" = x"rmteam" ]; then

2973 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2974 rmteam=`getpar rmteam|sed "s/'/''/g"`

2975 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2976 gname='$grp' AND user='$user' AND key='team'\

2977 AND val='$rmteam';\"`" ]; then

2978 grp_rm_team $grid "$rmteam" $usel

2979 else

2980 echo "所属していないチームの除去操作はできません。"|html p

2981 fi

2982 else

2983 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2984 fi

2985 fi

2986 fi

2987 # POST count summary

2988 from=`getpar from`; to=`getpar to`

2989 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2990 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2991 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2992 # New entry

2993 sql="WITH mems AS (

2994 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2995 ON gm.user=g.name

2996 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2997 ), target_article AS (

2998 SELECT id FROM article_s

2999 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3000 ), posts AS (

3001 SELECT author, count(author) post

3002 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3003 WHERE blogid IN (SELECT id FROM blog_s

3004 WHERE key='owner'

3005 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3006 AND key='text'

3007 GROUP BY author

3008 ), teams AS (

3009 SELECT user, group_concat(val, ', ') team

3010 FROM grp_mem_m

3011 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3012 AND key='team'

3013 GROUP BY user

3014 ), user_post AS (

3015 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3016 FROM mems m LEFT JOIN posts

3017 ON m.name=posts.author

3018 GROUP by m.rowid

3019 )

3020 SELECT

3021 CASE

3022 WHEN (SELECT user FROM grp_adm

3023 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3024 AND user=up.name) IS NOT NULL

3025 then 'k'

3026 ELSE ''

3027 END || rowid || ',' || gecos NAME,

3028 post POST, team _TEAM_

3029 FROM user_post up LEFT JOIN teams t

3030 ON up.name=t.user

3031 ORDER BY gecos;"

3032 ## err grpaction: "`echo \"$sql\"`"

3033 tf=$tmpd/title.$$

3034 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3035 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3036 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3037 for=\"cmadmin\">管理者委任</label>

3038 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3039 </p></div>"

3040 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3041 title=\"Shortcut: g${nl}Remove from the Group\"

3042 for=\"conf\">GRP登録解除</label>

3043 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3044 <p>この操作による通知は本人に行きません。

3045 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3046 </div>"

3047 # Get team list to which current user belongs into $hexteams

3048 allhexteams=$(hexteams "$grp")

3049 if [ -n "$isowner" ]; then

3050 myhexteams="$allhexteams" # admin can remove all teams' attr

3051 else

3052 myhexteams=$(hexteams "$grp" "$user")

3053 fi

3054 if [ -n "$myhexteams" ]; then

3055 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3056 title=\"Shortcut: s${nl}Strip a team tag from\"

3057 for=\"cmrmteam\">チーム属性除去</label>

3058 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3059 を除去します: `cgi_checkbox teamconfirm yes` 確認

3060 <p>この操作による通知は本人に行きません。

3061 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3062 </div>

3063 "

3064 fi

3065 stf=$tmpd/selteam.$$

3066 cgi_select_h selteam "5445414d" $allhexteams > $stf

3067 b1='<label> <input type="checkbox" name="usel" value="'

3068 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3069 br='<span id="reverse" title="Reverse Selection"></span>'

3070 #b2='"> <span>' b3='</span></label>'

3071 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3072 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3073 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

3074 cgi_form grpaction<<EOF \

3075 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

3076 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

3077 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

3078 | _m4 -D_TITLE_="spaste(\`$tf')" \

3079 -D_SUBTITLE_="チェック後操作ボタン" \

3080 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3081 $layout/form+dump.m4.html \

3082 | _m4 -D_TEAM_="spaste(\`$stf')"

3083 <p>下でチェックした人を対象として:</p>

3084 <div class="foldtabs">

3085 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3086 title="Shortcut: a${nl}Add a team tag to"

3087 for="cmteam">同じチーム属性を付与</label>

3088 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3089 `cgi_datalist_h teams $allhexteams`

3090 </div>

3091 ${rmteammsg}

3092 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3093 title="Shortcut: d${nl}DirectMail to"

3094 for="sendmsg" title="Direct Message">DM送信</label>

3095 <div>

3096 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3097 title="Using email format">email書式を使う</label>

3098 <div class="folded">

3099 <table>

3100 <tr><td>From: </td><td>$user</td></tr>

3101 <tr><td>このFrom:で送る</td>

3102 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3103 </td></tr>

3104 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3105 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3106 <tr><td>ファイル添付: </td>

3107 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3108 </table>

3109 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3110 </div>

3111 <div>本文:`cgi_textarea text "" cols=72`

3112 </div>

3113 </div>

3114 ${isowner:+$cmmsg$excmsg}

3115 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3116 </div>

3117 <h4>$htmlgrp 参加者一覧</h4>$fromtonote

3118 <table class="td2r thl">

3119 `sq $db -header -html "$sql"`

3120 </table>

3121 `cgi_hidden grp $grid`

3122 `cgi_hidden myuid $myuid id="myuid"`

3123 EOF

3124 }

3125 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3126 # Create TEMPORARY VIEW

3127 query<<EOF

3128 CREATE TEMPORARY VIEW writeusers AS

3129 SELECT DISTINCT author FROM article

3130 WHERE id in (

3131 select id from article where blogid=(select id from blog where rowid=$1)

3132 );

3133 CREATE TEMPORARY VIEW movablegroups AS

3134 SELECT g.rowid growid , g.gname

3135 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3136 ON grp.gname=gm.gname -- そのユーザが属している

3137 AND user='$user') g -- グループに絞る

3138 WHERE (SELECT author FROM writeusers

3139 EXCEPT

3140 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3141 IS NULL;

3142 $2

3143 EOF

3144 }

3145 sql4readableblogs() {

3146 # Create view of blogs that can be readable to $user

3147 # Blog is readable when:

3148 # 1: blog owner is an user

3149 # 2: else, 2.1: owner-group where the $user belongs

3150 # 2.2: else, owner-group is not moderated

3151 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3152 cat<<EOF ## | tee tmp/sql.out

3153 CREATE TEMPORARY VIEW readableblogs AS

3154 SELECT blog.rowid rid, id, author

3155 FROM blog

3156 NATURAL JOIN

3157 (SELECT id,

3158 max(CASE key WHEN 'owner' THEN val END) owner,

3159 max(CASE key WHEN 'mode' THEN val END) mode

3160 FROM blog_s GROUP by id) bs

3161 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3162 THEN 1 -- blog owner is an user, READABLE

3163 WHEN (SELECT val FROM grp_s

3164 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3165 AND

3166 (SELECT user FROM grp_mem

3167 WHERE gname=bs.owner AND user='$user') IS NULL

3168 THEN 0

3169 WHEN mode IN ('quiz', 'enquete')

3170 THEN 0 -- "quiz" mode blog cannot be searched

3171 ELSE 1

3172 END;

3173 EOF

3174 }

3175 editheading() { # $1=rowid-of-heading

3176 rowid=${1%%[!A-Z0-9a-z_]*}

3177 if [ -z "$rowid" ]; then

3178 echo "話題番号が未指定です。" | html p

3179 return

3180 fi

3181 owner=`getvalbyid blog owner $rowid`

3182 title=`getvalbyid blog title $rowid`

3183 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3184 | _m4 -D_TITLE_="修正" \

3185 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3186 -D_BLOGS_="" -D_DUMPTABLE_="" \

3187 -D_FORM_="syscmd(\`cat')" \

3188 $layout/html.m4.html $layout/form+dump.m4.html

3189 # Move to group

3190 if isuser "$owner"; then

3191 crview4article $rowid

3192 n=`query "SELECT count(*) FROM writeusers;"`

3193 ## err N=$n

3194 if [ $((n)) -gt 0 ]; then

3195 ## err ROWID=$rowid

3196 sql="SELECT growid || ':' || gname FROM movablegroups;"

3197 cat<<-EOF

3198 <div class="fold">

3199 `cgi_checkbox mv send id="mv"`<label

3200 for="mv">この話題をグループ所有に移動する</label>

3201 <div>

3202 <form action="?mvart" method="POST" enctype="multipart/form-data">

3203 移動先グループ:

3204 <select name="mv2grp">

3205 EOF

3206 query ".mode html"

3207 query<<-EOF |

3208 $sql

3209 .mode list

3210 EOF

3211 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3212 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3213 cat<<-EOF

3214 </select>

3215 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3216 そのグループに加入しているものに限られます)</p>

3217 <p>`cgi_checkbox cfm yes`<label>確認

3218 (この操作は元に戻すことができません)</label></p>

3219 `cgi_hidden blogrowid $rowid`

3220 `cgi_submit 移動`

3221 `cgi_reset Reset`

3222 </form>

3223 </div>

3224 </div>

3225 EOF

3226 fi

3227 # end of isuser "$owner"

3228 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3229 [ -n "$hexteams" ];}; then

3230 none="`echo なし|hexize`"

3231 cat<<-EOF

3232 <div class="fold">

3233 `cgi_checkbox mv2team send id="mv2team"`<label

3234 for="mv2team">この話題を以下のチームのものにする</label>

3235 <div><p>現在の所属チーム設定:

3236 `query "SELECT

3237 coalesce((SELECT val FROM blog_s

3238 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3239 AND key='team'),

3240 ':なし');"`</p>

3241 <form action="?mvart" method="POST" enctype="multipart/form-data">

3242 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3243 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3244 `cgi_hidden blogrowid $rowid`<br>

3245 `cgi_submit 移動`

3246 `cgi_reset Reset`

3247 </form></div></div>

3248 EOF

3249 fi

3250 }

3251 mvart() { # move diary to some group or team

3252 # or move blog of group to team which belong to the group

3253 blogrowid=`getpar blogrowid`

3254 cfm=`getpar cfm`

3255 ##### echo move blog:$blogrowid to $mv2grp | html p

3256 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3257 . ./s4-blog.sh

3258 if [ -z "$blogrowid" ]; then

3259 echo "無効な指定です(mvart)。" | html p

3260 return

3261 elif [ x"$cfm" != x"yes" ]; then

3262 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3263 elif { mv2grp=`getpar mv2grp`

3264 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3265 [ -n "$mv2grp" ]; }; then

3266 crview4article $blogrowid

3267 ########## TRANSACTION BEGIN

3268 query "BEGIN;"

3269 n=`query "SELECT count(*) FROM writeusers;"`

3270 ## err Nwriteuser=$n

3271 if [ $((n)) -gt 0 ]; then

3272 query<<-EOF

3273 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3274 WHERE key='owner'

3275 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3276 AND $mv2grp IN (SELECT growid FROM movablegroups);

3277 EOF

3278 fi

3279 query "END;"

3280 ########## TRANSACTION END

3281 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3282 [ -n "$mv2team" ];}; then

3283 # blog owner can move it to ANY team

3284 case "$mv2team" in

3285 'なし')

3286 cat<<-EOF

3287 DELETE FROM blog_s

3288 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3289 AND key='team';

3290 EOF

3291 ;;

3292 "") ;;

3293 *)cat<<-EOF

3294 BEGIN;

3295 REPLACE INTO blog_s(id, key, val)

3296 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3297 'team', '$mv2team');

3298 REPLACE INTO blog_s(id, key, val)

3299 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3300 'notify', 'all'); -- Change notify to all

3301 END;

3302 EOF

3303 esac | query

3304 fi

3305 blog_reply $blogrowid

3306 echo yes | html p

3307 }

3308 editart() { # $1=article-rowid $2=blogrowid

3309 rowid=${1%%[!A-Z0-9a-z_]*}

3310 blogrowid=${2%%[!A-Z0-9a-z_]*}

3311 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3312 echo "表示する記事番号が未指定です。" | html p

3313 return

3314 fi

3315 owner=`getvalbyid blog owner $blogrowid`

3316 title=`getvalbyid blog title $blogrowid`

3317 author=`getvalbyid article author $rowid`

3318 ## err EDITart: owner=$owner, author=$author

3319 if isgrpowner "$user" "$owner"; then

3320 : EDIT OK

3321 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3322 echo "本人か所有者しか編集できません." | html p

3323 return

3324 fi

3325 aid=`query "select id from article where rowid=$rowid;"`

3326 tmpout=$tmpd/editart.$$.out

3327 GF_ACTION="?replyblog+$blogrowid#$aid" \

3328 edittable $formdir/article.def article $rowid \

3329 > $tmpout

3330 rm -f /tmp/editart.out

3331 # Cannot use pipelining to m4 with genform() because of stdin stack

3332 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3333 -D_FORM_="syscmd(cat $tmpout)" \

3334 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3335 -D_BLOGS_= -D_DUMPTABLE_= \

3336 $layout/html.m4.html $layout/form+dump.m4.html

3337 }

3338 send2mem() {

3339 rowid=`getpar grp`

3340 rowid=${rowid%%[!0-9]*} # Cleaning

3341 if [ -z "$rowid" ]; then

3342 echo "グループが未指定です。" | html p

3343 return

3344 fi

3345 message=`getpar message`

3346 if [ -z "$message" ]; then

3347 echo "文章を入れてください。" | html p

3348 return

3349 fi

3350 grp=`getgroupbyid $rowid`

3351 members=`collectemail "$grp"`

3352 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3353 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3354 mailfrom="`gecos "$user"` <$mailfrom>"

3355 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3356 msg=$(cat<<-EOF

3357 $urlbase?grp+$rowid

3358 グループ $grp に所属する

3359 `gecos $user` さんよりメッセージ:

3360

3361 $message

3362 EOF

3363 )

3364 # smail rcpt subj (file)

3365 for m in $members; do

3366 echo "$msg" |

3367 MAIL_FROM=$mailfrom \

3368 SENDER=$noreply \

3369 REPLYTO=$mailfrom \

3370 smail "$m" "$sj"

3371 done

3372 cat<<EOF

3373 <p>以下のユーザに送信しました。</p>

3374 <pre>

3375 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3376 </pre>

3377 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3378 EOF

3379 }

3380 joingrpadmit() {

3381 # $1=yes/no $2=session-key

3382 if [ -z "$2" ]; then

3383 echo "bye bye" | html p; return

3384 fi

3385 t_usr=`session=$2 getpar adduser`

3386 t_grp=`session=$2 getpar group`

3387 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3388 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3389 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3390 echo "無効な加入依頼です。" | html p

3391 echo "有効期限が切れたか、

3392 他の管理者がいる場合は処理済みの可能性があります。" | html p

3393 return

3394 fi

3395 if ! isgrpowner "$user" "$t_grp"; then

3396 echo "グループ管理者のみの機能です。" | html p; return

3397 fi

3398 case $1 in

3399 yes) joingrp "$t_grp" "$t_usr" yes ;;

3400 no) joingrp "$t_grp" "$t_usr" no ;;

3401 *)

3402 echo "無効な指定です($1)。" | html p

3403 return ;;

3404 esac

3405 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3406 rcpts="`getgroupadminmails $t_grp` $user"

3407 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3408 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3409 に

3410 $t_usr

3411 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3412 しました。"

3413 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3414 query "delete from session where id='$2';"

3415 echo "$body" | html p

3416 }

3417

3418 joingrprequest() {

3419 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3420 jss="joingrp-`date +%s`-`genrandom 12`"

3421 addsession $jss +${memoplimitdays}days

3422 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3423 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3424 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3425 $url

3426 $user さんから

3427 グループ $1

3428 に加入依頼がありました。

3429

3430 承認する:

3431 $urlbase?joingrpadmit+yes+$jss

3432

3433 白紙に戻す:

3434 $urlbase?joingrpadmit+no+$jss

3435 EOF

3436 echo "管理者に加入依頼を出しました。

3437 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3438 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3439 }

3440 joingrp() {

3441 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3442 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3443 if isgrpowner "$user" "$1"; then

3444 isowner="yes"

3445 elif [ -n "$5" ]; then

3446 isowner="yes"

3447 else

3448 isowner=""

3449 fi

3450 ## err jg:isgrpowner: isowner="$isowner"

3451 if [ -n "$isowner" ]; then

3452 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3453 elif [ x"$2" != x"$user" ]; then # if user is not login user

3454 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3455 return

3456 elif [ x"$3" = x"no" ]; then

3457 : # Do not pursue those who leave

3458 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3459 : # Member can change own email address for the joining moderated group

3460 else # adding user is $user itself

3461 case `getgroupattr $1 regmode` in

3462 moderated)

3463 joingrprequest "$@" # Request only

3464 return

3465 ;;

3466 *)

3467 ;;

3468 esac

3469 fi

3470 qgname=`sqlquote "$1"`

3471 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3472 cond="where gname=$qgname and user='$2'"

3473 if [ x"$3" = x"yes" ]; then

3474 query "replace into grp_mem values($qgname, '$2');"

3475 # Notify joingrp to admin

3476 action="に加入しました。"

3477 if [ -n "$4" ]; then

3478 if msg=`emaildomaincheck "$4"`; then

3479 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3480 'string', '$4', NULL);"

3481 else

3482 echo $msg

3483 fi

3484 else

3485 query "delete from grp_mem_s $cond and key='email';"

3486 fi

3487 if [ -n "$5" ]; then # as ADMIN

3488 # Coming here means newly created group

3489 sql="select case\

3490 when (select count(*) from grp_mem where gname=$qgname)=1\

3491 then (select user from grp_mem\

3492 where gname=$qgname and user='$user')\

3493 else '' end; "

3494 err NewGrpChk: $sql

3495 if [ -n "`query \"$sql\"`" ]; then

3496 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3497 query "replace into grp_adm values($qgname, '$user');"

3498 fi

3499 fi

3500 else

3501 query "begin;

3502 delete from grp_mem $cond;

3503 delete from grp_mem_s $cond;

3504 delete from grp_mem_m $cond;

3505 delete from grp_adm $cond;

3506 delete from grp_adm_s $cond;

3507 delete from grp_adm_m $cond;

3508 end;"

3509 action="から脱退しました。"

3510 fi

3511 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3512 $url?grp+$grid

3513 $user (`gecos $user`)さんが

3514 グループ $1

3515 $action

3516 EOF

3517 }

3518 grp_add_team() (

3519 # $1=grp-rowid $2=team $3...=user-rowid(s)

3520 grp=`getgroupbyid $1`

3521 team=$2; shift; shift

3522 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3523 { echo "BEGIN;"

3524 for user; do

3525 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3526 '$grp',\

3527 (SELECT name FROM user WHERE rowid=$user),\

3528 'team', 'string', '$team');"

3529 done

3530 echo "END;"

3531 } | query

3532 )

3533 grp_rm_team() (

3534 # $1=grp-rowid $2=team $3...=user-rowid(s)

3535 grid=$1

3536 qgrp=$(sqlquote "`getgroupbyid $grid`")

3537 team=$2; shift; shift

3538 [ -z "$grid" -o -z "$team" ] && return

3539 { echo "BEGIN;"

3540 for user; do

3541 echo "DELETE FROM grp_mem_m\

3542 WHERE gname=$qgrp \

3543 AND user=(SELECT name FROM user WHERE rowid=$user)\

3544 AND key='team' AND val='$team';"

3545 done

3546 cat<<-EOF

3547 DELETE FROM blog_s

3548 WHERE rowid=(

3549 SELECT rowid

3550 FROM blog_s a

3551 WHERE key='team'

3552 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3553 AND NOT EXISTS (SELECT * FROM grp_mem_m

3554 WHERE key='team' AND val=a.val -- a.val=team

3555 AND gname = (SELECT val FROM blog_s b

3556 WHERE a.id=b.id AND key='owner')

3557 ));

3558 EOF

3559

3560 echo "END;"

3561 } | query

3562 )

3563 grp_reg_adm() {

3564 # $1=grp-rowid $2...=user-rowid

3565 grid=$1

3566 grp=`getgroupbyid "$1"`

3567 if [ -z "$grp" ]; then

3568 echo "無効なグループIDです" | html p; return

3569 fi

3570 if ! isgrpowner "$user" "$grp"; then

3571 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3572 fi

3573 shift

3574 for urid; do

3575 newadm=`query "select name from user where rowid=$urid;"`

3576 if [ -z "$newadm" ]; then

3577 echo "指定ユーザIDがおかしいようです。" | html p; return

3578 fi

3579 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3580 err ismember $newadm $grp

3581 if ismember $newadm "$grp"; then

3582 # OK, go ahead

3583 getgname="(select gname from grp where rowid=$grid)"

3584 query "replace into grp_adm values($getgname, '$newadm');"

3585 # confirm insertion

3586 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3587 if [ -n "`query \"$sql;\"`" ]; then

3588 echo "追加完了: $newadm" | html p

3589 else

3590 echo "追加失敗($1 $urid)" | html p

3591 fi

3592 fi

3593 showgroup $grid

3594 done

3595 }

3596 dt_rowhack() {

3597 # From: <TR>

3598 # ....

3599 # <TD>rowclass=foo</TD>

3600 # </TR>

3601 # To: <TR class="foo">....<TD>foo</TD></TR>

3602 sed -e '

3603 /^<TR>/ {

3604 :loop

3605 s/\n//

3606 N

3607 /<\/TR>/ {

3608 s/\n//

3609 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3610 n

3611 }

3612 $q

3613 b loop

3614 }'

3615 }

3616 dumptable() {

3617 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3618 # textのフィールドだけ全てダンプにしたほうがいいか

3619 # $DT_VIEW sets link

3620 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3621 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3622 VIEW=${DT_VIEW-replyblog}

3623 if [ -n "$VIEW" ]; then

3624 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3625 fi

3626 sqlfile=$tmpd/dump.sql

3627 : > $sqlfile # ensure to be empty

3628 printf '.mode html\n.header 1\n' > $sqlfile

3629 # $DT_CHLD=ChildTable:BindColumn

3630 if [ -n "$DT_CHLD" ]; then

3631 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3632 cat<<-EOF >> $sqlfile

3633 -- presql

3634 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3635 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3636 EOF

3637 # Speed up counting of new articles

3638 cat<<-EOF >> $sqlfile

3639 -- presql2

3640 DROP TABLE IF EXISTS _counts;

3641 CREATE TEMPORARY TABLE _counts AS

3642 SELECT $_i, count($_i) cnt

3643 FROM $_t GROUP BY $_i;

3644 /* Prepare NEW count table */

3645 CREATE TEMPORARY TABLE _target AS

3646 SELECT b.rowid trowid, b.id

3647 FROM "$2" b JOIN "$2_s" s

3648 ON b.id=s.id AND s.key='owner'

3649 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3650

3651 DROP TABLE IF EXISTS _children;

3652 CREATE TEMPORARY TABLE _children AS

3653 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3654 FROM (SELECT t.trowid, t.id $_i, a.id

3655 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3656 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3657

3658 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3659 DROP TABLE IF EXISTS _news;

3660 DROP VIEW IF EXISTS _news;

3661

3662 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3663 -- INSERT INTO _news

3664 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3665 CREATE TEMPORARY VIEW _news AS

3666 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3667 FROM (SELECT DISTINCT id FROM _target)

3668 a LEFT JOIN

3669 (SELECT $_i, count(ctime) newcnt

3670 FROM _children x

3671 WHERE ctime > coalesce((SELECT time from myacclog

3672 WHERE tblrowid=x.trowid),

3673 '1970-01-01')

3674 GROUP BY $_i) b

3675 ON a.id=b.$_i;

3676 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3677 EOF

3678 # REMOVE next line until 2019/5/1

3679 cntall="(select count($_i) from $_t where $_i=a.id)"

3680 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3681 # REMOVE next assignment until 2019/5/1

3682 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3683 and id in (select id from $_t where $_i=a.id) \

3684 and val > coalesce((select time from myacclog where \

3685 tblrowid=a.rowid),\

3686 '1970-01-01'))"

3687 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3688 cnt="$cntnew as '新着', $cntall as '総数',"

3689 dt_class=" td2r td3r dumpblogs"

3690 fi

3691 # Construct join expression

3692 eav="" scols=""

3693 pk=`gettblpkey $2`

3694 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3695 substr=${substr:-%s}

3696 for col in ${3:-`gettbl_s_cols $2`}; do

3697 valvar=val

3698 case $col in

3699 gecos) scols="$scols${scols:+, }${col#}"

3700 continue ;; # built-in column name

3701 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3702 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3703 case "$as" in

3704 *:*=*) cnd=${as#*:}

3705 h=${cnd%%=*} v=${cnd#*=}

3706 h=`sqlquotestr "$h"`

3707 v=`sqlquotestr "$v"`

3708 valvar="CASE val WHEN $h THEN $v END"

3709 as=${as%%:*} ;;

3710 esac

3711 ;;

3712 *) as=${col} ;;

3713 esac

3714 ss=`printf "$substr" "$valvar"`

3715 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3716 scols="$scols${scols:+, }b.$as"

3717 done

3718 #case author when '$user' then a.rowid else '---' end as ID,

3719 if [ -n "$DT_SQL" ]; then

3720 echo "$DT_SQL"

3721 else

3722 cat<<-EOF

3723 SELECT a.rowid as LINK, $cnt $scols

3724 FROM $2 a LEFT JOIN

3725 (SELECT $pk,$eav,

3726 max(CASE key

3727 WHEN 'owner'

3728 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3729 as gecos

3730 FROM ${2}_s c GROUP BY $pk)

3731 b ON a.$pk=b.$pk $4;

3732 EOF

3733 fi >> $sqlfile

3734 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3735 sqlog<<-EOF

3736 *** SQL-file: $sqlfile ***

3737 `cat $sqlfile`

3738 EOF

3739 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3740 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3741 cat $sqlfile >> tmp/sql

3742 # query ".read $sqlfile" > $tmpd/foo

3743 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3744 cp $tmpd/foo tmp/

3745 err "----- `gdate +%FT%T.%3N` ------------555555"

3746 ## $ddd LINE exists at the end of this function

3747 fi

3748 printf '.mode list\n.header 0\n' >> $sqlfile

3749 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3750 <div>

3751 <div class="dumptable">

3752 <table class="b$dt_class">

3753 `query ".read $sqlfile"`

3754 </table>

3755 </div>

3756 </div>

3757 EOF

3758 ### `query ".read $sqlfile"`

3759 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3760

3761 ## REMOVE THIS!

3762 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3763 }

3764

3765 clean-orphaned() {

3766 # This shoud be done by foreign_key rules, but some db lack them

3767 query<<-EOF

3768 -- Find blogs that have no parent

3769 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedblog AS

3770 SELECT blog.id,val FROM blog JOIN blog_s bs

3771 ON blog.id=bs.id AND key='owner'

3772 WHERE val NOT IN (SELECT gname FROM grp)

3773 AND val NOT IN (SELECT name FROM user);

3774 -- Remove them

3775 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3776

3777 -- Find articles that have no parent blog

3778 CREATE TEMPORARY VIEW IF NOT EXISTS orphanedarticle AS

3779 SELECT id FROM article

3780 WHERE blogid NOT IN (SELECT id FROM blog);

3781

3782 -- Remove them

3783 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3784 EOF

3785 }

3786 par2table() (

3787 # copy current parameters of par into destination table

3788 # $1=definition-file

3789 # Using $user and $session

3790 # Return value:

3791 # 0: Stored successfully

3792 # 1: Insufficient fillings

3793 # 2: No permission to modify the record

3794 # 3: Invalid rowid

3795 # 4: SUCCESS to delete

3796 # 5: Stop deletion for lack of confirm check

3797 # 6: Password length too short

3798 # 7: Password mismatch

3799 # 8: Old password incorrect

3800 # 9: Duplicated post

3801 rowid=`getpar rowid`

3802 if [ ! -e $1 ]; then

3803 echo "テーブル定義ファイルが見付かりません" | html p

3804 exit 1

3805 fi

3806 tbl=${1%.def}

3807 tbl=${tbl##*/}

3808 if [ -n "$rowid" ]; then # Modify existing entry

3809 if [ x"$tbl" = x"user" ]; then

3810 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3811 elif [ x"$tbl" = x"grp" ]; then

3812 sql="select gname from $tbl where rowid=$rowid;"

3813 ##err p2t:grp:q $sql

3814 isgrpowner "$user" "`query $sql`" && rowowner=$user

3815 elif [ x"$tbl" = x"blog" ]; then

3816 # Check if owner in blog_s

3817 blogowner=`getvalbyid blog owner "$rowid"`

3818 if isgrpowner "$user" "$blogowner"; then

3819 rowowner=$user

3820 else

3821 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3822 fi

3823 else

3824 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3825 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3826 fi

3827 ### err rowowner=$rowowner

3828 if [ x"$user" != x"$rowowner" ]; then

3829 echo "他人のレコードはいじれないの" | html p

3830 return 2

3831 elif [ -z "$rowowner" ]; then

3832 echo "指定したレコードはないみたい" | html p

3833 return 3

3834 fi

3835 rm=`getpar rm` cfm=`getpar confirm`

3836 # Editing existent entry

3837 if [ x"$rm" = x"yes" ]; then

3838 if [ x"$rm$cfm" = x"yesyes" ]; then

3839 query "delete from $tbl where rowid=$rowid;"

3840 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

3841 clean-orphaned

3842 fi

3843 return 4

3844 else

3845 echo "消去確認のチェックがないので消さなかったの..." | html p

3846 return 5

3847 fi

3848 fi

3849 fi

3850

3851 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3852 if [ -n "$rowid" ]; then

3853 # Update of existing record

3854 for col in `gettblcols $tbl`; do

3855 val=`getparquote $col`

3856 [ -z "$val" ] && continue

3857 ## err query "update $tbl set $col=$val where rowid=$rowid"

3858 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3859 sql="update $tbl set $col=$val where rowid=$rowid;"

3860 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3861 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3862 ## User name cannot be changed with interface provided with this

3863 ## script. But we offer the trigger to change owner user

3864 ## of blog_s table.

3865 #err "select quote($col) from $tbl where rowid=$rowid;"

3866 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3867 cat<<-EOF | query

3868 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3869 -- should use EXCLUSIVE transaction outside of this.

3870 SAVEPOINT par2table;

3871 $sql

3872 update blog_s set val=$val

3873 where key='owner' and val=$old;

3874 RELEASE SAVEPOINT par2table;

3875 EOF

3876 ## XX: DIRTY Hack Ends here

3877 ## We should keep blog's owner as a single column which has

3878 ## foreign key constraint with primary key of grp/user.

3879 else

3880 query "$sql"

3881 fi

3882 done

3883 # Then, set up $pval for further insertion of tbl_s and tbl_m

3884 for col in `gettblpkey $tbl`; do

3885 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3886 pval="$pval${pval:+, }\"$val\""

3887 done

3888 else

3889 # New entry

3890 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3891 # Avoid empty repost of article.

3892 if [ x"$tbl" = x"article" ]; then

3893 # If rowid is empty and ID exists in article-table, that is REPOST!

3894 aid=`getpar id`

3895 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3896 if [ -n "$xaid" ]; then

3897 # REPOST of article

3898 html p <<-EOF

3899 書き込み直後のリロードなので上書きを回避します。

3900 最新記事は末尾の「再読み込み」ボタンから見てください。

3901 EOF

3902 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3903 return 9 # STOP Duplicated posting

3904 fi

3905 fi

3906 # Generate values() for primary keys

3907 for col in `gettblpkey $tbl`; do

3908 # Genuine primary keys for _m and _s

3909 val=`getvalquote $tbl $col`

3910 [ -z "$val" ] && continue

3911 pval="$pval${pval:+, }$val"

3912 done

3913 ##err pval=$pval

3914 for col in `gettblfkey $tbl`; do

3915 # args for values() to insertion into master table

3916 val=`getvalquote $tbl $col`

3917 [ -z "$val" ] && continue

3918 formaster=$formaster"${formaster:+, }$val"

3919 done

3920 formaster="$pval${formaster:+, }$formaster"

3921 ## err formaster=$formaster

3922 if [ -z "$formaster" ]; then

3923 echo "項目を全て埋めてください" | html pre

3924 return 1

3925 fi

3926 ## err "replace into $tbl values($formaster);"

3927 query "replace into $tbl values($formaster);"

3928 ## Insertion to master table, done

3929 fi

3930

3931 transaction=$tmpd/sqlfile.sql; touch $transaction

3932 for kt in s m; do

3933 tb2=${tbl}_$kt

3934 for col in `gettbl_${kt}_cols $tbl`; do

3935 ptype=`getpartype $col "limit 1"`

3936

3937 # First, check update of existing entries in _m

3938 if [ $kt = m ]; then

3939 # sessID|address.1.22|string|Somewhere-x.y.z

3940 sql=""

3941 ##err dots from query "select var from par where var like '$col.%';"

3942 for v in `query "select var from par where var like '$col.%';"`; do

3943 # v=address.1.22

3944 st_rowid=${v##*.}

3945 origcol=${v%%.*} # original column derived from

3946 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3947 ##case `getpartype $v` in

3948 ## err CASE `gettbl_coltype $tbl/$origcol` in

3949 ## err edit flag = `getpar action.$v`

3950 case `getpar action.$v` in

3951 rm)

3952 if [ x`getpar confirm.$v` = x"yes" ]; then

3953 newsql="delete from $tb2"

3954 else

3955 echo "削除確認未チェック" | html p

3956 fi ;;

3957 edit)

3958 case `gettbl_coltype $tbl/$origcol` in

3959 image|document|binary)

3960 file=$tmpd/`getparfilename $v`

3961 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3962 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3963 continue

3964 fi

3965 ## err type=file=$file

3966 [ -z "$file" ] && continue

3967 bn=`sqlquotestr "${file##*/}"`

3968 bin="X'"$(hexize "$file")"'"

3969 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3970 type=\"file:$ct\"

3971 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3972 cachedir=`getcachedir "$tbl/$rowid"`

3973 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3974 rm -rf $cachedir

3975 ;;

3976 *)

3977 newsql="update $tb2 set val=(select val from par where var \

3978 like '$col.%.$st_rowid')"

3979 ;;

3980 esac

3981 ;;

3982 *) # maybe "keep", do not modify value

3983 continue

3984 ;;

3985 esac

3986 # err newsql=$newsql

3987 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3988 done

3989

3990 if [ x"$bin" = x"NULL" ]; then

3991 ## err repl:normal sql=`echo $sql`

3992 if [ -n "$transaction" ]; then

3993 cat<<-EOF >> $transaction

3994 $sql

3995 DELETE FROM $tb2 WHERE type='string' AND val='';

3996 EOF

3997 else

3998 query "$sql

3999 delete from $tb2 where type='string' and val='';"

4000 ## err repl:normal done

4001 fi

4002 else

4003 # Binary update line is TOO LONG to pipelining

4004 sqlfile="$tmpd/sqlf.$$"

4005 if [ -n "$transaction" ]; then

4006 echo "$sql" >> $transaction

4007 else

4008 echo "$sql" > $sqlfile

4009 query ".read $sqlfile"

4010 fi

4011 fi

4012 # Rest of kt==m: set multiple mode

4013 nr=`getparcount $col`

4014 else

4015 nr=1 # for kt==s, number of records is 1

4016 fi

4017

4018 i=0

4019 while [ $i -lt $nr ]; do

4020 limit="limit 1 offset $i"

4021 i=$((i+1)) # increase beforehand against continue

4022 val=`getvalquote $tbl $col "$limit"`

4023 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4024 ## err $col=$val

4025 bin=NULL

4026 ## err partype$col=`getpartype $col "$limit"`

4027 case $ptype in

4028 file) file=$tmpd/`getparfilename $col "$limit"`

4029 ## err parfile-$col=$file

4030 [ -z "$file" ] && continue

4031 bin="X'"$(hexize "$file")"'"

4032 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4033 type=\"file:$ct\" ;;

4034 "*"*) continue ;; # foreign table

4035 *) type=\"string\" ;;

4036 esac

4037 case `gettbl_coltype $tbl/$col` in

4038 password) # special care for password

4039 # name={password,pswd1,pswd2}

4040 p1=`getpar pswd1 "$limit"`

4041 if [ -z "$p1" ]; then

4042 continue # SKIP password setting, if p1 is empty

4043 else

4044 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4045 ## err pswd=$pswd

4046 if pwcheck "$pswd"; then

4047 if [ x"$p1" = x"$p2" ]; then

4048 case "$p1" in

4049 ??????????*) ;;

4050 *) echo "パスワードは10字以上にしてください。" | html p

4051 return 6;;

4052 esac

4053 val="\"`echo $p1|mypwhash`\""

4054 else

4055 echo "2つの新パスワード不一致" | html p

4056 return 7

4057 fi

4058 else

4059 echo "旧パスワード違います" | html p

4060 return 8

4061 fi

4062 fi

4063 ;;

4064 esac

4065 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4066 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4067 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4068 if [ x"$bin" = x"NULL" ]; then

4069 ## err Normal-query: `echo $sql`

4070 if [ -n "$transaction" ]; then

4071 echo "$sql" >> $transaction

4072 else

4073 query "$sql"

4074 fi

4075 else

4076 sqlfile="$tmpd/query.$$"

4077 ## err sqlfile=`ls -lF $sqlfile`

4078 if [ -n "$transaction" ]; then

4079 echo "$sql" >> $transaction

4080 else

4081 echo "$sql" > $sqlfile

4082 query ".read $sqlfile"

4083 fi

4084 fi

4085 ## err p2t done

4086 done

4087 done

4088 done

4089 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4090 -- We cannot use transaction here, because groupupdate may use it.

4091 SAVEPOINT pa2table_insert;

4092 .read $transaction

4093 RELEASE SAVEPOINT pa2table_insert;

4094 EOF

4095 rc=$?

4096 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

4097 ## err "Table:$tbl update done "

4098 return $rc

4099 )

4100 genform() {

4101 # $1 = form definition file

4102 # $2, $3 (optional)= table name and ROWID

4103 # If $GF_VIEWONLY set and nonNull, output values without form

4104 # If $GF_ARGS set, use it as content-strings in the form

4105 # If $GF_OWNER set, use it as value of name="owner"

4106 # If $GF_STAGE set, use it as value of name="stage"

4107 forms="" hiddens="" rowid=$3

4108 if [ ! -e "$1" ]; then

4109 echo "そのようなデータベースはないようです($2)。" | html p

4110 return

4111 elif [ -n "$2" ]; then

4112 rec=`query "select * from $2 where rowid='$rowid';"`

4113 if [ -z "$rec" ]; then

4114 pk=`gettblpkey $2`

4115 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4116 rec=`query "select rowid from $2 where $pk='$rowid';"`

4117 rowid=$rec

4118 rec=$3

4119 fi

4120 if [ -z "$rec" ]; then

4121 echo "そんなレコードはないみたいね..." | html p

4122 return

4123 fi

4124 fi

4125 if [ -z "$GF_VIEWONLY" ]; then

4126 rm='<input id="rm" name="rm" type="checkbox"

4127 value="yes"><label for="rm">このエントリの削除</label>

4128 <span>ほんとうに消しますよ(確認)!

4129 <input name="confirm" type=checkbox value="yes">はい</span>'

4130 fi

4131 # Image Cache dir

4132 ## err genform: getcache=$2/$rowid

4133 td=`getcachedir "$2/$rowid"`

4134 while IFS=: read prompt name keytype type args; do

4135 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4136 sp="${args:+ }"

4137 form="" val=""

4138 if [ -n "$rowid" ]; then

4139 # err genform2a: Seeking for "$2.$name, type=$type"

4140 rawval=`getvalbyid $2 $name $rowid $td`

4141 val=`echo "$rawval"|htmlescape`

4142 ## err genform3a: getvalbyid $2 $name $rowid $td

4143 ## err genform3b: val="[$val]" type="$type"

4144 fi

4145 if [ -n "$GF_VIEWONLY" ]; then

4146 is_hidden "$2" "$name" && continue

4147 fi

4148 case "$type" in

4149 text*)

4150 cgiform=cgi_multi_$type

4151 if [ -s $td/$name.count -a -n "$val" ]; then

4152 form=`$cgiform $name $td`

4153 val=$(echo "$val"|

4154 while read fn; do

4155 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4156 </td></tr>$nl"

4157 done)

4158 val="<table>$nl$val$nl</table>"

4159 else

4160 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4161 form=`cgi_$type $name "$rawval" "$args"`

4162 fi

4163 ;;

4164 [Rr][Aa][Dd][Ii][Oo])

4165 fh="<label><input type=\"radio\" name=\"$name\""

4166 form="`echo $args|sed -e \

4167 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4168 ;;

4169 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4170 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4171 ;;

4172 [Ss][Ee][Ll][Ee][Cc][Tt])

4173 fh="<select name=\"$name\">$nl"

4174 form=$(for l in $args; do

4175 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4176 done)

4177 if [ -n "$val" ]; then

4178 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4179 fi

4180 form="$fh$form</select>"

4181 ;;

4182 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4183 if [ -s $td/$name.count ]; then

4184 form=`cgi_multi_file $name $td "$args"`

4185 if [ -n "$val" ]; then

4186 hrfb="$myname?showattc+$2_m"

4187 val=$(echo "$rawval" \

4188 | while read fn; do

4189 data=`percenthex "$td/$fn"`

4190 #ct=`cat $td/$fn.content-type`

4191 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4192 ri=`cat "$td/$fn.rowid"`

4193 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4194 #imgsrc="<img src=\"data:$ct,$data\">"

4195 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4196 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4197 done)

4198 fi

4199 else

4200 form="<input type=\"file\" name=\"$name\" $args>"

4201 if [ -n "$val" ]; then

4202 imgs=$(echo "$rawval"\

4203 |while read fn;do

4204 data=`percenthex "$td/$fn"`

4205 echo "<img src=\"data:image/png,$data\">$fn<br>"

4206 done)

4207 form=$form"<br>$imgs"

4208 val=$imgs # 2015-06-15

4209 else

4210 form="<input type=\"file\" name=\"$name\" $args>"

4211 fi

4212 fi

4213 ;;

4214 [Hh][Ii][Dd][Dd][Ee][Nn])

4215 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4216 args="value=\"$GF_STAGE\""

4217 fi

4218 form="<input type=\"hidden\" name=\"$name\" $args>"

4219 prompt='' # Remove prompt

4220 ;;

4221 [Aa][Uu][Tt][Hh][Oo][Rr])

4222 [ -n "$GF_VIEWONLY" ] && continue

4223 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4224 prompt="" ;;

4225 [Oo][Ww][Nn][Ee][Rr])

4226 [ -n "$GF_VIEWONLY" ] && continue

4227 val=${GF_OWNER:-$val}

4228 val=${val:-$user}

4229 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4230 prompt="" ;;

4231 [Uu][Ss][Ee][Rr])

4232 # XXX: is null $user ok?

4233 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4234 [ -n "$GF_VIEWONLY" ] && continue

4235 form="$user"

4236 ;;

4237 [Pp]assword)

4238 [ -n "$GF_VIEWONLY" ] && continue

4239 form="`cgi_passwd`"

4240 val=""

4241 ;;

4242 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4243 [ -n "$GF_VIEWONLY" ] && continue

4244 if [ -z "$rowid" ]; then

4245 val=`genserial`

4246 fi

4247 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4248 prompt="" ;;

4249 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4250 prompt=""

4251 ;;

4252 parent|path|blog*)

4253 prompt=""

4254 ;;

4255 "*"*)

4256 tail=$tail"``"

4257 continue ;;

4258 esac

4259 if [ -n "$prompt" ]; then

4260 if [ -n "${GF_VIEWONLY}" ]; then

4261 form=$val

4262 else

4263 :

4264 fi

4265 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4266 else

4267 hiddens=$hiddens$nl"$form"

4268 fi

4269 done < $1

4270 # enctype="multipart/form-data"

4271 cat<<EOF

4272 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4273 ${rowid:+$rm}

4274 <table class="b $2">

4275 $forms

4276 </table>$hiddens

4277 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4278 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4279 EOF

4280 if [ -z $GF_VIEWONLY ]; then

4281 cat<<EOF

4282 <input type="submit" name="sub" value="OK">

4283 <input type="reset" name="res" value="Reset">

4284 EOF

4285 fi

4286 cat<<EOF

4287 $GF_ARGS</form>

4288 $tail

4289 EOF

4290 }

4291 edittable() {

4292 # $1=form-def $2=table $3 rowid

4293 genform "$@"

4294 }

4295 viewtable() {

4296 GF_VIEWONLY=1 genform "$@"

4297 }

4298 showattc() {

4299 # $1=table_m $2=rowid &optional $3=RawFlag

4300 ## err \$1=$1 \$2=$2 \$3=$3

4301 if ! isfilereadable $user $1 $2; then

4302 contenttype; echo

4303 echo "このファイルは管理者のみしか見られません" | html p

4304 putfooter; exit

4305 fi

4306 idir=`umask 002; mktempd` || exit 1

4307 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4308 bin=$idir/$myname-$$.bin

4309 sql="select quote(bin) from $1 where rowid='$2';"

4310 ## err showattc: sql: $sql

4311 sq $db "$sql" | unhexize > $bin

4312 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4313 type=${tv%//*} fn=${tv#*//}

4314 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4315 ct=${type#file:}

4316 case $ct in # all text/* changed to text/plain

4317 text/*)

4318 charset=`nkf -g $bin|cut -d' ' -f1`

4319 case $charset in

4320 ASCII*) charset="" ;;

4321 esac

4322 if [ -z "$3" ]; then

4323 ct="text/html${charset:+; charset=$charset}"

4324 link="?showattc+$1+$2+raw"

4325 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4326 | sed 's,^,<span></span>,' \

4327 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4328 -D_LINK_="$link" \

4329 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4330 exit $?

4331 fi

4332 ct="text/plain${charset:+; charset=$charset}"

4333 ;;

4334 esac

4335 contenttype "$ct"

4336 echo "Content-Disposition: filename=\"$fn\""

4337 echo "Content-Length: " `cat $bin | wc -c`; echo

4338 #echo "Content-Type: " ${type#file:}; echo

4339 cat $bin

4340 }

4341 #

4342 # Some default stupid handler on CGI values

4343 #

4344 default_storedb() {

4345 # ARG: $1=table-def-file

4346 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4347 tbl=`basename $1`

4348 tbl=${tbl%.def}

4349 cols="`grep :text $1|cut -d: -f2`"

4350 col=`echo "$cols"|head -1`

4351 vcol=`getpar $col`

4352 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4353 if [ -n "$vcol" ]; then

4354 par2table $1

4355 else

4356 return 2 # No insertion occurred

4357 fi

4358 }

4359

4360 default_view() { # $1=def-file

4361 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4362 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4363 default_storedb "$@"

4364 query "select rowid from $tbl order by rowid desc;" \

4365 | while read rowid; do

4366 viewtable $1 $tbl $rowid

4367 done | _m4 -D_TITLE_="$tbl" \

4368 -D_FORM_="`genform $1`" \

4369 -D_DUMPTABLE_="syscmd(cat)" \

4370 $layout/html.m4.html $layout/form+dump.m4.html

4371 }

4372 default_viewtext() { # $1=def-file

4373 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4374 default_storedb "$@"

4375 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4376 | _m4 -D_TITLE_="$tbl" \

4377 -D_FORM_="`genform $1`" \

4378 -D_DUMPTABLE_="syscmd(cat)" \

4379 $layout/html.m4.html $layout/form+dump.m4.html

4380 }

4381 default_smail() {

4382 default_storedb "$@"

4383 if [ $? -eq 2 ]; then

4384 _m4 -D_TITLE_="入力" \

4385 -D_FORM_="`genform $1`" \

4386 -D_DUMPTABLE_="" \

4387 $layout/html.m4.html $layout/form+dump.m4.html

4388 return

4389 fi

4390 cond=""

4391 for pk in `gettblpkey $tbl`; do

4392 pv=$(sqlquote "$(getpar $pk)")

4393 cond="$cond${cond:+ and }$pk=$pv"

4394 done

4395 sql="select rowid from $tbl where $cond;"

4396 rowid=`query "$sql"`

4397 ## err smail1 - "$sql" "-> rowid=$rowid"

4398

4399 while IFS=: read prompt name keytype type args; do # Read from $1

4400 val=`getpar $name`

4401 if [ -n "$val" ]; then

4402 text="$text

4403 $prompt

4404 $name=$val

4405 ---------------------------------------------------------"

4406 fi

4407 case "$type" in

4408 image|document|file)

4409 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4410 fns=$(echo "$fn"|while read fn; do

4411 err mv $tmpd/$fn.orig $tmpd/$fn

4412 mv $tmpd/$fn.orig $tmpd/$fn

4413 rm $tmpd/$fn.rowid # Remove cache flag

4414 ## err "`ls $tmpd/$fn`"

4415 echo $fn

4416 done)

4417 files="$files $fns"

4418 ;;

4419 esac

4420 done < $1

4421 ## err FILES=$files "`ls -lF $tmpd`"

4422 subj="from ${REMOTE_ADDR}"

4423 (echo "$url"

4424 echo "への書き込みがありました。"

4425 echo "------"

4426 echo "$text"

4427 ) | (cd $tmpd &&

4428 err LS="`ls -lF`" &&

4429 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4430 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4431 echo "以下の内容で送信しました。" | html p

4432 viewtable $1 $tbl \

4433 `query "select rowid from $tbl order by rowid desc limit 1;"`

4434 echo "戻る" | html a "href=\"?\""

4435 }