s4: af0cb6f59c8e s4-funcs.sh

s4

view s4-funcs.sh @ 743:af0cb6f59c8e

s/annex/world/

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 06 Jun 2020 11:43:42 +0900
parents	3d2502af9c07
children	52e74eb05b7e

line source

1 #!/bin/sh

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

3 # $HGid$

6 case "$0" in

7 *-world-*)

8 S4WORLD=${0#*world-}

9 S4WORLD=${S4WORLD%.*}

10 echo S4WORLD=$S4WORLD >&2

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

12 ;;

13 esac

15 echo worldconf=$worldconf >&2

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

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

18 echo DB=$DB >&2

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

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

22 myargs="$@"

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

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 $S4WORLDS; 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=UserOrGroup

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

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 else

584 echo -n "grp+$1"

585 fi

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

587 }

588 hreflink() {

589 # s4 specific notation:

590 # ^href=URL

591 # ^iframe=URL

592 # ^video=URL

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

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

595 # OSM umap Wikistyle Notation:

596 # [[URL]] - Simle Link

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

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

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

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

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

602 # Other Style

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

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

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

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

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

608 # SPC+SPC+$ - <br>

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

610 checkboxON="${cb} checked>"

611 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

636

637 }

638 minitbl() {

639 sed -n '

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

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

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

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

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

645 H; # Concat this line to HoldSpace

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

647 $ b cont

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

649 }

650 :cont

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

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

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

654 # . of ^. is workaround for FreeBSD sed

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

656 p; # Print whole "table" element

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

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

659 }

660 x; # Back to the newest line

661 p; # Print rest' | miniul

662 }

663 miniul() {

664 sed -e '

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

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

667 :top

668 s/\n//;

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

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

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

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

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

674 $ b cont

675 N; # 次の行を読む

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

677 /^ /b top

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

679 p; s/.*//;

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

681 /^\* /b top

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

683 }

684

685 :cont

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

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

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

689 H; x

690 }

691 x'

692 }

693 acclog() (

694 # $1=table, $2=rowid

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

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

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

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

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

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

701 fi

702 )

703 gecos() (

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

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

706 )

707 setpar() {

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

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

710 case "$1" in

711 user) _user="$v" ;;

712 skey) _skey="$v" ;;

713 esac

714 fi

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

716 }

717 replpar() {

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

719 }

720 getpar() {

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

722 val=""

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

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

725 user) val=$_user ;;

726 skey) val=$_skey ;;

727 esac

728 fi

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

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

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

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

733 fi

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

735 case "$var" in

736 owner)

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

738 echo $user; return

739 elif ismember $user $val; then

740 echo $val; return

741 fi ;;

742 esac

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

744 echo "$val"

745 }

746 setskey() {

747 # For quick response...(?)

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

749 }

750 chkskey() {

751 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

757 return 0

758 fi

759 return 1

760 }

761 resetskey() {

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

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

764 fi

765 }

766 getpartype() {

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

768 }

769 getparcount() {

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

771 }

772 getparfilename() {

773 # null if type of $1 is not file

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

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

776 }

777 sqlquote() {

778 (v="$1"

779 case "$v" in

780 "") return ;; # null

781 "X'"*) # quoted hex string

782 echo $1 ;;

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

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

785 echo "\"$v\""

786 return ;;

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

788 echo "\"$v\""

789 return ;;

790 *)

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

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

793 else

794 echo "\"$v\""

795 fi ;;

796 esac)

797 }

798 sqlquotestr() (

799 case "$1" in

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

801 echo "'$v'" ;;

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

803 esac

804 )

805 mktempd() {

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

807 }

808 getcachedir() { # $1=maintable

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

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

811 else

812 echo $tmpd/$thumbxy

813 fi

814 }

815 getval() {

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

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

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

819 echo "$user" ;;

820 stamp|datetime)

821 date "+%F %T" ;;

822 serial)

823 (s=`getpar $2`

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

825 *)

826 getpar "$2" "$3";;

827 esac

828 }

829

830 getvalquote() {

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

832 (v=`getval "$@"`

833 case "$v" in

834 "") echo NULL ;;

835 *) sqlquote "$v" ;;

836 esac)

837 }

838 getparquote() {

839 sqlquote "`getpar $1`"

840 }

841 getbinbyid() {

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

843

844 }

845 getvalbyid() {

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

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

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

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

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

851

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

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

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

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

856 return

857 fi

858 done

859 rowid=$3

860 pk=`gettblpkey $1`

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

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

863 td=${4:-$tmpd}

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

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

866 for kt in s m; do

867 t=${1}_$kt

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

869 vcount=1 # count(val)

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

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

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

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

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

875 if [ $kt = m ]; then

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

877 # Reset val to store filenames if type is string

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

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

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

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

882 i=0

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

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

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

886 i=$((i+1))

887 fn=$c.$i

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

889 case $type in

890 file:*)

891 #file=$td/$val

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

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

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

895 # FOR SPEED: Skip file generation if imgcache exists

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

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

898 && continue

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

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

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

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

903 select rowid from $cond $slice;

904 .output '$td/$fn'

905 select val from $cond $slice;

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

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

908 .output stdout

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

910 EOF

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

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

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

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

915 ls -lh "$file" |

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

917 case "$type" in

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

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

920 *|*:[Aa]pplication*)

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

922 png:- > "$file"

923 ;;

924 esac

925 ;;

926 *)

927 sq $db<<EOF

928 .output $td/$fn.rowid

929 select rowid from $cond $slice;

930 .output $td/$fn

931 select val from $cond $slice;

932 EOF

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

934 ;;

935 esac

936 done

937 else

938 rm -f $td/$c.count

939 case $type in

940 file:*)

941 echo "$val" \

942 | while read fn; do

943 file=$td/$fn

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

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

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

947 | unhexize > "$file"

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

949 case $type in

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

951 *:[Aa]pplication*)

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

953 png:- > $file ;;

954 esac

955 fi

956 done

957 ;;

958 esac

959 fi

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

961 return

962 fi

963 done

964 done)

965 }

966 getvalbypkey() (

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

968 pk=`gettblpkey $1`

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

970 getvalbyid "$1" "$2" $rowid $4

971 )

972 getvalbycond() {

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

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

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

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

977 getvalbyid "$1" "$2" $rowid "$4"

978 fi

979 }

980 getpwfield() {

981 # getpwfield user column

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

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

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

985 echo "$val"

986 return 0

987 else

988 return 1

989 fi

990 }

991 numericalize() {

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

993 }

994 encode() {

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

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

997 sha1=sha1

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

999 sha1=sha1sum

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

1001 sha1=gsha1sum

1002 fi

1003 fi

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

1005 }

1006 enjpeg() {

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

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

1009 cjpeg="cjpeg"

1010 else

1011 cjpeg="convert - jpeg:-"

1012 fi

1013 fi

1014 $cjpeg "$@"

1015 }

1016 mycrypt() (

1017 key=$1 salt=$2

1018 # err \$2=$2

1019 case $2 in

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

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

1022 esac

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

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

1025 )

1026 hexize() {

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

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

1029 hexize="xxd -p"

1030 else

1031 hexize_hd() {

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

1033 }

1034 hexize="hexize_hd"

1035 fi

1036 fi

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

1038 }

1039 unhexize() {

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

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

1042 unhex="xxd -p -r"

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

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

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

1046 EOF

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

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

1049 # world writable...:)

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

1051 fi

1052 fi

1053 cat "$@" | $unhex

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

1055 }

1056 percenthex() {

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

1058 }

1059 htmlescape() {

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

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

1062 }

1063 enascii() {

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

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

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

1067 else

1068 enascii_now=`date +%FT%T`

1069 enascii_sed() {

1070 nkf -Z0Z1Z2 \

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

1072 }

1073 enascii="enascii_sed"

1074 fi

1075 fi

1076 cat "$@" | $enascii

1077 }

1078 size_h() {

1079 i="$1" oi=$1

1080 set -- B B KB MB GB TB

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

1082 oi=$i

1083 i=$((i/1024))

1084 shift

1085 done

1086 echo ${oi}$1

1087 }

