BDAccess/Docs

• sqlDropLast
• sqlInitizalize
• sqlBackUp
• sqlShowSamples
• sqlGenOVID
• sqlWriteTemp
• sqlSincBD
• sqlMultiSamples

---

sqlDropLast

Description

Removes from Database the last (or the amount specified) entry.

Usage

sqlDropLast(conn, tablename, droplast=1, dbtype=NULL)

Arguments

Argument	Description
conn	connection handle returned by odbcConnect.
tablename	character: a database table name accessible from the connected DSN.
droplast	the amount of lines to be removed from the table strating from tail. By default, it removes only 1 line.
dbtype	used to manually specify the database type. It defaults to NULL and the type is deduced.

Details

Removes from Database the last (or the amount specified) entry.

Value

Invisibly for success (and failures cause errors).

Examples

dta<-odbcConnect("test")
sqlDropLast(dta, "TableTest")

Function

sqlDropLast<-function(conn, tablename, droplast=1,dbtype=NULL){
  if(sqlTables(conn) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){dbtype<-"UM"}
  if(sqlTables(conn) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){dbtype<-"OV"}
  
  table<-sqlFetch(conn, tablename)
  table<-table[1:(nrow(table)-droplast),]
  
  if (dbtype == "OV"){sqlSave(conn, table, tablename = tablename, safer = F)}
  if (dbtype == "UM"){
    sqlDrop(conn, tablename)
    sqlSave(conn, table, tablename = tablename, safer = F)}
}

---

sqlInitizalize

Description

Loads required libraries and gets the db location.

Usage

sqlInitialize()

Arguments

Argument	Description

Details

Loads required libraries and gets the db location from "ruta_database.R" file.

Value

Invisibly for success (and failures cause errors).

Examples

sqlInitialize()

Function

sqlInitialize<-function(){
  library(tidyverse)
  library(RODBC)
  library(openxlsx)
  
  ## Conexión a la base de datos
  source("ruta_database.R", encoding = "UTF-8")
}

---

sqlBackUp

Description

Creates a Back Up copy of the database.

Usage

sqlBackUp(dbfile=file,conn=dta,bu.dir=NULL)

Arguments

Argument	Description
dbfile	Database File location.
conn	connection handle returned by odbcConnect.
bu.dir	Directory under the DB file where the back up will be placed. It defaults to NULL and is deduced from conn database.

Details

Creates a Back Up copy of the database. It adds the date in front of the back up file.

Value

Invisibly for success (and failures cause errors).

Examples

sqlInitialize()
sqlBackUp()

Function

sqlBackUp<-function(dbfile=file,conn=dta,bu.dir=NULL){
  if(sqlTables(conn) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){bu.dir<-"BU_UM"}
  if(sqlTables(conn) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){bu.dir<-"BU_OVARIO"}
  
  db=strsplit(dbfile, "/")[[1]]%>% tail(n=1)
  bu_path<-gsub(db,bu.dir,dbfile)
  if (!dir.exists(bu_path)){
    dir.create(bu_path)
    print(paste0("Back Up directory ", bu_path, " created"))
  }
  cp_bu<-paste0(bu_path, "/", format(Sys.time(), format="%Y%m%d"),"-",db)
  file.copy(dbfile, cp_bu)
}

---

sqlShowSamples

Description

Shows if there are already samples from the specified NHCs.

Usage

sqlShowSamples(conn=dta, nhcs=nhc.test, verb=F, dbtype=NULL)

Arguments

Argument	Description
conn	connection handle returned by odbcConnect.
nhcs	Character vector with the NHCs to test.
verb	Verbose: if TRUE, all the columns from "SAMPLES" table are printed.
dbtype	used to manually specify the database type. It defaults to NULL and the type is deduced.

Details

Takes the NHCs listed in the nhcs vector and checks if there are already samples from those patients.

Value

A data.frame with information about the patients.

Examples

dta<-odbcConnect("test")
nhc.test<-c("XXXXXXXX","XXXXXXX")
sqlShowSamples()

Function

