checkbox filtering not working in Documents: ∆ `ferc_docs.ck_*` from character to boolean

[bbest]

There was an error related to the fact that the data type for ck_* fields was character, not logical as they should be. This should've been handled at the update_projects() stage when reading in the Google Sheet, but now that these records are being updated via the Edit app, made this change directly to the database (note use of poolCheckout() for data manipulation):

# FIX ferc_docs.ck_* to logical
tbl(con, "ferc_docs") %>% collect() %>% select(starts_with("ck_")) %>% names() %>% paste(collapse = ', ')
conn <- poolCheckout(con)
dbSendQuery(
  conn,
  "ALTER TABLE ferc_docs
      ALTER COLUMN ck_ixn  TYPE BOOLEAN USING ck_ixn::BOOLEAN,
      ALTER COLUMN ck_obs  TYPE BOOLEAN USING ck_obs::BOOLEAN,
      ALTER COLUMN ck_mp   TYPE BOOLEAN USING ck_mp::BOOLEAN,
      ALTER COLUMN ck_amp  TYPE BOOLEAN USING ck_amp::BOOLEAN,
      ALTER COLUMN ck_pme  TYPE BOOLEAN USING ck_pme::BOOLEAN,
      ALTER COLUMN ck_bmps TYPE BOOLEAN USING ck_bmps::BOOLEAN")
poolReturn(conn)

[bbest]

Also noticing multiple rows for same record showing up in Documents:

This has to do with d_to_tags_html(d) in global.R expecting only rowid, tag_sql columns in ferc_doc_tags table, so dropped excess fields in database that were used for lookup and assignment from original content:

conn <- poolCheckout(con)
  dbSendQuery(
    conn,
    "ALTER TABLE ferc_doc_tags
      DROP COLUMN content, 
      DROP COLUMN tag_category, 
      DROP COLUMN content_tag")
  poolReturn(conn)

[bbest]

Also fixing source table ferc_docs where true NA should be applied to values of "NA" and "":

ferc_docs <- tbl(con, "ferc_docs") %>% 
    collect() %>% 
    mutate(across(is.character, na_if, "NA")) %>%
    mutate(across(is.character, na_if, ""))
  dbWriteTable(con, "ferc_docs", ferc_docs, overwrite = T)