日程調整システムの構築

日程調整テーブル(暫定版)

CREATE TABLE sched (
  name, date, okng,
  UNIQUE(name, date)
);

日程調整テーブル

CREATE TABLE datelist (date PRIMARY KEY);
CREATE TABLE sched (
  name, date, okng,
  UNIQUE(name, date),
  FOREIGN KEY(date) REFERENCES datelist(date)
);

datelist.csv

10/3
10/4
10/10
10/11
10/17
10/18

./imoni.sh -a 庄内三十彦        # データの追加
10/3=yes
10/4=no
10/10=no
10/11=no
10/17=yes
10/18=yes
[C-d]

read a b c
The quick fox jumps over the lazy dog.
echo a=$a
a=The
echo b=$b
b=quick
echo c=$c
c=fox jumps over the lazy dog.

while IFS="=" read date value; do
  case $value in
    [Yy][Ee][Ss])	val='○' ;;
    [Nn][Oo])		val='×' ;;
    *)			val='-' ;;
  esac
  sqlite3 $db "REPLACE INTO sched VALUES('$who', '$date', '$val');"
done

imoni0.sh(暫定版)

#!/bin/sh
db=${DB:-imoni.sq3}
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}

create() {
  # sqlite_master から datelist テーブルを探す
  tbl=`query "SELECT name FROM sqlite_master WHERE name='datelist';"`
  if [ -z "$tbl" ]; then	# $tbl が空文字列ならテーブル未定義
    query<<-EOF
	CREATE TABLE datelist (date PRIMARY KEY);
	CREATE TABLE sched (
	  name, date, okng,
	  UNIQUE(name, date),	--nameとdateの同一組み合わせは重複不可
	  -- date は dateliste テーブルの date に存在しなければならない
	  FOREIGN KEY(date) REFERENCES datelist(date)
	);
	EOF
  fi
  if [ -s datelist.csv ]; then	# 外部キーの初期値をcsvファイルから取得
    query<<-EOF
	.mode csv
	.import datelist.csv datelist
	EOF
  fi
}

add() {				# 1人分の予定回答を登録する関数
  who="$1"			# 登録者の氏名
  while IFS="=" read date value; do	# 単語区切りを = にして2変数に読み込む
    case $value in
      [Yy][Ee][Ss])	val='○' ;;	# 右辺がyesであれば ○
      [Nn][Oo])		val='×' ;;	# 右辺が noであれば ×
      *)		val='-' ;;	# それ以外はとりあえず - にしておく
    esac
    query "REPLACE INTO sched VALUES('$who', '$date', '$val');"
done
}

while getopts a:i i; do
  case "$i" in
    a)	job=add
	who=$OPTARG ;;
    i)	job=create ;;
  esac
done
shift $((OPTIND-1))

case $job in
  add)				# 「-a 氏名」で起動した場合
    add $who ;;
  *)
    $job "$@" ;;
esac

./imoni0.sh -i		# datelist.csvを用意してから!
./imoni0.sh -a 庄内三十彦
10/3=yes
10/4=no
10/10=no
10/11=no
10/17=yes
10/18=yes
[C-d]
./imoni0.sh -a 山形醤二
10/3=yes
10/4=yes
10/10=yes
10/11=yes
10/17=yes
10/18=no
[C-d]

sqlite3 imoni.sq3
SELECT * FROM sched;
庄内三十彦|10/3|○
庄内三十彦|10/4|×
庄内三十彦|10/10|×
庄内三十彦|10/11|×
庄内三十彦|10/17|○
庄内三十彦|10/18|○
山形醤二|10/3|○
山形醤二|10/4|○
山形醤二|10/10|○
山形醤二|10/11|○
山形醤二|10/17|○
山形醤二|10/18|×

予定表の縦持ちから横持ちへの変換SQL

SELECT name,
       max(CASE date WHEN '10/3'  THEN okng END) "10/3", --カラム別名を"10/3"に
       max(CASE date WHEN '10/4'  THEN okng END) "10/4", --以下同様
       max(CASE date WHEN '10/10' THEN okng END) "10/10",
       max(CASE date WHEN '10/11' THEN okng END) "10/11",
       max(CASE date WHEN '10/17' THEN okng END) "10/17",
       max(CASE date WHEN '10/18' THEN okng END) "10/18"
FROM sched
GROUP BY name;

CASE 式 WHEN 比較値1 THEN 返却値1
        WHEN 比較値2 THEN 返却値2
		:
        WHEN 比較値n THEN 返却値n
	ELSE 返却値e
END