sqlShowSamples<-function(conn=dta, nhcs=nhc.test, verb=F, dbtype=NULL){
  if(sqlTables(dta) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){dbtype<-"UM"}
  if(sqlTables(dta) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){dbtype<-"OV"}
  
  if (dbtype == "OV"){
    db<-c("dbtables"="SAMPLES", "dbcode"="OVID", "dbsamples"="samples")
    query<-paste0("SELECT O.NHC,S.* FROM ",db["dbtables"]," S INNER JOIN ",db["dbcode"]," O ON O.",db["dbcode"],"=S.",db["dbcode"])
  }
  if (dbtype == "UM"){
    db<-c("dbtables"="MUESTRAS", "dbcode"="UMID", "dbsamples"="CODIGO")
    query<-paste0("SELECT O.NHC,S.* FROM ",db["dbtables"]," S INNER JOIN ",db["dbcode"]," O ON O.",db["dbcode"],"=S.",db["dbcode"])
  }
  if (nrow(sqlQuery(conn, query) %>% filter(NHC %in% nhcs)) == 0){
    return("No hay muestras de ningún paciente.")
  }
  if (isFALSE(verb)){
    sqlQuery(conn, query) %>% filter(NHC %in% nhcs) %>% 
    group_by(NHC,UQ(rlang::sym(db["dbcode"]))) %>% summarise(Samples=length(UQ(rlang::sym(db["dbsamples"]))), Names=paste0(UQ(rlang::sym(db["dbsamples"])), collapse = ";")) %>% 
      merge(data.frame(NHC=nhcs),all=T) %>% mutate(NHC=factor(NHC,levels = nhcs)) %>% arrange(NHC)
  }else{
    sqlQuery(conn, query) %>% filter(NHC %in% nhcs)
  }
}

---

sqlGenOVID

Description

Generates new consecutive OVID or UMID code for the patients that are not found in the DB.

Usage

sqlGenOVID(conn=dta, nhcs=nhc.test, verb=T, sinc=F, dbtype=NULL)

Arguments

Argument	Description
conn	connection handle returned by odbcConnect.
nhcs	Character vector with the NHCs to test.
verb	Verbose: if TRUE (default), it prints the data.frame with the generated OVID codes.
sinc	If TRUE (default is FALSE for security), it adds the new entries to the "OVID" table in the DB.
dbtype	used to manually specify the database type. It defaults to NULL and the type is deduced.

Details

Generates new consecutive OVID or UMID code for the patients that are not found in the DB.

Value

If verb is TRUE, it returns a data.frame.

Examples

dta<-odbcConnect("test")
nhc.test<-c("XXXXXXXX","XXXXXXX")
sqlGenOVID(sinc=T)

Function

sqlGenOVID<-function(conn=dta, nhcs=nhc.test, verb=T, sinc=F, dbtype=NULL){
  if(sqlTables(dta) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){dbtype<-"UM"}
  if(sqlTables(dta) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){dbtype<-"OV"}
  
  if (dbtype == "OV"){
    db<-c("dbcode"="OVID")
  }
  if (dbtype == "UM"){
    db<-c("dbcode"="UMID")
  }
  
  dbid<-sqlFetch(conn,db["dbcode"])
  
  new.nhc<-nhcs[!nhcs %in% dbid$NHC]
  next.num<-gsub(db["dbcode"],"",dbid[,db["dbcode"]]) %>% as.numeric %>% max(na.rm=T)+1
  last.num<-next.num+(length(new.nhc)-1)
  newtab<-data.frame("NHC"=new.nhc, "ID"=sprintf("%s%04d",db["dbcode"],next.num:last.num)) %>% rename(!!db["dbcode"]:="ID")
  if(dbtype=="OV"){
    dbid<-rbind(dbid,newtab)
  }
  if(dbtype=="UM"){
    dbid<-merge(dbid, newtab, all=T) %>% select(Id,NHC,UMID) %>% arrange(Id)
    dbid$Id<-as.numeric(rownames(dbid))
    dbid$NHC<-as.numeric(dbid$NHC)
  }
  rownames(dbid)<-as.character(1:nrow(dbid))
  dbid<-filter(dbid, NHC %in% new.nhc) %>% mutate(NHC=as.character(NHC))
  
  if (sinc){
    ### !! Atención, esto cambia la base de datos:
    sqlSave(conn, dbid, tablename=db["dbcode"], append = T)
    print("La base ha sido actualizada.")
  }
  if (verb){
    return(dbid)
  }
}