1088 gettblconf() {

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

1090 ## tconfs=`sq $db \

1091 tconfs=`query \

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

1093 fi

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

1095 }

1096 gettblkeys() {

1097 # $1=tbl

1098 gettblconf

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

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

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

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

1103 case $type in

1104 *p*)

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

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

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

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

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

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

1111 esac

1112 case $type in

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

1114 esac

1115 done

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

1117 }

1118 gettblpkey() {

1119 # $1=tbl

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

1121 }

1122 gettblfkey() {

1123 (x=`gettblkeys $1`

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

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

1126 }

1127 gettblcols() {

1128 (x=`gettblkeys $1`

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

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

1131 }

1132 gettbl_s_cols() {

1133 (x=`gettblkeys $1`

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

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

1136 }

1137 gettbl_m_cols() {

1138 (x=`gettblkeys $1`

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

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

1141 }

1142 gettbl_h_cols() {

1143 (x=`gettblkeys $1`

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

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

1146 }

1147 gettbl_coltype() (

1148 gettblconf

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

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

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

1152 )

1153 is_hidden() {

1154 # $1=Tbl $2=col

1155 gettblconf

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

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

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

1159 case $x in

1160 *h*) return 0 ;;

1161 *) return 1 ;;

1162 esac

1163 }

1164

1165 dbsetbyid() {

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

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

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

1169 quotedp=$(sqlquotestr "$p")

1170 unset primary update

1171 gettblconf

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

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

1174 #err conf=$conf

1175 case ${conf#*=} in

1176 p*) primary=1 ;;

1177 f*) update=1 ;;

1178 u*) ;;

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

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

1181 esac

1182 #err t=$t

1183 type=string fn=""

1184 case $conf in

1185 */password)

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

1187 ;;

1188 */image*|*/document*)

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

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

1191 ;;

1192 esac

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

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

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

1196 if [ "$primary" ]; then

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

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

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

1200 elif [ "$update" ]; then

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

1202 else

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

1204 fi

1205 )

1206 }

1207 expire() (

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

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

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

1211 )

1212 addsession() {

1213 # expireをセット

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

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

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

1217 # Remove old session parameters

1218 now=`expire now`

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

1220 }

1221 gencookie() (

1222 path=${URL#*:/}

1223 path=${URL%/*}

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

1225 for kv; do

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

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

1228 done

1229 )

1230 contenttype() {

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

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

1233 }

1234 putheader() {

1235

1236 }

1237 putfooter() {

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

1239 }

1240 getcookie() {

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

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

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

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

1245 case "$k" in

1246 user) _user="$v" ;;

1247 skey) _skey="$v" ;;

1248 esac

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

1250 done

1251 }

1252 genrandom() {

1253 # $1=columns (default: 10)

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

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

1256 }

1257 genserial() {

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

1259 }

1260 smail() {

1261 # smail rcpts subj (file)

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

1263 # $MAIL_FROM <- From: header value

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

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

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

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

1268 sender=${SENDER:-$admin}

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

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

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

1272 }

1273 smail_queue_flush() {

1274 # $1=timelimit

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

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

1277 SELECT rowid FROM smailq

1278 GROUP BY rcpts, subj

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

1280 EOF

1281 )

1282 for rid in $rowids; do

1283 sq $workdb \

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

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

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

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

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

1289

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

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

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

1293 .separator "\n" "------------------\n\n"

1294 SELECT time, text FROM smailq

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

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

1297 ORDER by time;

1298 EOF

1299 then

1300 echo rowid=$rid

1301 cat <<-EOF | sq $workdb

1302 DELETE FROM smailq

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

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

1305 EOF

1306 fi

1307 fi

1308 done

1309 }

1310 smail_queue() {

1311 # $1=Rcpts, $@=subj

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

1313 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1322 EOF

1323 )

1324 ## XXX: Adhoc load mitigation

1325 case "$now" in

1326 *[01])

1327 err flush_queue=$mintime

1328 smail_queue_flush ;;

1329 esac

1330 fi

1331 }

1332 setviastring() {

1333 table=par

1334 oifs="$IFS"

1335 IFS="&"

1336 for us in $1; do

1337 k=${us%%=*}

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

1339 setpar "$k" "string" "$v"

1340 done

1341 IFS="$oifs"

1342 }

1343 checkdomain() (

1344 # Check the validity of domain by referring DNS

1345 item=$1

1346 err checkdomain $1

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

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

1349 )

1350 pwcheck() {

1351 # $1=passwd

1352 dbpswd=`getpwfield $user pswd`

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

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

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

1356 }

1357 mypwhash() {

1358 mycrypt "`cat`" `genrandom 5`

1359 }

1360 wasureta() {

1361 user=$1

1362 if ! checkdomain $user; then

1363 contenttype; echo

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

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

1366 putfooter

1367 exit 0

1368 fi

1369 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1371 encpswd=`echo $newpswd|mypwhash`

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

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

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

1375 $msgdir/mail-newaccount.m4 \

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

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

1378 }

1379 checkauth() {

1380 user=`getpar user`

1381 skc=`getpar skey` # from cookie

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

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

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

1385 if chkskey "$skc"; then

1386 return 0

1387 fi

1388 fi

1389 pswd=`getpar pswd`

1390 quser=`sqlquotestr "$user"`

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

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

1393 return 4 # 4=server too heavy

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

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

1396 return 2 # 2=login fail

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

1398 wasureta "$user"

1399 return 1 # wasureta error

1400 fi

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

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

1403 if pwcheck "$pswd"; then

1404 newsession=`genrandom 34`

1405 if setskey "$user" "$newsession" &&

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

1407 gencookie "user=$user" "skey=$newsession"

1408 return 0

1409 else

1410 return 4 # Heavy load??

1411 fi

1412 fi

1413 err "Login failed: [$user]"

1414 return 2 # Password mismatch

1415 }

1416 showlogin() {

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

1418 test -z "$args" && resetskey

1419 s4name=${S4NAME:-s4}

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

1421 cat $layout/login.m4.html

1422 echo '</body></html>'

1423 ) | _m4 \

1424 -D_BODYCLASS_="login" \

1425 -D_TITLE_="$s4name" \

1426 -D_SYSNAME_="Welcome to $s4name" \

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

1428 exit 0

1429 }

1430 dologin() {

1431 checkauth

1432 st=$?

1433 if [ $st != 0 ]; then

1434 contenttype; echo

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

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

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

1438 showlogin # and EXIT

1439 fi

1440 }

1441

1442 # Do instant jobs here

1443 dbsetup

1444 trap cleanup INT HUP EXIT TERM PIPE

1445 # trap cleanup INT HUP

1446

1447

1448 cgiinit() {

1449 tmpd=`tmpd=$tmpdir mktempd`

1450 tmpf=$tmpd/stream.$$

1451 tmpfiles=$tmpfiles" $tmpd"

1452 addsession $session

1453 getcookie

1454 case "$REQUEST_METHOD" in

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

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

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

1458 # cat > $tmpf # too much?

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

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

1461 s="`cat $tmpf`"

1462 ;;

1463 esac

1464 case "$CONTENT_TYPE" in

1465 *boundary*)

1466 bndry=${CONTENT_TYPE#*boundary=}

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

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

1469 do

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

1471 #echo u=$us

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

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

1474 # err k=$k v=$v

1475 case "$k" in

1476 *:filename)

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

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

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

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

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

1482 :

1483 fi

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

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

1486 case "$mimetype" in

1487 [Ii]mage/x-xcf)

1488 bzip2 "$tmpd/$v"

1489 v=${v}.bz2

1490 ;;

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

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

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

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

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

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

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

1498 v=$vjpg

1499 else

1500 mimetype="Not supported"

1501 fi

1502 ;;

1503 [Ii]mage/*)

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

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

1506 ;;

1507 esac

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

1509 then

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

1511 continue

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

1513 replpar text string \

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

1515 continue

1516 fi

1517 ;;

1518 *)

1519 type='string'

1520 ;;

1521 esac

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

1523 setpar "$k" "$type" "$v"

1524 done

1525 ;;

1526 *)

1527 setviastring "$s"

1528 ;;

1529 esac

1530 }

1531 email4group() {

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

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

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

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

1536 sql="WITH

1537 grpemails AS (

1538 SELECT gname, user, val email

1539 FROM grp_mem NATURAL JOIN grp_mem_s

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

1541 useremails AS (

1542 SELECT user.name, val email

1543 FROM user

1544 LEFT JOIN user_m

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

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

1547 FROM useremails u LEFT JOIN grpemails g

1548 ON u.name=g.user

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

1550 query "$sql"

1551 }

1552 email4groupbyuid() {

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

1554 g=$1; shift

1555 uids=`echo "$@"`

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

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

1558 email4group "$g" `query "$sql"`

1559 }

1560 myemail4group() {

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

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

1563 }

1564 collectmembersbyid() {

1565 # Collect user names of group specified by grid

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

1567 query "SELECT user FROM grp_mem \

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

1569 }

1570 collectmembersbyid() {

1571 # Collect user names of group name

1572 qgrp=`sqlquote "$1"`

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

1574 }

1575 collectgecosesbyid() {

1576 # Collect user gecoses of group

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

1578 query<<-EOF

1579 SELECT gecos

1580 FROM gecoses

1581 WHERE name IN (SELECT user FROM grp_mem

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

1583 EOF

1584 }

1585 collectemail() (

1586 # Collect email addresses for group $1

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

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

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

1590 for e; do

1591 if isuser "$e"; then

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

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

1594 else

1595 qgrp=`sqlquote "$e"`

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

1597 gmem="grp_mem"

1598 else

1599 tm=`sqlquote "$TEAM"`

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

1601 fi

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

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

1604 $gmem g left join grp_mem_s s

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

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

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

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

1609 query "$sql"

1610 fi

1611 done

1612 )

1613 sendinvitation() (

1614 # $1=email

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

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

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

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

1619 gecos=`gecos`

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

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

1622 _m4 -D_URL_="$urlbase" \

1623 -D_USER_="$name" \

1624 -D_EMAIL_="$1" \

1625 -D_REGIST_="$regist" \

1626 -D_ADMIN_="$admin" \

1627 $msgdir/mail-invite.m4 \

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

1629 return 0

1630 )

1631 emaildomaincheck() {

1632 case "$1" in

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

1634 *@*)

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

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

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

1638 return 2

1639 fi

1640 return 0

1641 ;;

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

1643 esac

1644 }

1645 invite() {

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

1647 case "$email" in

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

1649 *@*)

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

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

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

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

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

1655 elif sendinvitation $email; then

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

1657 else # Cannot be reached here

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

1659 fi ;;

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

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

1662 esac

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

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

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

1666 fi

1667 if [ -f $invite_policy ]; then

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

1669 else

1670 pol="$invite_policy"

1671 fi

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

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

1674 -D_INVITE_POLICY_="$pol" \

1675 $layout/html.m4.html $layout/invite.m4.html

1676 }

1677 regist() {

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

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

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

1681 echo "bye bye" | html p

1682 reutrn

1683 fi

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

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

1686 cat<<EOF

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

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

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

1690 EOF

1691 return

1692 fi

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

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

1695 # Fake login password to wasureta

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

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

1698 wasureta $email

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

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

1701 addsession $1 # for removal after 1 minute

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

1703 $layout/login.m4.html

1704 return

1705 }

1706 group_safename() {

1707 # Convert $1 to safe group name

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

1709 }

1710 groupupdate() {

1711 gname=`getpar gname`

1712 qgname=`sqlquote "$gname"`

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

1714 # See ALSO same job in showgroup()

1715 newgname=`group_safename "$gname"`

1716 err newgname=$newgname

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

1718 err NewGNAME: gname=$newgname

1719 gname=$newgname

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

1721 replpar gname string "$gname"

1722 fi

1723 # Name confliction check

1724 parow=`getpar rowid`

1725 ## err parow=$parow

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

1727 query "BEGIN EXCLUSIVE;"

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

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

1730 if [ $count -gt 0 ]; then

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

1732 query "END;"

1733 return

1734 fi

1735 par2table $formdir/grp.def

1736 query "END TRANSACTION;"

1737 # Remove orphan

1738 : <<EOF

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

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

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

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

1743 EOF

1744 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1748 sql="delete from blog where id in

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

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

1751 query "$sql";

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

1753 return # and return

1754 fi

1755 fi

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

1757 fi

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

1759 grid=$(query $sql)

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

1761 grp $grid

1762 }

1763 groupclone() {

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

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

1766 case "$1" in

1767 */noteam)

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

1769 esac

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

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

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

1773 return

1774 fi

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

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

1777 return

1778 fi

1779 i=0

1780 while true; do

1781 copy="-copy$i"

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

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

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

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

1786 i=$((i++))

1787 continue

1788 fi

1789 break

1790 done

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

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

1793 query<<-EOF

1794 BEGIN;

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

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

1797 SELECT $newqname, key, val

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

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

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

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

1802 -- Copy members and their configuration --

1803 REPLACE INTO grp_mem SELECT $newqname, user

1804 FROM grp_mem WHERE gname=$qgrp;

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

1806 FROM grp_mem_s WHERE gname=$qgrp;

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

1808 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1809 -- Copy administrators --

1810 REPLACE INTO grp_adm SELECT $newqname, user

1811 FROM grp_adm WHERE gname=$qgrp;

1812 COMMIT;

1813 EOF

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

1815 STOPCLONEMSG=1 groupconf "$newrowid"

1816 }

1817 groupman() {

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

1819

1820 GF_STAGE="grpconf"

1821 GF_STAGE=groupupdate

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

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

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

1825 -D_DUMPTABLE_="syscmd(cat)" \

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

1827 }

1828 userconf() {

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

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

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

1832 }

1833 groupconf() {

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

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

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

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

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

1839

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

1841 grp=`getgroupbyid $rowid`

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

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

1844 return

1845 fi

1846

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

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

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

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

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

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

1853 <div>

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

1855 <table>

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

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

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

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

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

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

1862 </table>

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

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

1865 </div>

1866 EOF

1867 fi

1868 }

1869 mems() {

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

1871 kwd=`getpar kwd`

1872 listmember $kwd

1873 }

1874 grps() {

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

1876 kwd=`getpar kwd`

1877 listgroup $kwd \

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

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

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

1881 $layout/form+dump.m4.html

1882 }

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

1884 gpg=`getpar grp`

1885 grid=${1:-$gpg}

1886 grp=`getgroupbyid "$grid"`

1887 ## . ./s4-blog.sh

1888 jg=`getpar joingrp`

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

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

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

1892 fi

1893 htmlheader=$layout/html.m4.html

1894 showgroup "$grid"

1895 }

1896 sql4interestblogs() {

1897 cat<<EOF

1898 CREATE TEMPORARY VIEW interestblogs AS

1899 SELECT blog.rowid rid, id, author

1900 FROM blog

1901 NATURAL JOIN

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

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

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

1905 WHEN (SELECT user FROM grp_mem

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

1907 THEN 0

1908 ELSE 1

1909 END;

1910 EOF

1911 }

1912 listnewblogsql() { # $1=user

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

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

1915 newdays=${newdays:-14}

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

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

1918 WHERE user='$user'

1919 AND tblrowid IN

1920 ($blogreadflagrowid,

1921 $blogcutoffflagrowid)),

1922 $basetime -- "0"

1923 );"`

1924 cat<<EOF

1925 `sql4interestblogs`

1926 WITH article_ctime as (

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

1928 FROM article join article_s s using(id)

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

1930 GROUP BY id

1931 ), blog_title_owner as (

1932 SELECT blg.rid brid, id,

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

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

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

1936 ), blogall as (

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

1938 ), news as (

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

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

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

1942 FROM blogall bl

1943 LEFT JOIN

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

1945 ON bl.brid=al.tblrowid

1946 WHERE atime < bl.ctime

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

1948 LIMIT 10

1949 ) SELECT brid LINK, "新着",

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

1951 ctime, title,

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

1953 FROM news;

1954 EOF

1955 }

1956

1957 search_form() {

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

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

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

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

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

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

1964 @week → 最近一週間

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

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

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

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

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

1970 auth=""

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

1972 case "$1" in

1973 author=*)

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

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

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

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

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

1979 ;;

1980 grp=*)

1981 a=`echo "${1#grp=}"` # group name cannot have quoting marks

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

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

1984 ;;

1985 esac

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

1987 cat<<-EOF

1988 <div class="right">

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

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

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

1992

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

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

1995

1996 </form>

1997 </div>

1998 EOF

1999 }

2000

