loadDB.R

#####
#
# Import SIMO Results (SQLite-Databases) in R environment
# CB
# 2019-07-12
#
#####



## load libraries
library(RSQLite)
library(tidyr)
library(dplyr)



### Select the columns that should be importat from the table UNIT (which was created before via a querry - script structure_SIMO_rslDB)
# An overview on all available outcomes can be found under: params/Overview_outcomes_SIMO
columns <-  paste0("id, 
                   year, 
                   branch, 
                   branch_desc, 
                   branching_group,
                   Age,
                   area,
                   cash_flow, 
                   V_total_deadwood, 
                   BA, 
                   V, 
                   N, 
                   H_dom, 
                   D_gm, 
                   Harvested_V,
                   Biomass,
                   income_biomass,
                   CARBON_STORAGE")


# Variant 1: create a single dataframe for each SQL Database (selected columns of table UNIT)
# Dataframes are given the names of the DB and column "test" is added with the name of the DB

for (name in db_names){
  
  db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/simulated_without_", name ,"_rsu.db"))
  rsl  <- dbGetQuery( db, paste0("select ", columns, " from UNIT"))
  rsl$gpkg <- name
  dbDisconnect(db)
  
  rsl <- rsl %>% 
    left_join(regime, by = "branching_group", all.x = TRUE)
  
  write.table(rsl, paste0(path, "output/rsl_",name,".csv" ), sep = ";", row.names = F, col.names = TRUE)
  
  # assign( paste("rsl",name, sep="_"), rsl)
  rm(db, rsl)
  
}


# Variant 2: create one hughe dataframe combining all SQL databases (selected columns of table UNIT)
# Different DBs are indicated by an additional column "test"

rslt <- NULL

for (name in db_names){
  db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/simulated_without_", name ,"_rsu.db"))
  rsl  <- dbGetQuery( db, paste0("select ", columns, " from UNIT"))
  rsl$gpkg <- name
  dbDisconnect(db)
  rslt <- rbind(rslt, rsl)
  rm(db, rsl) 
}  

rslt <- rslt %>% 
  left_join(regime, by= "branching_group", all.X = TRUE)

write.table(rslt, paste0(path, "output/rslt_all.csv" ), sep = ";", row.names = F, col.names = TRUE)








# ##### Getting all Colnames from a SQL querry
# 
# # was used to create List of SIMO output -> What indicators can be calculate for ES
# # For comparison between countries and 
# 
# db <- dbConnect(dbDriver("SQLite"), dbname = paste0(path,"input_data/JENNI.db"))
# rsl  <- dbGetQuery(db, 'select * from UNIT')
# dbDisconnect(db)
# 
# colnames_FBE <- colnames(rsl) 
# noquote(colnames_FBE)
# write.table(colnames_FBE, paste0(path,"temp/colnames_FBE.csv"), row.names = FALSE)