Skip to contents

Build or Update a Local golfastr Database

Source: R/update_db.R
update_db.Rd

Writes golfastr's hosted data into a database on your computer, so it can be queried with SQL or dplyr. By default this is an SQLite file (golfastr_db in dbdir); pass db_connection to use any other DBI database, such as DuckDB or Postgres.

Usage

update_db(
  dbdir = getOption("golfastr.dbdirectory", default = "."),
  dbname = "golfastr_db",
  tables = c("events", "holes", "leaderboards", "strokes_gained", "skill_ratings",
    "player_ids", "event_ids"),
  force_rebuild = FALSE,
  db_connection = NULL
)

Arguments

dbdir

Folder for the SQLite file. Defaults to getOption("golfastr.dbdirectory"), or the working directory.

dbname

File name of the SQLite database.

tables

Tables to build. The default leaves out the ShotLink tables, which are large (shots is about a million rows per season); add "shots", "tee_times" and "course_setup" to include them.

force_rebuild

If TRUE, drop the requested tables and build them again from scratch.

db_connection

Optional DBI connection to write to instead of the SQLite file. It is left open.

Value

The DBI connection, invisibly. When golfastr opened the SQLite file itself it is closed and NULL is returned; reopen it with DBI::dbConnect(RSQLite::SQLite(), file.path(dbdir, dbname)).

Details

The first call downloads everything requested. Later calls add seasons that are missing and refresh the latest season, so running it once a week keeps the database current. Lookup tables (ratings, IDs, strokes gained) are replaced in full each time.

Every season-based table has a season column. Tables built from ESPN data (holes, events, leaderboards, skill_ratings) use ESPN IDs in tournament_id and player_id. ShotLink tables (shots, tee_times, course_setup) name theirs pga_tournament_id and pga_player_id, and also carry espn_tournament_id and espn_player_id where the ID crosswalk has a match, so the two sources join directly. Dates are stored as "YYYY-MM-DD" text and times as ISO 8601 text in UTC.

Examples

# \donttest{
if (requireNamespace("RSQLite", quietly = TRUE)) {
  dir <- tempdir()
  update_db(dbdir = dir, tables = c("events", "player_ids"))
  con <- DBI::dbConnect(RSQLite::SQLite(), file.path(dir, "golfastr_db"))
  DBI::dbGetQuery(con, "SELECT season, COUNT(*) AS events FROM events GROUP BY season")
  DBI::dbDisconnect(con)
}
#> "events": 23 seasons written
#> "player_ids": 1620 rows
# }