2001 imgsrc_cache() (

2002 # $1 = directory for cache'ing

2003 # $2 = table (user_m or grp_m)

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

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

2006 dir="$1" tbl="$2"

2007 keycond="$3"

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

2009 ORDER BY rowid DESC LIMIT 1"

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

2011 tmpf=$tmpd/imgsrc_cache.$$

2012 case "$4" in

2013 [Ss]) size=S ;;

2014 [Oo]) size=O ;;

2015 *) size=M ;;

2016 esac

2017 # ImageCache filename storing schema:

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

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

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

2021 valtype=`query "$sql0"`

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

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

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

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

2026 case "$filetype" in

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

2028 esac

2029 fi

2030 cacheimg_S=$dir/S_$filename

2031 cacheimg_M=$dir/M_$filename

2032 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2040 else

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

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

2043 fifo2=$fifo.2

2044 mkfifo $fifo $fifo2

2045 fmt=${filename##*.}

2046 ## [[ NOTE ]]

2047 ## a. convert oldimage newimage

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

2049 ## b is much smaller than a

2050 cat $tmpf | unhexize \

2051 | tee $fifo \

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

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

2054 | tee $fifo2 \

2055 | convert - "$cacheimg_M" &

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

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

2058 | convert - "$cacheimg_S" &

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

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

2061 echo '">'

2062 echo "$sum" > $sumfile

2063 fi

2064 ## Now preparing cache image, done.

2065 ## Store this information to DB

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

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

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

2069 query <<-EOF

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

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

2072 EOF

2073 )

2074

2075 showhome() {

2076 # $1=userRowIdToShow

2077 err showhome \$1=$1

2078 case "$1" in

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

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

2081 esac

2082 ## err ShowHome: uname=$uname

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

2084 gecos=`gecos "$uname"`

2085 ## err SH:gecos=$gecos

2086 GF_VIEWONLY=1

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

2088 search_form_args=""

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

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

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

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

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

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

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

2096 fi

2097 usermenu="$usermenu

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

2099 # Display folders

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

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

2102 and type like 'file:%';"

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

2104 nfile=`query "$sql"`

2105 # err nfile=$nfile

2106 if [ $nfile -gt 0 ]; then

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

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

2109 fi

2110 else

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

2112 GROUP BY user;"`

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

2114 search_form_args="author=$uname"

2115 fi

2116 . ./s4-blog.sh

2117

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

2119 search_form "$search_form_args" > $sf

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

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

2122 viewtable $formdir/user.def user $1

2123 echo "</div>"

2124 } > $pf

2125

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

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

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

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

2130 if true; then

2131 tbl=user_m

2132 enticond="name='$uname'"

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

2134 else

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

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

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

2138 echo '">'

2139 }

2140 fi > $imf

2141 fi

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

2143 val='$uname';"`

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

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

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

2147 listblog $uname > $bf

2148 ## unset ddd

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

2150

2151 hometail=$tmpd/tail.$$

2152 mkfifo $hometail

2153

2154 #Calling listgroupbytable, originally here

2155

2156 (

2157 # Display Most Recent Entry

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

2159 shortval=${shortval:-val}

2160

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

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

2163 ctime,

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

2165 (SELECT gecos FROM gecoses

2166 WHERE name=(SELECT val FROM blog_s

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

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

2169 FROM blog b

2170 JOIN

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

2172 FROM article a, article_s s

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

2174 GROUP BY blogid ORDER BY val DESC LIMIT 50

2175 ) m

2176 ON b.id=m.blogid;"

2177 # This should be as follows

2178 : <<EOF

2179 WITH arts AS(

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

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

2182 FROM article a NATURAL JOIN article_s s

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

2184 GROUP by s.id

2185 )

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

2187 FROM arts a0

2188 JOIN

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

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

2191 ORDER BY ctime DESC LIMIT 50;

2192 EOF

2193

2194 cat<<-EOF

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

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

2197 <div class="lcto">

2198 `DT_VIEW=replyblog dumptable html blog`

2199 </div>

2200 EOF

2201 unset DT_SQL

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

2203 # Display NEWS

2204 # 2016-06-26

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

2206 acclog blog $blogreadflagrowid

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

2208 fi

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

2210 sql=`listnewblogsql "$user"`

2211 # echo "$sql" > tmp/listnew

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

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

2214 cont=$((cont-1))

2215 err newcount=$cont

2216 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2223 `cgi_submit '確定'`

2224 `cgi_hidden read yes`

2225 EOF

2226 echo "$new10 "

2227 echo "</div>"

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

2229 acclog blog $blogcutoffflagrowid # for speed

2230 fi

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

2232 : # DT_SQL=

2233 fi

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

2235 #

2236 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2242 -D_NBLOG_="$nblog" \

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

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

2245 $layout/html.m4.html $layout/home.m4.html

2246

2247 # Record access log

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

2249 }

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

2251 contenttype; echo

2252 ## err commission: "$@"

2253 gname=`getgroupbyid $1`

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

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

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

2257 grp_reg_adm "$@"

2258 else

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

2260 fi

2261 }

