Create database functions for rankings
Ranking info can be added, queried, and returned. Tests also written.
Signed-off-by: Yash Chainani <yashchainani28@gmail.com>
Change-Id: I03268c14d5835dce36f6a7fbb31ad23f822dcdbc
diff --git a/scouting/db/db.go b/scouting/db/db.go
index 85395e1..d3faa58 100644
--- a/scouting/db/db.go
+++ b/scouting/db/db.go
@@ -50,6 +50,12 @@
Notes []string
}
+type Ranking struct {
+ TeamNumber int
+ Losses, Wins, Ties int32
+ Rank, Dq int32
+}
+
// Opens a database at the specified port on localhost. We currently don't
// support connecting to databases on other hosts.
func NewDatabase(user string, password string, port int) (*Database, error) {
@@ -138,6 +144,24 @@
return nil, errors.New(fmt.Sprint("Failed to create notes table: ", err))
}
+ statement, err = database.Prepare("CREATE TABLE IF NOT EXISTS rankings (" +
+ "id SERIAL PRIMARY KEY, " +
+ "Losses INTEGER, " +
+ "Wins INTEGER, " +
+ "Ties INTEGER, " +
+ "Rank INTEGER, " +
+ "Dq INTEGER, " +
+ "TeamNumber INTEGER)")
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to prepare rankings table creation: ", err))
+ }
+ defer statement.Close()
+
+ _, err = statement.Exec()
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to create rankings table: ", err))
+ }
+
return database, nil
}
@@ -169,6 +193,16 @@
return errors.New(fmt.Sprint("Failed to drop notes table: ", err))
}
return nil
+
+ statement, err = database.Prepare("DROP TABLE IF EXISTS rankings")
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to prepare dropping rankings table: ", err))
+ }
+ _, err = statement.Exec()
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to drop rankings table: ", err))
+ }
+ return nil
}
// This function will also populate the Stats table with six empty rows every time a match is added
@@ -278,6 +312,48 @@
return nil
}
+func (database *Database) AddOrUpdateRankings(r Ranking) error {
+ statement, err := database.Prepare("UPDATE rankings SET " +
+ "Losses = $1, Wins = $2, Ties = $3, " +
+ "Rank = $4, Dq = $5, TeamNumber = $6 " +
+ "WHERE TeamNumber = $6")
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to prepare rankings database update: ", err))
+ }
+ defer statement.Close()
+
+ result, err := statement.Exec(r.Losses, r.Wins, r.Ties,
+ r.Rank, r.Dq, r.TeamNumber)
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to update rankings database: ", err))
+ }
+
+ numRowsAffected, err := result.RowsAffected()
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to query rows affected: ", err))
+ }
+ if numRowsAffected == 0 {
+ statement, err := database.Prepare("INSERT INTO rankings(" +
+ "Losses, Wins, Ties, " +
+ "Rank, Dq, TeamNumber) " +
+ "VALUES (" +
+ "$1, $2, $3, " +
+ "$4, $5, $6)")
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to prepare insertion into rankings database: ", err))
+ }
+ defer statement.Close()
+
+ _, err = statement.Exec(r.Losses, r.Wins, r.Ties,
+ r.Rank, r.Dq, r.TeamNumber)
+ if err != nil {
+ return errors.New(fmt.Sprint("Failed to insert into rankings database: ", err))
+ }
+ }
+
+ return nil
+}
+
func (database *Database) ReturnMatches() ([]Match, error) {
rows, err := database.Query("SELECT * FROM matches")
if err != nil {
@@ -328,6 +404,28 @@
return teams, nil
}
+func (database *Database) ReturnRankings() ([]Ranking, error) {
+ rows, err := database.Query("SELECT * FROM rankings")
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to SELECT * FROM rankings: ", err))
+ }
+ defer rows.Close()
+
+ all_rankings := make([]Ranking, 0)
+ for rows.Next() {
+ var ranking Ranking
+ var id int
+ err = rows.Scan(&id,
+ &ranking.Losses, &ranking.Wins, &ranking.Ties,
+ &ranking.Rank, &ranking.Dq, &ranking.TeamNumber)
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to scan from rankings: ", err))
+ }
+ all_rankings = append(all_rankings, ranking)
+ }
+ return all_rankings, nil
+}
+
func (database *Database) QueryMatches(teamNumber_ int32) ([]Match, error) {
rows, err := database.Query("SELECT * FROM matches WHERE "+
"R1 = $1 OR R2 = $2 OR R3 = $3 OR B1 = $4 OR B2 = $5 OR B3 = $6",
@@ -400,6 +498,28 @@
return NotesData{TeamNumber, notes}, nil
}
+func (database *Database) QueryRankings(TeamNumber int) ([]Ranking, error) {
+ rows, err := database.Query("SELECT * FROM rankings WHERE TeamNumber = $1", TeamNumber)
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to select from rankings: ", err))
+ }
+ defer rows.Close()
+
+ all_rankings := make([]Ranking, 0)
+ for rows.Next() {
+ var ranking Ranking
+ var id int
+ err = rows.Scan(&id,
+ &ranking.Losses, &ranking.Wins, &ranking.Ties,
+ &ranking.Rank, &ranking.Dq, &ranking.TeamNumber)
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to scan from rankings: ", err))
+ }
+ all_rankings = append(all_rankings, ranking)
+ }
+ return all_rankings, nil
+}
+
func (database *Database) AddNotes(data NotesData) error {
if len(data.Notes) > 1 {
return errors.New("Can only insert one row of notes at a time")
diff --git a/scouting/db/db_test.go b/scouting/db/db_test.go
index 02e8d50..5725dcb 100644
--- a/scouting/db/db_test.go
+++ b/scouting/db/db_test.go
@@ -82,6 +82,36 @@
}
}
+func TestAddOrUpdateRankingsDB(t *testing.T) {
+ fixture := createDatabase(t)
+ defer fixture.TearDown()
+
+ correct := []Ranking{
+ Ranking{
+ TeamNumber: 123,
+ Losses: 1, Wins: 7, Ties: 0,
+ Rank: 2, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 125,
+ Losses: 2, Wins: 4, Ties: 0,
+ Rank: 2, Dq: 0,
+ },
+ }
+
+ for i := 0; i < len(correct); i++ {
+ err := fixture.db.AddOrUpdateRankings(correct[i])
+ check(t, err, "Failed to add ranking data")
+ }
+
+ got, err := fixture.db.ReturnRankings()
+ check(t, err, "Failed ReturnRankings()")
+
+ if !reflect.DeepEqual(correct, got) {
+ t.Fatalf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+}
+
func TestAddToStatsDB(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
@@ -283,6 +313,54 @@
}
}
+func TestQueryRankingsDB(t *testing.T) {
+ fixture := createDatabase(t)
+ defer fixture.TearDown()
+
+ testDatabase := []Ranking{
+ Ranking{
+ TeamNumber: 123,
+ Losses: 1, Wins: 7, Ties: 2,
+ Rank: 2, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 124,
+ Losses: 3, Wins: 4, Ties: 0,
+ Rank: 4, Dq: 2,
+ },
+ Ranking{
+ TeamNumber: 125,
+ Losses: 5, Wins: 2, Ties: 0,
+ Rank: 17, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 126,
+ Losses: 0, Wins: 7, Ties: 0,
+ Rank: 5, Dq: 0,
+ },
+ }
+
+ for i := 0; i < len(testDatabase); i++ {
+ err := fixture.db.AddOrUpdateRankings(testDatabase[i])
+ check(t, err, fmt.Sprint("Failed to add rankings ", i))
+ }
+
+ correct := []Ranking{
+ Ranking{
+ TeamNumber: 126,
+ Losses: 0, Wins: 7, Ties: 0,
+ Rank: 5, Dq: 0,
+ },
+ }
+
+ got, err := fixture.db.QueryRankings(126)
+ check(t, err, "Failed QueryRankings()")
+
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+}
+
func TestReturnMatchDB(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
@@ -328,6 +406,46 @@
}
}
+func TestReturnRankingsDB(t *testing.T) {
+ fixture := createDatabase(t)
+ defer fixture.TearDown()
+
+ correct := []Ranking{
+ Ranking{
+ TeamNumber: 123,
+ Losses: 1, Wins: 7, Ties: 2,
+ Rank: 2, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 124,
+ Losses: 3, Wins: 4, Ties: 0,
+ Rank: 4, Dq: 2,
+ },
+ Ranking{
+ TeamNumber: 125,
+ Losses: 5, Wins: 2, Ties: 0,
+ Rank: 17, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 126,
+ Losses: 0, Wins: 7, Ties: 0,
+ Rank: 5, Dq: 0,
+ },
+ }
+
+ for i := 0; i < len(correct); i++ {
+ err := fixture.db.AddOrUpdateRankings(correct[i])
+ check(t, err, fmt.Sprint("Failed to add rankings", i))
+ }
+
+ got, err := fixture.db.ReturnRankings()
+ check(t, err, "Failed ReturnRankings()")
+
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+}
+
func TestReturnStatsDB(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
@@ -400,6 +518,59 @@
}
}
+func TestRankingsDbUpdate(t *testing.T) {
+ fixture := createDatabase(t)
+ defer fixture.TearDown()
+
+ testDatabase := []Ranking{
+ Ranking{
+ TeamNumber: 123,
+ Losses: 1, Wins: 7, Ties: 2,
+ Rank: 2, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 124,
+ Losses: 3, Wins: 4, Ties: 0,
+ Rank: 4, Dq: 2,
+ },
+ Ranking{
+ TeamNumber: 125,
+ Losses: 5, Wins: 2, Ties: 0,
+ Rank: 17, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 126,
+ Losses: 0, Wins: 7, Ties: 0,
+ Rank: 5, Dq: 0,
+ },
+ Ranking{
+ TeamNumber: 125,
+ Losses: 2, Wins: 4, Ties: 1,
+ Rank: 5, Dq: 0,
+ },
+ }
+
+ for i := 0; i < len(testDatabase); i++ {
+ err := fixture.db.AddOrUpdateRankings(testDatabase[i])
+ check(t, err, fmt.Sprint("Failed to add rankings ", i))
+ }
+
+ correct := []Ranking{
+ Ranking{
+ TeamNumber: 125,
+ Losses: 2, Wins: 4, Ties: 1,
+ Rank: 5, Dq: 0,
+ },
+ }
+
+ got, err := fixture.db.QueryRankings(125)
+ check(t, err, "Failed QueryRankings()")
+
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+}
+
func TestNotes(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()