---

sqlWriteTemp

Description

Fills the Query Template file with the OVID or UMID and OV or UM newly generated codes.

Usage

sqlWriteTemp(conn=dta, nhcs=nhc.test, file="queryOV.xlsx", samples.mod=T, clinics.mod=T, dbtype=NULL)

Arguments

Argument	Description
conn	connection handle returned by odbcConnect.
nhcs	Character vector with the NHCs to test.
file	Template file that will be used to interact with the DB.
samples.mod	If TRUE (default), it fills the "samples" template sheet.
clinics.mod	If TRUE (default), it fills the "CLINICS" template sheet.
dbtype	used to manually specify the database type. It defaults to NULL and the type is deduced.

Details

Fills the Query Template file with the OVID and OV or UMID and UM newly generated codes. It is required that the DB has been updated with the sqlGenOVID function. It replaces previous content in the template file sheets that are filled. In the case of "CLINICS" table, if there were already an entry in the DB for that OVID/UMID code, the template file is filled with that information.

Value

Invisibly for success (and failures cause errors).

Examples

dta<-odbcConnect("test")
nhc.test<-c("XXXXXXXX","XXXXXXX")
sqlGenOVID(sinc=T)
sqlWriteTemp()

Function

sqlWriteTemp<-function(conn=dta, nhcs=nhc.test, file="queryOV.xlsx", samples.mod=T, clinics.mod=T, dbtype=NULL){
  if(sqlTables(dta) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){dbtype<-"UM"}
  if(sqlTables(dta) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){dbtype<-"OV"}
  
  if (dbtype=="OV"){
    upd.ovid<-sqlFetch(conn, "OVID") %>% filter(NHC %in% nhcs)
    if (samples.mod){
      ## Generar código para las nuevas muestras
      samples<-sqlFetch(conn, "SAMPLES")
      if(sum(grepl(paste0("OV",Sys.time() %>% format("%y")), samples$samples)) > 0){
        next.samp<-gsub(paste0("OV",Sys.time() %>% format("%y")),"", samples$samples) %>% as.numeric %>% max(na.rm=T)+1  
      }else{
        next.samp<-1
      }
      last.samp<-next.samp+(length(nhcs)-1)
      new.samp<-sprintf("OV%s%02d",Sys.time() %>% format("%y"),next.samp:last.samp)
      new.samp.df<-merge(sqlFetch(dta,"OVID") %>% merge(data.frame("NHC"=nhcs)), data.frame("NHC"=nhcs, "samples"=new.samp))
      samples.exp<-merge(samples %>% slice(0), new.samp.df %>% select(-NHC), all=T) %>% select(colnames(samples)) %>% arrange(samples)
    }
    
    if (clinics.mod){
      ## Importar los datos clínicos de pacientes existentes y generar nueva entrada par los nuevos
      upd.clinics<-sqlFetch(conn, "CLINICS")
      ovid.new<-sqlFetch(conn, "OVID") %>% filter(NHC %in% nhcs)
      upd.clinics<-merge(ovid.new,upd.clinics, all.x=T, by="OVID")
      upd.clinics$NHC<-as.character(upd.clinics$NHC)
      for (i in colnames(upd.clinics)[sapply(upd.clinics, lubridate::is.POSIXct)]){upd.clinics[,i]<-as.Date(upd.clinics[,i])}
    }
  
    ## Exportar tablas a la plantilla de entrada para su rellenado
    wb <- loadWorkbook(file)
    writeData(wb, "NHC", upd.ovid)
    if (samples.mod){writeData(wb,"samples",samples.exp)}
    if (clinics.mod){writeData(wb,"CLINICS",upd.clinics)}
    saveWorkbook(wb,file,overwrite = TRUE)
  }
  if (dbtype=="UM"){
    upd.umid<-sqlFetch(conn, "UMID") %>% filter(NHC %in% nhcs)
    if (samples.mod){
      ## Generar código para las nuevas muestras
      samples<-sqlFetch(conn, "MUESTRAS")
      if(sum(grepl(paste0("UM",Sys.time() %>% format("%y")), samples$CODIGO)) > 0){
        next.samp<-gsub(paste0("UM",Sys.time() %>% format("%y")),"", samples$CODIGO) %>% as.numeric %>% max(na.rm=T)+1  
      }else{
        next.samp<-1
      }
      last.samp<-next.samp+(length(nhcs)-1)
      new.samp<-sprintf("UM%s%02d",Sys.time() %>% format("%y"),next.samp:last.samp)
      new.samp.df<-merge(sqlFetch(dta,"UMID") %>% merge(data.frame("NHC"=nhcs)), data.frame("NHC"=nhcs, "CODIGO"=new.samp))
      samples.exp<-merge(samples %>% slice(0), new.samp.df %>% select(-NHC), all=T) %>% select(colnames(samples)) %>% arrange(CODIGO)
    }
    
    if (clinics.mod){
      ## Importar los datos clínicos de pacientes existentes y generar nueva entrada par los nuevos
      upd.clinics<-sqlFetch(conn, "CLINICOS")
      umid.new<-sqlFetch(conn, "UMID") %>% filter(NHC %in% nhcs)
      upd.clinics<-merge(umid.new,upd.clinics %>% select(-Id), all.x=T, by="UMID")
      upd.clinics$NHC<-as.character(upd.clinics$NHC)
      for (i in colnames(upd.clinics)[sapply(upd.clinics, lubridate::is.POSIXct)]){upd.clinics[,i]<-as.Date(upd.clinics[,i])}
    }
    
    ## Exportar tablas a la plantilla de entrada para su rellenado
    wb <- loadWorkbook(file)
    writeData(wb, "NHC", upd.umid)
    if (samples.mod){writeData(wb,"samples",samples.exp)}
    if (clinics.mod){writeData(wb,"CLINICS",upd.clinics)}
    saveWorkbook(wb,file,overwrite = TRUE)
  }
}