SELECT name,
       CASE date WHEN '10/3'  THEN okng END "10/3", -- カラム別名を "10/3" に
       CASE date WHEN '10/4'  THEN okng END "10/4",
       CASE date WHEN '10/10' THEN okng END "10/10",
       CASE date WHEN '10/11' THEN okng END "10/11",
       CASE date WHEN '10/17' THEN okng END "10/17",
       CASE date WHEN '10/18' THEN okng END "10/18"
FROM sched;

CASE date WHEN '10/3'  THEN okng END "10/3"

CASE date WHEN '10/4'  THEN okng END "10/4"

「○」を数える問い合わせ(暫定版)

SELECT '○の数',
       count(CASE date WHEN '10/3'  THEN okng END) "10/3",
       count(CASE date WHEN '10/4'  THEN okng END) "10/4",
       count(CASE date WHEN '10/10' THEN okng END) "10/10",
       count(CASE date WHEN '10/11' THEN okng END) "10/11",
       count(CASE date WHEN '10/17' THEN okng END) "10/17",
       count(CASE date WHEN '10/18' THEN okng END) "10/18"
FROM sched
WHERE CASE date WHEN '10/3'  THEN okng END='○'
   OR CASE date WHEN '10/4'  THEN okng END='○'
   OR CASE date WHEN '10/10' THEN okng END='○'
   OR CASE date WHEN '10/11' THEN okng END='○'
   OR CASE date WHEN '10/17' THEN okng END='○'
   OR CASE date WHEN '10/18' THEN okng END='○';

WITH 名前 AS (副問い合わせ) 主問い合わせ;

○×表と数え上げ表の合体

WITH sched2 AS (	-- 一時表 sched2 を定義する
  SELECT name,
       CASE date WHEN '10/3'  THEN okng END "10/3",
       CASE date WHEN '10/4'  THEN okng END "10/4",
       CASE date WHEN '10/10' THEN okng END "10/10",
       CASE date WHEN '10/11' THEN okng END "10/11",
       CASE date WHEN '10/17' THEN okng END "10/17",
       CASE date WHEN '10/18' THEN okng END "10/18"
  FROM sched
) SELECT name,		-- 「○×表」を生成するSELECT文
         max("10/3")  "10/3",  max("10/4")  "10/4",  max("10/10") "10/10",
         max("10/11") "10/11", max("10/17") "10/17", max("10/18") "10/18"
	 -- カラム "10/3" の最大値をさらにカラム名を "10/3" としている
  FROM sched2
  GROUP BY name

  UNION ALL		-- 上と下の2つの表の和集合を取る

  SELECT '○の数',	-- 「数え上げ表」を生成するSELECT文
         count("10/3"),  count("10/4"),  count("10/10"),
	 count("10/11"), count("10/17"), count("10/17")
  FROM sched2
  WHERE '○' IN ("10/3", "10/4", "10/10", "10/11", "10/17", "10/18");

'○' IN ("10/3", "10/4", "10/10", "10/11", "10/17", "10/18")

"10/3"='○' OR "10/4"='○' OR "10/10"='○'
OR "10/11"='○' OR "10/17"='○' OR "10/18"='○'

○×数え上げ問い合わせの骨子

WITH sched2 AS (
  SELECT name,
     ここに日付候補の数だけCASE文などが並ぶ
  FROM sched
) SELECT name,
     ここにmaxの列挙
  FROM sched2 GROUP BY name

  UNION ALL

  SELECT '○の値',
     ここにcountの列挙
  FROM sched2
  WHERE '○' IN (ここにカラム別名の列挙)

gensql.sh

#!/bin/sh
db=${DB:-imoni.sq3}
nlt="
	"		# 改行文字+Tab
sqlite3 $db "SELECT date FROM datelist;" \
  | sort -n -t / \
  | { while read d; do
	# パイプの先の while 内で設定した変数を使いたいので
	# { } でグルーピング
        sq1=$sq1${sq1:+,$nlt}"CASE date WHEN '$d' THEN okng END \"$d\""
        sq2=$sq2${sq2:+,$nlt}"max(\"$d\") \"$d\""
        sq3=$sq3${sq3:+,$nlt}"count(\"$d\")"
        sq4=$sq4${sq4:+, }"\"$d\""
      done
      cat<<EOF
WITH sched2 AS (
  SELECT name,
     $sq1
  FROM sched
) SELECT name,
     $sq2
  FROM sched2 GROUP BY name

  UNION ALL

  SELECT '○の値',
     $sq3
  FROM sched2
  WHERE '○' IN ($sq4);
EOF
    }

