Changeset: 6154551e33d2 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6154551e33d2
Modified Files:
clients/R/MonetDB.R/NEWS
clients/R/MonetDB.R/R/dbi.R
clients/R/Tests/dbi.R
clients/R/Tests/dbi.stable.err
clients/R/Tests/dbi.stable.out
clients/R/Tests/dplyr-flights.stable.err
clients/R/Tests/dplyr.stable.err
clients/R/build-for-cran.sh
Branch: default
Log Message:
R Connector: Overhaul of quoting
diffs (206 lines):
diff --git a/clients/R/MonetDB.R/NEWS b/clients/R/MonetDB.R/NEWS
--- a/clients/R/MonetDB.R/NEWS
+++ b/clients/R/MonetDB.R/NEWS
@@ -9,7 +9,9 @@ 1.0.0
- Fix for empty result set on dbGetQuery(), no longer returning NULL (Thanks,
Fabian)
- Fix for dbConnect(), it ignored the url parameter somehow, which broke some
sqlsurvey (Thanks, Anthony)
- Added col.names argument to monet.read.csv()
+- Added lower.case.names argument to monet.read.csv() in case users want to
avoid quoting (a bit)
- Fix for dbConnect() that should be more robust to invalid connections
+- Cleaned up quoting behavior in dbListTables(), dbRemoveTable() etc.
0.9.7
- Fixed crash on Windows (Sorry, everyone)
diff --git a/clients/R/MonetDB.R/R/dbi.R b/clients/R/MonetDB.R/R/dbi.R
--- a/clients/R/MonetDB.R/R/dbi.R
+++ b/clients/R/MonetDB.R/R/dbi.R
@@ -153,18 +153,14 @@ setMethod("dbDisconnect", "MonetDBConnec
return(invisible(TRUE))
})
-setMethod("dbListTables", "MonetDBConnection", def=function(conn, ...,
sys_tables=F, schema_names=F, quote=F) {
+setMethod("dbListTables", "MonetDBConnection", def=function(conn, ...,
sys_tables=F, schema_names=F) {
q <- "select schemas.name as sn, tables.name as tn from sys.tables join
sys.schemas on tables.schema_id=schemas.id"
if (!sys_tables) q <- paste0(q, " where tables.system=false")
df <- dbGetQuery(conn, q)
- if (quote) {
- df$tn <- paste0("\"", df$tn, "\"")
- }
+ df$tn <- quoteIfNeeded(conn, df$tn, warn=F)
res <- df$tn
if (schema_names) {
- if (quote) {
- df$sn <- paste0("\"", df$sn, "\"")
- }
+ df$sn <- quoteIfNeeded(conn, df$sn, warn=F)
res <- paste0(df$sn, ".", df$tn)
}
return(as.character(res))
@@ -203,9 +199,9 @@ setMethod("dbListFields", "MonetDBConnec
})
setMethod("dbExistsTable", "MonetDBConnection", def=function(conn, name, ...) {
- # TODO: this is evil...
- return(tolower(gsub("(^\"|\"$)","",as.character(name))) %in%
- tolower(dbListTables(conn, sys_tables=T)))
+ name <- quoteIfNeeded(conn, name)
+ return(as.character(name) %in%
+ dbListTables(conn, sys_tables=T))
})
setMethod("dbGetException", "MonetDBConnection", def=function(conn, ...) {
@@ -213,6 +209,7 @@ setMethod("dbGetException", "MonetDBConn
})
setMethod("dbReadTable", "MonetDBConnection", def=function(conn, name, ...) {
+ name <- quoteIfNeeded(conn, name)
if (!dbExistsTable(conn, name))
stop(paste0("Unknown table: ", name));
dbGetQuery(conn, paste0("SELECT * FROM ", name))
@@ -284,14 +281,14 @@ setMethod("dbSendQuery", signature(conn=
})
# quoting
-quoteIfNeeded <- function(conn, x, ...) {
- chars <- !grepl("^[a-z][a-z0-9_]*$", x, perl=T) & !grepl("^\"[^\"]*\"$", x,
perl=T)
- if (any(chars)) {
- message("Identifier(s) ", paste(x[chars], collapse=", "), " contain
uppercase or reserved SQL characters and need(s) to be quoted in queries.")
+quoteIfNeeded <- function(conn, x, warn=T, ...) {
+ chars <- !grepl("^[a-z_][a-z0-9_]*$", x, perl=T) & !grepl("^\"[^\"]*\"$", x,
perl=T)
+ if (any(chars) && warn) {
+ message("Identifier(s) ", paste("\"", x[chars],"\"", collapse=", ",
sep=""), " contain uppercase or reserved SQL characters and need(s) to be
quoted in queries.")
}
reserved <- toupper(x) %in% .SQL92Keywords
- if (any(reserved)) {
- message("Identifier(s) ", paste(x[reserved], collapse=", "), " are
reserved SQL keywords and need(s) to be quoted in queries.")
+ if (any(reserved) && warn) {
+ message("Identifier(s) ", paste("\"", x[reserved],"\"", collapse=", ",
sep=""), " are reserved SQL keywords and need(s) to be quoted in queries.")
}
qts <- reserved | chars
x[qts] <- dbQuoteIdentifier(conn, x[qts])
@@ -364,8 +361,9 @@ setMethod("dbDataType", signature(dbObj=
setMethod("dbRemoveTable", "MonetDBConnection", def=function(conn, name, ...) {
+ name <- quoteIfNeeded(conn, name)
if (dbExistsTable(conn, name)) {
- dbSendUpdate(conn, paste("DROP TABLE", tolower(name)))
+ dbSendUpdate(conn, paste("DROP TABLE", name))
return(invisible(TRUE))
}
return(invisible(FALSE))
@@ -639,7 +637,7 @@ monet.read.csv <- monetdb.read.csv <- fu
types <- sapply(headers, function(df) sapply(df, dbDataType, dbObj=conn))
if(!all(types==types[, 1])) stop("Files have different variable types")
}
-
+ tablename <- quoteIfNeeded(conn, tablename)
if (create){
if(lower.case.names) names(headers[[1]]) <- tolower(names(headers[[1]]))
if(!is.null(col.names)) {
diff --git a/clients/R/Tests/dbi.R b/clients/R/Tests/dbi.R
--- a/clients/R/Tests/dbi.R
+++ b/clients/R/Tests/dbi.R
@@ -92,13 +92,23 @@ stopifnot(identical(dbExistsTable(con,tn
# test csv import
file <- tempfile()
write.table(iris,file,sep=",")
+tname2 <- "Need to quote this table name"
monetdb.read.csv(con,file,tname)
+monetdb.read.csv(con,file,tname2)
+###
+dbListTables(con)
+
unlink(file)
stopifnot(identical(dbExistsTable(con,tname),TRUE))
+stopifnot(identical(dbExistsTable(con,tname2),TRUE))
iris3 <- dbReadTable(con,tname)
+iris4 <- dbReadTable(con,tname2)
stopifnot(identical(dim(iris),dim(iris3)))
+stopifnot(identical(dim(iris),dim(iris4)))
stopifnot(identical(dbListFields(con,tname),names(iris)))
+stopifnot(identical(dbListFields(con,tname2),names(iris)))
dbRemoveTable(con,tname)
+dbRemoveTable(con,tname2)
stopifnot(identical(dbExistsTable(con,tname),FALSE))
# test dbWriteTable
diff --git a/clients/R/Tests/dbi.stable.err b/clients/R/Tests/dbi.stable.err
--- a/clients/R/Tests/dbi.stable.err
+++ b/clients/R/Tests/dbi.stable.err
@@ -30,12 +30,18 @@ stderr of test 'dbi` in directory 'clien
# 12:27:25 > "R" "--vanilla" "--slave" "--args" "31728"
# 12:27:25 >
-Identifier(s) Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species
contain uppercase or reserved SQL characters and need(s) to be quoted in
queries.
-Identifier(s) Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species
contain uppercase or reserved SQL characters and need(s) to be quoted in
queries.
-Identifier(s) some.dot contain uppercase or reserved SQL characters and
need(s) to be quoted in queries.
-Identifier(s) year, month, day are reserved SQL keywords and need(s) to be
quoted in queries.
-Identifier(s) sch.wide, comp.imp, yr.rnd, acs.k3, acs.46, acs.core, pct.resp,
not.hsg, some.col, col.grad, grad.sch, avg.ed, api.stu contain uppercase or
reserved SQL characters and need(s) to be quoted in queries.
-Identifier(s) full are reserved SQL keywords and need(s) to be quoted in
queries.
+Identifier(s) "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width",
"Species" contain uppercase or reserved SQL characters and need(s) to be quoted
in queries.
+Identifier(s) "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width",
"Species" contain uppercase or reserved SQL characters and need(s) to be quoted
in queries.
+Identifier(s) "Need to quote this table name" contain uppercase or reserved
SQL characters and need(s) to be quoted in queries.
+Identifier(s) "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width",
"Species" contain uppercase or reserved SQL characters and need(s) to be quoted
in queries.
+Identifier(s) "Need to quote this table name" contain uppercase or reserved
SQL characters and need(s) to be quoted in queries.
+Identifier(s) "Need to quote this table name" contain uppercase or reserved
SQL characters and need(s) to be quoted in queries.
+Identifier(s) "Need to quote this table name" contain uppercase or reserved
SQL characters and need(s) to be quoted in queries.
+Identifier(s) "Need to quote this table name" contain uppercase or reserved
SQL characters and need(s) to be quoted in queries.
+Identifier(s) "some.dot" contain uppercase or reserved SQL characters and
need(s) to be quoted in queries.
+Identifier(s) "year", "month", "day" are reserved SQL keywords and need(s) to
be quoted in queries.
+Identifier(s) "sch.wide", "comp.imp", "yr.rnd", "acs.k3", "acs.46",
"acs.core", "pct.resp", "not.hsg", "some.col", "col.grad", "grad.sch",
"avg.ed", "api.stu" contain uppercase or reserved SQL characters and need(s) to
be quoted in queries.
+Identifier(s) "full" are reserved SQL keywords and need(s) to be quoted in
queries.
# 12:27:27 >
# 12:27:27 > "Done."
diff --git a/clients/R/Tests/dbi.stable.out b/clients/R/Tests/dbi.stable.out
--- a/clients/R/Tests/dbi.stable.out
+++ b/clients/R/Tests/dbi.stable.out
@@ -53,6 +53,9 @@ Ready.
[1] TRUE
[1] TRUE
[1] 150
+[1] 150
+[1] "monetdbtest" "\"Need to quote this table name\""
+[1] TRUE
[1] TRUE
[1] TRUE
[1] TRUE
diff --git a/clients/R/Tests/dplyr-flights.stable.err
b/clients/R/Tests/dplyr-flights.stable.err
--- a/clients/R/Tests/dplyr-flights.stable.err
+++ b/clients/R/Tests/dplyr-flights.stable.err
@@ -30,7 +30,7 @@ stderr of test 'dplyr-flights` in direct
# 11:44:05 > "R" "--vanilla" "--slave" "--args" "30588"
# 11:44:05 >
-Identifier(s) year, month, day, hour, minute are reserved SQL keywords and
need(s) to be quoted in queries.
+Identifier(s) "year", "month", "day", "hour", "minute" are reserved SQL
keywords and need(s) to be quoted in queries.
# 11:44:15 >
# 11:44:15 > "Done."
diff --git a/clients/R/Tests/dplyr.stable.err b/clients/R/Tests/dplyr.stable.err
--- a/clients/R/Tests/dplyr.stable.err
+++ b/clients/R/Tests/dplyr.stable.err
@@ -30,6 +30,9 @@ stderr of test 'dplyr` in directory 'cli
# 14:31:12 > "R" "--vanilla" "--slave" "--args" "32629"
# 14:31:12 >
+Identifier(s) "Batting" contain uppercase or reserved SQL characters and
need(s) to be quoted in queries.
+Identifier(s) "Master" contain uppercase or reserved SQL characters and
need(s) to be quoted in queries.
+Identifier(s) "HallOfFame" contain uppercase or reserved SQL characters and
need(s) to be quoted in queries.
# 17:39:45 >
# 17:39:45 > "Done."
diff --git a/clients/R/build-for-cran.sh b/clients/R/build-for-cran.sh
--- a/clients/R/build-for-cran.sh
+++ b/clients/R/build-for-cran.sh
@@ -25,6 +25,6 @@ mkdir -p /tmp/rcheck
$R CMD build MonetDB.R
$R CMD check -o /tmp/rcheck --as-cran MonetDB.R_*.tar.gz
-hgid=`hg id -i | sed s/+//`
-newname=`basename MonetDB.R_*.tar.gz .tar.gz`-$hgid.tar.gz
-mv MonetDB.R_*.tar.gz $newname
+#hgid=`hg id -i | sed s/+//`
+#newname=`basename MonetDB.R_*.tar.gz .tar.gz`-$hgid.tar.gz
+#mv MonetDB.R_*.tar.gz $newname
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