---

sqlSincBD

Description

Updates the DB with the information filled in the template file.

Usage

sqlSincBD(conn=dta, filetemp="queryOV.xlsx", sinc.samples=F, sinc.clinics=F, dbtype=NULL)

Arguments

Argument	Description
conn	connection handle returned by odbcConnect.
filetemp	Template file that will be used to interact with the DB.
sinc.samples	If TRUE (default is FALSE for security), it updates the SAMPLES table in the DB with the information in the "samples" template sheet.
clinics.mod	If TRUE (default is FALSE for security), it updates the CLINICS table in the DB with the information in the "CLINICS" template sheet.
dbtype	used to manually specify the database type. It defaults to NULL and the type is deduced.

Details

Updates the DB with the information filled in the template file. All the "samples" entries are added as new rows (as all samples are new even if the patient was already in the DB). The new patients included in the "CLINICS" sheet are introduced in the DB as new rows and the ones that were already there are modified in its previous row location.

Value

Invisibly for success (and failures cause errors).

Examples

dta<-odbcConnect("test")
nhc.test<-c("XXXXXXXX","XXXXXXX")
sqlGenOVID(sinc=T)
sqlWriteTemp()
sqlSincBD(sinc.samples=T, sinc.clinics=T)

Function

sqlSincBD<-function(conn=dta, filetemp="queryOV.xlsx", sinc.samples=F, sinc.clinics=F, dbtype=NULL){
  if(sqlTables(dta) %>% filter(TABLE_NAME == "UMID") %>% nrow > 0){dbtype<-"UM"}
  if(sqlTables(dta) %>% filter(TABLE_NAME == "OVID") %>% nrow > 0){dbtype<-"OV"}
  
  ## Añadir código de muestra nueva a la base de datos
  if (dbtype == "OV"){
    print("DB OV detectada")
    nsamples<-sqlFetch(conn, "SAMPLES") %>% nrow
    upd.samples<-read.xlsx(filetemp, sheet = "samples", detectDates = T)
    if (nrow(upd.samples) > 0){rownames(upd.samples)<-(nsamples+1):(nsamples+nrow(upd.samples)) %>% as.character}
    
    if (sinc.samples & nrow(upd.samples) > 0){
      upd.samples$IQ_date<-as.Date(upd.samples$IQ_date)
      upd.samples$TIL_date<-as.Date(upd.samples$TIL_date)
      ### !! Atención, esto cambia la base de datos:
      sqlSave(conn, upd.samples, tablename="SAMPLES", append = T, varTypes = c("IQ_date"="date","TIL_date"="date"))
      print("Tabla SAMPLES sincronizada.")
    }
  }
  if (dbtype == "UM"){
    print("DB UM detectada")
    nsamples<-sqlFetch(conn, "MUESTRAS") %>% nrow
    upd.samples<-read.xlsx(filetemp, sheet = "samples", detectDates = T)
    if (nrow(upd.samples) > 0){rownames(upd.samples)<-(nsamples+1):(nsamples+nrow(upd.samples)) %>% as.character}
    
    if (sinc.samples & nrow(upd.samples) > 0){
      upd.samples$FECHA_RECEPCION<-as.Date(upd.samples$FECHA_RECEPCION)
      upd.samples$TIPO<-as.character(upd.samples$TIPO)
      upd.samples$OBS<-as.character(upd.samples$OBS)
      ### !! Atención, esto cambia la base de datos:
      sqlSave(conn, upd.samples, tablename="MUESTRAS", append = T, varTypes = c("FECHA_RECEPCION"="Date"), rownames = F)
      print("Tabla MUESTRAS sincronizada.")
    }
  }
  
  ## Añadir datos clínicos modificados a la base de datos
  if (dbtype == "OV"){
    upd.clinics<-read.xlsx(filetemp, sheet = "CLINICS",detectDates = T)
    ovid.mod<-upd.clinics$OVID[upd.clinics$OVID %in% (sqlFetch(dta, "CLINICS") %>% pull(OVID))]
    rnames<-sqlFetch(conn, "CLINICS") %>% filter(OVID %in% ovid.mod) %>% rownames
    clinics.mod<-upd.clinics %>% filter(OVID %in% ovid.mod) %>% select(-NHC)
    rownames(clinics.mod)<-rnames
    
    ### !! Atención, esto cambia la base de datos:
    if (sinc.clinics){
      fechas<-colnames(clinics.mod)[grepl("DO|date", colnames(clinics.mod))]
      for (i in fechas){
        clinics.mod[,i]<-as.Date(clinics.mod[,i])
      }
      sqlUpdate(conn, clinics.mod,"CLINICS")
      print("Tabla CLINICS modificada.")
    }
  }
  if (dbtype == "UM"){
    upd.clinics<-read.xlsx(filetemp, sheet = "CLINICS",detectDates = T)
    umid.mod<-upd.clinics$UMID[upd.clinics$UMID %in% (sqlFetch(dta, "CLINICOS") %>% pull(UMID))]
    rnames<-sqlFetch(conn, "CLINICOS") %>% filter(UMID %in% umid.mod) %>% rownames
    clinics.mod<-upd.clinics %>% filter(UMID %in% umid.mod) %>% select(-NHC)
    rownames(clinics.mod)<-rnames
    
    ### !! Atención, esto cambia la base de datos:
    if (sinc.clinics){
      fechas<-colnames(clinics.mod)[grepl("date|MET_DX|DoB", colnames(clinics.mod), ignore.case = T)]
      for (i in fechas){
        clinics.mod[,i]<-as.Date(clinics.mod[,i])
      }
      sqlUpdate(conn, clinics.mod,"CLINICOS")
      print("Tabla CLINICOS modificada.")
    }
  }
  
  ## Añadir datos clínicos nuevos a la base de datos
  if (dbtype == "OV"){
    nsamples.clin<-sqlFetch(conn, "CLINICS") %>% nrow
    ovid.new<-upd.clinics$OVID[!upd.clinics$OVID %in% (sqlFetch(conn, "CLINICS") %>% pull(OVID))]
    clinics.new<-upd.clinics %>% filter(OVID %in% ovid.new) %>% select(-NHC)
    if (length(ovid.new) > 0){rownames(clinics.new)<-(nsamples.clin+1):(nsamples.clin+nrow(clinics.new)) %>% as.character}
    
    ### !! Atención, esto cambia la base de datos:
    if (sinc.clinics){
      fechas<-colnames(clinics.new)[grepl("DO|date", colnames(clinics.new))]
      varTypes<-rep("Date",length(fechas))
      names(varTypes)<-fechas
      for (i in fechas){
        clinics.new[,i]<-as.Date(clinics.new[,i])
      }
      sqlSave(conn, clinics.new, tablename="CLINICS", append = T, varTypes = varTypes)
      print("Tabla CLINICS sincronizada.")
    }
  }
  if (dbtype == "UM"){
    nsamples.clin<-sqlFetch(conn, "CLINICOS") %>% nrow
    umid.new<-upd.clinics$UMID[!upd.clinics$UMID %in% (sqlFetch(conn, "CLINICOS") %>% pull(UMID))]
    clinics.new<-upd.clinics %>% filter(UMID %in% umid.new) %>% select(-NHC)
    if (length(umid.new) > 0){rownames(clinics.new)<-(nsamples.clin+1):(nsamples.clin+nrow(clinics.new)) %>% as.character}
    
    ### !! Atención, esto cambia la base de datos:
    if (sinc.clinics){
      fechas<-colnames(clinics.new)[grepl("date|MET_DX|DoB", colnames(clinics.new), ignore.case = T)]
      varTypes<-rep("Date",length(fechas))
      names(varTypes)<-fechas
      for (i in fechas){
        clinics.new[,i]<-as.Date(clinics.new[,i])
      }
      sqlSave(conn, clinics.new, tablename="CLINICOS", append = T, varTypes = varTypes)
      print("Tabla CLINICOS sincronizada.")
    }
  }
}