2262 listgroupbytable() {

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

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

2265 and="${1:+and }" where=${1:+where }

2266 href="<a href=\"$myname?grp+"

2267 echo '<div class="listgroup">'

2268 NGsql="select distinct tag from\

2269 (select gname, max(case key when 'tag' then val end) as tag, \

2270 max(case key when 'ctime' then val end) as ctime\

2271 from grp_s group by gname order by ctime);"

2272 sql="select val from grp_s where key='tag' $and$* group by val;"

2273 ## err ListGRP: query sql="$sql"

2274 for tag in `query "$sql"`

2275 do

2276 ## err ListGrp: tag=$tag

2277 tn=${tagline%%=${tag}*}

2278 tn=${tn##*[ :]}

2279 sql="select rowid||':'||gname as 'グループ名',説明 from

2280 (select (select rowid from grp g where g.gname=grp_s.gname)

2281 as rowid,

2282 gname,

2283 max(case key when 'gecos' then val end) as '説明',

2284 max(case key when 'tag' then val end) as 'tag',

2285 max(case key when 'mtime' then val end) as mtime from grp_s

2286 $where$* group by gname having tag='$tag' order by mtime desc);"

2287 ## err PersonalGroupList= `echo $sql`

2288 echo "<h2>$tn</h2>"

2289 echo '<table class="b listgroup">'

2290 sq -header -html $db "$sql" \

2291 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2292 echo '</table>'

2293 done

2294 echo '</div>'

2295 }

2296 iconhref() (

2297 # $1=icon-file, $2=Href $3=title $4...=anchor

2298 data=`percenthex "$1"`

2299 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2300 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2301 href=$2; title=$3; shift 3

2302 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2303 )

2304 iconhref2() (

2305 # $1=icon-file, $2=Href $3=title $4...=anchor

2306 src=$1

2307 href=$2; title=$3; shift 3

2308 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2309 )

2310 listentry() (

2311 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2312 # Referring variable $iamowner=$grp to attach owner-request links

2313 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2314 cond='' hiddens=''

2315 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2316 if [ -z "$offset" ]; then

2317 offset=`getpar start`; offset=${offset%%[!0-9]*}

2318 offset=$((offset-1))

2319 fi

2320 offset=$((offset + 0)) # change to numeric forcibly

2321 [ $offset -lt 0 ] && offset=0

2322 limit=$listentlimit

2323 dir=`getcachedir "$1"`

2324 if [ x"$1" = x"user" ]; then

2325 hrb="$myname?home"

2326 deficon=person-default.png

2327 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2328 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2329 gcs=gecos

2330 else # if group

2331 hrb="$myname?grp"

2332 deficon=group-default.png

2333 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2334 gcs=name

2335 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2336 if [ -n "$tagline" ]; then

2337 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2338 ## err tagconv=$tagconv

2339 fi

2340 fi

2341 if [ ! -d $dir ]; then

2342 mkdir -p $dir

2343 fi

2344 if [ ! -s $dir/$deficon ]; then

2345 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2346 fi

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

2348 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2349 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2350 fi

2351 tag=`getpar tag` tag2=`getpar tag2`

2352 if [ x"$tag" = x"NULL" ]; then

2353 tag="" tag2=""

2354 fi

2355 if [ -n "$tag$tag2" ]; then

2356 tag=${tag:-$tag2}

2357 qtag=`sqlquote "$tag"`

2358 cond2="tag=$qtag"

2359 fi

2360 if [ -n "$cond1$cond2" ]; then

2361 cond="$cond1${cond2:+ AND $cond2}"

2362 cond="WHERE ${cond# AND }"

2363 fi

2364

2365 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2366 # grpは呼出し元の動的スコープ変数でよくないな...

2367 ##qgrp=`sqlquote $grp`

2368 getgrp="(select gname from grp where rowid=${rowid:--1})"

2369 sql="select a.rowid, a.$link,

2370 coalesce(b.$gcs, a.$nm) as nick,

2371 quote(a.$nm) as qname,

2372 (SELECT val FROM ${tbl}_s

2373 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2374 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2375 || case when a.$nm in (select gname from grp)

2376 then printf('(%d名)',

2377 (select count(user) from grp_mem where gname=a.$nm))

2378 else ' <'||a.$nm||'>'

2379 end

2380 as name,

2381 b.tag,

2382 case when a.$nm in (select user from grp_adm

2383 where gname=$getgrp) then '管理者'

2384 when '$user' in (select user from grp_adm where gname=a.$nm)

2385 then 'ADMIN'

2386 when '$user' in (select user from grp_mem where gname=a.$nm)

2387 then 'Member'

2388 when '$iamowner' = '' then ''

2389 else ',not='||a.rowid end as ownerlink,

2390 CASE '$entity'

2391 WHEN 'グループ'

2392 THEN coalesce(

2393 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2394 'open')

2395 ||

2396 CASE WHEN '$user'

2397 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2398 THEN ' ismember'

2399 ELSE ''

2400 END

2401 ELSE 'user'

2402 END regmode

2403 from $tbl a left join

2404 (select $nm as name,

2405 max(case key when 'gecos' then val end) as gecos,

2406 max(case key when 'tag' then val end) as tag,

2407 max(case key when 'mtime' then val end) as mtime,

2408 max(case key when 'wtime' then val end) as wtime,

2409 max(case key when 'login' then val end) as login

2410 from ${tbl}_s group by $nm)

2411 b on a.$nm=b.name $cond $3

2412 order by b.wtime desc, b.login desc,

2413 b.mtime desc, b.tag desc, a.rowid asc"

2414 # Give precedence to newer maintained groups (2016-09-24)

2415 # Note that mtime is stored only in grp_s.

2416 ## err LE:sql.1="$sql"

2417 total=`query "with x as ($sql) select count(*) from x;"`

2418 echo "${entity} 一覧" | html h2

2419 echo '<div class="listentry">' # List-entry div

2420 # Show owner/member filter button

2421 METHOD=GET

2422 hiddens="$hiddens

2423 `cgi_hidden kwd \"$kwd\"`

2424 `cgi_hidden stage \"$stage\"`"

2425 if [ x"$tbl" = x"grp" ]; then

2426 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2427 fh="<select name=\"tag\">$nl"

2428 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2429 for l in $args; do

2430 val=${l#*=} tname=${l%=*}

2431 if [ x"$val" = x"$tag" ]; then

2432 s=" selected"

2433 selectedtags="(種別[${tname}]のみ)"

2434 else

2435 s=""

2436 fi

2437 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2438 done

2439 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2440 cat<<-EOF

2441 <form action="$myname" method="$METHOD">

2442 </form>

2443 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2444 for="ismembtn">参加中以外隠す</label>

2445 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2446 for="isadmbtn">管理者参加以外隠す</label>

2447 EOF

2448 # limit=3

2449 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2450 fi

2451 if [ $total -gt $limit ]; then

2452 echo '<div>'

2453 METHOD=GET cgi_form $stage <<EOF

2454 $form

2455 <label>次の語を含む${entity}で検索:

2456 `cgi_text kwd "$kwd"`</label>

2457 $hiddens

2458 EOF

2459 echo '</div>'

2460 else

2461 echo $selectedtags | html p

2462 fi

2463 cat<<EOF

2464 <form action="$myname" method="$METHOD">

2465 <p>${total}件中の<input class="hidesub" type="text" name="start"

2466 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2467 <input type="submit" value="確定"></p>

2468 </form>

2469 EOF

2470 if [ $((offset+limit)) -lt $total ]; then

2471 nextbtn=$(

2472 cat<<EOF

2473 <div class="right clear"><form action="$myname" method="$METHOD">

2474 `cgi_submit 次の${limit}件`

2475 $hiddens

2476 `cgi_hidden offset $((offset + limit))`</form></div>

2477 EOF

2478 )

2479 fi

2480 if [ $offset -gt 0 ]; then

2481 prevbtn=$(

2482 cat<<EOF

2483 <form action="$myname" method="$METHOD">

2484 `cgi_submit 前の${limit}件`

2485 $hiddens

2486 `cgi_hidden offset $((offset - limit))`</form>

2487 EOF

2488 )

2489 fi

2490 pnbtn="$nextbtn$prevbtn"

2491 echo $pnbtn

2492

2493 ## err ListEntry: `echo "$sql"\;`

2494 # sq $db here??? 2016-11-28

2495 query "$sql limit $limit ${offset:+offset $offset};" \

2496 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2497 # err name=$name owner=$ownerp lnk=$lnk

2498 # err newlnk=$lnk regmode=$regmode

2499 icondir=$dir/$id

2500 # Pick up only last icon

2501 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2502 <p class=\"tag _$tag\">$tag</p>" \

2503 | _m4 $tagconv

2504 if [ -n "$NOSPEEDUP" ]; then

2505 files=`getvalbyid $tbl profimg $id $icondir`

2506 if [ -n "$files" ]; then

2507 icon=`echo "$files"|tail -1`

2508 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2509 else

2510 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2511 fi

2512 elif [ -n "$icon" -a -s "$icon" ]; then

2513 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2514 else

2515 cond="$nm=$qname"

2516 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2517 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2518 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2519 # err "img=[$img]"

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

2521 echo "<a href=\"$hrb+$lnk\">"

2522 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2523 echo "</a>"

2524 else

2525 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2526 fi

2527 fi

2528 echo "<br>$name${ownerp:+<br>($ownerp)}"

2529 echo "</div>"

2530 done

2531 echo "</div>" # End of List-entry div

2532 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2533 )

2534 listmember() {

2535 listentry user "$@"

2536 }

2537 listgroup() {

2538 listentry group "$@"

2539 }

2540 hexteams() { # $1=gname, $2(optional)=user

2541 cond=${2:+" AND user='$2'"}

2542 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2543 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2544 }

2545 showgroup() { # $1=group-rowid

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

2547 grid=`getpar grid`

2548 grid=${grid%%[!0-9]*}

2549 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2550 else

2551 grid=$1

2552 fi

2553 grp=`getgroupbyid $grid`

2554 qgrp=`sqlquote "$grp"`

2555 htmlgrp=`echo "$grp"|htmlescape`

2556 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2557 if isgroup "$grp"; then

2558 tf=$tmpd/title.$$

2559 sf=$tmpd/search.$$

2560 bodyclass=`query "SELECT val FROM grp_s

2561 WHERE gname=$qgrp AND key='regmode';"`

2562 if ismember "$user" "$grp"; then

2563 ismember="ismember"

2564 bodyclass="$bodyclass${bodyclass:+ }ismember"

2565 else

2566 ismember="" # bodyclass="group"

2567 fi

2568 bodyclass="$bodyclass grouphome"

2569 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2570 echo "グループ $htmlgrp" > $tf

2571

2572 showgroupsub $formdir/grp.def "$grid" | \

2573 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2574 -D_FORM_="syscmd(\`cat')" \

2575 -D_BODYCLASS_="$bodyclass" \

2576 -D_DUMPTABLE_="" \

2577 $htmlheader $sf $layout/form+dump.m4.html

2578 # $htmlheader $layout/form+dump.m4.html

2579 # $htmlheader is defined in grp()

2580 else # if $grp is removed at par2table

2581 listgroup

2582 fi

2583 }

2584 showgroupsub() {

2585 # $1=def-file $2=group-rowid

2586 # Using $ismember

2587 rowid=$2

2588 grp=`getgroupbyid $2`

2589 qgrp=`sqlquote "$grp"`

2590 td=`getcachedir grp/"$2"`

2591 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2592 if [ -z "$rowid" ]; then

2593 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2594 #grp=`sq $db "select gname from grp where rowid=$grp"`

2595 echo "showgroupsub: invalid argument($1 $2)" | html p

2596 return

2597 fi

2598 val=`getvalbyid grp profimg $rowid $tmpd`

2599 enticond="gname=$qgrp"

2600 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2602 cat<<-EOF

2603 <p class="groupimg">

2604 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2605 EOF

2606 fi

2607 echo "<div class=\"noprofimg\">"

2608 viewtable $1 grp $rowid

2609 echo "</div>"

2610 if isgrpowner "$user" "$grp"; then

2611 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2612 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2613 iamowner=$rowid

2614 colmd=" mode"

2615 fi

2616 if [ -n "$ismember" ]; then

2617 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2618 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2619 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2620 cat<<-EOF

2621 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2622 href="?blog+$rowid">グループの新規話題作成</a>

2623 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2624 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2625 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2626 <div class="fold clear">

2627 `cgi_checkbox send yes id="send"`<label

2628 for="send">グループ全員にメッセージ送信</label>

2629 <div>

2630 `cgi_textarea message "" "cols=60"`

2631 `cgi_submit 送信`

2632 `cgi_reset リセット`

2633 </div>

2634 `cgi_hidden grp $rowid`

2635 </div></form>

2636 EOF

2637 fi

2638 # 加入ボタン + 加入者リスト

2639 if [ -n "$ismember" ]; then

2640 ismem='checked' state="(参加中)"

2641 else

2642 nomem='checked' state="(現在非加入)"

2643 fi

2644 # このグループでの加入アドレス

2645 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2646 and key='email';"`

2647 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2648 ## and key='email';"

2649 ##err email=$eml

2650 cat <<EOF

2651 <div class="fold clear">

2652 `cgi_checkbox reg yes id="reg"`<label

2653 for="reg">自身の加入状態を操作する</label>$state

2654 <div>

2655 EOF

2656 cgi_form grp <<EOF

2657 <p>このグループに</p>

2658 <table class="b">

2659 <tr><th>メンバーとして</th><td>

2660 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2661 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2662 <tr><th>参加する場合のメイルアドレス<br>

2663 <small>(メインのアドレスとは違うものにする場合に記入<br>

2664 同じでよい場合は空欄に)</small></th>

2665 <td>`cgi_text email $eml`</td></tr>

2666 </table>

2667 `cgi_hidden grp $rowid`

2668 EOF

2669 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2670 echo "moderated (承認加入の)グループなので実際に参加できるのは

2671 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2672 fi

2673 echo '</div></div>'

2674 echo '<h2>話題一覧</h2>'

2675 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2676 cat<<-EOF

2677 <form class="summary" action="$myname" title="$thelp">

2678 `cgi_hidden owner "$grp"`

2679 `cgi_hidden kwd "@week"`

2680 `cgi_hidden stage searchart`

2681 `cgi_submit "一週間のまとめ"`

2682 </form>

2683 EOF

2684 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2685 colstate="state:稼動状態:frozen=rowclass=凍結"

2686 DT_CHLD=article:blogid \

2687 DT_QOWNER="$qgrp" \

2688 DT_VIEW=replyblog dumptable html blog \

2689 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2690

2691 getgname="(select gname from grp where rowid=$rowid)"

2692 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2693 cm="?commission+$rowid"

2694 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2695 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2696 # team list

2697 hexteams=`hexteams "$grp"`

2698 if [ -n "$hexteams" ]; then

2699 echo "チーム一覧" | html h2

2700 echo '<div class="dumptable"><table class="b">'

2701 sq $db -html -header<<-EOF

2702 SELECT val TEAM,

2703 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2704 MEMBERS

2705 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2706 EOF

2707 echo '</table></div>'

2708 fi

2709 }

2710 grp_getbodyclass() {

2711 # Get css class name for document.

2712 # `moderated' for moderated groups

2713 # `ismember' for groups where user belongs

2714 # $1=GroupName (w/o quote)

2715 # $user=userNameCurrentlyLogin

2716 ## err grp_getbodyclass: 1="$1"

2717 qgrp=`sqlquote "$1"`

2718 query<<-EOF

2719 SELECT coalesce(

2720 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2721 'open')

2722 ||

2723 CASE WHEN '$user'

2724 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2725 THEN ' ismember'

2726 ELSE ''

2727 END;

2728 EOF

2729 }

2730 grpaction() { # $1=group-rowid

2731 err GRP_ACTION:IN

2732 grid=${1:-`getpar grp`}

2733 grp=`getgroupbyid "$grid"`

2734 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2735 if [ -z "$grp" ]; then

2736 echo "無効な指定です。" | html p; return

2737 fi

2738 if ! ismember $user "$grp"; then

2739 echo "加入者のみに許可された操作です。" | html p; return

2740 fi

2741 echo "グループ $grp 個別選択操作" \

2742 | _m4 -D_TITLE_="syscmd(\`cat')" \

2743 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2744 $layout/html.m4.html

2745

2746 isowner=""

2747 isgrpowner "$user" "$grp" && isowner="yes"

2748 usel=`getpar usel`

2749 if [ -n "$usel" ]; then

2750 uids=$(echo `echo $usel`|tr ' ' ',')

2751 ## err grpaction-1: grp=$grp, `echo $sql`

2752 text=`getpar text|tr -d '\r'`

2753

2754 rm=`getpar rm` cfm=`getpar confirm`

2755 ## err rm=$rm cfm=$cfm

2756 if [ x"$rm" = x"yes" ]; then

2757 if [ "$isowner" ]; then

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

2759 # Eliminate

2760 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2761 for tbl in grp_mem grp_mem_s grp_mem_m; do

2762 sql="delete from $tbl $cond;"

2763 # echo "sql=$sql"

2764 query "$sql"

2765 err rmGRPuser "$sql"

2766 done

2767 num=`query "select count(*) from user where rowid in ($uids);"`

2768 #err num=$num

2769 if [ 0$num -gt 0 ]; then

2770 sql="select coalesce(b.val,a.name) from user a left join \

2771 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2772 # err `echo "$sql"`

2773 html pre<<EOF

2774 以下の${num}名のグループ $grp 登録を解除しました。

2775 `query "$sql"`

2776 EOF

2777 fi

2778 else

2779 echo "確認のチェックがないのでやめておきます。" | html p

2780 return

2781 fi

2782 else # not Group Owner

2783 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2784 return

2785 fi

2786 cat<<EOF

2787

2788 EOF

2789 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2790 if [ -z "$text" ]; then # if msg is empty

2791 echo "なにかメッセージを..." | html p

2792 return 0

2793 fi

2794 gecos=`gecos $user`

2795 safegc=`echo "$gecos" | tr -d '<>@'`

2796 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2797 fromad=`myemail4group "$grp"`

2798 ###mail_from="$safegc <$fromad>"

2799 mail_from="$safegc <$user>" # TEST: 2020/5/13

2800 test -n `getpar sender` &&

2801 export SENDER=$user # TEST: 2020/5/15

2802 replyto=$fromad

2803

2804 ## Start parse of attachment files

2805 if [ -n "`getpar email`" ]; then

2806 ar=`getpar supprcpt`

2807 if [ -n "$ar" ]; then

2808 for a in $ar; do

2809 if checkdomain "$a"; then

2810 supprcpt="$supprcpt $a"

2811 else

2812 err "SupprcptErr=[$a] by $user"

2813 removercpt="$removercpt $a"

2814 fi

2815 done

2816 fi

2817 subj=`getpar subject`

2818 afiles=""

2819 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2820 do

2821 f=$tmpd/$fn

2822 if [ -s $f ]; then

2823 afiles=$afiles"${afiles:+ }$f"

2824 fi

2825 done

2826 else

2827 preface=$(cat <<-EOF

2828 $url

2829 のグループ「$grp」のメンバーである $gecos さんから、

2830 あなた宛のメッセージです。

2831 ----------------------------------------------------------

2832 EOF

2833 )

2834 fi

2835 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2836 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2837 subj="${subj:-$gecos さんからのメッセージ}"

2838 REPLYTO=$replyto

2839 MAIL_FROM=$mail_from

2840 export REPLYTO SMAIL_TO MAIL_FROM

2841 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2842 for r in $rcpts; do

2843 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2844 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2845 else

2846 # Show sender and rcpts address for guest

2847 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2848 fi

2849 if [ -n "$afiles" ];then

2850 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2851 else

2852 smail "$r" "$subj"

2853 fi <<EOF

2854 ${preface:+$preface$nl}$text

2855 EOF

2856 done

2857 if [ $? = 0 ]; then

2858 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2859 sql="select coalesce(b.val, a.name) from

2860 (select name from user where rowid in ($uids)) a

2861 left join user_s b on a.name=b.name and b.key='gecos';"

2862 html pre<<EOF

2863 `query "$sql"`

2864 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2865 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2866 EOF

2867 err SendDone: `echo $sql`

2868 fi

2869 elif [ x"$rm" = x"commission" ]; then

2870 grp_reg_adm $grid $usel

2871 elif [ x"$rm" = x"addteam" ]; then

2872 team=`getpar team|sed "s/'/''/g"` # for single quotation

2873 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2874 if [ x"$team" != x"$newteam" ]; then

2875 echo "チーム名に使えない文字を除去しました" | html p

2876 team=$newteam

2877 fi

2878 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2879 cat<<-EOF | html p

2880 有効なチーム名を入力してください。

2881 カンマだけ、「なし」という名前は使えません。

2882 EOF

2883 echo "有効なチーム名を入力してください。" | html p

2884 else

2885 grp_add_team $grid "$team" $usel

2886 fi

2887 elif [ x"$rm" = x"rmteam" ]; then

2888 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2889 rmteam=`getpar rmteam|sed "s/'/''/g"`

2890 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2891 gname='$grp' AND user='$user' AND key='team'\

2892 AND val='$rmteam';\"`" ]; then

2893 grp_rm_team $grid "$rmteam" $usel

2894 else

2895 echo "所属していないチームの除去操作はできません。"|html p

2896 fi

2897 else

2898 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2899 fi

2900 fi

2901 fi

2902 # POST count summary

2903 from=`getpar from`; to=`getpar to`

2904 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2905 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2906 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2907 # New entry

2908 sql="WITH mems AS (

2909 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2910 ON gm.user=g.name

2911 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2912 ), target_article AS (

2913 SELECT id FROM article_s

2914 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2915 ), posts AS (

2916 SELECT author, count(author) post

2917 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2918 WHERE blogid IN (SELECT id FROM blog_s

2919 WHERE key='owner'

2920 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2921 AND key='text'

2922 GROUP BY author

2923 ), teams AS (

2924 SELECT user, group_concat(val, ', ') team

2925 FROM grp_mem_m

2926 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2927 AND key='team'

2928 GROUP BY user

2929 ), user_post AS (

2930 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2931 FROM mems m LEFT JOIN posts

2932 ON m.name=posts.author

2933 GROUP by m.rowid

2934 )

2935 SELECT

2936 CASE

2937 WHEN (SELECT user FROM grp_adm

2938 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2939 AND user=up.name) IS NOT NULL

2940 then 'k'

2941 ELSE ''

2942 END || rowid || ',' || gecos NAME,

2943 post POST, team _TEAM_

2944 FROM user_post up LEFT JOIN teams t

2945 ON up.name=t.user

2946 ORDER BY gecos;"

2947 ## err grpaction: "`echo \"$sql\"`"

2948 tf=$tmpd/title.$$

2949 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2950 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2951 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2952 for=\"cmadmin\">管理者委任</label>

2953 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2954 </p></div>"

2955 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2956 title=\"Shortcut: g${nl}Remove from the Group\"

2957 for=\"conf\">GRP登録解除</label>

2958 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2959 <p>この操作による通知は本人に行きません。

2960 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2961 </div>"

2962 # Get team list to which current user belongs into $hexteams

2963 allhexteams=$(hexteams "$grp")

2964 if [ -n "$isowner" ]; then

2965 myhexteams="$allhexteams" # admin can remove all teams' attr

2966 else

2967 myhexteams=$(hexteams "$grp" "$user")

2968 fi

2969 if [ -n "$myhexteams" ]; then

2970 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2971 title=\"Shortcut: s${nl}Strip a team tag from\"

2972 for=\"cmrmteam\">チーム属性除去</label>

2973 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2974 を除去します: `cgi_checkbox teamconfirm yes` 確認

2975 <p>この操作による通知は本人に行きません。

2976 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2977 </div>

2978 "

2979 fi

2980 stf=$tmpd/selteam.$$

2981 cgi_select_h selteam "5445414d" $allhexteams > $stf

2982 b1='<label> <input type="checkbox" name="usel" value="'

2983 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2984 br='<span id="reverse" title="Reverse Selection"></span>'

2985 #b2='"> <span>' b3='</span></label>'

2986 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2987 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2988 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2989 cgi_form grpaction<<EOF \

2990 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2991 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2992 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2993 | _m4 -D_TITLE_="spaste(\`$tf')" \

2994 -D_SUBTITLE_="チェック後操作ボタン" \

2995 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2996 $layout/form+dump.m4.html \

2997 | _m4 -D_TEAM_="spaste(\`$stf')"

2998 <p>下でチェックした人を対象として:</p>

2999 <div class="foldtabs">

3000 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3001 title="Shortcut: a${nl}Add a team tag to"

3002 for="cmteam">同じチーム属性を付与</label>

3003 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3004 `cgi_datalist_h teams $allhexteams`

3005 </div>

3006 ${rmteammsg}

3007 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3008 title="Shortcut: d${nl}DirectMail to"

3009 for="sendmsg" title="Direct Message">DM送信</label>

3010 <div>

3011 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3012 title="Using email format">email書式を使う</label>

3013 <div class="folded">

3014 <table>

3015 <tr><td>From: </td><td>$user</td></tr>

3016 <tr><td>このFrom:で送る</td>

3017 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3018 </td></tr>

3019 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3020 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3021 <tr><td>ファイル添付: </td>

3022 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3023 </table>

3024 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

3025 </div>

3026 <div>本文:`cgi_textarea text "" cols=72`

3027 </div>

3028 </div>

3029 ${isowner:+$cmmsg$excmsg}

3030 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3031 </div>

3032 <h4>$grp 参加者一覧</h4>$fromtonote

3033 <table class="td2r thl">

3034 `sq $db -header -html "$sql"`

3035 </table>

3036 `cgi_hidden grp $grid`

3037 `cgi_hidden myuid $myuid id="myuid"`

3038 EOF

3039 }

3040 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3041 # Create TEMPORARY VIEW

3042 query<<EOF

3043 CREATE TEMPORARY VIEW writeusers AS

3044 SELECT DISTINCT author FROM article

3045 WHERE id in (

3046 select id from article where blogid=(select id from blog where rowid=$1)

3047 );

3048 CREATE TEMPORARY VIEW movablegroups AS

3049 SELECT g.rowid growid , g.gname

3050 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3051 ON grp.gname=gm.gname -- そのユーザが属している

3052 AND user='$user') g -- グループに絞る

3053 WHERE (SELECT author FROM writeusers

3054 EXCEPT

3055 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3056 IS NULL;

3057 $2

3058 EOF

3059 }

3060 sql4readableblogs() {

3061 # Create view of blogs that can be readable to $user

3062 # Blog is readable when:

3063 # 1: blog owner is an user

3064 # 2: else, 2.1: owner-group where the $user belongs

3065 # 2.2: else, owner-group is not moderated

3066 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3067 cat<<EOF ## | tee tmp/sql.out

3068 CREATE TEMPORARY VIEW readableblogs AS

3069 SELECT blog.rowid rid, id, author

3070 FROM blog

3071 NATURAL JOIN

3072 (SELECT id,

3073 max(CASE key WHEN 'owner' THEN val END) owner,

3074 max(CASE key WHEN 'mode' THEN val END) mode

3075 FROM blog_s GROUP by id) bs

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

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

3078 WHEN (SELECT val FROM grp_s

3079 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3080 AND

3081 (SELECT user FROM grp_mem

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

3083 THEN 0

3084 WHEN mode IN ('quiz', 'enquete')

3085 THEN 0 -- "quiz" mode blog cannot be searched

3086 ELSE 1

3087 END;

3088 EOF

3089 }

3090 editheading() { # $1=rowid-of-heading

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

3092 if [ -z "$rowid" ]; then

3093 echo "話題番号が未指定です。" | html p

3094 return

3095 fi

3096 owner=`getvalbyid blog owner $rowid`

3097 title=`getvalbyid blog title $rowid`

3098 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3099 | _m4 -D_TITLE_="修正" \

3100 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3101 -D_BLOGS_="" -D_DUMPTABLE_="" \

3102 -D_FORM_="syscmd(\`cat')" \

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

3104 # Move to group

3105 if isuser "$owner"; then

3106 crview4article $rowid

3107 n=`query "SELECT count(*) FROM writeusers;"`

3108 ## err N=$n

3109 if [ $((n)) -gt 0 ]; then

3110 ## err ROWID=$rowid

3111 sql="SELECT growid || ':' || gname FROM movablegroups;"

3112 cat<<-EOF

3113 <div class="fold">

3114 `cgi_checkbox mv send id="mv"`<label

3115 for="mv">この話題をグループ所有に移動する</label>

3116 <div>

3117 <form action="?mvart" method="POST" enctype="multipart/form-data">

3118 移動先グループ:

3119 <select name="mv2grp">

3120 EOF

3121 query ".mode html"

3122 query<<-EOF |

3123 $sql

3124 .mode list

3125 EOF

3126 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3127 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3128 cat<<-EOF

3129 </select>

3130 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3131 そのグループに加入しているものに限られます)</p>

3132 <p>`cgi_checkbox cfm yes`<label>確認

3133 (この操作は元に戻すことができません)</label></p>

3134 `cgi_hidden blogrowid $rowid`

3135 `cgi_submit 移動`

3136 `cgi_reset Reset`

3137 </form>

3138 </div>

3139 </div>

3140 EOF

3141 fi

3142 # end of isuser "$owner"

3143 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3144 [ -n "$hexteams" ];}; then

3145 none="`echo なし|hexize`"

3146 cat<<-EOF

3147 <div class="fold">

3148 `cgi_checkbox mv2team send id="mv2team"`<label

3149 for="mv2team">この話題を以下のチームのものにする</label>

3150 <div><p>現在の所属チーム設定:

3151 `query "SELECT

3152 coalesce((SELECT val FROM blog_s

3153 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3154 AND key='team'),

3155 ':なし');"`</p>

3156 <form action="?mvart" method="POST" enctype="multipart/form-data">

3157 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3158 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3159 `cgi_hidden blogrowid $rowid`<br>

3160 `cgi_submit 移動`

3161 `cgi_reset Reset`

3162 </form></div></div>

3163 EOF

3164 fi

3165 }

3166 mvart() { # move diary to some group or team

3167 # or move blog of group to team which belong to the group

3168 blogrowid=`getpar blogrowid`

3169 cfm=`getpar cfm`

3170 ##### echo move blog:$blogrowid to $mv2grp | html p

3171 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3172 . ./s4-blog.sh

3173 if [ -z "$blogrowid" ]; then

3174 echo "無効な指定です(mvart)。" | html p

3175 return

3176 elif [ x"$cfm" != x"yes" ]; then

3177 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3178 elif { mv2grp=`getpar mv2grp`

3179 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3180 [ -n "$mv2grp" ]; }; then

3181 crview4article $blogrowid

3182 ########## TRANSACTION BEGIN

3183 query "BEGIN;"

3184 n=`query "SELECT count(*) FROM writeusers;"`

3185 ## err Nwriteuser=$n

3186 if [ $((n)) -gt 0 ]; then

3187 query<<-EOF

3188 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3189 WHERE key='owner'

3190 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3191 AND $mv2grp IN (SELECT growid FROM movablegroups);

3192 EOF

3193 fi

3194 query "END;"

3195 ########## TRANSACTION END

3196 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3197 [ -n "$mv2team" ];}; then

3198 # blog owner can move it to ANY team

3199 case "$mv2team" in

3200 'なし')

