Migrate the scouting database to postgres
The existing database is sqlite3. That is not usable by Tableau which
is the visualization tool we use. Tableau also appears incapable of
scraping, say, JSON from our scouting app.
This patch migrates our application to use postgres instead of
sqlite3. That database will be accessible by Tableau.
I created a `testdb_server` application that sets up postgres in the
linux-sandbox in a bazel test. This is useful in the various tests
that we run on the scouting application.
Signed-off-by: Philipp Schrader <philipp.schrader@gmail.com>
Change-Id: I9cd260f8998b9607e1e3229ab70f243cdded5ec5
diff --git a/scouting/webserver/main.go b/scouting/webserver/main.go
index 282e248..a4d549a 100644
--- a/scouting/webserver/main.go
+++ b/scouting/webserver/main.go
@@ -1,6 +1,7 @@
package main
import (
+ "encoding/json"
"errors"
"flag"
"fmt"
@@ -8,8 +9,8 @@
"log"
"os"
"os/signal"
- "path/filepath"
"syscall"
+ "time"
"github.com/frc971/971-Robot-Code/scouting/db"
"github.com/frc971/971-Robot-Code/scouting/scraping"
@@ -18,40 +19,88 @@
"github.com/frc971/971-Robot-Code/scouting/webserver/static"
)
-func getDefaultDatabasePath() string {
- // If using `bazel run`, let's create the database in the root of the
- // workspace.
- workspaceDir := os.Getenv("BUILD_WORKSPACE_DIRECTORY")
- if workspaceDir != "" {
- return filepath.Join(workspaceDir, "scouting.db")
+type DatabaseConfig struct {
+ Username string `json:"username"`
+ Password string `json:"password"`
+ Port int `json:"port"`
+}
+
+func readDatabaseConfig(configPath string) (*DatabaseConfig, error) {
+ content, err := ioutil.ReadFile(configPath)
+ if err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to open database config at ", configPath, ": ", err))
}
- // If we're inside a `bazel test`, then we will create the database in
- // a temporary directory.
- testTempDir := os.Getenv("TEST_TMPDIR")
- if testTempDir != "" {
- tempDir, err := ioutil.TempDir(testTempDir, "db")
- if err != nil {
- log.Fatal("Failed to create temporary directory in TEST_TMPDIR: ", err)
- }
- return filepath.Join(tempDir, "scouting.db")
+
+ var config DatabaseConfig
+ if err := json.Unmarshal([]byte(content), &config); err != nil {
+ return nil, errors.New(fmt.Sprint("Failed to parse database config file as JSON: ", err))
}
- return filepath.Join(".", "scouting.db")
+
+ return &config, nil
}
func main() {
portPtr := flag.Int("port", 8080, "The port number to bind to.")
dirPtr := flag.String("directory", ".", "The directory to serve at /.")
- dbPathPtr := flag.String("database", getDefaultDatabasePath(), "The path to the database.")
+ dbConfigPtr := flag.String("db_config", "",
+ "The postgres database JSON config. It needs the following keys: "+
+ "\"username\", \"password\", and \"port\". "+
+ "This option cannot be used in conjunction with -testdb_port.")
+ testDbPortPtr := flag.Int("testdb_port", 0,
+ "If set, connects to an instance of //scouting/db/testdb_server at the "+
+ "specified port. This option cannot be used in conjunction with "+
+ "-db_config.")
+ dbConnectRetries := flag.Int("db_retries", 5,
+ "The number of seconds to retry connecting to the database on startup.")
blueAllianceConfigPtr := flag.String("tba_config", "",
"The path to your The Blue Alliance JSON config. "+
"It needs an \"api_key\" field with your TBA API key. "+
"Optionally, it can have a \"base_url\" field with the TBA API base URL.")
flag.Parse()
- database, err := db.NewDatabase(*dbPathPtr)
+ // Set up the database config. It's either specified via a JSON file on
+ // disk (-db_config) or we can generate an in-memory config when the
+ // user wants to connect to a `testdb_server` instance (-testdb_port).
+ var dbConfig *DatabaseConfig
+ var err error
+ if *dbConfigPtr != "" {
+ if *testDbPortPtr != 0 {
+ log.Fatal("Cannot specify both -db_config and -testdb_port. Choose one.")
+ }
+ dbConfig, err = readDatabaseConfig(*dbConfigPtr)
+ if err != nil {
+ log.Fatal("Failed to read ", *dbConfigPtr, ": ", err)
+ }
+ } else if *testDbPortPtr != 0 {
+ dbConfig = &DatabaseConfig{
+ Username: "test",
+ Password: "password",
+ Port: *testDbPortPtr,
+ }
+ } else {
+ log.Fatal("Must specify one of -db_config and -testdb_port. See " +
+ "https://github.com/frc971/971-Robot-Code/blob/master/scouting/README.md " +
+ "for more information.")
+ }
+
+ // Connect to the database. It may still be starting up so we do a
+ // bunch of retries here. The number of retries can be set on the
+ // command line.
+ var database *db.Database
+ for i := 0; i < *dbConnectRetries*10; i++ {
+ database, err = db.NewDatabase(dbConfig.Username, dbConfig.Password, dbConfig.Port)
+ if err == nil {
+ break
+ }
+ if i%10 == 0 {
+ log.Println("Failed to connect to the database. Retrying in a bit.")
+ }
+ time.Sleep(1 * time.Millisecond)
+ }
if err != nil {
log.Fatal("Failed to connect to database: ", err)
}
+ defer database.Close()
scrapeMatchList := func(year int32, eventCode string) ([]scraping.Match, error) {
if *blueAllianceConfigPtr == "" {
@@ -69,7 +118,8 @@
// Block until the user hits Ctrl-C.
sigint := make(chan os.Signal, 1)
signal.Notify(sigint, syscall.SIGINT)
- fmt.Println("Waiting for CTRL-C or SIGINT.")
+ signal.Notify(sigint, syscall.SIGTERM)
+ fmt.Println("Waiting for CTRL-C or SIGTERM.")
<-sigint
fmt.Println("Shutting down.")