sqlite3 $db "SELECT date FROM datelist;"

sort -n -t /

| { while文
    変数を利用した処理
  }

sql文 | sort文 | while ...

./gensql.sh | sqlite3 imoni.sq3
山形醤二|○|○|○|○|○|×
庄内三十彦|○|×|×|×|○|○
○の値|2|1|1|1|2|1

./imoni.sh -d 山形醤二

delete() {
  who="$1"		# 第1引数 = 削除したい人
  query "DELETE FROM sched WHERE name='$who';"
}

imoni.sh

#!/bin/sh
db=${DB:-imoni.sq3}
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}

create() {		# テーブル作成と外部キーテーブル初期化
  tbl=`query "SELECT name FROM sqlite_master WHERE name='datelist';"`
  echo tbl="[$tbl]"
  if [ -z "$tbl" ]; then	# $tbl が空文字列なら
    query<<-EOF
	CREATE TABLE datelist (date PRIMARY KEY);
	CREATE TABLE sched (
	  name, date, okng,
	  UNIQUE(name, date),
	  FOREIGN KEY(date) REFERENCES datelist(date)
	);
	EOF
  fi
  if [ -s datelist.csv ]; then
    query<<-EOF
	.mode csv
	.import datelist.csv datelist
	EOF
  fi
}

add() {			# レコード登録機能
  who="$1"
  while IFS="=" read date value; do
    case $value in
      [Yy][Ee][Ss])	val='○' ;;
      [Nn][Oo])		val='×' ;;
      *)		val='-' ;;
    esac
    query "REPLACE INTO sched VALUES('$who', '$date', '$val');"
  done
}

list() {		# gensql.sh を利用してHTMLの表を出力
  echo '<table border="1">'
  ./gensql.sh | sqlite3 -header -html $db
  echo "</table>"
}

delete() {		# 削除機能
  who="$1"		# 第1引数 = 削除したい人
  query "DELETE FROM sched WHERE name='$who';"
}

while getopts a:id:l i; do
  case "$i" in
    a)	job=add
	who=$OPTARG ;;
    i)	job=create ;;
    l)	job=list ;;
    d)	job=delete who=$OPTARG ;;
  esac
done
shift $((OPTIND-1))

case $job in
  add|delete)		# オプション指定なしの場合
    $job $who
    ;;
  *)
    $job "$@" ;;
esac

db=${DB:-imoni.sh}
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd '.timeout 3000' $db "$@"
}

add() {
  who="$1"
  while IFS="=" read date value; do
    case $value in
      [Yy][Ee][Ss])	val='○' ;;
      [Nn][Oo])		val='×' ;;
      *)		val='-' ;;
    esac
    echo "REPLACE INTO sched VALUES('$who', '$date', '$val');"
  done | query
}

BEGIN;
 SQL文の並び
COMMIT;

add() {
  who="$1"
  { echo "BEGIN;"
    while IFS="=" read date value; do
      case $value in
        [Yy][Ee][Ss])	val='○' ;;
        [Nn][Oo])	val='×' ;;
        *)		val='-' ;;
      esac
      echo "REPLACE INTO sched VALUES('$who', '$date', '$val');"
    done
    echo "COMMIT;"
  } | query
}

imoni2.sh

#!/bin/sh
db=${DB:-imoni.sq3}
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd '.timeout 3000' $db "$@"
}

create() {		# テーブル作成と外部キーテーブル初期化
  tbl=`query "SELECT name FROM sqlite_master WHERE name='datelist';"`
  if [ -z "$tbl" ]; then	# $tbl が空文字列なら
    query<<-EOF
	CREATE TABLE datelist (date PRIMARY KEY);
	CREATE TABLE sched (
	  name, date, okng,
	  UNIQUE(name, date),
	  FOREIGN KEY(date) REFERENCES datelist(date)
	);
	EOF
  fi
  ndate=`query "SELECT count(date) FROM datelist;"`
  if [ "$ndate" -eq 0 -a -s datelist.csv ]; then
    query<<-EOF
	.mode csv
	.import datelist.csv datelist
	EOF
  fi
}

add() {			# レコード登録機能
  who="$1"
  { echo "BEGIN;"
    while IFS="=" read date value; do
      case $value in
	[Yy][Ee][Ss])	val='○' ;;
	[Nn][Oo])		val='×' ;;
	*)		val='-' ;;
      esac
      echo "REPLACE INTO sched VALUES('$who', '$date', '$val');"
    done
    echo "COMMIT;"
  } | query
}

