Issue: FEAT - External SQL DB with Bigquery - Can't subset rows with `logs$date >= input$overview_period[1] & ...`

Hi everyone!

Hope everything is ok. I found an issue on the new FEAT to add external SQL DB using Bigquery. I am not sure exactly where is the issue but I hope you can share some thoughts.

The issue is related with 2 main things:

Persistant call to the data base when the app is lunch. In my case it was 12 calls to the data base and this makes the loggin page works slow at the beggining.
When the logs server function is call an error occurs when try to filter the logs table according to the dates defined in the input overview_period. The error messages is Logical subscript logs$date >= input$overview_period[1] & ... must be size 1 or 1, not 0. I can imagine that this error happens because when lunch the app the input is set to NULL or something like that.

According to the documentation, to set up the external data base it is important to define a config_db file (.yml) that contains the instruction to call the data base. In my case this file works fine when I create the database using the shinymanager::create_sql_db function with the corresponding credentials data.frameand the path to the config file. The .yml file for bigquery that I am ussing is the following:

# needed R packages for DB connection. Use comma separation for multiple dependencies like [package1, package2]
r_packages: [bigrquery]

# connection usong DBI Interface
# Possible to use !expr Sys.getenv("NAME_ENV_VAR")
# all args to be passed to DBI::dbConnect
connect:
  drv: !expr bigrquery::bigquery()
  project: !expr Sys.getenv("bq_project")
  dataset: "shiny_admin" 
  use_legacy_sql: FALSE

tables:
  credentials:
    tablename: credentials                    # if you want to change tablename
    # user, password, start, expire, admin = mandatory with this name
    # after you can add optionnal custom columns next, getting back in server.R and admin interface
    init: CREATE TABLE {`tablename`} (
            user STRING, 
            password  STRING,
            is_hashed_password BOOL,
            start  DATE,                   
            expire DATE,                   
            admin BOOL,
            client STRING

        )
    # Keep same {glue_name*}, update request only if needed.   
    select: SELECT * FROM {`tablename`} WHERE user IN ({user*})
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE user IN ({udpate_users*})
    delete: DELETE FROM {`tablename`} WHERE user IN ({del_users*})
  pwd_mngt:
    tablename: pwd_mngt                       # if you want to change tablename
    # user, must_change, have_changed, date_change, n_wrong_pwd = mandatory with this name
    #  No additionnal columns here
    init: CREATE TABLE {`tablename`} (
            user STRING, 
            must_change  BOOL,           
            have_changed  BOOL,        
            date_change DATE, 
            n_wrong_pwd INT64
          )
    # Keep same {glue_name*}, update request only if needed.  
    select: SELECT * FROM {`tablename`} WHERE user IN ({user*})
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE user IN ({udpate_users*})
    delete: DELETE FROM {`tablename`} WHERE user IN ({del_users*})
  logs:
    tablename: logs                           # if you want to change tablename
    #  all = mandatory with this name
    #  No additionnal columns here
    init: CREATE TABLE {`tablename`} (
            id STRING DEFAULT GENERATE_UUID(),
            user  STRING,             
            server_connected TIMESTAMP, 
            token STRING,          
            logout TIMESTAMP,          
            status STRING,          
            app STRING               
          )
    # Keep same {glue_name*}, update request only if needed. 
    check_token : SELECT * FROM {`tablename`} WHERE token IN ({token*})
    select: SELECT * FROM {`tablename`} WHERE user IN ({user*}) AND server_connected >= {`date_h_begin`}  AND server_connected <= {`date_h_end`}
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE token IN ({token*})

The the script for the UI and server of the shiny app looks like this:

###### Loading packages 

library(shiny)
library(shinymanager)
library(bigrquery)
library(yaml)
library(scrypt)

####### Reading .Renviron and setup bigrquery authentication.

readRenviron(".Renviron") # This is a custom file that contains environmental variables
bq_auth(path = Sys.getenv("bigquery_credentials")) # This line is important to authenticate to bigquery services.

 ### Use in shiny

 ui <- fluidPage(
   tags$h2("My secure application"),
   verbatimTextOutput("auth_output")
 )

 # Wrap your UI with secure_app
 ui <- secure_app(ui, enable_admin = TRUE)

server <- function(input, output, session) {

  # call the server part
  # check_credentials returns a function to authenticate users

  res_auth <- secure_server(
    check_credentials = check_credentials(
      db = "bq_config.yml",
    )
  )

  observe({

    if(is.null(input$shinymanager_where) || (!is.null(input$shinymanager_where) && input$shinymanager_where %in% "application")){

      output$auth_output <- renderPrint({
        reactiveValuesToList(res_auth)
      })

      observe({
        print(input$shinymanager_where)
        print(input$shinymanager_language)
      }) 

    }

  })

  # your classic server logic

  }

shinyApp(ui, server)

Finally the output of this is the mesages from the DBI functions that read the tables or run the queries and the error logs$date >= input$overview_period[1] & ... must be size 1 or 1, not 0`.

My guess:

About all the calls to the database. Calling the database through DBI functionality depends on the speed of the API's that handle the conexion to bigquery. In this case, this persistant calling occurs allways even when the app is not in the admin panel. So, is there a way to prevent this calls to the data bases for the pwd_mngt and logs tables if the app is not at the admin panel.
About the error logs$date >= input$overview_period[1] & ... must be size 1 or 1, not 0. This error happens in the logs function of the modules-logs.R script in this repository, in the observer at lines from 174 to 185 (line 177 exactly). My guess of the cause of the error is that at the begining of lunch of the app the input overview_period is set to NULL or something like that. Also, the calls to the db (bigquery API in this case) takes a couple of seconds to run and this might be a reason why the input overview_period is set to null and never updates to the current date(If I understood correctly the code, the update of this input occurs after this observer). Then, the call input$overview_period[1] raised an error because a null object of length 0. Is it possible to add some validation to set the overview_period input to the current date when the input is null?

Thanks in advance for your comments and also thank you so much for this magnificent package.

Regards

Enrique

P.D. Because of security reason, I can not share the environmental variables and bigquery credentials.

datastorm-open / shinymanager

Issue: FEAT - External SQL DB with Bigquery - Can't subset rows with `logs$date >= input$overview_period[1] & ...` #181