Add database library for scouting
Signed-off-by: Sabina Leaver <100027607@mvla.net>
Change-Id: Iefd4fe4b568f31cfde8232e0dd87a0068afca051
diff --git a/scouting/BUILD b/scouting/BUILD
index 836e5c3..8a3b3f0 100644
--- a/scouting/BUILD
+++ b/scouting/BUILD
@@ -1,4 +1,4 @@
-load("@io_bazel_rules_go//go:def.bzl", "go_binary", "go_library")
+load("@io_bazel_rules_go//go:def.bzl", "go_binary", "go_library", "go_test")
go_binary(
name = "sql_demo",
@@ -15,3 +15,19 @@
visibility = ["//visibility:private"],
deps = ["@com_github_mattn_go_sqlite3//:go-sqlite3"],
)
+
+go_library(
+ name = "database",
+ srcs = ["db.go"],
+ importpath = "github.com/frc971/971-Robot-Code/scouting",
+ target_compatible_with = ["@platforms//cpu:x86_64"],
+ visibility = ["//visibility:private"],
+ deps = ["@com_github_mattn_go_sqlite3//:go_default_library"],
+)
+
+go_test(
+ name = "db_test",
+ srcs = ["db_test.go"],
+ embed = [":database"],
+ target_compatible_with = ["@platforms//cpu:x86_64"],
+)
diff --git a/scouting/db.go b/scouting/db.go
new file mode 100644
index 0000000..86d332e
--- /dev/null
+++ b/scouting/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_test.go b/scouting/db_test.go
new file mode 100644
index 0000000..3c6d9c5
--- /dev/null
+++ b/scouting/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()
+}