---

sqlMultiSamples

Description

Prints a table compiling information about laboratory samples, scRNAseq samples and RNA/DNA samples.

Usage

sqlMultiSamples(kbl=F, NHC=F, full=F)

Arguments

Argument	Description
kbl	formats the output table with the kableEstra style. Defaults to F.
NHC	adds the NHC to the table in addition to the UMID. Defaults to F.
full	prints also the patients that doesn't appear in the MUESTRAS table. Defaults to F.

Details

Prints a table compiling information about laboratory samples, scRNAseq samples and RNA/DNA samples.

Value

A data.frame or a kableExtra table.

Examples

dta<-odbcConnect("test")
sqlMultiSamples()

Function

sqlMultiSamples<-function(kbl=F, NHC=F, full=F){
  query<-sqlQuery(dta, "SELECT U.UMID,U.NHC,M.FECHA_RECEPCION,M.TIPO,M.CODIGO,C.CODIGO,R.CODIGO,R.ESTADO 
         FROM ((UMID U
         LEFT OUTER JOIN MUESTRAS M ON U.UMID=M.UMID)
         LEFT OUTER JOIN CNAG C ON M.CODIGO=C.CODIGO)
         LEFT OUTER JOIN RNADNA R ON M.CODIGO=R.CODIGO") %>% rename("CNAG"="CODIGO.1","RNADNA"="CODIGO.2")
  
  if (full==F){query<- query %>% filter(!is.na(CODIGO))}
  if (NHC==F){query<- query %>% select(-NHC)}
  
  query<-query %>% mutate(
    CNAG=case_when(!is.na(CNAG)~"X",TRUE~""),
    RNADNA=case_when((!is.na(RNADNA) & (ESTADO=="ENV"))~"X",
                     (!is.na(RNADNA) & (is.na(ESTADO)))~".",
                     TRUE~"")
  ) %>% select(-ESTADO)
  if (kbl==T){
    query %>% kableExtra::kbl() %>% kableExtra::kable_styling(full_width = F, bootstrap_options = c("striped"))
  }else{
    return(query)
    }
}