1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
### =========================================================================
### PolyPhenDb methods
### =========================================================================
setMethod("keys", "PolyPhenDb",
function(x)
{
sql <- paste("SELECT RSID FROM ppdata ", sep="")
unique(dbGetQuery(x$conn, sql))[,1]
}
)
setMethod("columns", "PolyPhenDb",
function(x)
{
dbListFields(conn=x$conn, "ppdata")
}
)
setMethod("select", "PolyPhenDb",
function(x, keys, columns, keytype, ...)
{
sql <- .createPPDbQuery(x, keys, columns)
if (length(sql)) {
raw <- dbGetQuery(x$conn, sql)
.formatPPDbSelect(raw, keys)
} else {
data.frame()
}
}
)
.createPPDbQuery <- function(x, keys, cols)
{
if (missing(keys) && missing(cols)) {
sql <- "SELECT * FROM ppdata"
}
if (!missing(keys)) {
if(.missingKeys(x, keys, "PolyPhen"))
return(character())
if (!missing(cols)) {
if (.missingCols(x, cols, "PolyPhen"))
return(character())
if (!"RSID" %in% cols)
cols <- c("RSID", cols)
fmtcols <- paste(cols, collapse=",")
fmtkeys <- .sqlIn(keys)
sql <- paste("SELECT ", fmtcols, " FROM ppdata WHERE RSID
IN (", fmtkeys, ")", sep="")
} else {
fmtkeys <- .sqlIn(keys)
sql <- paste("SELECT * FROM ppdata WHERE RSID IN (",
fmtkeys, ")", sep="")
}
} else {
if (.missingCols(x, cols, "PolyPhen"))
return(character())
if (!"RSID" %in% cols)
cols <- c("RSID", cols)
fmtcols <- paste(cols, collapse=",")
sql <- paste("SELECT ", fmtcols, " FROM ppdata", sep="")
}
sql
}
.formatPPDbSelect <- function(raw, keys)
{
## no data
if (!nrow(raw))
return(data.frame())
if (missing(keys)) {
df <- data.frame(raw)
rownames(df) <- NULL
df
} else {
## restore key order
missing <- (!keys %in% as.character(raw$RSID))
lst <- as.list(rep(NA_character_, length(keys)))
raw <- raw[!duplicated(raw), ]
for (i in which(missing == FALSE))
lst[[i]] <- raw[raw$RSID %in% keys[i], ]
df <- do.call(rbind, lst)
df$RSID[is.na(df$RSID)] <- keys[missing]
rownames(df) <- NULL
df
}
}
duplicateRSID <- function(db, keys, ...)
{
fmtrsid <- .sqlIn(keys)
sql <- paste("SELECT * FROM duplicates WHERE RSID IN (",
fmtrsid, ")", sep="")
q1 <- dbGetQuery(db$conn, sql)
fmtgp <- .sqlIn(unique(q1$DUPLICATEGROUP))
gpsql <- paste("SELECT * FROM duplicates WHERE DUPLICATEGROUP IN (",
fmtgp, ")", sep="")
q2 <- dbGetQuery(db$conn, gpsql)
matched <- q2[!q2$RSID %in% keys, ]
matchedlst <- split(matched$RSID, matched$DUPLICATEGROUP)
names(matchedlst) <- q1$RSID[match(names(matchedlst), q1$DUPLICATEGROUP)]
missing <- !keys %in% q2$RSID
if (any(missing)) {
warning(paste("keys not found in database : ", keys[missing],
sep=""))
missinglst <- list(rep(NA, sum(missing)))
names(missinglst) <- keys[missing]
matchedlst <- c(matchedlst, missinglst)
}
matchedlst[order(match(names(matchedlst), keys))]
}
|
