create `get_table_schema()` to return table schema

[PietrH]

It would be convenient to be able to return the full table schema by a single function call:

• There is a vignette on the etn r package website that could use this information: https://inbo.github.io/etn/articles/etn_fields.html
• the get_acoustic_detections() function in {etn} currently creates an empty tibble with hardcoded column names as an early return if a query results in no rows (COUNT), it would be more robust not to hard code the columns, and to return the correct types. But a LIMIT 0 query takes much longer. (Why?)
• This function could be useful in creating new mappings, but does it need to be available over the API in that case?
• Is there any information in the table schema that I shouldn't make widely available? I don't think so.. An overview of what data is in ETN might actually be quite useful for powerusers.

[PietrH]

Actually, a LIMIT 0 query is also very very fast, so any slowdown I have on that in {etn} is probably coming from elsewhere. Maybe the two step request method, or maybe validate_login() ?

Some drafting I did on get_table_schema():

get_table_schema <- function(credentials = list(
  username = Sys.getenv("userid"),
  password = Sys.getenv("pwd")
)) {
  # Check if credentials object has right shape
  check_credentials(credentials)
  
  # Create connection object
  connection <- connect_to_etn(credentials$username, credentials$password)
  
  # Check connection
  check_connection(connection)
  
  query <- glue::glue_sql(.con = connection,
                          "
  SELECT table_schema,
       table_name,
       ordinal_position as position,
       column_name,
       data_type,
       case when character_maximum_length is not null
            then character_maximum_length
            else numeric_precision end as max_length,
       is_nullable,
       column_default as default_value
FROM information_schema.columns
WHERE table_schema not in ('information_schema', 'pg_catalog')
ORDER BY table_schema, 
         table_name,
         ordinal_position;

                        ")
  
  table_schema <- DBI::dbGetQuery(connection, query)
  
  return(table_schema)
}

init_tibble <- function(names) {
  dplyr::tibble(!!!rlang::rep_named(names, NA))
}

table_schema <- get_table_schema()

table_schema %>% 
  dplyr::filter(table_name == "detections_animal") %>% 
  dplyr::pull(column_name) %>% 
  init_tibble

# Getting the table columns via a LIMIT 0 query:
connection <- connect_to_etn(username = Sys.getenv("ETN_USER"),
                             password = Sys.getenv("ETN_PWD"))
query <- 
  glue::glue_sql(
    "SELECT * FROM acoustic.detections_animal LIMIT 0",
    .con = connection
  )

DBI::dbGetQuery(query, conn = connection) %>% 
  dplyr::as_tibble()
  

[PietrH]

the page_size argument of get_acoustic_detections_page allows only for positive integers, I could change this. But I'd have to go via a two step request process anyway because otherwise the column types wouldn't be retained (via JSON vs RDS).

The JSON workflow will also drop empty columns by default, which defeats the point.