In KateMMiller/forestNETN: Package for importing, joining and querying NETN forest database tables

knitr::opts_chunk$set(echo = FALSE)

Testing code to document NETN Forest SQL database. Still a WIP.

library(odbc)
library(DBI)
library(tidyverse)
#library(dbplyr)

# Connect to database
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 #Server = "INP2300VTSQL16\\IRMADEV1",
                 Server = "localhost\\SQLEXPRESS",
                 Database = "NETN_Forest",
                 Trusted_Connection = 'True'
                  )

# List tables in ANALYSIS schema
view_list <- dbListTables(con, schema = "ANALYSIS")
view_list

# Import ANALYSIS views SQL code 
sql_list <- unique(c(paste0("EXEC sp_helptext 'ANALYSIS.", view_list, "'")))

sql_code <- lapply(seq_along(view_list),
                   function(x){code = dbFetch(dbSendQuery(con, sql_list[[x]]))}) %>%
  setNames(view_list)


views <- lapply(seq_along(view_list),
                function(x){dbListFields(conn = con, name = view_list[[x]], schema = "ANALYSIS") %>%
                            setNames(view_list[[x]]) %>% unlist() %>% data.frame()
                }) %>% setNames(view_list)

Still working out how to print in a readable format. Currently just printing raw text.

comb_output <- lapply(seq_along(view_list), function(x){
                 print(paste0("SQL Code for View: ", names(views[x])))
                 cat(sql_code[[x]]$Text, sep = "\t\r\n")
                 print(paste0("Fields for View : ", names(views[x])))
                 print(data.frame(field = t(matrix(unlist(views[[x]]), 
                   nrow = length(views[[x]]), byrow = TRUE))))
                 cat("\n")
            }
)

`r xfun::file_string("NETN_QuadSpecies_Wide.sql")'

dbDisconnect(con)
getwd()

KateMMiller/forestNETN documentation built on April 1, 2024, 11:49 p.m.