3201 cat<<-EOF

3202 DELETE FROM blog_s

3203 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3204 AND key='team';

3205 EOF

3206 ;;

3207 "") ;;

3208 *)cat<<-EOF

3209 BEGIN;

3210 REPLACE INTO blog_s(id, key, val)

3211 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3212 'team', '$mv2team');

3213 REPLACE INTO blog_s(id, key, val)

3214 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3215 'notify', 'all'); -- Change notify to all

3216 END;

3217 EOF

3218 esac | query

3219 fi

3220 blog_reply $blogrowid

3221 echo yes | html p

3222 }

3223 editart() { # $1=article-rowid $2=blogrowid

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

3225 blogrowid=${2%%[!A-Z0-9a-z_]*}

3226 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3227 echo "表示する記事番号が未指定です。" | html p

3228 return

3229 fi

3230 owner=`getvalbyid blog owner $blogrowid`

3231 title=`getvalbyid blog title $blogrowid`

3232 author=`getvalbyid article author $rowid`

3233 ## err EDITart: owner=$owner, author=$author

3234 if isgrpowner "$user" "$owner"; then

3235 : EDIT OK

3236 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3237 echo "本人か所有者しか編集できません." | html p

3238 return

3239 fi

3240 aid=`query "select id from article where rowid=$rowid;"`

3241 tmpout=$tmpd/editart.$$.out

3242 GF_ACTION="?replyblog+$blogrowid#$aid" \

3243 edittable $formdir/article.def article $rowid \

3244 > $tmpout

3245 rm -f /tmp/editart.out

3246 # Cannot use pipelining to m4 with genform() because of stdin stack

3247 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3248 -D_FORM_="syscmd(cat $tmpout)" \

3249 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3250 -D_BLOGS_= -D_DUMPTABLE_= \

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

3252 }

