API_url_tracker.Rmd

---
title: "API URL tracker"
author: "Experiences Dashboard"
date: "2023-09-04"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(experiencesdashboard)
```

# Last rendered at `r round(Sys.time())`

## load the api job table from the database

```{r}
conn <- odbc::dbConnect(
  drv = odbc::odbc(),
  driver = Sys.getenv("odbc_driver"),
  server = Sys.getenv("HOST_NAME"),
  UID = Sys.getenv("DB_USER"),
  PWD = Sys.getenv("MYSQL_PASSWORD"),
  database = "TEXT_MINING",
  Port = 3306
)

# connect to a pin board to save the prediction in case database writing fails. 
board <- pins::board_connect()
# OR
# # Set board to Null if database writing is no longer an issue
# board = NULL

pending_jobs <- dplyr::tbl(
  conn,
  dbplyr::in_schema(
    "TEXT_MINING",
    "api_jobs"
  )
) |>
  dplyr::filter(status == "submitted") |>
  dplyr::collect()
```

## GET THE PREDICTION
```{r track job}
Sys.sleep(2) # Sleep for 5 seconds to allow any pending tasks to start in the API.

# check the url of each submitted job and update the sentiment column of the main database table if the prediction is ready
if (nrow(pending_jobs) > 0) {
  pending_jobs |>
    apply(1, track_api_job,
      conn = conn, write_db = TRUE,
      board = board
    )
} else {
  paste("No pending job")
}
```