scouting: Migrate database code to gorm
This patch migrates our `db.go` from raw SQL code to the gorm library.
https://gorm.io/index.html
It's not fantastic, but it's better than what we had. We might want to
investigate other ORMs later.
The functionality should be the same as before.
Signed-off-by: Philipp Schrader <philipp.schrader@gmail.com>
Change-Id: I986f74361fef1fac50b5499118b0af1d237f85f1
diff --git a/scouting/db/BUILD b/scouting/db/BUILD
index 7fbd2e2..154cab6 100644
--- a/scouting/db/BUILD
+++ b/scouting/db/BUILD
@@ -6,7 +6,12 @@
importpath = "github.com/frc971/971-Robot-Code/scouting/db",
target_compatible_with = ["@platforms//cpu:x86_64"],
visibility = ["//visibility:public"],
- deps = ["@com_github_jackc_pgx//stdlib"],
+ deps = [
+ "@io_gorm_driver_postgres//:postgres",
+ "@io_gorm_gorm//:gorm",
+ "@io_gorm_gorm//clause",
+ "@io_gorm_gorm//logger",
+ ],
)
go_test(
@@ -18,4 +23,5 @@
],
embed = [":db"],
target_compatible_with = ["@platforms//cpu:x86_64"],
+ deps = ["@com_github_davecgh_go_spew//spew"],
)
diff --git a/scouting/db/db.go b/scouting/db/db.go
index 3d514e3..37c8a6b 100644
--- a/scouting/db/db.go
+++ b/scouting/db/db.go
@@ -1,33 +1,50 @@
package db
import (
- "database/sql"
"errors"
"fmt"
- _ "github.com/jackc/pgx/stdlib"
+ "gorm.io/driver/postgres"
+ "gorm.io/gorm"
+ "gorm.io/gorm/clause"
+ "gorm.io/gorm/logger"
)
type Database struct {
- *sql.DB
+ *gorm.DB
}
type Match struct {
- MatchNumber, SetNumber int32
- CompLevel string
+ // TODO(phil): Rework this be be one team per row.
+ // Makes queries much simpler.
+ MatchNumber int32 `gorm:"primaryKey"`
+ SetNumber int32 `gorm:"primaryKey"`
+ CompLevel string `gorm:"primaryKey"`
R1, R2, R3, B1, B2, B3 int32
}
type Shift struct {
- MatchNumber int32
+ MatchNumber int32 `gorm:"primaryKey"`
R1scouter, R2scouter, R3scouter, B1scouter, B2scouter, B3scouter string
}
type Stats struct {
- TeamNumber, MatchNumber, SetNumber int32
- CompLevel string
- StartingQuadrant int32
- AutoBallPickedUp [5]bool
+ TeamNumber int32 `gorm:"primaryKey"`
+ MatchNumber int32 `gorm:"primaryKey"`
+ SetNumber int32 `gorm:"primaryKey"`
+ CompLevel string `gorm:"primaryKey"`
+ StartingQuadrant int32
+ // This field is for the balls picked up during auto. Use this field
+ // when using this library. Ignore the AutoBallPickedUpX fields below.
+ AutoBallPickedUp [5]bool `gorm:"-:all"`
+ // These fields are internal implementation details. Do not use these.
+ // TODO(phil): Figure out how to use the JSON gorm serializer instead
+ // of manually serializing/deserializing these.
+ AutoBallPickedUp1 bool
+ AutoBallPickedUp2 bool
+ AutoBallPickedUp3 bool
+ AutoBallPickedUp4 bool
+ AutoBallPickedUp5 bool
// TODO(phil): Re-order auto and teleop fields so auto comes first.
ShotsMissed, UpperGoalShots, LowerGoalShots int32
ShotsMissedAuto, UpperGoalAuto, LowerGoalAuto int32
@@ -50,12 +67,13 @@
}
type NotesData struct {
+ ID uint `gorm:"primaryKey"`
TeamNumber int32
- Notes []string
+ Notes string
}
type Ranking struct {
- TeamNumber int
+ TeamNumber int `gorm:"primaryKey"`
Losses, Wins, Ties int32
Rank, Dq int32
}
@@ -66,217 +84,48 @@
var err error
database := new(Database)
- psqlInfo := fmt.Sprintf("postgres://%s:%s@localhost:%d/postgres", user, password, port)
- database.DB, err = sql.Open("pgx", psqlInfo)
+ dsn := fmt.Sprintf("host=localhost user=%s password=%s dbname=postgres port=%d sslmode=disable", user, password, port)
+ database.DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
+ Logger: logger.Default.LogMode(logger.Silent),
+ })
if err != nil {
+ database.Delete()
return nil, errors.New(fmt.Sprint("Failed to connect to postgres: ", err))
}
- statement, err := database.Prepare("CREATE TABLE IF NOT EXISTS matches (" +
- "MatchNumber INTEGER, " +
- "SetNumber INTEGER, " +
- "CompLevel VARCHAR, " +
- "R1 INTEGER, " +
- "R2 INTEGER, " +
- "R3 INTEGER, " +
- "B1 INTEGER, " +
- "B2 INTEGER, " +
- "B3 INTEGER, " +
- "PRIMARY KEY (MatchNumber, SetNumber, CompLevel))")
+ err = database.AutoMigrate(&Match{}, &Shift{}, &Stats{}, &NotesData{}, &Ranking{})
if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to prepare matches table creation: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec()
- if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to create matches table: ", err))
- }
-
- statement, err = database.Prepare("CREATE TABLE IF NOT EXISTS shift_schedule (" +
- "id SERIAL PRIMARY KEY, " +
- "MatchNumber INTEGER, " +
- "R1Scouter VARCHAR, " +
- "R2Scouter VARCHAR, " +
- "R3Scouter VARCHAR, " +
- "B1Scouter VARCHAR, " +
- "B2Scouter VARCHAR, " +
- "B3scouter VARCHAR)")
- if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to prepare shift schedule table creation: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec()
- if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to create shift schedule table: ", err))
- }
-
- statement, err = database.Prepare("CREATE TABLE IF NOT EXISTS team_match_stats (" +
- "TeamNumber INTEGER, " +
- "MatchNumber INTEGER, " +
- "SetNumber INTEGER, " +
- "CompLevel VARCHAR, " +
- "StartingQuadrant INTEGER, " +
- "AutoBall1PickedUp BOOLEAN, " +
- "AutoBall2PickedUp BOOLEAN, " +
- "AutoBall3PickedUp BOOLEAN, " +
- "AutoBall4PickedUp BOOLEAN, " +
- "AutoBall5PickedUp BOOLEAN, " +
- "ShotsMissed INTEGER, " +
- "UpperGoalShots INTEGER, " +
- "LowerGoalShots INTEGER, " +
- "ShotsMissedAuto INTEGER, " +
- "UpperGoalAuto INTEGER, " +
- "LowerGoalAuto INTEGER, " +
- "PlayedDefense INTEGER, " +
- "DefenseReceivedScore INTEGER, " +
- "Climbing INTEGER, " +
- "Comment VARCHAR, " +
- "CollectedBy VARCHAR, " +
- "PRIMARY KEY (TeamNumber, MatchNumber, SetNumber, CompLevel))")
- if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to prepare stats table creation: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec()
- if err != nil {
- database.Close()
- return nil, errors.New(fmt.Sprint("Failed to create team_match_stats table: ", err))
- }
-
- statement, err = database.Prepare("CREATE TABLE IF NOT EXISTS team_notes (" +
- "id SERIAL PRIMARY KEY, " +
- "TeamNumber INTEGER, " +
- "Notes TEXT)")
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to prepare notes table creation: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec()
- if err != nil {
- 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))
+ database.Delete()
+ return nil, errors.New(fmt.Sprint("Failed to create/migrate tables: ", err))
}
return database, nil
}
func (database *Database) Delete() error {
- statement, err := database.Prepare("DROP TABLE IF EXISTS matches")
+ sql, err := database.DB.DB()
if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare dropping matches table: ", err))
+ return err
}
- _, err = statement.Exec()
- if err != nil {
- return errors.New(fmt.Sprint("Failed to drop matches table: ", err))
- }
-
- statement, err = database.Prepare("DROP TABLE IF EXISTS shift_schedule")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare dropping shifts table: ", err))
- }
- _, err = statement.Exec()
- if err != nil {
- return errors.New(fmt.Sprint("Failed to drop shifts table: ", err))
- }
-
- statement, err = database.Prepare("DROP TABLE IF EXISTS team_match_stats")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare dropping stats table: ", err))
- }
- _, err = statement.Exec()
- if err != nil {
- return errors.New(fmt.Sprint("Failed to drop stats table: ", err))
- }
-
- statement, err = database.Prepare("DROP TABLE IF EXISTS team_notes")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare dropping notes table: ", err))
- }
- _, err = statement.Exec()
- if err != nil {
- 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
+ return sql.Close()
}
-// This function will also populate the Stats table with six empty rows every time a match is added
-func (database *Database) AddToMatch(m Match) error {
- statement, err := database.Prepare("INSERT INTO matches(" +
- "MatchNumber, SetNumber, CompLevel, " +
- "R1, R2, R3, B1, B2, B3) " +
- "VALUES (" +
- "$1, $2, $3, " +
- "$4, $5, $6, $7, $8, $9) " +
- "ON CONFLICT (MatchNumber, SetNumber, CompLevel) DO UPDATE SET " +
- "R1 = EXCLUDED.R1, R2 = EXCLUDED.R2, R3 = EXCLUDED.R3, " +
- "B1 = EXCLUDED.B1, B2 = EXCLUDED.B2, B3 = EXCLUDED.B3")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare insertion into match database: ", err))
- }
- defer statement.Close()
+func (database *Database) SetDebugLogLevel() {
+ database.DB.Logger = database.DB.Logger.LogMode(logger.Info)
+}
- _, err = statement.Exec(m.MatchNumber, m.SetNumber, m.CompLevel,
- m.R1, m.R2, m.R3, m.B1, m.B2, m.B3)
- if err != nil {
- return errors.New(fmt.Sprint("Failed to insert into match database: ", err))
- }
- return nil
+func (database *Database) AddToMatch(m Match) error {
+ result := database.Clauses(clause.OnConflict{
+ UpdateAll: true,
+ }).Create(&m)
+ return result.Error
}
func (database *Database) AddToShift(sh Shift) error {
- statement, err := database.Prepare("INSERT INTO shift_schedule(" +
- "MatchNumber, " +
- "R1scouter, R2scouter, R3scouter, B1scouter, B2scouter, B3scouter) " +
- "VALUES (" +
- "$1, " +
- "$2, $3, $4, $5, $6, $7)")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare insertion into shift database: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec(sh.MatchNumber,
- sh.R1scouter, sh.R2scouter, sh.R3scouter, sh.B1scouter, sh.B2scouter, sh.B3scouter)
- if err != nil {
- return errors.New(fmt.Sprint("Failed to insert into shift database: ", err))
- }
- return nil
+ result := database.Clauses(clause.OnConflict{
+ UpdateAll: true,
+ }).Create(&sh)
+ return result.Error
}
func (database *Database) AddToStats(s Stats) error {
@@ -297,304 +146,117 @@
" in match ", s.MatchNumber, " in the schedule."))
}
- statement, err := database.Prepare("INSERT INTO team_match_stats(" +
- "TeamNumber, MatchNumber, SetNumber, CompLevel, " +
- "StartingQuadrant, " +
- "AutoBall1PickedUp, AutoBall2PickedUp, AutoBall3PickedUp, " +
- "AutoBall4PickedUp, AutoBall5PickedUp, " +
- "ShotsMissed, UpperGoalShots, LowerGoalShots, " +
- "ShotsMissedAuto, UpperGoalAuto, LowerGoalAuto, " +
- "PlayedDefense, DefenseReceivedScore, Climbing, " +
- "Comment, CollectedBy) " +
- "VALUES (" +
- "$1, $2, $3, $4, " +
- "$5, " +
- "$6, $7, $8, " +
- "$9, $10, " +
- "$11, $12, $13, " +
- "$14, $15, $16, " +
- "$17, $18, $19, " +
- "$20, $21)")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare stats update statement: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec(
- s.TeamNumber, s.MatchNumber, s.SetNumber, s.CompLevel,
- s.StartingQuadrant,
- s.AutoBallPickedUp[0], s.AutoBallPickedUp[1], s.AutoBallPickedUp[2],
- s.AutoBallPickedUp[3], s.AutoBallPickedUp[4],
- s.ShotsMissed, s.UpperGoalShots, s.LowerGoalShots,
- s.ShotsMissedAuto, s.UpperGoalAuto, s.LowerGoalAuto,
- s.PlayedDefense, s.DefenseReceivedScore, s.Climbing,
- s.Comment, s.CollectedBy)
- if err != nil {
- return errors.New(fmt.Sprint("Failed to update stats database: ", err))
- }
-
- return nil
+ // Unpack the auto balls array.
+ s.AutoBallPickedUp1 = s.AutoBallPickedUp[0]
+ s.AutoBallPickedUp2 = s.AutoBallPickedUp[1]
+ s.AutoBallPickedUp3 = s.AutoBallPickedUp[2]
+ s.AutoBallPickedUp4 = s.AutoBallPickedUp[3]
+ s.AutoBallPickedUp5 = s.AutoBallPickedUp[4]
+ result := database.Create(&s)
+ return result.Error
}
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
+ result := database.Clauses(clause.OnConflict{
+ UpdateAll: true,
+ }).Create(&r)
+ return result.Error
}
func (database *Database) ReturnMatches() ([]Match, error) {
- rows, err := database.Query("SELECT * FROM matches")
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to select from matches: ", err))
- }
- defer rows.Close()
-
- matches := make([]Match, 0)
- for rows.Next() {
- var match Match
- err := rows.Scan(&match.MatchNumber, &match.SetNumber, &match.CompLevel,
- &match.R1, &match.R2, &match.R3, &match.B1, &match.B2, &match.B3)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from matches: ", err))
- }
- matches = append(matches, match)
- }
- return matches, nil
+ var matches []Match
+ result := database.Find(&matches)
+ return matches, result.Error
}
func (database *Database) ReturnAllShifts() ([]Shift, error) {
- rows, err := database.Query("SELECT * FROM shift_schedule")
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to select from shift: ", err))
- }
- defer rows.Close()
+ var shifts []Shift
+ result := database.Find(&shifts)
+ return shifts, result.Error
+}
- shifts := make([]Shift, 0)
- for rows.Next() {
- var shift Shift
- var id int
- err := rows.Scan(&id, &shift.MatchNumber,
- &shift.R1scouter, &shift.R2scouter, &shift.R3scouter, &shift.B1scouter, &shift.B2scouter, &shift.B3scouter)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from shift: ", err))
- }
- shifts = append(shifts, shift)
+// Packs the stats. This really just consists of taking the individual auto
+// ball booleans and turning them into an array. The individual booleans are
+// cleared so that they don't affect struct comparisons.
+func packStats(stats *Stats) {
+ stats.AutoBallPickedUp = [5]bool{
+ stats.AutoBallPickedUp1,
+ stats.AutoBallPickedUp2,
+ stats.AutoBallPickedUp3,
+ stats.AutoBallPickedUp4,
+ stats.AutoBallPickedUp5,
}
- return shifts, nil
+ stats.AutoBallPickedUp1 = false
+ stats.AutoBallPickedUp2 = false
+ stats.AutoBallPickedUp3 = false
+ stats.AutoBallPickedUp4 = false
+ stats.AutoBallPickedUp5 = false
}
func (database *Database) ReturnStats() ([]Stats, error) {
- rows, err := database.Query("SELECT * FROM team_match_stats")
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to SELECT * FROM team_match_stats: ", err))
+ var stats []Stats
+ result := database.Find(&stats)
+ // Pack the auto balls array.
+ for i := range stats {
+ packStats(&stats[i])
}
- defer rows.Close()
-
- teams := make([]Stats, 0)
- for rows.Next() {
- var team Stats
- err = rows.Scan(
- &team.TeamNumber, &team.MatchNumber, &team.SetNumber, &team.CompLevel,
- &team.StartingQuadrant,
- &team.AutoBallPickedUp[0], &team.AutoBallPickedUp[1], &team.AutoBallPickedUp[2],
- &team.AutoBallPickedUp[3], &team.AutoBallPickedUp[4],
- &team.ShotsMissed, &team.UpperGoalShots, &team.LowerGoalShots,
- &team.ShotsMissedAuto, &team.UpperGoalAuto, &team.LowerGoalAuto,
- &team.PlayedDefense, &team.DefenseReceivedScore, &team.Climbing,
- &team.Comment, &team.CollectedBy)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from stats: ", err))
- }
- teams = append(teams, team)
- }
- return teams, nil
+ return stats, result.Error
}
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
+ var rankins []Ranking
+ result := database.Find(&rankins)
+ return rankins, result.Error
}
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",
- teamNumber_, teamNumber_, teamNumber_, teamNumber_, teamNumber_, teamNumber_)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to select from matches for team: ", err))
- }
- defer rows.Close()
-
var matches []Match
- for rows.Next() {
- var match Match
- err = rows.Scan(&match.MatchNumber, &match.SetNumber, &match.CompLevel,
- &match.R1, &match.R2, &match.R3, &match.B1, &match.B2, &match.B3)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from matches: ", err))
- }
- matches = append(matches, match)
- }
- return matches, nil
+ result := database.
+ Where("r1 = $1 OR r2 = $1 OR r3 = $1 OR b1 = $1 OR b2 = $1 OR b3 = $1", teamNumber_).
+ Find(&matches)
+ return matches, result.Error
}
func (database *Database) QueryAllShifts(matchNumber_ int) ([]Shift, error) {
- rows, err := database.Query("SELECT * FROM shift_schedule WHERE MatchNumber = $1", matchNumber_)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to select from shift for team: ", err))
- }
- defer rows.Close()
-
var shifts []Shift
- for rows.Next() {
- var shift Shift
- var id int
- err = rows.Scan(&id, &shift.MatchNumber,
- &shift.R1scouter, &shift.R2scouter, &shift.R3scouter, &shift.B1scouter, &shift.B2scouter, &shift.B3scouter)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from matches: ", err))
- }
- shifts = append(shifts, shift)
- }
- return shifts, nil
+ result := database.Where("match_number = ?", matchNumber_).Find(&shifts)
+ return shifts, result.Error
}
func (database *Database) QueryStats(teamNumber_ int) ([]Stats, error) {
- rows, err := database.Query("SELECT * FROM team_match_stats WHERE TeamNumber = $1", teamNumber_)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to select from stats: ", err))
+ var stats []Stats
+ result := database.Where("team_number = ?", teamNumber_).Find(&stats)
+ // Pack the auto balls array.
+ for i := range stats {
+ packStats(&stats[i])
}
- defer rows.Close()
-
- var teams []Stats
- for rows.Next() {
- var team Stats
- err = rows.Scan(
- &team.TeamNumber, &team.MatchNumber, &team.SetNumber, &team.CompLevel,
- &team.StartingQuadrant,
- &team.AutoBallPickedUp[0], &team.AutoBallPickedUp[1], &team.AutoBallPickedUp[2],
- &team.AutoBallPickedUp[3], &team.AutoBallPickedUp[4],
- &team.ShotsMissed, &team.UpperGoalShots, &team.LowerGoalShots,
- &team.ShotsMissedAuto, &team.UpperGoalAuto, &team.LowerGoalAuto,
- &team.PlayedDefense, &team.DefenseReceivedScore, &team.Climbing,
- &team.Comment, &team.CollectedBy)
- if err != nil {
- return nil, errors.New(fmt.Sprint("Failed to scan from stats: ", err))
- }
- teams = append(teams, team)
- }
- return teams, nil
+ return stats, result.Error
}
-func (database *Database) QueryNotes(TeamNumber int32) (NotesData, error) {
- rows, err := database.Query("SELECT * FROM team_notes WHERE TeamNumber = $1", TeamNumber)
- if err != nil {
- return NotesData{}, errors.New(fmt.Sprint("Failed to select from notes: ", err))
+func (database *Database) QueryNotes(TeamNumber int32) ([]string, error) {
+ var rawNotes []NotesData
+ result := database.Where("team_number = ?", TeamNumber).Find(&rawNotes)
+ if result.Error != nil {
+ return nil, result.Error
}
- defer rows.Close()
- var notes []string
- for rows.Next() {
- var id int32
- var data string
- err = rows.Scan(&id, &TeamNumber, &data)
- if err != nil {
- return NotesData{}, errors.New(fmt.Sprint("Failed to scan from notes: ", err))
- }
- notes = append(notes, data)
+ notes := make([]string, len(rawNotes))
+ for i := range rawNotes {
+ notes[i] = rawNotes[i].Notes
}
- return NotesData{TeamNumber, notes}, nil
+ return 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
+ var rankins []Ranking
+ result := database.Where("team_number = ?", TeamNumber).Find(&rankins)
+ return rankins, result.Error
}
-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")
- }
- statement, err := database.Prepare("INSERT INTO " +
- "team_notes(TeamNumber, Notes)" +
- "VALUES ($1, $2)")
- if err != nil {
- return errors.New(fmt.Sprint("Failed to prepare insertion into notes table: ", err))
- }
- defer statement.Close()
-
- _, err = statement.Exec(data.TeamNumber, data.Notes[0])
- if err != nil {
- return errors.New(fmt.Sprint("Failed to insert into Notes database: ", err))
- }
- return nil
+func (database *Database) AddNotes(teamNumber int, data string) error {
+ result := database.Create(&NotesData{
+ TeamNumber: int32(teamNumber),
+ Notes: data,
+ })
+ return result.Error
}
diff --git a/scouting/db/db_test.go b/scouting/db/db_test.go
index 438e52e..77a8bd6 100644
--- a/scouting/db/db_test.go
+++ b/scouting/db/db_test.go
@@ -9,6 +9,8 @@
"strings"
"testing"
"time"
+
+ "github.com/davecgh/go-spew/spew"
)
// Shortcut for error checking. If the specified error is non-nil, print the
@@ -26,7 +28,6 @@
func (fixture dbFixture) TearDown() {
fixture.db.Delete()
- fixture.db.Close()
log.Println("Shutting down testdb")
fixture.server.Process.Signal(os.Interrupt)
fixture.server.Process.Wait()
@@ -55,9 +56,17 @@
}
log.Println("Connected to postgres.")
+ fixture.db.SetDebugLogLevel()
+
return fixture
}
+func checkDeepEqual(t *testing.T, expected interface{}, actual interface{}) {
+ if !reflect.DeepEqual(expected, actual) {
+ t.Fatalf(spew.Sprintf("Got %#v,\nbut expected %#v.", actual, expected))
+ }
+}
+
func TestAddToMatchDB(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
@@ -77,9 +86,7 @@
got, err := fixture.db.ReturnMatches()
check(t, err, "Failed ReturnMatches()")
- if !reflect.DeepEqual(correct, got) {
- t.Fatalf("Got %#v,\nbut expected %#v.", got, correct)
- }
+ checkDeepEqual(t, correct, got)
}
func TestAddOrUpdateRankingsDB(t *testing.T) {
@@ -198,6 +205,7 @@
stats := Stats{
TeamNumber: 1236, MatchNumber: 7,
+ SetNumber: 1, CompLevel: "qual",
StartingQuadrant: 2,
AutoBallPickedUp: [5]bool{false, false, false, true, false},
ShotsMissed: 9, UpperGoalShots: 5, LowerGoalShots: 4,
@@ -698,25 +706,20 @@
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)
- }
+ checkDeepEqual(t, correct, got)
}
func TestNotes(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
- expected := NotesData{
- TeamNumber: 1234,
- Notes: []string{"Note 1", "Note 3"},
- }
+ expected := []string{"Note 1", "Note 3"}
- err := fixture.db.AddNotes(NotesData{1234, []string{"Note 1"}})
+ err := fixture.db.AddNotes(1234, "Note 1")
check(t, err, "Failed to add Note")
- err = fixture.db.AddNotes(NotesData{1235, []string{"Note 2"}})
+ err = fixture.db.AddNotes(1235, "Note 2")
check(t, err, "Failed to add Note")
- err = fixture.db.AddNotes(NotesData{1234, []string{"Note 3"}})
+ err = fixture.db.AddNotes(1234, "Note 3")
check(t, err, "Failed to add Note")
actual, err := fixture.db.QueryNotes(1234)
diff --git a/scouting/webserver/main.go b/scouting/webserver/main.go
index 5d4ab01..d2fbdfe 100644
--- a/scouting/webserver/main.go
+++ b/scouting/webserver/main.go
@@ -117,7 +117,7 @@
if err != nil {
log.Fatal("Failed to connect to database: ", err)
}
- defer database.Close()
+ defer database.Delete()
scrapeMatchList := func(year int32, eventCode string) ([]scraping.Match, error) {
if *blueAllianceConfigPtr == "" {
diff --git a/scouting/webserver/requests/requests.go b/scouting/webserver/requests/requests.go
index 7464875..0ffeaee 100644
--- a/scouting/webserver/requests/requests.go
+++ b/scouting/webserver/requests/requests.go
@@ -66,8 +66,8 @@
QueryMatches(int32) ([]db.Match, error)
QueryAllShifts(int) ([]db.Shift, error)
QueryStats(int) ([]db.Stats, error)
- QueryNotes(int32) (db.NotesData, error)
- AddNotes(db.NotesData) error
+ QueryNotes(int32) ([]string, error)
+ AddNotes(int, string) error
}
type ScrapeMatchList func(int32, string) ([]scraping.Match, error)
@@ -470,10 +470,7 @@
return
}
- err = handler.db.AddNotes(db.NotesData{
- TeamNumber: request.Team(),
- Notes: []string{string(request.Notes())},
- })
+ err = handler.db.AddNotes(int(request.Team()), string(request.Notes()))
if err != nil {
respondWithError(w, http.StatusInternalServerError, fmt.Sprintf("Failed to insert notes: %v", err))
return
@@ -501,14 +498,14 @@
return
}
- notesData, err := handler.db.QueryNotes(request.Team())
+ notes, err := handler.db.QueryNotes(request.Team())
if err != nil {
respondWithError(w, http.StatusInternalServerError, fmt.Sprintf("Failed to query notes: %v", err))
return
}
var response RequestNotesForTeamResponseT
- for _, data := range notesData.Notes {
+ for _, data := range notes {
response.Notes = append(response.Notes, &request_notes_for_team_response.NoteT{data})
}
diff --git a/scouting/webserver/requests/requests_test.go b/scouting/webserver/requests/requests_test.go
index 44fd5db..24c99f6 100644
--- a/scouting/webserver/requests/requests_test.go
+++ b/scouting/webserver/requests/requests_test.go
@@ -324,7 +324,7 @@
}
expected := []db.NotesData{
- {TeamNumber: 971, Notes: []string{"Notes"}},
+ {TeamNumber: 971, Notes: "Notes"},
}
if !reflect.DeepEqual(database.notes, expected) {
@@ -336,7 +336,7 @@
database := MockDatabase{
notes: []db.NotesData{{
TeamNumber: 971,
- Notes: []string{"Notes"},
+ Notes: "Notes",
}},
}
scoutingServer := server.NewScoutingServer()
@@ -584,18 +584,21 @@
return []db.Stats{}, nil
}
-func (database *MockDatabase) QueryNotes(requestedTeam int32) (db.NotesData, error) {
+func (database *MockDatabase) QueryNotes(requestedTeam int32) ([]string, error) {
var results []string
for _, data := range database.notes {
if data.TeamNumber == requestedTeam {
- results = append(results, data.Notes[0])
+ results = append(results, data.Notes)
}
}
- return db.NotesData{TeamNumber: requestedTeam, Notes: results}, nil
+ return results, nil
}
-func (database *MockDatabase) AddNotes(data db.NotesData) error {
- database.notes = append(database.notes, data)
+func (database *MockDatabase) AddNotes(teamNumber int, notes string) error {
+ database.notes = append(database.notes, db.NotesData{
+ TeamNumber: int32(teamNumber),
+ Notes: notes,
+ })
return nil
}