Newer
Older
#' 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()
for(df in df.list) {
w <- Well$new(dbConnection = self$dbConnection,
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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
Jean-Karim Heriche
committed
#' @param screen (optional) a Screen object to limit to wells from this screen
#' @return List of Well objects
Jean-Karim Heriche
committed
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)
Jean-Karim Heriche
committed
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)
Jean-Karim Heriche
committed
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)
Jean-Karim Heriche
committed
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)
Jean-Karim Heriche
committed
VALUES (?, ?, ?, ?, ?)"
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
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))
}
}
}
}
)
)