Rework primary keys in the data scouting table
The ID is not actually used anywhere. The new scheme of using team
number, match number, round, and comp level as a unique key will let
us update data scouting data.
More importantly, this adds the missing round and comp level
information into the stats table. We need that to be able to collect
eliminations data.
Signed-off-by: Philipp Schrader <philipp.schrader@gmail.com>
Change-Id: I5614ada40a044a68bde168526ae65df47fd21db1
diff --git a/scouting/db/db.go b/scouting/db/db.go
index 32646f1..e21ca43 100644
--- a/scouting/db/db.go
+++ b/scouting/db/db.go
@@ -19,9 +19,10 @@
}
type Stats struct {
- TeamNumber, MatchNumber int32
- StartingQuadrant int32
- AutoBallPickedUp [5]bool
+ TeamNumber, MatchNumber, Round int32
+ CompLevel string
+ StartingQuadrant int32
+ AutoBallPickedUp [5]bool
// TODO(phil): Re-order auto and teleop fields so auto comes first.
ShotsMissed, UpperGoalShots, LowerGoalShots int32
ShotsMissedAuto, UpperGoalAuto, LowerGoalAuto int32
@@ -90,9 +91,10 @@
}
statement, err = database.Prepare("CREATE TABLE IF NOT EXISTS team_match_stats (" +
- "id SERIAL PRIMARY KEY, " +
"TeamNumber INTEGER, " +
"MatchNumber INTEGER, " +
+ "Round INTEGER, " +
+ "CompLevel VARCHAR, " +
"StartingQuadrant INTEGER, " +
"AutoBall1PickedUp BOOLEAN, " +
"AutoBall2PickedUp BOOLEAN, " +
@@ -109,7 +111,8 @@
"DefenseReceivedScore INTEGER, " +
"Climbing INTEGER, " +
"Comment VARCHAR, " +
- "CollectedBy VARCHAR)")
+ "CollectedBy VARCHAR, " +
+ "PRIMARY KEY (TeamNumber, MatchNumber, Round, CompLevel))")
if err != nil {
database.Close()
return nil, errors.New(fmt.Sprint("Failed to prepare stats table creation: ", err))
@@ -240,7 +243,7 @@
}
statement, err := database.Prepare("INSERT INTO team_match_stats(" +
- "TeamNumber, MatchNumber, " +
+ "TeamNumber, MatchNumber, Round, CompLevel, " +
"StartingQuadrant, " +
"AutoBall1PickedUp, AutoBall2PickedUp, AutoBall3PickedUp, " +
"AutoBall4PickedUp, AutoBall5PickedUp, " +
@@ -249,21 +252,21 @@
"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)")
+ "$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.TeamNumber, s.MatchNumber, s.Round, s.CompLevel,
s.StartingQuadrant,
s.AutoBallPickedUp[0], s.AutoBallPickedUp[1], s.AutoBallPickedUp[2],
s.AutoBallPickedUp[3], s.AutoBallPickedUp[4],
@@ -350,9 +353,8 @@
teams := make([]Stats, 0)
for rows.Next() {
var team Stats
- var id int
- err = rows.Scan(&id,
- &team.TeamNumber, &team.MatchNumber,
+ err = rows.Scan(
+ &team.TeamNumber, &team.MatchNumber, &team.Round, &team.CompLevel,
&team.StartingQuadrant,
&team.AutoBallPickedUp[0], &team.AutoBallPickedUp[1], &team.AutoBallPickedUp[2],
&team.AutoBallPickedUp[3], &team.AutoBallPickedUp[4],
@@ -422,9 +424,8 @@
var teams []Stats
for rows.Next() {
var team Stats
- var id int
- err = rows.Scan(&id,
- &team.TeamNumber, &team.MatchNumber,
+ err = rows.Scan(
+ &team.TeamNumber, &team.MatchNumber, &team.Round, &team.CompLevel,
&team.StartingQuadrant,
&team.AutoBallPickedUp[0], &team.AutoBallPickedUp[1], &team.AutoBallPickedUp[2],
&team.AutoBallPickedUp[3], &team.AutoBallPickedUp[4],
diff --git a/scouting/db/db_test.go b/scouting/db/db_test.go
index 290f451..391f336 100644
--- a/scouting/db/db_test.go
+++ b/scouting/db/db_test.go
@@ -6,6 +6,7 @@
"os"
"os/exec"
"reflect"
+ "strings"
"testing"
"time"
)
@@ -191,6 +192,40 @@
}
}
+func TestAddDuplicateStats(t *testing.T) {
+ fixture := createDatabase(t)
+ defer fixture.TearDown()
+
+ stats := Stats{
+ TeamNumber: 1236, MatchNumber: 7,
+ StartingQuadrant: 2,
+ AutoBallPickedUp: [5]bool{false, false, false, true, false},
+ ShotsMissed: 9, UpperGoalShots: 5, LowerGoalShots: 4,
+ ShotsMissedAuto: 3, UpperGoalAuto: 2, LowerGoalAuto: 1,
+ PlayedDefense: 2, DefenseReceivedScore: 0, Climbing: 3,
+ Comment: "this is a comment", CollectedBy: "josh",
+ }
+
+ err := fixture.db.AddToMatch(Match{
+ MatchNumber: 7, Round: 1, CompLevel: "quals",
+ R1: 1236, R2: 1001, R3: 777, B1: 1000, B2: 4321, B3: 1234,
+ })
+ check(t, err, "Failed to add match")
+
+ // Add stats. This should succeed.
+ err = fixture.db.AddToStats(stats)
+ check(t, err, "Failed to add stats to DB")
+
+ // Try again. It should fail this time.
+ err = fixture.db.AddToStats(stats)
+ if err == nil {
+ t.Fatal("Failed to get error when adding duplicate stats.")
+ }
+ if !strings.Contains(err.Error(), "ERROR: duplicate key value violates unique constraint") {
+ t.Fatal("Expected error message to be complain about duplicate key value, but got ", err)
+ }
+}
+
func TestQueryMatchDB(t *testing.T) {
fixture := createDatabase(t)
defer fixture.TearDown()
@@ -232,14 +267,14 @@
testDatabase := []Stats{
Stats{
- TeamNumber: 1235, MatchNumber: 94,
+ TeamNumber: 1235, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 1,
AutoBallPickedUp: [5]bool{false, false, false, false, false},
ShotsMissed: 2, UpperGoalShots: 2, LowerGoalShots: 2,
ShotsMissedAuto: 2, UpperGoalAuto: 2, LowerGoalAuto: 2,
PlayedDefense: 2, DefenseReceivedScore: 1, Climbing: 2},
Stats{
- TeamNumber: 1234, MatchNumber: 94,
+ TeamNumber: 1234, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 2,
AutoBallPickedUp: [5]bool{false, false, false, false, true},
ShotsMissed: 4, UpperGoalShots: 4, LowerGoalShots: 4,
@@ -247,7 +282,7 @@
PlayedDefense: 7, DefenseReceivedScore: 1, Climbing: 2,
},
Stats{
- TeamNumber: 1233, MatchNumber: 94,
+ TeamNumber: 1233, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 3,
AutoBallPickedUp: [5]bool{false, false, false, false, false},
ShotsMissed: 3, UpperGoalShots: 3, LowerGoalShots: 3,
@@ -255,7 +290,7 @@
PlayedDefense: 3, DefenseReceivedScore: 0, Climbing: 3,
},
Stats{
- TeamNumber: 1232, MatchNumber: 94,
+ TeamNumber: 1232, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 2,
AutoBallPickedUp: [5]bool{true, false, false, false, true},
ShotsMissed: 5, UpperGoalShots: 5, LowerGoalShots: 5,
@@ -263,7 +298,7 @@
PlayedDefense: 7, DefenseReceivedScore: 2, Climbing: 1,
},
Stats{
- TeamNumber: 1231, MatchNumber: 94,
+ TeamNumber: 1231, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 3,
AutoBallPickedUp: [5]bool{false, false, true, false, false},
ShotsMissed: 6, UpperGoalShots: 6, LowerGoalShots: 6,
@@ -271,7 +306,7 @@
PlayedDefense: 7, DefenseReceivedScore: 3, Climbing: 1,
},
Stats{
- TeamNumber: 1239, MatchNumber: 94,
+ TeamNumber: 1239, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 4,
AutoBallPickedUp: [5]bool{false, true, true, false, false},
ShotsMissed: 7, UpperGoalShots: 7, LowerGoalShots: 7,
@@ -292,7 +327,7 @@
correct := []Stats{
Stats{
- TeamNumber: 1235, MatchNumber: 94,
+ TeamNumber: 1235, MatchNumber: 94, Round: 2, CompLevel: "quals",
StartingQuadrant: 1,
AutoBallPickedUp: [5]bool{false, false, false, false, false},
ShotsMissed: 2, UpperGoalShots: 2, LowerGoalShots: 2,
@@ -486,14 +521,14 @@
correct := []Stats{
Stats{
- TeamNumber: 1235, MatchNumber: 94,
+ TeamNumber: 1235, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 1,
AutoBallPickedUp: [5]bool{false, false, false, false, false},
ShotsMissed: 2, UpperGoalShots: 2, LowerGoalShots: 2,
ShotsMissedAuto: 2, UpperGoalAuto: 2, LowerGoalAuto: 2,
PlayedDefense: 2, DefenseReceivedScore: 3, Climbing: 2},
Stats{
- TeamNumber: 1236, MatchNumber: 94,
+ TeamNumber: 1236, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 2,
AutoBallPickedUp: [5]bool{false, false, false, false, true},
ShotsMissed: 4, UpperGoalShots: 4, LowerGoalShots: 4,
@@ -501,7 +536,7 @@
PlayedDefense: 7, DefenseReceivedScore: 1, Climbing: 2,
},
Stats{
- TeamNumber: 1237, MatchNumber: 94,
+ TeamNumber: 1237, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 3,
AutoBallPickedUp: [5]bool{false, false, false, false, false},
ShotsMissed: 3, UpperGoalShots: 3, LowerGoalShots: 3,
@@ -509,7 +544,7 @@
PlayedDefense: 3, DefenseReceivedScore: 0, Climbing: 3,
},
Stats{
- TeamNumber: 1238, MatchNumber: 94,
+ TeamNumber: 1238, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 2,
AutoBallPickedUp: [5]bool{true, false, false, false, true},
ShotsMissed: 5, UpperGoalShots: 5, LowerGoalShots: 5,
@@ -517,7 +552,7 @@
PlayedDefense: 7, DefenseReceivedScore: 4, Climbing: 1,
},
Stats{
- TeamNumber: 1239, MatchNumber: 94,
+ TeamNumber: 1239, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 3,
AutoBallPickedUp: [5]bool{false, false, true, false, false},
ShotsMissed: 6, UpperGoalShots: 6, LowerGoalShots: 6,
@@ -525,7 +560,7 @@
PlayedDefense: 7, DefenseReceivedScore: 4, Climbing: 1,
},
Stats{
- TeamNumber: 1233, MatchNumber: 94,
+ TeamNumber: 1233, MatchNumber: 94, Round: 1, CompLevel: "quals",
StartingQuadrant: 4,
AutoBallPickedUp: [5]bool{false, true, true, false, false},
ShotsMissed: 7, UpperGoalShots: 7, LowerGoalShots: 7,