Change match structure in db
Change match structure so it contains information on a single team in a match rather than all matches to make querying easier
Signed-off-by: Emily Markova <emily.markova@gmail.com>
Change-Id: Id558680d0c897f356f280657be2aa4ebbd1b14d9
diff --git a/scouting/db/db.go b/scouting/db/db.go
index 8f043de..940d96e 100644
--- a/scouting/db/db.go
+++ b/scouting/db/db.go
@@ -3,7 +3,6 @@
import (
"errors"
"fmt"
-
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/gorm/clause"
@@ -14,13 +13,13 @@
*gorm.DB
}
-type Match struct {
- // 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 TeamMatch struct {
+ MatchNumber int32 `gorm:"primaryKey"`
+ SetNumber int32 `gorm:"primaryKey"`
+ CompLevel string `gorm:"primaryKey"`
+ Alliance string `gorm:"primaryKey"` // "R" or "B"
+ AlliancePosition int32 `gorm:"primaryKey"` // 1, 2, or 3
+ TeamNumber int32
}
type Shift struct {
@@ -141,7 +140,7 @@
return nil, errors.New(fmt.Sprint("Failed to connect to postgres: ", err))
}
- err = database.AutoMigrate(&Match{}, &Shift{}, &Stats{}, &Stats2023{}, &Action{}, &NotesData{}, &Ranking{}, &DriverRankingData{})
+ err = database.AutoMigrate(&TeamMatch{}, &Shift{}, &Stats{}, &Stats2023{}, &Action{}, &NotesData{}, &Ranking{}, &DriverRankingData{})
if err != nil {
database.Delete()
return nil, errors.New(fmt.Sprint("Failed to create/migrate tables: ", err))
@@ -162,7 +161,7 @@
database.DB.Logger = database.DB.Logger.LogMode(logger.Info)
}
-func (database *Database) AddToMatch(m Match) error {
+func (database *Database) AddToMatch(m TeamMatch) error {
result := database.Clauses(clause.OnConflict{
UpdateAll: true,
}).Create(&m)
@@ -240,8 +239,8 @@
return result.Error
}
-func (database *Database) ReturnMatches() ([]Match, error) {
- var matches []Match
+func (database *Database) ReturnMatches() ([]TeamMatch, error) {
+ var matches []TeamMatch
result := database.Find(&matches)
return matches, result.Error
}
@@ -304,18 +303,18 @@
return rankins, result.Error
}
-func (database *Database) queryMatches(teamNumber_ int32) ([]Match, error) {
- var matches []Match
+func (database *Database) queryMatches(teamNumber_ int32) ([]TeamMatch, error) {
+ var matches []TeamMatch
result := database.
- Where("r1 = $1 OR r2 = $1 OR r3 = $1 OR b1 = $1 OR b2 = $1 OR b3 = $1", teamNumber_).
+ Where("team_number = $1", teamNumber_).
Find(&matches)
return matches, result.Error
}
-func (database *Database) QueryMatchesString(teamNumber_ string) ([]Match, error) {
- var matches []Match
+func (database *Database) QueryMatchesString(teamNumber_ string) ([]TeamMatch, error) {
+ var matches []TeamMatch
result := database.
- Where("r1 = $1 OR r2 = $1 OR r3 = $1 OR b1 = $1 OR b2 = $1 OR b3 = $1", teamNumber_).
+ Where("team_number = $1", teamNumber_).
Find(&matches)
return matches, result.Error
}
diff --git a/scouting/db/db_test.go b/scouting/db/db_test.go
index 9e85651..ec5e776 100644
--- a/scouting/db/db_test.go
+++ b/scouting/db/db_test.go
@@ -71,17 +71,37 @@
fixture := createDatabase(t)
defer fixture.TearDown()
- correct := []Match{
- Match{
- MatchNumber: 7,
- SetNumber: 1,
- CompLevel: "quals",
- R1: 9999, R2: 1000, R3: 777, B1: 0000, B2: 4321, B3: 1234,
+ correct := []TeamMatch{
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 9999,
+ },
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1000,
+ },
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 777,
+ },
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 0000,
+ },
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 4321,
+ },
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1234,
},
}
- err := fixture.db.AddToMatch(correct[0])
- check(t, err, "Failed to add match data")
+ for _, match := range correct {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match data")
+ }
got, err := fixture.db.ReturnMatches()
check(t, err, "Failed ReturnMatches()")
@@ -179,15 +199,28 @@
Comment: "final comment", CollectedBy: "beth",
},
}
+ matches := []TeamMatch{
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 1236},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1001},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 777},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 1000},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 4321},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1234},
+ }
- err := fixture.db.AddToMatch(Match{
- MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
- R1: 1236, R2: 1001, R3: 777, B1: 1000, B2: 4321, B3: 1234,
- })
- check(t, err, "Failed to add match")
+ for _, match := range matches {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match")
+ }
for i := 0; i < len(correct); i++ {
- err = fixture.db.AddToStats(correct[i])
+ err := fixture.db.AddToStats(correct[i])
check(t, err, "Failed to add stats to DB")
}
@@ -214,14 +247,28 @@
Comment: "this is a comment", CollectedBy: "josh",
}
- err := fixture.db.AddToMatch(Match{
- MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
- R1: 1236, R2: 1001, R3: 777, B1: 1000, B2: 4321, B3: 1234,
- })
- check(t, err, "Failed to add match")
+ matches := []TeamMatch{
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 1236},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1001},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 777},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 1000},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 4321},
+ TeamMatch{MatchNumber: 7, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1234},
+ }
+
+ for _, match := range matches {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match")
+ }
// Add stats. This should succeed.
- err = fixture.db.AddToStats(stats)
+ err := fixture.db.AddToStats(stats)
check(t, err, "Failed to add stats to DB")
// Try again. It should fail this time.
@@ -323,13 +370,28 @@
},
}
- err := fixture.db.AddToMatch(Match{
- MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
- R1: 1235, R2: 1234, R3: 1233, B1: 1232, B2: 1231, B3: 1239})
- check(t, err, "Failed to add match")
+ matches := []TeamMatch{
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 1235},
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1234},
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 1233},
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 1232},
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 1231},
+ TeamMatch{MatchNumber: 94, SetNumber: 2, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1239},
+ }
+
+ for _, match := range matches {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match")
+ }
for i := 0; i < len(testDatabase); i++ {
- err = fixture.db.AddToStats(testDatabase[i])
+ err := fixture.db.AddToStats(testDatabase[i])
check(t, err, fmt.Sprint("Failed to add stats ", i))
}
@@ -404,27 +466,17 @@
fixture := createDatabase(t)
defer fixture.TearDown()
- correct := []Match{
- Match{
- MatchNumber: 2, SetNumber: 1, CompLevel: "quals",
- R1: 251, R2: 169, R3: 286, B1: 253, B2: 538, B3: 149,
- },
- Match{
- MatchNumber: 3, SetNumber: 1, CompLevel: "quals",
- R1: 147, R2: 421, R3: 538, B1: 126, B2: 448, B3: 262,
- },
- Match{
- MatchNumber: 4, SetNumber: 1, CompLevel: "quals",
- R1: 251, R2: 169, R3: 286, B1: 653, B2: 538, B3: 149,
- },
- Match{
- MatchNumber: 5, SetNumber: 1, CompLevel: "quals",
- R1: 198, R2: 1421, R3: 538, B1: 26, B2: 448, B3: 262,
- },
- Match{
- MatchNumber: 6, SetNumber: 1, CompLevel: "quals",
- R1: 251, R2: 188, R3: 286, B1: 555, B2: 538, B3: 149,
- },
+ correct := []TeamMatch{
+ TeamMatch{
+ MatchNumber: 8, SetNumber: 1, CompLevel: "quals", Alliance: "R", AlliancePosition: 1, TeamNumber: 6835},
+ TeamMatch{
+ MatchNumber: 8, SetNumber: 1, CompLevel: "quals", Alliance: "R", AlliancePosition: 2, TeamNumber: 4834},
+ TeamMatch{
+ MatchNumber: 9, SetNumber: 1, CompLevel: "quals", Alliance: "B", AlliancePosition: 3, TeamNumber: 9824},
+ TeamMatch{
+ MatchNumber: 7, SetNumber: 2, CompLevel: "quals", Alliance: "B", AlliancePosition: 1, TeamNumber: 3732},
+ TeamMatch{
+ MatchNumber: 8, SetNumber: 1, CompLevel: "quals", Alliance: "B", AlliancePosition: 1, TeamNumber: 3732},
}
for i := 0; i < len(correct); i++ {
@@ -444,19 +496,13 @@
fixture := createDatabase(t)
defer fixture.TearDown()
- testDatabase := []Match{
- Match{
- MatchNumber: 1, SetNumber: 1, CompLevel: "quals",
- R1: 251, R2: 169, R3: 286, B1: 253, B2: 538, B3: 149,
- },
- Match{
- MatchNumber: 2, SetNumber: 1, CompLevel: "quals",
- R1: 198, R2: 135, R3: 777, B1: 999, B2: 434, B3: 698,
- },
- Match{
- MatchNumber: 1, SetNumber: 1, CompLevel: "quals",
- R1: 147, R2: 421, R3: 538, B1: 126, B2: 448, B3: 262,
- },
+ testDatabase := []TeamMatch{
+ TeamMatch{
+ MatchNumber: 9, SetNumber: 1, CompLevel: "quals", Alliance: "B", AlliancePosition: 3, TeamNumber: 4464},
+ TeamMatch{
+ MatchNumber: 8, SetNumber: 1, CompLevel: "quals", Alliance: "R", AlliancePosition: 2, TeamNumber: 2352},
+ TeamMatch{
+ MatchNumber: 9, SetNumber: 1, CompLevel: "quals", Alliance: "B", AlliancePosition: 3, TeamNumber: 6321},
}
for i := 0; i < len(testDatabase); i++ {
@@ -464,15 +510,11 @@
check(t, err, fmt.Sprint("Failed to add match", i))
}
- correct := []Match{
- Match{
- MatchNumber: 2, SetNumber: 1, CompLevel: "quals",
- R1: 198, R2: 135, R3: 777, B1: 999, B2: 434, B3: 698,
- },
- Match{
- MatchNumber: 1, SetNumber: 1, CompLevel: "quals",
- R1: 147, R2: 421, R3: 538, B1: 126, B2: 448, B3: 262,
- },
+ correct := []TeamMatch{
+ TeamMatch{
+ MatchNumber: 8, SetNumber: 1, CompLevel: "quals", Alliance: "R", AlliancePosition: 2, TeamNumber: 2352},
+ TeamMatch{
+ MatchNumber: 9, SetNumber: 1, CompLevel: "quals", Alliance: "B", AlliancePosition: 3, TeamNumber: 6321},
}
got, err := fixture.db.ReturnMatches()
@@ -605,13 +647,28 @@
},
}
- err := fixture.db.AddToMatch(Match{
- MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
- R1: 1235, R2: 1236, R3: 1237, B1: 1238, B2: 1239, B3: 1233})
- check(t, err, "Failed to add match")
+ matches := []TeamMatch{
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 1235},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1236},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 1237},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 1238},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 1239},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1233},
+ }
+
+ for _, match := range matches {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match")
+ }
for i := 0; i < len(correct); i++ {
- err = fixture.db.AddToStats(correct[i])
+ err := fixture.db.AddToStats(correct[i])
check(t, err, fmt.Sprint("Failed to add stats ", i))
}
@@ -653,13 +710,28 @@
},
}
- err := fixture.db.AddToMatch(Match{
- MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
- R1: 1235, R2: 1236, R3: 1237, B1: 1238, B2: 1239, B3: 1233})
- check(t, err, "Failed to add match")
+ matches := []TeamMatch{
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 1, TeamNumber: 1235},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 2, TeamNumber: 1236},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "R", AlliancePosition: 3, TeamNumber: 1237},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 1, TeamNumber: 1238},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 2, TeamNumber: 1239},
+ TeamMatch{MatchNumber: 94, SetNumber: 1, CompLevel: "quals",
+ Alliance: "B", AlliancePosition: 3, TeamNumber: 1233},
+ }
+
+ for _, match := range matches {
+ err := fixture.db.AddToMatch(match)
+ check(t, err, "Failed to add match")
+ }
for i := 0; i < len(correct); i++ {
- err = fixture.db.AddAction(correct[i])
+ err := fixture.db.AddAction(correct[i])
check(t, err, fmt.Sprint("Failed to add to actions ", i))
}