R/04_DB_infos.R

In b-becker/eatGADS: Normalizing, Subsetting and Reshaping Hierarchical Data

#### Viewing Interface to data base
#############################################################################
#' Variable names in relational db.
#'
#' Function to get the variable names included in the relational data base.
#'
#' Get names of all variables that, sorted by optionally as a list, sorted by
#' subdata frame.
#'
#'@param filePath Path of the existing db file.
#'
#'@return Returns a named list of character vectors with the variables names included in a data table.
#'
#'@examples
#'# See vignette.
#'
#'
dbNames <- function(filePath, includeMeta = FALSE) {
  # check input
  check_dbPath(dbPath = filePath)

  # Establish Connection, disconnect when function exits
  con <- dbConnect(RSQLite::SQLite(), dbname = filePath)
  on.exit(dbDisconnect(con))

  # get data table names
  dtNames <- dbListTables(con)
  # get all variable names in these tables
  nameList <- lapply(dtNames, dbListFields, conn = con)
  names(nameList) <- dtNames

  # drop information about meta data tables
  if(!includeMeta) nameList$metaInformation <- nameList$Labels <- NULL

  nameList
}


#' Keys in relational db.
#'
#' Function to get the primary and foreign keys in the relational data base.
#'
#' Data in a relational data base are indexed by primary and foreign keys. Primary keys are unique indentifiers
#' inside the same data table. Foreign keys reference (link) to other data tables. This function returns the
#' key structure of a relational data base
#'
#'@param filePath Path of the existing db file.
#'
#'@return Returns a list named as the data tables in the db. Each elements contains a list with the primary key, the
#' other referenced data table and the foreign key.
#'
#'@examples
#'# See vignette.
#'
#'
dbKeys <- function(filePath, includeMeta = FALSE) {
  # check input
  check_dbPath(dbPath = filePath)

  # Establish Connection, disconnect when function exits
  con <- dbConnect(RSQLite::SQLite(), dbname = filePath)
  on.exit(dbDisconnect(con))

  # get db names
  dtNames <- names(dbNames(filePath, includeMeta = includeMeta))

  ## Primary keys
  # create query
  pkQueries <- paste("PRAGMA table_info(", dtNames, ")")
  # execute query and transform info
  pk_table <- lapply(pkQueries, dbGetQuery, conn = con)
  pk_list <- lapply(pk_table, extract_PKs)

  ## foreign keys
  fkQueries <- paste("PRAGMA foreign_key_list(", dtNames, ")")
  # execute query and transform info
  fk_table <- lapply(fkQueries, dbGetQuery, conn = con)
  fk_list <- lapply(fk_table, extract_FKs)

  ## structure and name output
  names(pk_list) <- dtNames
  names(fk_list) <- dtNames

  list(pkList = pk_list, fkList = fk_list)
}

### Check dbpath ---------------------------------------------------------
check_dbPath <- function(dbPath) {
  if(!file.exists(dbPath)){
    stop(paste(dbPath, " is not a valid path to a data base"))
  }
}



### Extract output ---------------------------------------------------------
## extract Primary Keys from SCHEMA output
extract_PKs <- function(table_info) {
  table_info[table_info$pk != 0, "name"]
}

## extract foreign Keys from SCHEMA output
extract_FKs <- function(table_info) {
  if(nrow(table_info) == 0) return(list(References = NULL, Keys = NULL))
  ref <- unique(table_info$table)
  if(length(ref) > 1) stop("Foreign keys for more than 1 table defined, check data base creation.")
  if(!identical(table_info$from, table_info$to)) stop("Foreign and primary key have different names, error was made during creation of data base!")
  keys <- table_info$from
  list(References = ref, Keys = keys)
}



#' Labels from relational db.
#'
#' Function to get the variable and value labels of the data stored in the relational data base.
#'
#' Variable, value and missing labels as stored in the original SPSS-files converted to long format. Value labels of
#' factors from R-Files
#'
#'
#'
#'@param filePath Path of the existing db file.
#'
#'@return Returns a long format data frame including variable names, labels, values, value labels and missing labels.
#'
#'@examples
#'# See vignette.
#'
#'
dbLabels <- function(filePath) {
  # check input
  check_dbPath(dbPath = filePath)
  df_name <- "Labels"

  # Establish Connection, disconnect when function exits
  con <- dbConnect(RSQLite::SQLite(), dbname = filePath)
  on.exit(dbDisconnect(con))

  # extract single table from data base
  out <- dbReadTable(conn = con, name = df_name)

  out
}

### notion: can be expanded to other single data-tables!