R/mod_database.R

In mx-serverstats/server_stats_web_statistics: Server Stats

#' database UI Function
#'
#' @description A shiny Module.
#'
#' @param id,input,output,session Internal parameters for {shiny}.
#'
#' @import DBI
#' @import pool
#'
#' @noRd
#'
#' @importFrom shiny NS tagList
mod_database_ui <- function(id) {
  ns <- NS(id)
}

#' database Server Functions
#'
#' @noRd
mod_database_server <- function(id) {
  moduleServer(id, function(input, output, session) {
    ns <- session$ns
    
    
    synapse_data <- get_synapse_data()
    
    list(
      users_by_room = reactive(synapse_data$users_by_room),
      servers_by_room = reactive(synapse_data$servers_by_room),
      users = reactive(synapse_data$users)
    )
  })
}

pool <- function() {
  dw <- get_golem_config("synapse")
  
  dbPool(
    drv = RPostgres::Postgres(),
    dbname = dw$dbname,
    host = dw$dbhost,
    user = dw$dbuser,
    password = dw$dbpwd,
    minSize = 10,
    idleTimeout = 3600000  # one hour
  )
}

get_synapse_data <- function() {
  current_pool <- pool()
  users_by_room_query <-
    paste0(
      "SELECT room_id, COUNT(state_key) FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND room_id IN (SELECT room_id FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND state_key = '@server_stats:nordgedanken.dev') GROUP BY room_id;"
    )
  
  servers_by_room_query <-
    paste0(
      "SELECT COUNT(DISTINCT split_part(state_key, ':', 2)), room_id FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND room_id IN (SELECT room_id FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND state_key = '@server_stats:nordgedanken.dev') GROUP BY room_id;"
    )
  servers_by_room <- dbGetQuery(current_pool, servers_by_room_query)
  servers_by_room <-
    transform(servers_by_room, count = as.numeric(count))
  
  users_query <-
    paste0(
      "SELECT DISTINCT state_key FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND room_id IN (SELECT room_id FROM current_state_events WHERE membership = 'join' AND type = 'm.room.member' AND state_key = '@server_stats:nordgedanken.dev');"
    )
  device_names_query <-
    paste0(
      "select content::json->>'device_display_name' AS displayname, COUNT(*) AS count from device_lists_remote_cache GROUP BY displayname ORDER BY count DESC;"
    )
  return(
    list(
      users_by_room = dbGetQuery(current_pool, users_by_room_query),
      servers_by_room = servers_by_room,
      users = dbGetQuery(current_pool, users_query)
    )
  )
}

## To be copied in the UI
# mod_database_ui("database_ui_1")

## To be copied in the server
# mod_database_server("database_ui_1")