3253 send2mem() {

3254 rowid=`getpar grp`

3255 rowid=${rowid%%[!0-9]*} # Cleaning

3256 if [ -z "$rowid" ]; then

3257 echo "グループが未指定です。" | html p

3258 return

3259 fi

3260 message=`getpar message`

3261 if [ -z "$message" ]; then

3262 echo "文章を入れてください。" | html p

3263 return

3264 fi

3265 grp=`getgroupbyid $rowid`

3266 members=`collectemail $grp`

3267 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3268 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3269 mailfrom="`gecos "$user"` <$mailfrom>"

3270 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3271 msg=$(cat<<-EOF

3272 $urlbase?grp+$rowid

3273 グループ $grp に所属する

3274 `gecos $user` さんよりメッセージ:

3275

3276 $message

3277 EOF

3278 )

3279 # smail rcpt subj (file)

3280 for m in $members; do

3281 echo "$msg" |

3282 MAIL_FROM=$mailfrom \

3283 SENDER=$noreply \

3284 REPLYTO=$mailfrom \

3285 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3286 smail "$m" "$sj"

3287 done

3288 cat<<EOF

3289 <p>以下のユーザに送信しました。</p>

3290 <pre>

3291 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3292 </pre>

3293 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3294 EOF

3295 }

3296 joingrpadmit() {

3297 # $1=yes/no $2=session-key

3298 if [ -z "$2" ]; then

3299 echo "bye bye" | html p; return

3300 fi

3301 t_usr=`session=$2 getpar adduser`

3302 t_grp=`session=$2 getpar group`

3303 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3304 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3305 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3306 echo "無効な加入依頼です。" | html p

3307 echo "有効期限が切れたか、

3308 他の管理者がいる場合は処理済みの可能性があります。" | html p

3309 return

3310 fi

3311 if ! isgrpowner "$user" "$t_grp"; then

3312 echo "グループ管理者のみの機能です。" | html p; return

3313 fi

3314 case $1 in

3315 yes) joingrp "$t_grp" "$t_usr" yes ;;

3316 no) joingrp "$t_grp" "$t_usr" no ;;

3317 *)

3318 echo "無効な指定です($1)。" | html p

3319 return ;;

3320 esac

3321 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3322 rcpts="`getgroupadminmails $t_grp` $user"

3323 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3324 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3325 に

3326 $t_usr

3327 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3328 しました。"

3329 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3330 query "delete from session where id='$2';"

3331 echo "$body" | html p

3332 }

3333

3334 joingrprequest() {

3335 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3336 jss="joingrp-`date +%s`-`genrandom 12`"

3337 addsession $jss +${memoplimitdays}days

3338 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3339 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3340 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3341 $url

3342 $user さんから

3343 グループ $1

3344 に加入依頼がありました。

3345

3346 承認する:

3347 $urlbase?joingrpadmit+yes+$jss

3348

3349 白紙に戻す:

3350 $urlbase?joingrpadmit+no+$jss

3351 EOF

3352 echo "管理者に加入依頼を出しました。

3353 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3354 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3355 }

3356 joingrp() {

3357 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3358 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3359 if isgrpowner "$user" "$1"; then

3360 isowner="yes"

3361 elif [ -n "$5" ]; then

3362 isowner="yes"

3363 else

3364 isowner=""

3365 fi

3366 ## err jg:isgrpowner: isowner="$isowner"

3367 if [ -n "$isowner" ]; then

3368 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3369 elif [ x"$2" != x"$user" ]; then # if user is not login user

3370 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3371 return

3372 elif [ x"$3" = x"no" ]; then

3373 : # Do not pursue those who leave

3374 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3375 : # Member can change own email address for the joining moderated group

3376 else # adding user is $user itself

3377 case `getgroupattr $1 regmode` in

3378 moderated)

3379 joingrprequest "$@" # Request only

3380 return

3381 ;;

3382 *)

3383 ;;

3384 esac

3385 fi

3386 qgname=`sqlquote "$1"`

3387 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3388 cond="where gname=$qgname and user='$2'"

3389 if [ x"$3" = x"yes" ]; then

3390 query "replace into grp_mem values($qgname, '$2');"

3391 # Notify joingrp to admin

3392 action="に加入しました。"

3393 if [ -n "$4" ]; then

3394 if msg=`emaildomaincheck "$4"`; then

3395 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3396 'string', '$4', NULL);"

3397 else

3398 echo $msg

3399 fi

3400 else

3401 query "delete from grp_mem_s $cond and key='email';"

3402 fi

3403 if [ -n "$5" ]; then # as ADMIN

3404 # Coming here means newly created group

3405 sql="select case\

3406 when (select count(*) from grp_mem where gname=$qgname)=1\

3407 then (select user from grp_mem\

3408 where gname=$qgname and user='$user')\

3409 else '' end; "

3410 err NewGrpChk: $sql

3411 if [ -n "`query \"$sql\"`" ]; then

3412 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3413 query "replace into grp_adm values($qgname, '$user');"

3414 fi

3415 fi

3416 else

3417 query "begin;

3418 delete from grp_mem $cond;

3419 delete from grp_mem_s $cond;

3420 delete from grp_mem_m $cond;

3421 delete from grp_adm $cond;

3422 delete from grp_adm_s $cond;

3423 delete from grp_adm_m $cond;

3424 end;"

3425 action="から脱退しました。"

3426 fi

3427 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3428 $url?grp+$grid

3429 $user (`gecos $user`)さんが

3430 グループ $1

3431 $action

3432 EOF

3433 }

3434 grp_add_team() (

3435 # $1=grp-rowid $2=team $3...=user-rowid(s)

3436 grp=`getgroupbyid $1`

3437 team=$2; shift; shift

3438 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3439 { echo "BEGIN;"

3440 for user; do

3441 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3442 '$grp',\

3443 (SELECT name FROM user WHERE rowid=$user),\

3444 'team', 'string', '$team');"

3445 done

3446 echo "END;"

3447 } | query

3448 )

3449 grp_rm_team() (

3450 # $1=grp-rowid $2=team $3...=user-rowid(s)

3451 grid=$1

3452 qgrp=$(sqlquote "`getgroupbyid $grid`")

3453 team=$2; shift; shift

3454 [ -z "$grid" -o -z "$team" ] && return

3455 { echo "BEGIN;"

3456 for user; do

3457 echo "DELETE FROM grp_mem_m\

3458 WHERE gname=$qgrp \

3459 AND user=(SELECT name FROM user WHERE rowid=$user)\

3460 AND key='team' AND val='$team';"

3461 done

3462 cat<<-EOF

3463 DELETE FROM blog_s

3464 WHERE rowid=(

3465 SELECT rowid

3466 FROM blog_s a

3467 WHERE key='team'

3468 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3469 AND NOT EXISTS (SELECT * FROM grp_mem_m

3470 WHERE key='team' AND val=a.val -- a.val=team

3471 AND gname = (SELECT val FROM blog_s b

3472 WHERE a.id=b.id AND key='owner')

3473 ));

3474 EOF

3475

3476 echo "END;"

3477 } | query

3478 )

3479 grp_reg_adm() {

3480 # $1=grp-rowid $2...=user-rowid

3481 grid=$1

3482 grp=`getgroupbyid "$1"`

3483 if [ -z "$grp" ]; then

3484 echo "無効なグループIDです" | html p; return

3485 fi

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

3487 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3488 fi

3489 shift

3490 for urid; do

3491 newadm=`query "select name from user where rowid=$urid;"`

3492 if [ -z "$newadm" ]; then

3493 echo "指定ユーザIDがおかしいようです。" | html p; return

3494 fi

3495 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3496 err ismember $newadm $grp

3497 if ismember $newadm "$grp"; then

3498 # OK, go ahead

3499 getgname="(select gname from grp where rowid=$grid)"

3500 query "replace into grp_adm values($getgname, '$newadm');"

3501 # confirm insertion

3502 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3503 if [ -n "`query \"$sql;\"`" ]; then

3504 echo "追加完了: $newadm" | html p

3505 else

3506 echo "追加失敗($1 $urid)" | html p

3507 fi

3508 fi

3509 showgroup $grid

3510 done

3511 }

3512 dt_rowhack() {

3513 # From: <TR>

3514 # ....

3515 # <TD>rowclass=foo</TD>

3516 # </TR>

3517 # To: <TR class="foo">....<TD>foo</TD></TR>

3518 sed -e '

3519 /^<TR>/ {

3520 :loop

3521 s/\n//

3522 N

3523 /<\/TR>/ {

3524 s/\n//

3525 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3526 n

3527 }

3528 $q

3529 b loop

3530 }'

3531 }

3532 dumptable() {

3533 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3534 # textのフィールドだけ全てダンプにしたほうがいいか

3535 # $DT_VIEW sets link

3536 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3537 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3538 VIEW=${DT_VIEW-replyblog}

3539 if [ -n "$VIEW" ]; then

3540 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3541 fi

3542 sqlfile=$tmpd/dump.sql

3543 : > $sqlfile # ensure to be empty

3544 printf '.mode html\n.header 1\n' > $sqlfile

3545 # $DT_CHLD=ChildTable:BindColumn

3546 if [ -n "$DT_CHLD" ]; then

3547 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3548 cat<<-EOF >> $sqlfile

3549 -- presql

3550 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3551 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3552 EOF

3553 # Speed up counting of new articles

3554 cat<<-EOF >> $sqlfile

3555 -- presql2

3556 DROP TABLE IF EXISTS _counts;

3557 CREATE TEMPORARY TABLE _counts AS

3558 SELECT $_i, count($_i) cnt

3559 FROM $_t GROUP BY $_i;

3560 /* Prepare NEW count table */

3561 CREATE TEMPORARY TABLE _target AS

3562 SELECT b.rowid trowid, b.id

3563 FROM "$2" b JOIN "$2_s" s

3564 ON b.id=s.id AND s.key='owner'

3565 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3566

3567 DROP TABLE IF EXISTS _children;

3568 CREATE TEMPORARY TABLE _children AS

3569 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3570 FROM (SELECT t.trowid, t.id $_i, a.id

3571 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3572 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3573

3574 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3575 DROP TABLE IF EXISTS _news;

3576 DROP VIEW IF EXISTS _news;

3577

3578 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3579 -- INSERT INTO _news

3580 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3581 CREATE TEMPORARY VIEW _news AS

3582 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3583 FROM (SELECT DISTINCT id FROM _target)

3584 a LEFT JOIN

3585 (SELECT $_i, count(ctime) newcnt

3586 FROM _children x

3587 WHERE ctime > coalesce((SELECT time from myacclog

3588 WHERE tblrowid=x.trowid),

3589 '1970-01-01')

3590 GROUP BY $_i) b

3591 ON a.id=b.$_i;

3592 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3593 EOF

3594 # REMOVE next line until 2019/5/1

3595 cntall="(select count($_i) from $_t where $_i=a.id)"

3596 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3597 # REMOVE next assignment until 2019/5/1

3598 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3599 and id in (select id from $_t where $_i=a.id) \

3600 and val > coalesce((select time from myacclog where \

3601 tblrowid=a.rowid),\

3602 '1970-01-01'))"

3603 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3604 cnt="$cntnew as '新着', $cntall as '総数',"

3605 dt_class=" td2r td3r dumpblogs"

3606 fi

3607 # Construct join expression

3608 eav="" scols=""

3609 pk=`gettblpkey $2`

3610 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3611 substr=${substr:-%s}

3612 for col in ${3:-`gettbl_s_cols $2`}; do

3613 valvar=val

3614 case $col in

3615 gecos) scols="$scols${scols:+, }${col#}"

