Move scouting/db.go into its own directory
Our Go tooling (i.e. gazelle) gets confused when the directory name
doesn't match the `package` name. I couldn't get it to resolve the
dependency on the `db` package.
This patch fixes that by moving the code (plus test) into its own
directory.
Signed-off-by: Philipp Schrader <philipp.schrader@gmail.com>
Change-Id: I7f57912966d0a8f677782318b1d6ea2042b8b0cb
diff --git a/scouting/db/BUILD b/scouting/db/BUILD
new file mode 100644
index 0000000..e89332d
--- /dev/null
+++ b/scouting/db/BUILD
@@ -0,0 +1,17 @@
+load("@io_bazel_rules_go//go:def.bzl", "go_library", "go_test")
+
+go_library(
+ name = "db",
+ srcs = ["db.go"],
+ importpath = "github.com/frc971/971-Robot-Code/scouting/db",
+ target_compatible_with = ["@platforms//cpu:x86_64"],
+ visibility = ["//visibility:public"],
+ deps = ["@com_github_mattn_go_sqlite3//:go-sqlite3"],
+)
+
+go_test(
+ name = "db_test",
+ srcs = ["db_test.go"],
+ embed = [":db"],
+ target_compatible_with = ["@platforms//cpu:x86_64"],
+)
diff --git a/scouting/db/db.go b/scouting/db/db.go
new file mode 100644
index 0000000..86d332e
--- /dev/null
+++ b/scouting/db/db.go
@@ -0,0 +1,183 @@
+package db
+
+import (
+ "database/sql"
+ "fmt"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type Database struct {
+ *sql.DB
+}
+
+type Match struct {
+ matchNumber, round int
+ compLevel string
+ r1, r2, r3, b1, b2, b3 int
+ // Each of these variables holds the matchID of the corresponding Stats row
+ r1ID, r2ID, r3ID, b1ID, b2ID, b3ID int
+}
+
+type Stats struct {
+ teamNumber, matchNumber int
+ shotsMissed, upperGoalShots, lowerGoalShots int
+ shotsMissedAuto, upperGoalAuto, lowerGoalAuto, playedDefense int
+ climbing int
+}
+
+func NewDatabase() (*Database, error) {
+ database := new(Database)
+ database.DB, _ = sql.Open("sqlite3", "./scouting.db")
+ statement, error_ := database.Prepare("CREATE TABLE IF NOT EXISTS matches " +
+ "(id INTEGER PRIMARY KEY, matchNumber INTEGER, round INTEGER, compLevel INTEGER, r1 INTEGER, r2 INTEGER, r3 INTEGER, b1 INTEGER, b2 INTEGER, b3 INTEGER, r1ID INTEGER, r2ID INTEGER, r3ID INTEGER, b1ID INTEGER, b2ID INTEGER, b3ID INTEGER)")
+ defer statement.Close()
+ if error_ != nil {
+ fmt.Println(error_)
+ return nil, error_
+ }
+ _, error_ = statement.Exec()
+ statement, error_ = database.Prepare("CREATE TABLE IF NOT EXISTS team_match_stats (id INTEGER PRIMARY KEY, teamNumber INTEGER, matchNumber DOUBLE, shotsMissed INTEGER, upperGoalShots INTEGER, lowerGoalShots INTEGER, shotsMissedAuto INTEGER, upperGoalAuto INTEGER, lowerGoalAuto INTEGER, playedDefense INTEGER, climbing INTEGER)")
+ defer statement.Close()
+ if error_ != nil {
+ fmt.Println(error_)
+ return nil, error_
+ }
+ _, error_ = statement.Exec()
+ return database, nil
+}
+
+func (database *Database) Delete() error {
+ statement, error_ := database.Prepare("DROP TABLE IF EXISTS matches")
+ if error_ != nil {
+ fmt.Println(error_)
+ return (error_)
+ }
+ _, error_ = statement.Exec()
+ statement, error_ = database.Prepare("DROP TABLE IF EXISTS team_match_stats")
+ if error_ != nil {
+ fmt.Println(error_)
+ return (error_)
+ }
+ _, error_ = statement.Exec()
+ return nil
+}
+
+// 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, error_ := database.Prepare("INSERT INTO team_match_stats(teamNumber, matchNumber, shotsMissed, upperGoalShots, lowerGoalShots, shotsMissedAuto, upperGoalAuto, lowerGoalAuto, playedDefense, climbing) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+ defer statement.Close()
+ if error_ != nil {
+ fmt.Println("failed to prepare stats database:", error_)
+ return (error_)
+ }
+ var rowIds [6]int64
+ for i, teamNumber := range []int{m.r1, m.r2, m.r3, m.b1, m.b2, m.b3} {
+ result, error_ := statement.Exec(teamNumber, m.matchNumber, 0, 0, 0, 0, 0, 0, 0, 0)
+ if error_ != nil {
+ fmt.Println("failed to execute statement 2:", error_)
+ return (error_)
+ }
+ rowIds[i], error_ = result.LastInsertId()
+ }
+ statement, error_ = database.Prepare("INSERT INTO matches(matchNumber, round, compLevel, r1, r2, r3, b1, b2, b3, r1ID, r2ID, r3ID, b1ID, b2ID, b3ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+ defer statement.Close()
+ if error_ != nil {
+ fmt.Println("failed to prepare match database:", error_)
+ return (error_)
+ }
+ _, error_ = statement.Exec(m.matchNumber, m.round, m.compLevel, m.r1, m.r2, m.r3, m.b1, m.b2, m.b3, rowIds[0], rowIds[1], rowIds[2], rowIds[3], rowIds[4], rowIds[5])
+ if error_ != nil {
+ fmt.Println(error_)
+ return (error_)
+ }
+ return nil
+}
+
+func (database *Database) AddToStats(s Stats) error {
+ statement, error_ := database.Prepare("UPDATE team_match_stats SET teamNumber = ?, matchNumber = ?, shotsMissed = ?, upperGoalShots = ?, lowerGoalShots = ?, shotsMissedAuto = ?, upperGoalAuto = ?, lowerGoalAuto = ?, playedDefense = ?, climbing = ? WHERE matchNumber = ? AND teamNumber = ?")
+ if error_ != nil {
+ fmt.Println(error_)
+ return (error_)
+ }
+ _, error_ = statement.Exec(s.teamNumber, s.matchNumber, s.shotsMissed, s.upperGoalShots, s.lowerGoalShots, s.shotsMissedAuto, s.upperGoalAuto, s.lowerGoalAuto, s.playedDefense, s.climbing, s.matchNumber, s.teamNumber)
+ if error_ != nil {
+ fmt.Println(error_)
+ return (error_)
+ }
+ return nil
+}
+
+func (database *Database) ReturnMatches() ([]Match, error) {
+ matches := make([]Match, 0)
+ rows, _ := database.Query("SELECT * FROM matches")
+ defer rows.Close()
+ for rows.Next() {
+ var match Match
+ var id int
+ error_ := rows.Scan(&id, &match.matchNumber, &match.round, &match.compLevel, &match.r1, &match.r2, &match.r3, &match.b1, &match.b2, &match.b3, &match.r1ID, &match.r2ID, &match.r3ID, &match.b1ID, &match.b2ID, &match.b3ID)
+ if error_ != nil {
+ fmt.Println(nil, error_)
+ return nil, error_
+ }
+ matches = append(matches, match)
+ }
+ return matches, nil
+}
+
+func (database *Database) ReturnStats() ([]Stats, error) {
+ rows, _ := database.Query("SELECT * FROM team_match_stats")
+ defer rows.Close()
+ teams := make([]Stats, 0)
+ var id int
+ for rows.Next() {
+ var team Stats
+ error_ := rows.Scan(&id, &team.teamNumber, &team.matchNumber, &team.shotsMissed, &team.upperGoalShots, &team.lowerGoalShots, &team.shotsMissedAuto, &team.upperGoalAuto, &team.lowerGoalAuto, &team.playedDefense, &team.climbing)
+ if error_ != nil {
+ fmt.Println(error_)
+ return nil, error_
+ }
+ teams = append(teams, team)
+ }
+ return teams, nil
+}
+
+func (database *Database) QueryMatches(teamNumber_ int) ([]Match, error) {
+ rows, error_ := database.Query("SELECT * FROM matches WHERE r1 = ? OR r2 = ? OR r3 = ? OR b1 = ? OR b2 = ? OR b3 = ?", teamNumber_, teamNumber_, teamNumber_, teamNumber_, teamNumber_, teamNumber_)
+ if error_ != nil {
+ fmt.Println("failed to execute statement 1:", error_)
+ return nil, error_
+ }
+ defer rows.Close()
+ var matches []Match
+ var id int
+ for rows.Next() {
+ var match Match
+ rows.Scan(&id, &match.matchNumber, &match.round, &match.compLevel, &match.r1, &match.r2, &match.r3, &match.b1, &match.b2, &match.b3, &match.r1ID, &match.r2ID, &match.r3ID, &match.b1ID, &match.b2ID, &match.b3ID)
+ matches = append(matches, match)
+ }
+ return matches, nil
+}
+
+func (database *Database) QueryStats(teamNumber_ int) ([]Stats, error) {
+ rows, error_ := database.Query("SELECT * FROM team_match_stats WHERE teamNumber = ?", teamNumber_)
+ if error_ != nil {
+ fmt.Println("failed to execute statement 3:", error_)
+ return nil, error_
+ }
+ defer rows.Close()
+ var teams []Stats
+ for rows.Next() {
+ var team Stats
+ var id int
+ error_ = rows.Scan(&id, &team.teamNumber, &team.matchNumber, &team.shotsMissed,
+ &team.upperGoalShots, &team.lowerGoalShots, &team.shotsMissedAuto, &team.upperGoalAuto,
+ &team.lowerGoalAuto, &team.playedDefense, &team.climbing)
+ teams = append(teams, team)
+ }
+ if error_ != nil {
+ fmt.Println("failed to execute statement 3:", error_)
+ return nil, error_
+ }
+ return teams, nil
+}
diff --git a/scouting/db/db_test.go b/scouting/db/db_test.go
new file mode 100644
index 0000000..3c6d9c5
--- /dev/null
+++ b/scouting/db/db_test.go
@@ -0,0 +1,163 @@
+package db
+
+import (
+ "fmt"
+ "reflect"
+ "testing"
+)
+
+func TestAddToMatchDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ correct := []Match{Match{matchNumber: 7, round: 1, compLevel: "quals", r1: 9999, r2: 1000, r3: 777, b1: 0000, b2: 4321, b3: 1234, r1ID: 1, r2ID: 2, r3ID: 3, b1ID: 4, b2ID: 5, b3ID: 6}}
+ db.AddToMatch(correct[0])
+ got, error_ := db.ReturnMatches()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ if !reflect.DeepEqual(correct, got) {
+ t.Fatalf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}
+
+func TestAddToStatsDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ correct := []Stats{
+ Stats{teamNumber: 1236, matchNumber: 7, shotsMissed: 9, upperGoalShots: 5, lowerGoalShots: 4, shotsMissedAuto: 3, upperGoalAuto: 2, lowerGoalAuto: 1, playedDefense: 2, climbing: 3},
+ Stats{teamNumber: 1001, matchNumber: 7, shotsMissed: 6, upperGoalShots: 9, lowerGoalShots: 9, shotsMissedAuto: 0, upperGoalAuto: 0, lowerGoalAuto: 0, playedDefense: 0, climbing: 0},
+ Stats{teamNumber: 777, matchNumber: 7, shotsMissed: 5, upperGoalShots: 7, lowerGoalShots: 12, shotsMissedAuto: 0, upperGoalAuto: 4, lowerGoalAuto: 0, playedDefense: 0, climbing: 0},
+ Stats{teamNumber: 1000, matchNumber: 7, shotsMissed: 12, upperGoalShots: 6, lowerGoalShots: 10, shotsMissedAuto: 0, upperGoalAuto: 7, lowerGoalAuto: 0, playedDefense: 0, climbing: 0},
+ Stats{teamNumber: 4321, matchNumber: 7, shotsMissed: 14, upperGoalShots: 12, lowerGoalShots: 3, shotsMissedAuto: 0, upperGoalAuto: 7, lowerGoalAuto: 0, playedDefense: 0, climbing: 0},
+ Stats{teamNumber: 1234, matchNumber: 7, shotsMissed: 3, upperGoalShots: 4, lowerGoalShots: 0, shotsMissedAuto: 0, upperGoalAuto: 9, lowerGoalAuto: 0, playedDefense: 0, climbing: 0},
+ }
+ db.AddToMatch(Match{matchNumber: 7, round: 1, compLevel: "quals", r1: 1236, r2: 1001, r3: 777, b1: 1000, b2: 4321, b3: 1234, r1ID: 1, r2ID: 2, r3ID: 3, b1ID: 4, b2ID: 5, b3ID: 6})
+ for i := 0; i < len(correct); i++ {
+ db.AddToStats(correct[i])
+ }
+ got, error_ := db.ReturnStats()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}
+
+func TestQueryMatchDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ fmt.Println("Error creating new database")
+ }
+
+ testDatabase := []Match{
+ Match{matchNumber: 2, round: 1, compLevel: "quals", r1: 251, r2: 169, r3: 286, b1: 253, b2: 538, b3: 149},
+ Match{matchNumber: 4, round: 1, compLevel: "quals", r1: 198, r2: 135, r3: 777, b1: 999, b2: 434, b3: 698},
+ Match{matchNumber: 3, round: 1, compLevel: "quals", r1: 147, r2: 421, r3: 538, b1: 126, b2: 448, b3: 262},
+ Match{matchNumber: 6, round: 1, compLevel: "quals", r1: 191, r2: 132, r3: 773, b1: 994, b2: 435, b3: 696},
+ }
+
+ for i := 0; i < len(testDatabase); i++ {
+ db.AddToMatch(testDatabase[i])
+ }
+
+ correct := []Match{
+ Match{matchNumber: 2, round: 1, compLevel: "quals", r1: 251, r2: 169, r3: 286, b1: 253, b2: 538, b3: 149, r1ID: 1, r2ID: 2, r3ID: 3, b1ID: 4, b2ID: 5, b3ID: 6},
+ Match{matchNumber: 3, round: 1, compLevel: "quals", r1: 147, r2: 421, r3: 538, b1: 126, b2: 448, b3: 262, r1ID: 13, r2ID: 14, r3ID: 15, b1ID: 16, b2ID: 17, b3ID: 18},
+ }
+
+ got, error_ := db.QueryMatches(538)
+ if !reflect.DeepEqual(correct, got) {
+ t.Fatalf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}
+
+func TestQueryStatsDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ testDatabase := []Stats{
+ Stats{teamNumber: 1235, matchNumber: 94, shotsMissed: 2, upperGoalShots: 2, lowerGoalShots: 2, shotsMissedAuto: 2, upperGoalAuto: 2, lowerGoalAuto: 2, playedDefense: 2, climbing: 2},
+ Stats{teamNumber: 1234, matchNumber: 94, shotsMissed: 4, upperGoalShots: 4, lowerGoalShots: 4, shotsMissedAuto: 4, upperGoalAuto: 4, lowerGoalAuto: 4, playedDefense: 7, climbing: 2},
+ Stats{teamNumber: 1233, matchNumber: 94, shotsMissed: 3, upperGoalShots: 3, lowerGoalShots: 3, shotsMissedAuto: 3, upperGoalAuto: 3, lowerGoalAuto: 3, playedDefense: 3, climbing: 3},
+ Stats{teamNumber: 1232, matchNumber: 94, shotsMissed: 5, upperGoalShots: 5, lowerGoalShots: 5, shotsMissedAuto: 5, upperGoalAuto: 5, lowerGoalAuto: 5, playedDefense: 7, climbing: 1},
+ Stats{teamNumber: 1231, matchNumber: 94, shotsMissed: 6, upperGoalShots: 6, lowerGoalShots: 6, shotsMissedAuto: 6, upperGoalAuto: 6, lowerGoalAuto: 6, playedDefense: 7, climbing: 1},
+ Stats{teamNumber: 1239, matchNumber: 94, shotsMissed: 7, upperGoalShots: 7, lowerGoalShots: 7, shotsMissedAuto: 7, upperGoalAuto: 7, lowerGoalAuto: 3, playedDefense: 7, climbing: 1},
+ }
+ db.AddToMatch(Match{matchNumber: 94, round: 1, compLevel: "quals", r1: 1235, r2: 1234, r3: 1233, b1: 1232, b2: 1231, b3: 1239})
+ for i := 0; i < len(testDatabase); i++ {
+ db.AddToStats(testDatabase[i])
+ }
+ correct := []Stats{
+ Stats{teamNumber: 1235, matchNumber: 94, shotsMissed: 2, upperGoalShots: 2, lowerGoalShots: 2, shotsMissedAuto: 2, upperGoalAuto: 2, lowerGoalAuto: 2, playedDefense: 2, climbing: 2},
+ }
+ got, error_ := db.QueryStats(1235)
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}
+
+func TestReturnMatchDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ correct := []Match{
+ Match{matchNumber: 2, round: 1, compLevel: "quals", r1: 251, r2: 169, r3: 286, b1: 253, b2: 538, b3: 149, r1ID: 1, r2ID: 2, r3ID: 3, b1ID: 4, b2ID: 5, b3ID: 6},
+ Match{matchNumber: 3, round: 1, compLevel: "quals", r1: 147, r2: 421, r3: 538, b1: 126, b2: 448, b3: 262, r1ID: 7, r2ID: 8, r3ID: 9, b1ID: 10, b2ID: 11, b3ID: 12},
+ Match{matchNumber: 4, round: 1, compLevel: "quals", r1: 251, r2: 169, r3: 286, b1: 653, b2: 538, b3: 149, r1ID: 13, r2ID: 14, r3ID: 15, b1ID: 16, b2ID: 17, b3ID: 18},
+ Match{matchNumber: 5, round: 1, compLevel: "quals", r1: 198, r2: 1421, r3: 538, b1: 26, b2: 448, b3: 262, r1ID: 19, r2ID: 20, r3ID: 21, b1ID: 22, b2ID: 23, b3ID: 24},
+ Match{matchNumber: 6, round: 1, compLevel: "quals", r1: 251, r2: 188, r3: 286, b1: 555, b2: 538, b3: 149, r1ID: 25, r2ID: 26, r3ID: 27, b1ID: 28, b2ID: 29, b3ID: 30},
+ }
+ for i := 0; i < len(correct); i++ {
+ db.AddToMatch(correct[i])
+ }
+ got, error_ := db.ReturnMatches()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}
+
+func TestReturnStatsDB(t *testing.T) {
+ db, error_ := NewDatabase()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ correct := []Stats{
+ Stats{teamNumber: 1235, matchNumber: 94, shotsMissed: 2, upperGoalShots: 2, lowerGoalShots: 2, shotsMissedAuto: 2, upperGoalAuto: 2, lowerGoalAuto: 2, playedDefense: 2, climbing: 2},
+ Stats{teamNumber: 1236, matchNumber: 94, shotsMissed: 4, upperGoalShots: 4, lowerGoalShots: 4, shotsMissedAuto: 4, upperGoalAuto: 4, lowerGoalAuto: 4, playedDefense: 7, climbing: 2},
+ Stats{teamNumber: 1237, matchNumber: 94, shotsMissed: 3, upperGoalShots: 3, lowerGoalShots: 3, shotsMissedAuto: 3, upperGoalAuto: 3, lowerGoalAuto: 3, playedDefense: 3, climbing: 3},
+ Stats{teamNumber: 1238, matchNumber: 94, shotsMissed: 5, upperGoalShots: 5, lowerGoalShots: 5, shotsMissedAuto: 5, upperGoalAuto: 5, lowerGoalAuto: 5, playedDefense: 7, climbing: 1},
+ Stats{teamNumber: 1239, matchNumber: 94, shotsMissed: 6, upperGoalShots: 6, lowerGoalShots: 6, shotsMissedAuto: 6, upperGoalAuto: 6, lowerGoalAuto: 6, playedDefense: 7, climbing: 1},
+ Stats{teamNumber: 1233, matchNumber: 94, shotsMissed: 7, upperGoalShots: 7, lowerGoalShots: 7, shotsMissedAuto: 7, upperGoalAuto: 7, lowerGoalAuto: 3, playedDefense: 7, climbing: 1},
+ }
+ db.AddToMatch(Match{matchNumber: 94, round: 1, compLevel: "quals", r1: 1235, r2: 1236, r3: 1237, b1: 1238, b2: 1239, b3: 1233})
+ for i := 0; i < len(correct); i++ {
+ db.AddToStats(correct[i])
+ }
+ got, error_ := db.ReturnStats()
+ if error_ != nil {
+ t.Fatalf(error_.Error())
+ }
+ if !reflect.DeepEqual(correct, got) {
+ t.Errorf("Got %#v,\nbut expected %#v.", got, correct)
+ }
+ db.Delete()
+}