当SQL数据库更改时，如何使renderUI更改？

Question

library(shiny)
library(shinyWidgets)
library(miniUI)
library(shinymanager)
library(RMariaDB)
library(DBI)

inactivity <- "function idleTimer() {
var t = setTimeout(logout, 120000);
window.onmousemove = resetTimer; // catches mouse movements
window.onmousedown = resetTimer; // catches mouse movements
window.onclick = resetTimer;     // catches mouse clicks
window.onscroll = resetTimer;    // catches scrolling
window.onkeypress = resetTimer;  //catches keyboard actions

function logout() {
window.close();  //close the window
}

function resetTimer() {
clearTimeout(t);
t = setTimeout(logout, 120000);  // time is in milliseconds (1000 is 1 second)
}
}
idleTimer();"

# data.frame with credentials info
credentials <- data.frame(
  user = c("1", "fanny", "victor", "benoit"),
  password = c("1", "azerty", "12345", "azerty"),
   stringsAsFactors = FALSE
)
ui <- secure_app(head_auth = tags$script(inactivity), miniPage( 
  gadgetTitleBar("Welcome!"),
  miniTabstripPanel(
    miniTabPanel("Test", icon = icon("truck"),
                 
                   h2(" "),
                 uiOutput("wq_print"),
                 actionButton("finish", "Finish!", class = "btn btn-primary")#btn btn-primary btn-lg btn-success
                 
    )
    
) ) )

server <- function(input, output,session) {
  
  result_auth <- secure_server(check_credentials = check_credentials(credentials))
  
  output$res_auth <- renderPrint({
    reactiveValuesToList(result_auth)
  })
  id=1
localuserpassword="MYPASSWORD"

con=dbConnect(RMariaDB::MariaDB(), user='USER_NAME', password=localuserpassword, dbname='DBNAME', host='localhost')
query <-  function(...) dbGetQuery(con, ...) 

  wq = data.frame()
  values <- reactiveValues()
  values$df <- data.frame()

   ##### the problem is here -------------------------------------------################

  
   a <- reactive({ paste("SELECT COL1  FROM TABLE where id = ", id, sep="") })
  
   observe({
      wq <- reactive({  query( a() ) })
      
      output$wq_print <- renderUI( { h1(wq()$COL1,align = "center") } )
      
       })
  
   ##### the problem is above-------------------------------------------################
    
  id=1
  localuserpassword <- "MYPASSWORD"
  storiesDb <- dbConnect(RMariaDB::MariaDB(), user='USER_NAME', password=localuserpassword, dbname='DBNAME', host='localhost')
  querysel=paste("select COL1 from TABLE where id ='",id,"'",sep = ''  )
  rs = dbSendQuery(storiesDb,querysel)
  dbClearResult(rs)
  dbDisconnect(storiesDb)
     
  observeEvent(input$finish,{
    confirmSweetAlert(
      session = session,
      inputId = "Confirm",
      type = "question",
      title = "Do you want to confirm?",
      danger_mode = F,
      closeOnClickOutside = T,
      showCloseButton =T
    )
    observeEvent(input$Confirm, {
      if(isTRUE(input$Confirm)){
        Free="Free"
        localuserpassword="MYPASSWORD"
        storiesDb <- dbConnect(RMariaDB::MariaDB(), user='USER_NAME', password=localuserpassword, dbname='DBNAME', host='localhost')
        query = paste("update TABLE set COL1= '",Free,"' where id ='",id,"' ",sep = '')
        rs = dbSendQuery(storiesDb,query)
        }
      
    } )
    
  })
  }
# Run the app ----
shinyApp(ui = ui, server = server)

当SQL数据库更改时，如何使renderUI更改？由于上面的代码，只有当我重新启动应用程序，并希望它更新时，有变化，更新。代码问题出现在"#####“中

我相信，这个问题的答案将为R+Shiny打开许多可能性。

Answer 1

您的代码的问题(除了不可复制-请检查How to make a great R reproducible example)是您的reactives没有触发，因为他们看到的输入没有改变：

a <- reactive({ paste("SELECT COL1  FROM TABLE where id = ", id, sep="") })

此反应性将触发一次(当id更改时)，但不会再次触发(因为id不会更改)。因此：

wq <- reactive({  query( a() ) })

不会触发，因为a保持不变。

你想要达到的目标并不是一清二楚。您想让renderUI在有新id时触发吗？在这种情况下，您必须对用户输入或其他任何内容进行id响应。

如果希望在数据库更改时触发renderUI，则可以使用reactivePoll，在这种情况下可以使用reactivePoll。

您将定义一个相对便宜的check函数，以确定是否对数据库进行了更改(例如，SELECT COUNT(*) FROM TABLE)。检查函数是定期调用的(如intervalMillis提供的那样)，每当此值发生变化时，调用valueFunc，在其中执行真正的数据库查询(假定在资源上更重)。

或者，您也可以使用以下内容：

get_data <- reactive({
 invalidateLater(1000) # tell r to invalidate this input every 1000 millisecs
 # your query
 dbGetQuery(con, "SELECT * FROM TABLE WHERE id = 1")
}) 

但是这将每毫秒对数据库进行一次查询，如果您的查询相当繁重，这可能会造成过度的损失。