R/CreateMRIDatabase.R

In dmenne/dmrixml: Zuerich MRI-XML Support and Database Functions

##' @title Creating MRI Database tables in sample database
##' @description
##' \code{CreateMRIDatabase} creates a sample database in SQLServer
##' using a script
##'
##'
##' @name CreateMRIDatabase
##' @param channel the ROCDBC channel
##' @param sqlfile the file to use to create the database structure. Defaults to
##' CreateMRIDatabase.sql in extdata directory.
##' @param drop should existing tables be dropped?
##' @import RODBC
##' @export CreateMRIDatabase
##' @author Dieter Menne <dieter.menne@@menne-biomed.de>

CreateMRIDatabase = function(channel,sqlfile = NULL, drop = FALSE) {
  if (class(channel) != "RODBC")
    stop("Invalid channel in CreateMRIDatabase")
  if (is.null(sqlfile))
    sqlfile =
      system.file("extdata", "CreateMRIDatabase.sql", package = "Dmrixml")
  if (!file.exists(sqlfile))
    stop(str_c("SQL file <",sqlfile,"> to create database does not exist."))
  sql = paste(readLines(sqlfile,warn = FALSE),collapse = "\n")
  # Some protection against reading wrong encoding
  created = sum(str_count(sql,"CREATE TABLE"))
  if (created < 4)
    stop(
      str_c(
        "It is expected that at least 4 tables should be created,
        but only ",created, " CREATE statement where found"
      )
      )
  if (drop)
    SafeDropTables(channel, c("voxel","slice","scan","record"))
  ExcecuteSqlScript(channel,sql)
}

ExcecuteSqlScript = function(channel,sql) {
  # ODBC does not like "GO", so we have to do it step by step
  sqlGo = str_split(sql,"\nGO")[[1]]
  lapply(sqlGo,function(sql1) {
    if (str_trim(sql1) != "") {
      q = sqlQuery(channel,sql1)
      if (length(q) > 0)
        stop("Error executing SQL\n ",paste(q,collapse = "\n"))
      invisible(NULL)
    }
  })
  invisible(NULL)
}

SafeDropTables = function(channel, tables) {
  dummy = lapply(tables,function(x)
    sqlQuery(
      channel, sprintf(
        "IF EXISTS(SELECT * FROM
        INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '%s')
        DROP TABLE %s",x[1],x[1]
    )
      ))
  invisible(NULL)
}