Replace create_event_table() with equivalent sql create table

[andybega]

Other tables are created from SQL files, but the "events" table is not. Path dependence, probably because it was the first table I set up, or maybe because it has indices. Which in any case can be part of the create table SQL file.

Then just call "events.sql" with "execute_sql()" like the other tables.

[andybega]

Seems to have been fixed already. This is the current code:

#' Create event table and indices
#'
#' @template dbp
#' @keywords internal
create_event_table <- function(db_path) {
  con <- connect(db_path)
  on.exit(DBI::dbDisconnect(con))

  execute_sql("events.sql", db_path)

  # Create indices
  idx_columns <- c("source_file", "cameo_code", "country", "year",
                   "yearmonth")
  idx_names <- paste0("events_", gsub(" ", "_", tolower(idx_columns)), "_idx")
  existing_indices <- list_indices(db_path)$name
  need_cols <- idx_columns[!idx_names %in% existing_indices]
  if (length(need_cols) > 0) {
    #cat("Building indices\n")
    need_names <- idx_names[!idx_names %in% existing_indices]
    for (i in 1:length(need_cols)) {
      #cat(sprintf("Creating index for '%s'\n", need_cols[i]))
      sql <- sprintf("CREATE INDEX IF NOT EXISTS %s ON events(`%s`);", need_names[i], need_cols[i])
      res <- DBI::dbSendQuery(con, sql)
      DBI::dbClearResult(res)
    }
  }
  invisible(NULL)
}