WellHandle.R

#' WellHandle
#' @description Object for handling database operations related to the storage and 
#' retrieval of Well objects.
#' @field dbConnection A dbConnection object
#' @export
WellHandle <- R6Class(
  classname = "WellHandle",
  lock_class = TRUE,
  public = list(
    dbConnection = NULL,
    #' @description Create an object for handling database operations related to 
    #' the storage and retrieval of Well objects.
    #' @param dbConnection A dbConnection object
    initialize = function(dbConnection = NA) {
      self$dbConnection <- dbConnection
    },
    #' @description Retrieve a Well object using its database ID
    #' @param plateID Database ID of the Plate object the well belongs to
    #' @param position Position of the well in the plate
    #' @return A Well object
    get_by_id = function(plateID, position) {
      query <- "SELECT * FROM Well WHERE Plate_ID = ? and position = ?"
      df <- self$dbConnection$get_data(query, list(plateID, position))
      if(length(df$Plate_ID)>0) {
        well <- Well$new(dbConnection = self$dbConnection,
                         ID = df$Plate_ID, position = df$Well_position, 
                         label = df$label, temperature = df$temperature)
        return(well)
      } else { return(NULL) }
    },
    #' @description Retrieve all wells belonging to the same plate
    #' @param plate A Plate object
    #' @return List of Well objects
    get_all_by_plate = function(plate) {
      query <- "SELECT * FROM Well WHERE Plate_ID = ?"
      data <- self$dbConnection$get_data(query, list(plate$ID))
      if(length(data$Plate_ID)>0) {
        wells <- list()
        df.list <- split(data, data$position)
        for(df in df.list) {
          w <- Well$new(dbConnection = self$dbConnection,
                        plate_ID = df$Plate_ID, position = df$position,
                        label = df$label, temperature = df$temperature)
          wells <- c(wells, w)
        }
        return(wells)
      } else { return(NULL) }
    },
    #' @description Retrieve replicates of the given well. A replicate is a well
    #' from the same screen and with same samples and compounds (at the same 
    #' dose and unit).
    #' @param well A Well object
    #' @return List of Well objects
    get_replicates = function(well) {
      compoundID <- well$compounds[[1]]$ID
      dose <- well$compounds[[1]]$dose
      unit <- well$compounds[[1]]$unit
      sampleID <- well$samples[[1]]$ID
      screenID <- well$plate$screen$ID
      
      # Note: This query only works to identify replicates if there's only one 
      # compound and one sample per well
      query <- "SELECT W.* 
                FROM Well AS W, 
                     Well_has_Sample AS WhS, 
                     Well_has_Compound AS WhC
                WHERE WhC.Compound_ID = ? AND
                      WhC.dose = ? AND 
                      WhC.unit = ? AND 
                      WhS.Sample_ID = ? AND 
                      WhC.Plate_ID = WhS.Plate_ID AND
                      WhC.Well_position = WhS.Well_position AND
                      WhC.Plate_ID = W.Plate_ID AND
                      WhC.Well_position = W.position AND
                      W.Plate_ID IN (SELECT Plate_ID
                                     FROM Screen_has_Plate
                                     WHERE Screen_ID = ?)"
      data <- self$dbConnection$get_data(query, list(compoundID,
                                                     dose,
                                                     unit,
                                                     sampleID,
                                                     screenID))
      if(length(data$Plate_ID)>0) {
        wells <- list()
        df.list <- split(data, paste0(data$Plate_ID, data$position))
        for(df in df.list) {
          w <- Well$new(dbConnection = self$dbConnection,
                        plate_ID = df$Plate_ID, position = df$position,
                        label = df$label, temperature = df$temperature)
          wells <- c(wells, w)
        }
        return(wells)
      } else { return(NULL) }
    },
    #' @description Retrieve all wells where a compound is found
    #' @param compound A Compound object
    #' @param screen (optional) a Screen object to limit to wells from this screen
    #' @param dose (optional) limit to wells with this dose. Unit must also be specified.
    #' @param unit (optional) unit for the dose value.
    #' @return List of Well objects
    get_all_by_compound = function(compound, screen = NULL, dose = NULL, unit = NULL) {
      if(is.null(screen)) {
        if(is.null(dose) || is.null(unit)) {
          query <- "SELECT W.* FROM Well AS W, Well_has_Compound AS WhC
                    WHERE WhC.Compound_ID = ?
                    AND WhC.Plate_ID = W.Plate_ID
                    AND WhC.Well_position = W.position"
          data <- self$dbConnection$get_data(query, list(compound$ID))
        } else {
          query <- "SELECT W.* FROM Well AS W, Well_has_Compound AS WhC
                    WHERE WhC.Compound_ID = ?
                    AND WhC.Plate_ID = W.Plate_ID
                    AND WhC.Well_position = W.position
                    AND WhC.dose = ? AND lower(WhC.unit) = ?"
          data <- self$dbConnection$get_data(query, list(compound$ID, dose, tolower(unit)))
        }
      } else {
        if(is.null(dose) || is.null(unit)) {
          query <- "SELECT W.* FROM Well AS W, Well_has_Compound AS WhC, Screen_has_Plate AS ShP
                    WHERE WhC.Compound_ID = ? AND ShP.Screen_ID = ?
                    AND ShP.Plate_ID = WhC.Plate_ID
                    AND WhC.Plate_ID = W.Plate_ID
                    AND WhC.Well_position = W.position" 
          data <- self$dbConnection$get_data(query, list(compound$ID, screen$ID))
        } else {
          query <- "SELECT W.* FROM Well AS W, Well_has_Compound AS WhC, Screen_has_Plate AS ShP
                    WHERE WhC.Compound_ID = ? AND ShP.Screen_ID = ?
                    AND ShP.Plate_ID = WhC.Plate_ID
                    AND WhC.Plate_ID = W.Plate_ID
                    AND WhC.Well_position = W.position
                    AND WhC.dose = ? AND lower(WhC.unit) = ?" 
          data <- self$dbConnection$get_data(query, list(compound$ID, screen$ID, dose, tower(unit))
  )      }
      }
      if(length(data$position)>0) {
        wells <- list()
        df.list <- split(data, paste0(data$Plate_ID, data$position))
        for(df in df.list) {
          w <- Well$new(dbConnection = self$dbConnection,
                        plate_ID = df$Plate_ID, position = df$position,
                        label = df$label, temperature = df$temperature)
          wells <- c(wells, w)
        }
        return(wells)
      } else { return(NULL) }
    },
    #' @description Store Well objects in the database
    #' @param well.list A list of well objects
    store = function(well.list) {
      statement <- "INSERT IGNORE INTO Well (Plate_ID, position, label, temperature)
                                     VALUES (?, ?, ?, ?)" 
      for(well in well.list) {
        result <- self$dbConnection$execute(statement, list(well$plate_ID,
                                                            well$position,
                                                            well$label,
                                                            well$temperature))
        if(length(well$datafiles)>0) {
          dfh <- DatafileHandle$new(self$dbConnection)
          result <- dfh$store(well$datafiles)
          for(datafile in well$datafiles) {
            st <- "INSERT IGNORE INTO Well_has_Data (Plate_ID, Well_position, Datafile_ID)
                                             VALUES (?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    datafile$ID))
          }
        }
        if(length(well$qc)>0) {
          qch <- QCHandle$new(self$dbConnection)
          result <- qch$store(well$qc)
          for(qc in well$qc) {
            st <- "INSERT IGNORE INTO Well_has_QC (Plate_ID, Well_position, QC_ID)
                                           VALUES (?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    qc$ID))
          }
        }
        if(length(well$compounds)>0) {
          ch <- CompoundHandle$new(self$dbConnection)
          result <- ch$store(well$compounds)
          for(compound in well$compounds) {
            st <- "INSERT IGNORE INTO Well_has_Compound (Plate_ID, Well_position, 
                                                  Compound_ID, dose, unit)
                                                 VALUES (?, ?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    compound$ID,
                                                    compound$dose,
                                                    compound$unit))
          }
        }
        if(length(well$samples)>0) {
          sh <- SampleHandle$new(self$dbConnection)
          result <- sh$store(well$samples)
          for(sample in well$samples) {
            st <- "INSERT IGNORE INTO Well_has_Sample (Plate_ID, Well_position, Sample_ID, count)
                        VALUES (?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    sample$ID,
                                                    sample$count))
          }
        }
        if(length(well$phenotypes)>0) {
          ph <- PhenotypeHandle$new(self$dbConnection)
          result <- ph$store(well$phenotypes)
          for(phenotype in well$phenotypes) {
            st <- "INSERT IGNORE INTO Well_has_Phenotype (Plate_ID, Well_position, Phenotype_ID, value, unit)
                        VALUES (?, ?, ?, ?, ?)"
            r <- self$dbConnection$execute(st, list(well$plate_ID,
                                                    well$position,
                                                    phenotype$ID,
                                                    phenotype$value,
                                                    phenotype$unit))
          }
        }
      }
    }
  )
)