list() {		# gensql.sh を利用してHTMLの表を出力
  echo ''
  ./gensql.sh | sqlite3 -header -html $db
  echo ""
}

delete() {		# 削除機能
  who="$1"		# 第1引数 = 削除したい人
  query "DELETE FROM sched WHERE name='$who';"
}

while getopts a:id:l i; do
  case "$i" in
    a)	job=add
	who=$OPTARG ;;
    i)	job=create ;;
    l)	job=list ;;
    d)	job=delete who=$OPTARG ;;
  esac
done
shift $((OPTIND-1))

case $job in
  add|delete)		# オプション指定なしの場合
    $job $who
    ;;
  *)
    $job "$@" ;;
esac

edit-date.sh

#!/bin/sh
PATH=/usr/local/sqlite3/bin:$PATH
db=${DB:-imoni.sq3}
query() {
  sqlite3 -cmd "PRAGMA foreign_keys=ON" $db "$@"
}

adddate() {
  case $1 in
    */*/*)	exit 1 ;;
    */*)	query "REPLACE INTO datelist VALUES('$1');" ;;
    *)		exit 1 ;;
  esac
}
listdate() {
  query "SELECT * FROM datelist;"
}
deldate() {
  query "DELETE FROM datelist WHERE date='$1';"
}

while getopts a:d:l f; do
  case $f in
    a)	adddate $OPTARG ;;
    d)	deldate $OPTARG ;;
    l)	listdate ;;
    \?)	exit 1 ;;
  esac
done
shift $((OPTIND-1))

  case $1 in
    */*/*)	exit 1 ;;
    */*)	query "REPLACE INTO datelist VALUES('$1');" ;;
    *)		exit 1 ;;
  esac

gdate -d 09/0030 +%m/%d
09/30
gdate -d 09/300 +%m/%d
gdate: invalid date '09/300'
gdate -d 09/xyz +%m/%d
gdate: invalid date '09/xyz'

  newdate=`gdate +%m/%d -d "$1"` || {
  	echo Invalid date >&2; exit 1; }
  query "REPLACE INTO datelist VALUES('$newdate');" 

  query "REPLACE INTO datelist VALUES('${newdate#0}');" 

SELECT date FROM datelist;
10/3
10/4
10/10
10/11
10/17
10/18

gensql.sql

SELECT 'WITH sched2 AS (
 SELECT name,';
-- (1)↓日付候補の数だけのCASE文列挙
SELECT group_concat(
         printf('  CASE date WHEN ''%s'' THEN okng END "%s"', date, date),
	 X'2c0a')		-- 「カンマ(0x2c)+改行(0x0a)」で連結する
FROM   datelist;

SELECT ' FROM sched
) SELECT name,';
-- (2)↓maxの列挙
SELECT group_concat(printf('   max("%s") "%s"', date, date), X'2c0a')
FROM   datelist;

SELECT ' FROM sched2
 GROUP BY name

 UNION ALL

 SELECT ''○の数'',';
-- (3)↓count関数の列挙
SELECT group_concat(printf('    count("%s")', date), X'2c0a')
FROM   datelist;
SELECT ' FROM sched2 WHERE ''○'' IN (';
-- (4)↓INに対する値の列挙
SELECT group_concat(printf('"%s"', date)) FROM datelist;

SELECT ');';

sqlite3 imoni.sq3 < gensql.sql
WITH sched2 AS (
 SELECT name,
  CASE date WHEN '10/3' THEN okng END "10/3",
  CASE date WHEN '10/4' THEN okng END "10/4",
  CASE date WHEN '10/10' THEN okng END "10/10",
  CASE date WHEN '10/11' THEN okng END "10/11",
  CASE date WHEN '10/17' THEN okng END "10/17",
  CASE date WHEN '10/18' THEN okng END "10/18"
 FROM sched
) SELECT name,
   max("10/3") "10/3",
   max("10/4") "10/4",
   max("10/10") "10/10",
   max("10/11") "10/11",
   max("10/17") "10/17",
   max("10/18") "10/18"
 FROM sched2
 GROUP BY name

 UNION ALL

 SELECT '○の数',
    count("10/3"),
    count("10/4"),
    count("10/10"),
    count("10/11"),
    count("10/17"),
    count("10/18")
 FROM sched2 WHERE '○' IN (
"10/3","10/4","10/10","10/11","10/17","10/18"
);

sqlite3 imoni.sq3 < gensql.sql | sqlite3 imoni.sq3
山形醤二|○|○|○|○|○|×
庄内三十彦|○|×|×|×|○|○
○の数|2|1|1|1|2|1