#' 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
#' @return List of Well objects
get_all_by_compound = function(compound, screen = NULL) {
if(is.null(screen)) {
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, 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))
}
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))
}
}
}
}
)
)