3616 continue ;; # built-in column name

3617 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3618 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3619 case "$as" in

3620 *:*=*) cnd=${as#*:}

3621 h=${cnd%%=*} v=${cnd#*=}

3622 h=`sqlquotestr "$h"`

3623 v=`sqlquotestr "$v"`

3624 valvar="CASE val WHEN $h THEN $v END"

3625 as=${as%%:*} ;;

3626 esac

3627 ;;

3628 *) as=${col} ;;

3629 esac

3630 ss=`printf "$substr" "$valvar"`

3631 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3632 scols="$scols${scols:+, }b.$as"

3633 done

3634 #case author when '$user' then a.rowid else '---' end as ID,

3635 if [ -n "$DT_SQL" ]; then

3636 echo "$DT_SQL"

3637 else

3638 cat<<-EOF

3639 SELECT a.rowid as LINK, $cnt $scols

3640 FROM $2 a LEFT JOIN

3641 (SELECT $pk,$eav,

3642 max(CASE key

3643 WHEN 'owner'

3644 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3645 as gecos

3646 FROM ${2}_s c GROUP BY $pk)

3647 b ON a.$pk=b.$pk $4;

3648 EOF

3649 fi >> $sqlfile

3650 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3651 sqlog<<-EOF

3652 *** SQL-file: $sqlfile ***

3653 `cat $sqlfile`

3654 EOF

3655 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3656 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3657 cat $sqlfile >> tmp/sql

3658 # query ".read $sqlfile" > $tmpd/foo

3659 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3660 cp $tmpd/foo tmp/

3661 err "----- `gdate +%FT%T.%3N` ------------555555"

3662 ## $ddd LINE exists at the end of this function

3663 fi

3664 printf '.mode list\n.header 0\n' >> $sqlfile

3665 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3666 <div>

3667 <div class="dumptable">

3668 <table class="b$dt_class">

3669 `query ".read $sqlfile"`

3670 </table>

3671 </div>

3672 </div>

3673 EOF

3674 ### `query ".read $sqlfile"`

3675 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3676

3677 ## REMOVE THIS!

3678 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3679 }

3680

3681 par2table() (

3682 # copy current parameters of par into destination table

3683 # $1=definition-file

3684 # Using $user and $session

3685 # Return value:

3686 # 0: Stored successfully

3687 # 1: Insufficient fillings

3688 # 2: No permission to modify the record

3689 # 3: Invalid rowid

3690 # 4: SUCCESS to delete

3691 # 5: Stop deletion for lack of confirm check

3692 # 6: Password length too short

3693 # 7: Password mismatch

3694 # 8: Old password incorrect

3695 # 9: Duplicated post

3696 rowid=`getpar rowid`

3697 if [ ! -e $1 ]; then

3698 echo "テーブル定義ファイルが見付かりません" | html p

3699 exit 1

3700 fi

3701 tbl=${1%.def}

3702 tbl=${tbl##*/}

3703 if [ -n "$rowid" ]; then # Modify existing entry

3704 if [ x"$tbl" = x"user" ]; then

3705 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3706 elif [ x"$tbl" = x"grp" ]; then

3707 sql="select gname from $tbl where rowid=$rowid;"

3708 ##err p2t:grp:q $sql

3709 isgrpowner "$user" "`query $sql`" && rowowner=$user

3710 elif [ x"$tbl" = x"blog" ]; then

3711 # Check if owner in blog_s

3712 blogowner=`getvalbyid blog owner "$rowid"`

3713 if isgrpowner "$user" "$blogowner"; then

3714 rowowner=$user

3715 else

3716 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3717 fi

3718 else

3719 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3720 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3721 fi

3722 ### err rowowner=$rowowner

3723 if [ x"$user" != x"$rowowner" ]; then

3724 echo "他人のレコードはいじれないの" | html p

3725 return 2

3726 elif [ -z "$rowowner" ]; then

3727 echo "指定したレコードはないみたい" | html p

3728 return 3

3729 fi

3730 rm=`getpar rm` cfm=`getpar confirm`

3731 # Editing existent entry

3732 if [ x"$rm" = x"yes" ]; then

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

3734 query "delete from $tbl where rowid=$rowid;"

3735 return 4

3736 else

3737 echo "消去確認のチェックがないので消さなかったの..." | html p

3738 return 5

3739 fi

3740 fi

3741 fi

3742

3743 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3745 # Update of existing record

3746 for col in `gettblcols $tbl`; do

3747 val=`getparquote $col`

3748 [ -z "$val" ] && continue

3749 ## err query "update $tbl set $col=$val where rowid=$rowid"

3750 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3751 sql="update $tbl set $col=$val where rowid=$rowid;"

3752 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3753 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3754 ## User name cannot be changed with interface provided with this

3755 ## script. But we offer the trigger to change owner user

3756 ## of blog_s table.

3757 #err "select quote($col) from $tbl where rowid=$rowid;"

3758 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3759 cat<<-EOF | query

3760 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3761 -- should use EXCLUSIVE transaction outside of this.

3762 SAVEPOINT par2table;

3763 $sql

3764 update blog_s set val=$val

3765 where key='owner' and val=$old;

3766 RELEASE SAVEPOINT par2table;

3767 EOF

3768 ## XX: DIRTY Hack Ends here

3769 ## We should keep blog's owner as a single column which has

3770 ## foreign key constraint with primary key of grp/user.

3771 else

3772 query "$sql"

3773 fi

3774 done

3775 # Then, set up $pval for further insertion of tbl_s and tbl_m

3776 for col in `gettblpkey $tbl`; do

3777 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3778 pval="$pval${pval:+, }\"$val\""

3779 done

3780 else

3781 # New entry

3782 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3783 # Avoid empty repost of article.

3784 if [ x"$tbl" = x"article" ]; then

3785 # If rowid is empty and ID exists in article-table, that is REPOST!

3786 aid=`getpar id`

3787 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3788 if [ -n "$xaid" ]; then

3789 # REPOST of article

3790 html p <<-EOF

3791 書き込み直後のリロードなので上書きを回避します。

3792 最新記事は末尾の「再読み込み」ボタンから見てください。

3793 EOF

3794 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3795 return 9 # STOP Duplicated posting

3796 fi

3797 fi

3798 # Generate values() for primary keys

3799 for col in `gettblpkey $tbl`; do

3800 # Genuine primary keys for _m and _s

3801 val=`getvalquote $tbl $col`

3802 [ -z "$val" ] && continue

3803 pval="$pval${pval:+, }$val"

3804 done

3805 ##err pval=$pval

3806 for col in `gettblfkey $tbl`; do

3807 # args for values() to insertion into master table

3808 val=`getvalquote $tbl $col`

3809 [ -z "$val" ] && continue

3810 formaster=$formaster"${formaster:+, }$val"

3811 done

3812 formaster="$pval${formaster:+, }$formaster"

3813 ## err formaster=$formaster

3814 if [ -z "$formaster" ]; then

3815 echo "項目を全て埋めてください" | html pre

3816 return 1

3817 fi

3818 ## err "replace into $tbl values($formaster);"

3819 query "replace into $tbl values($formaster);"

3820 ## Insertion to master table, done

3821 fi

3822

3823 transaction=$tmpd/sqlfile.sql; touch $transaction

3824 for kt in s m; do

3825 tb2=${tbl}_$kt

3826 for col in `gettbl_${kt}_cols $tbl`; do

3827 ptype=`getpartype $col "limit 1"`

3828

3829 # First, check update of existing entries in _m

3830 if [ $kt = m ]; then

3831 # sessID|address.1.22|string|Somewhere-x.y.z

3832 sql=""

3833 ##err dots from query "select var from par where var like '$col.%';"

3834 for v in `query "select var from par where var like '$col.%';"`; do

3835 # v=address.1.22

3836 st_rowid=${v##*.}

3837 origcol=${v%%.*} # original column derived from

3838 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3839 ##case `getpartype $v` in

3840 ## err CASE `gettbl_coltype $tbl/$origcol` in

3841 ## err edit flag = `getpar action.$v`

3842 case `getpar action.$v` in

3843 rm)

3844 if [ x`getpar confirm.$v` = x"yes" ]; then

3845 newsql="delete from $tb2"

3846 else

3847 echo "削除確認未チェック" | html p

3848 fi ;;

3849 edit)

3850 case `gettbl_coltype $tbl/$origcol` in

3851 image|document|binary)

3852 file=$tmpd/`getparfilename $v`

3853 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3854 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3855 continue

3856 fi

3857 ## err type=file=$file

3858 [ -z "$file" ] && continue

3859 bn=`sqlquotestr "${file##*/}"`

3860 bin="X'"$(hexize "$file")"'"

3861 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3862 type=\"file:$ct\"

3863 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3864 cachedir=`getcachedir "$tbl/$rowid"`

3865 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3866 rm -rf $cachedir

3867 ;;

3868 *)

3869 newsql="update $tb2 set val=(select val from par where var \

3870 like '$col.%.$st_rowid')"

3871 ;;

3872 esac

3873 ;;

3874 *) # maybe "keep", do not modify value

3875 continue

3876 ;;

3877 esac

3878 # err newsql=$newsql

3879 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3880 done

3881

3882 if [ x"$bin" = x"NULL" ]; then

3883 ## err repl:normal sql=`echo $sql`

3884 if [ -n "$transaction" ]; then

3885 cat<<-EOF >> $transaction

3886 $sql

3887 DELETE FROM $tb2 WHERE type='string' AND val='';

3888 EOF

3889 else

3890 query "$sql

3891 delete from $tb2 where type='string' and val='';"

3892 ## err repl:normal done

3893 fi

3894 else

3895 # Binary update line is TOO LONG to pipelining

3896 sqlfile="$tmpd/sqlf.$$"

3897 if [ -n "$transaction" ]; then

3898 echo "$sql" >> $transaction

3899 else

3900 echo "$sql" > $sqlfile

3901 query ".read $sqlfile"

3902 fi

3903 fi

3904 # Rest of kt==m: set multiple mode

3905 nr=`getparcount $col`

3906 else

3907 nr=1 # for kt==s, number of records is 1

3908 fi

3909

3910 i=0

3911 while [ $i -lt $nr ]; do

3912 limit="limit 1 offset $i"

3913 i=$((i+1)) # increase beforehand against continue

3914 val=`getvalquote $tbl $col "$limit"`

3915 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3916 ## err $col=$val

3917 bin=NULL

3918 ## err partype$col=`getpartype $col "$limit"`

3919 case $ptype in

3920 file) file=$tmpd/`getparfilename $col "$limit"`

3921 ## err parfile-$col=$file

3922 [ -z "$file" ] && continue

3923 bin="X'"$(hexize "$file")"'"

3924 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3925 type=\"file:$ct\" ;;

3926 "*"*) continue ;; # foreign table

3927 *) type=\"string\" ;;

3928 esac

3929 case `gettbl_coltype $tbl/$col` in

3930 password) # special care for password

3931 # name={password,pswd1,pswd2}

3932 p1=`getpar pswd1 "$limit"`

3933 if [ -z "$p1" ]; then

3934 continue # SKIP password setting, if p1 is empty

3935 else

3936 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3937 ## err pswd=$pswd

3938 if pwcheck "$pswd"; then

3939 if [ x"$p1" = x"$p2" ]; then

3940 case "$p1" in

3941 ??????????*) ;;

3942 *) echo "パスワードは10字以上にしてください。" | html p

3943 return 6;;

3944 esac

3945 val="\"`echo $p1|mypwhash`\""

3946 else

3947 echo "2つの新パスワード不一致" | html p

3948 return 7

3949 fi

3950 else

3951 echo "旧パスワード違います" | html p

3952 return 8

3953 fi

3954 fi

3955 ;;

3956 esac

3957 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3958 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3959 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3960 if [ x"$bin" = x"NULL" ]; then

3961 ## err Normal-query: `echo $sql`

