Writing forecast to SQLite fails when fcst_model starts with numeric character

[JMFaundez]

Hi,

I don't know if it is a desired behavior, but as the title says, when attempting to output to sqlite with read_forecastwith an experiment's name starting with a number, it fails.

I think it is due to sql complaining about tables or columns with names starting with a numeric character without quotation. But I'm not sure since I'm quite new to both R and sql.

I found a workaround by defining a new file_template and renaming the fcst_model as follows

fcst <-read_forecast(
    dttm          = 2024081100,
    #fcst_model    = "4dvar_ref",
    fcst_model    = "exp4dvar_ref",
    parameter     = NULL,
    lead_time     = 9,
    file_path     = here("archive/extract"),
    #file_template = "vfld", 
    file_template = "{file_path}/4dvar_ref/vfld4dvar_ref{YYYY}{MM}{DD}{HH}{LDT2}",
    return_data   = TRUE,
    output_file_opts = fctable_opts(path = here("data/FCTABLE"))
)

I guess I can keep doing it this way if this is an intended behavior.

Thanks!
Jose

In case of interest, the error I was getting was:

Error in map2():
ℹ In index: 1.
Caused by error:
! no such table: FC
Traceback:

1. write_forecast(data_df, output_file_opts)
2. purrr::walk2(df[["data"]], df[["file_name"]], write_fctable_to_sqlite,
   . primary_key = opts[["index_cols"]], synchronous = opts[["synchronous"]],
   . journal_mode = opts[["journal_mode"]], remove_model_elev = opts[["remove_model_elev"]])
3. map2(.x, .y, .f, ..., .progress = .progress)
4. map2_("list", .x, .y, .f, ..., .progress = .progress)
5. with_indexed_errors(i = i, names = names, error_call = .purrr_error_call,
   . call_with_cleanup(map2_impl, environment(), .type, .progress,
   . n, names, i))
6. withCallingHandlers(expr, error = function(cnd) {
   . if (i == 0L) {
   . }
   . else {
   . message <- c(i = "In index: {i}.")
   . if (!is.null(names) && !is.na(names[[i]]) && names[[i]] !=
   . "") {
   . name <- names[[i]]
   . message <- c(message, i = "With name: {name}.")
   . }
   . else {
   . name <- NULL
   . }
   . cli::cli_abort(message, location = i, name = name, parent = cnd,
   . call = error_call, class = "purrr_error_indexed")
   . }
   . })
7. call_with_cleanup(map2_impl, environment(), .type, .progress,
   . n, names, i)
8. .f(.x[[i]], .y[[i]], ...)
9. db_clean_and_write(sqlite_db, tablename, data, primary_key, index_constraint = "unique")
10. DBI::dbListFields(db_conn, db_table)
11. DBI::dbListFields(db_conn, db_table)
12. list_fields(conn, name)
13. dbSendQuery(conn, paste("SELECT * FROM ", dbQuoteIdentifier(conn,
    . name), "LIMIT 0"))
14. dbSendQuery(conn, paste("SELECT * FROM ", dbQuoteIdentifier(conn,
    . name), "LIMIT 0"))
15. .local(conn, statement, ...)
16. new("SQLiteResult", sql = statement, ptr = result_create(conn@ptr,
    . statement), conn = conn, bigint = conn@bigint)
17. initialize(value, ...)
18. initialize(value, ...)
19. result_create(conn@ptr, statement)
20. .handleSimpleError(function (cnd)
    . {
    . if (i == 0L) {
    . }
    . else {
    . message <- c(i = "In index: {i}.")
    . if (!is.null(names) && !is.na(names[[i]]) && names[[i]] !=
    . "") {
    . name <- names[[i]]
    . message <- c(message, i = "With name: {name}.")
    . }
    . else {
    . name <- NULL
    . }
    . cli::cli_abort(message, location = i, name = name, parent = cnd,
    . call = error_call, class = "purrr_error_indexed")
    . }
    . }, "no such table: FC", base::quote(NULL))
21. h(simpleError(msg, call))
22. cli::cli_abort(message, location = i, name = name, parent = cnd,
    . call = error_call, class = "purrr_error_indexed")
23. rlang::abort(message, ..., call = call, use_cli_format = TRUE,
    . .frame = .frame)
24. signal_abort(cnd, .file)
25. signalCondition(cnd)

[andrew-MET]

This is because column names are not allowed to begin with an integer in SQLite. The sqlite schema uses <fcst_model> as the root of the column name that stores the forecast data.

As it is the only way around it is to have experiment names that don't begin with an integer, or to change the name via setting fcst_model and a bit of hardcoding in the template like you have done. Unfortunately t's very unlikely that we'll implement a built in way of dealing with such cases as it would either require a complete redesign of the SQLite schema, or changing fcst_model behind the scenes - both of which seem to be quite risky moves.

[JMFaundez]

I see. Thanks for the input!