#!/bin/sh
db=score.sq3
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}
tbl=`query "SELECT name FROM sqlite_master WHERE name='students';"`
if [ -z "$tbl" ]; then		# $tbl が空文字列なら
  query<<EOF
CREATE TABLE students(sid PRIMARY KEY, name);
CREATE TABLE lectpts(
  sid, nlec, pts,
  UNIQUE(sid, nlec),
  FOREIGN KEY(sid) REFERENCES students(sid)
);
EOF
  if [ -s students.csv ]; then		# students.csv ファイルが空でなければ
    query<<EOF			# CSVモードに切り替えてインポート
.mode csv
.import students.csv students
EOF
  fi
fi
if [ x"$1" = x"-d" -a -n "$2" ]; then	# -d オプションなら削除
  query "DELETE FROM lectpts WHERE sid='$1' AND nlec=$2;"
elif [ x"$1" = x"-l" ]; then		# -l オプションなら集計出力
  query<<-EOF
	SELECT l.sid,s.name,sum(l.pts)
	FROM lectpts l JOIN students s ON l.sid=s.sid
	GROUP BY l.sid;
	EOF
elif [ -n "$3" ]; then			# 第3引数まで与えられたら登録
  sqlite3 $db "REPLACE INTO lectpts VALUES('$1', $2, $3);"
fi