3962 if [ -n "$transaction" ]; then

3963 echo "$sql" >> $transaction

3964 else

3965 query "$sql"

3966 fi

3967 else

3968 sqlfile="$tmpd/query.$$"

3969 ## err sqlfile=`ls -lF $sqlfile`

3970 if [ -n "$transaction" ]; then

3971 echo "$sql" >> $transaction

3972 else

3973 echo "$sql" > $sqlfile

3974 query ".read $sqlfile"

3975 fi

3976 fi

3977 ## err p2t done

3978 done

3979 done

3980 done

3981 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3982 -- We cannot use transaction here, because groupupdate may use it.

3983 SAVEPOINT pa2table_insert;

3984 .read $transaction

3985 RELEASE SAVEPOINT pa2table_insert;

3986 EOF

3987 rc=$?

3988 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

3989 ## err "Table:$tbl update done "

3990 return $rc

3991 )

3992 genform() {

3993 # $1 = form definition file

3994 # $2, $3 (optional)= table name and ROWID

3995 # If $GF_VIEWONLY set and nonNull, output values without form

3996 # If $GF_ARGS set, use it as content-strings in the form

3997 # If $GF_OWNER set, use it as value of name="owner"

3998 # If $GF_STAGE set, use it as value of name="stage"

3999 forms="" hiddens="" rowid=$3

4000 if [ ! -e "$1" ]; then

4001 echo "そのようなデータベースはないようです($2)。" | html p

4002 return

4003 elif [ -n "$2" ]; then

4004 rec=`query "select * from $2 where rowid='$rowid';"`

4005 if [ -z "$rec" ]; then

4006 pk=`gettblpkey $2`

4007 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4008 rec=`query "select rowid from $2 where $pk='$rowid';"`

4009 rowid=$rec

4010 rec=$3

4011 fi

4012 if [ -z "$rec" ]; then

4013 echo "そんなレコードはないみたいね..." | html p

4014 return

4015 fi

4016 fi

4017 if [ -z "$GF_VIEWONLY" ]; then

4018 rm='<input id="rm" name="rm" type="checkbox"

4019 value="yes"><label for="rm">このエントリの削除</label>

4020 <span>ほんとうに消しますよ(確認)!

4021 <input name="confirm" type=checkbox value="yes">はい</span>'

4022 fi

4023 # Image Cache dir

4024 ## err genform: getcache=$2/$rowid

4025 td=`getcachedir "$2/$rowid"`

4026 while IFS=: read prompt name keytype type args; do

4027 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4028 sp="${args:+ }"

4029 form="" val=""

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

4031 # err genform2a: Seeking for "$2.$name, type=$type"

4032 rawval=`getvalbyid $2 $name $rowid $td`

4033 val=`echo "$rawval"|htmlescape`

4034 ## err genform3a: getvalbyid $2 $name $rowid $td

4035 ## err genform3b: val="[$val]" type="$type"

4036 fi

4037 if [ -n "$GF_VIEWONLY" ]; then

4038 is_hidden "$2" "$name" && continue

4039 fi

4040 case "$type" in

4041 text*)

4042 cgiform=cgi_multi_$type

4043 if [ -s $td/$name.count -a -n "$val" ]; then

4044 form=`$cgiform $name $td`

4045 val=$(echo "$val"|

4046 while read fn; do

4047 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4048 </td></tr>$nl"

4049 done)

4050 val="<table>$nl$val$nl</table>"

4051 else

4052 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4053 form=`cgi_$type $name "$rawval" "$args"`

4054 fi

4055 ;;

4056 [Rr][Aa][Dd][Ii][Oo])

4057 fh="<label><input type=\"radio\" name=\"$name\""

4058 form="`echo $args|sed -e \

4059 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4060 ;;

4061 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4062 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4063 ;;

4064 [Ss][Ee][Ll][Ee][Cc][Tt])

4065 fh="<select name=\"$name\">$nl"

4066 form=$(for l in $args; do

4067 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4068 done)

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

4070 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4071 fi

4072 form="$fh$form</select>"

4073 ;;

4074 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4075 if [ -s $td/$name.count ]; then

4076 form=`cgi_multi_file $name $td "$args"`

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

4078 hrfb="$myname?showattc+$2_m"

4079 val=$(echo "$rawval" \

4080 | while read fn; do

4081 data=`percenthex "$td/$fn"`

4082 #ct=`cat $td/$fn.content-type`

4083 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4084 ri=`cat "$td/$fn.rowid"`

4085 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4086 #imgsrc="<img src=\"data:$ct,$data\">"

4087 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4088 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4089 done)

4090 fi

4091 else

4092 form="<input type=\"file\" name=\"$name\" $args>"

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

4094 imgs=$(echo "$rawval"\

4095 |while read fn;do

4096 data=`percenthex "$td/$fn"`

4097 echo "<img src=\"data:image/png,$data\">$fn<br>"

4098 done)

4099 form=$form"<br>$imgs"

4100 val=$imgs # 2015-06-15

4101 else

4102 form="<input type=\"file\" name=\"$name\" $args>"

4103 fi

4104 fi

4105 ;;

4106 [Hh][Ii][Dd][Dd][Ee][Nn])

4107 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4108 args="value=\"$GF_STAGE\""

4109 fi

4110 form="<input type=\"hidden\" name=\"$name\" $args>"

4111 prompt='' # Remove prompt

4112 ;;

4113 [Aa][Uu][Tt][Hh][Oo][Rr])

4114 [ -n "$GF_VIEWONLY" ] && continue

4115 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4116 prompt="" ;;

4117 [Oo][Ww][Nn][Ee][Rr])

4118 [ -n "$GF_VIEWONLY" ] && continue

4119 val=${GF_OWNER:-$val}

4120 val=${val:-$user}

4121 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4122 prompt="" ;;

4123 [Uu][Ss][Ee][Rr])

4124 # XXX: is null $user ok?

4125 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4126 [ -n "$GF_VIEWONLY" ] && continue

4127 form="$user"

4128 ;;

4129 [Pp]assword)

4130 [ -n "$GF_VIEWONLY" ] && continue

4131 form="`cgi_passwd`"

4132 val=""

4133 ;;

4134 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4135 [ -n "$GF_VIEWONLY" ] && continue

4136 if [ -z "$rowid" ]; then

4137 val=`genserial`

4138 fi

4139 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4140 prompt="" ;;

4141 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4142 prompt=""

4143 ;;

4144 parent|path|blog*)

4145 prompt=""

4146 ;;

4147 "*"*)

4148 tail=$tail"``"

4149 continue ;;

4150 esac

4151 if [ -n "$prompt" ]; then

4152 if [ -n "${GF_VIEWONLY}" ]; then

4153 form=$val

4154 else

4155 :

4156 fi

4157 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4158 else

4159 hiddens=$hiddens$nl"$form"

4160 fi

4161 done < $1

4162 # enctype="multipart/form-data"

4163 cat<<EOF

4164 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4165 ${rowid:+$rm}

4166 <table class="b $2">

4167 $forms

4168 </table>$hiddens

4169 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4170 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4171 EOF

4172 if [ -z $GF_VIEWONLY ]; then

4173 cat<<EOF

4174 <input type="submit" name="sub" value="OK">

4175 <input type="reset" name="res" value="Reset">

4176 EOF

4177 fi

4178 cat<<EOF

4179 $GF_ARGS</form>

4180 $tail

4181 EOF

4182 }

4183 edittable() {

4184 # $1=form-def $2=table $3 rowid

4185 genform "$@"

4186 }

4187 viewtable() {

4188 GF_VIEWONLY=1 genform "$@"

4189 }

4190 showattc() {

4191 # $1=table_m $2=rowid &optional $3=RawFlag

4192 ## err \$1=$1 \$2=$2 \$3=$3

4193 if ! isfilereadable $user $1 $2; then

4194 contenttype; echo

4195 echo "このファイルは管理者のみしか見られません" | html p

4196 putfooter; exit

4197 fi

4198 idir=`umask 002; mktempd` || exit 1

4199 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4200 bin=$idir/$myname-$$.bin

4201 sql="select quote(bin) from $1 where rowid='$2';"

4202 ## err showattc: sql: $sql

4203 sq $db "$sql" | unhexize > $bin

4204 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4205 type=${tv%//*} fn=${tv#*//}

4206 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4207 ct=${type#file:}

4208 case $ct in # all text/* changed to text/plain

4209 text/*)

4210 charset=`nkf -g $bin|cut -d' ' -f1`

4211 case $charset in

4212 ASCII*) charset="" ;;

4213 esac

4214 if [ -z "$3" ]; then

4215 ct="text/html${charset:+; charset=$charset}"

4216 link="?showattc+$1+$2+raw"

4217 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4218 | sed 's,^,<span></span>,' \

4219 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4220 -D_LINK_="$link" \

4221 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4222 exit $?

4223 fi

4224 ct="text/plain${charset:+; charset=$charset}"

4225 ;;

4226 esac

4227 contenttype "$ct"

4228 echo "Content-Disposition: filename=\"$fn\""

4229 echo "Content-Length: " `cat $bin | wc -c`; echo

4230 #echo "Content-Type: " ${type#file:}; echo

4231 cat $bin

4232 }

4233 #

4234 # Some default stupid handler on CGI values

4235 #

4236 default_storedb() {

4237 # ARG: $1=table-def-file

4238 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4239 tbl=`basename $1`

4240 tbl=${tbl%.def}

4241 cols="`grep :text $1|cut -d: -f2`"

4242 col=`echo "$cols"|head -1`

4243 vcol=`getpar $col`

4244 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4245 if [ -n "$vcol" ]; then

4246 par2table $1

4247 else

4248 return 2 # No insertion occurred

4249 fi

4250 }

4251

4252 default_view() { # $1=def-file

4253 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4254 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4255 default_storedb "$@"

4256 query "select rowid from $tbl order by rowid desc;" \

4257 | while read rowid; do

4258 viewtable $1 $tbl $rowid

4259 done | _m4 -D_TITLE_="$tbl" \

4260 -D_FORM_="`genform $1`" \

4261 -D_DUMPTABLE_="syscmd(cat)" \

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

4263 }

4264 default_viewtext() { # $1=def-file

4265 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4266 default_storedb "$@"

4267 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4268 | _m4 -D_TITLE_="$tbl" \

4269 -D_FORM_="`genform $1`" \

4270 -D_DUMPTABLE_="syscmd(cat)" \

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

4272 }

4273 default_smail() {

4274 default_storedb "$@"

4275 if [ $? -eq 2 ]; then

4276 _m4 -D_TITLE_="入力" \

4277 -D_FORM_="`genform $1`" \

4278 -D_DUMPTABLE_="" \

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

4280 return

4281 fi

4282 cond=""

4283 for pk in `gettblpkey $tbl`; do

4284 pv=$(sqlquote "$(getpar $pk)")

4285 cond="$cond${cond:+ and }$pk=$pv"

4286 done

4287 sql="select rowid from $tbl where $cond;"

4288 rowid=`query "$sql"`

4289 ## err smail1 - "$sql" "-> rowid=$rowid"

4290

4291 while IFS=: read prompt name keytype type args; do # Read from $1

4292 val=`getpar $name`

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

4294 text="$text

4295 $prompt

4296 $name=$val

4297 ---------------------------------------------------------"

4298 fi

4299 case "$type" in

4300 image|document|file)

4301 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4302 fns=$(echo "$fn"|while read fn; do

4303 err mv $tmpd/$fn.orig $tmpd/$fn

4304 mv $tmpd/$fn.orig $tmpd/$fn

4305 rm $tmpd/$fn.rowid # Remove cache flag

4306 ## err "`ls $tmpd/$fn`"

4307 echo $fn

4308 done)

4309 files="$files $fns"

4310 ;;

4311 esac

4312 done < $1

4313 ## err FILES=$files "`ls -lF $tmpd`"

4314 subj="from ${REMOTE_ADDR}"

4315 (echo "$url"

4316 echo "への書き込みがありました。"

4317 echo "------"

4318 echo "$text"

4319 ) | (cd $tmpd &&

4320 err LS="`ls -lF`" &&

4321 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4322 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4323 echo "以下の内容で送信しました。" | html p

4324 viewtable $1 $tbl \

4325 `query "select rowid from $tbl order by rowid desc limit 1;"`

4326 echo "戻る" | html a "href=\"?\""

4327 }