Changeset: bf4003213d1a for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=bf4003213d1a
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.R
clients/R/Tests/dplyr.stable.out
Branch: default
Log Message:
R Connector: Overhaul of dbQuoteIdentifier()
diffs (truncated from 423 to 300 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
@@ -1,5 +1,5 @@
0.9.9
-- dbWriteTable now quotes column names
+- dbWriteTable now quotes table/column names if necessary, and outputs
warnings if it did
0.9.8
- Added support for esoteric data types such as MONTH_INTERVAL (Thanks, Roman)
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
@@ -205,7 +205,7 @@ 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)))
+ tolower(dbListTables(conn, sys_tables=T)))
})
setMethod("dbGetException", "MonetDBConnection", def=function(conn, ...) {
@@ -215,7 +215,7 @@ setMethod("dbGetException", "MonetDBConn
setMethod("dbReadTable", "MonetDBConnection", def=function(conn, name, ...) {
if (!dbExistsTable(conn, name))
stop(paste0("Unknown table: ", name));
- dbGetQuery(conn,paste0("SELECT * FROM ", name))
+ dbGetQuery(conn, paste0("SELECT * FROM ", name))
})
# This one does all the work in this class
@@ -281,20 +281,28 @@ setMethod("dbSendQuery", signature(conn=
}
}
- return(new("MonetDBResult", env=env))
+ new("MonetDBResult", env=env)
})
# quoting
-setMethod("dbQuoteIdentifier", c("MonetDBConnection", "character"),
function(conn, x, ...) {
- qts <- !grepl("^[a-z][a-z0-9_]+$",x,perl=T)
- x[qts] <- paste('"', gsub('"', '""', x[qts], fixed = TRUE), '"', sep = "")
- SQL(x)
-})
+quoteIfNeeded <- function(conn, x, ...) {
+ chars <- !grepl("^[A-Za-z][A-Za-z0-9_]*$", x, perl=T) &&
!grepl("^\"[^\"]*\"$", x, perl=T)
+ if (any(chars)) {
+ message("Identifier(s) ", paste(x[chars], collapse=", "), " contain
reserved SQL characters and need to be quoted.")
+ }
+ reserved <- toupper(x) %in% .SQL92Keywords
+ if (any(reserved)) {
+ message("Identifier(s) ", paste(x[reserved], collapse=", "), " are
reserved SQL keywords and need to be quoted.")
+ }
+ qts <- reserved || chars
+ x[qts] <- dbQuoteIdentifier(conn, x[qts])
+ x
+}
-# overload as per DBI documentation
-setMethod("dbQuoteIdentifier", c("MonetDBConnection", "SQL"), function(conn,
x, ...) {x})
+# # overload as per DBI documentation
+# setMethod("dbQuoteIdentifier", c("MonetDBConnection", "SQL"), function(conn,
x, ...) {x})
# adapted from RMonetDB, very useful...
setMethod("dbWriteTable", "MonetDBConnection", def=function(conn, name, value,
overwrite=FALSE,
@@ -310,7 +318,8 @@ setMethod("dbWriteTable", "MonetDBConnec
if (overwrite && append) {
stop("Setting both overwrite and append to true makes no sense.")
}
- qname <- make.db.names(conn, name)
+
+ qname <- quoteIfNeeded(conn, name)
if (dbExistsTable(conn, qname)) {
if (overwrite) dbRemoveTable(conn, qname)
if (!overwrite && !append) stop("Table ", qname, " already exists. Set
overwrite=TRUE if you want
@@ -319,7 +328,7 @@ setMethod("dbWriteTable", "MonetDBConnec
}
if (!dbExistsTable(conn, qname)) {
fts <- sapply(value, dbDataType, dbObj=conn)
- fdef <- paste('"', make.db.names(conn, names(value)), '"', fts,
collapse=', ')
+ fdef <- paste(quoteIfNeeded(conn, names(value)), fts, collapse=', ')
ct <- paste("CREATE TABLE ", qname, " (", fdef, ")", sep= '')
dbSendUpdate(conn, ct)
}
@@ -458,8 +467,8 @@ monetdbRtype <- function(dbType) {
}
setMethod("fetch", signature(res="MonetDBResult", n="numeric"),
def=function(res, n, ...) {
- # dbGetQuery() still calls fetch(), thus no error message yet
- # warning("fetch() is deprecated, use dbFetch()")
+ # DBI on CRAN still uses fetch()
+ # message("fetch() is deprecated, use dbFetch()")
dbFetch(res, n, ...)
})
@@ -568,7 +577,7 @@ setMethod("dbFetch", signature(res="Mone
# if (getOption("monetdb.profile", T)) .profiler_clear()
- return(df)
+ df
})
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
@@ -54,21 +54,20 @@ stopifnot(identical(dbExistsTable(con,tn
stopifnot(identical(dbExistsTable(con,"monetdbtest2"),FALSE))
stopifnot(tname %in% dbListTables(con))
-stopifnot(identical(dbListFields(con,tname),c("sepal_length","sepal_width",
- "petal_length","petal_width","species")))
+stopifnot(identical(dbListFields(con,tname),names(iris)))
# get stuff, first very convenient
iris2 <- dbReadTable(con,tname)
stopifnot(identical(dim(iris),dim(iris2)))
# then manually
-res <- dbSendQuery(con,"SELECT species, sepal_width FROM monetdbtest")
+res <- dbSendQuery(con,"SELECT \"Species\", \"Sepal.Width\" FROM monetdbtest")
stopifnot(dbIsValid(res))
stopifnot(identical(class(res)[[1]],"MonetDBResult"))
stopifnot(identical(res@env$success,TRUE))
-stopifnot(dbColumnInfo(res)[[1,1]] == "species")
-stopifnot(dbColumnInfo(res)[[2,1]] == "sepal_width")
+stopifnot(dbColumnInfo(res)[[1,1]] == "Species")
+stopifnot(dbColumnInfo(res)[[2,1]] == "Sepal.Width")
stopifnot(dbGetInfo(res)$row.count == 150 && res@env$info$rows == 150)
@@ -99,8 +98,7 @@ unlink(file)
stopifnot(identical(dbExistsTable(con,tname),TRUE))
iris3 <- dbReadTable(con,tname)
stopifnot(identical(dim(iris),dim(iris3)))
-stopifnot(identical(dbListFields(con,tname),c("sepal_length","sepal_width",
- "petal_length","petal_width","species")))
+stopifnot(identical(dbListFields(con,tname),names(iris)))
dbRemoveTable(con,tname)
stopifnot(identical(dbExistsTable(con,tname),FALSE))
@@ -173,6 +171,13 @@ dbRollback(conn)
# this returns a column with esoteric type MONTH_INTERVAL
stopifnot(identical(1L, as.integer(dbGetQuery(con, "select cast('2015-03-02'
as date) - cast('2015-03-01' as date)")[[1]][[1]])))
+# reserved words in data frame column names
+stopifnot(dbIsValid(conn))
+dbBegin(conn)
+dbWriteTable(conn, "evilt", data.frame(year=42, month=12, day=24),
transaction=F)
+stopifnot(dbExistsTable(conn, "evilt"))
+dbRollback(conn)
+
stopifnot(dbIsValid(conn))
#thrice to catch null pointer errors
stopifnot(identical(dbDisconnect(con),TRUE))
@@ -180,14 +185,6 @@ stopifnot(!dbIsValid(conn))
stopifnot(identical(dbDisconnect(con),TRUE))
stopifnot(identical(dbDisconnect(con),TRUE))
-# reserved words in data frame column names
-stopifnot(dbIsValid(conn))
-dbBegin(conn)
-dbWriteTable(conn, "evilt", data.frame(year=42,month=12, day=24),
transaction=F)
-stopifnot(dbExistsTable(conn, "evilt"))
-dbRollback(conn)
-
-
#test merovingian control code
#cannot really do this in Mtest, sorry
#stopifnot(dbname %in% monetdbd.liststatus("monetdb")$dbname)
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
@@ -1,11 +1,11 @@
stderr of test 'dbi` in directory 'clients/R` itself:
-# 14:24:02 >
-# 14:24:02 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=37882" "--set"
"mapi_usock=/var/tmp/mtest-79204/.s.monetdb.37882" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/Users/hannes/monetdb-oct2014-install/var/MonetDB/mTests_clients_R"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
-# 14:24:02 >
+# 16:49:50 >
+# 16:49:50 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36003" "--set"
"mapi_usock=/var/tmp/mtest-66827/.s.monetdb.36003" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/Users/hannes/monetdb-install/var/MonetDB/mTests_clients_R" "--set"
"mal_listing=0" "--set" "embedded_r=yes"
+# 16:49:50 >
-# builtin opt gdk_dbpath =
/Users/hannes/monetdb-oct2014-install/var/monetdb5/dbfarm/demo
+# builtin opt gdk_dbpath =
/Users/hannes/monetdb-install/var/monetdb5/dbfarm/demo
# builtin opt gdk_debug = 0
# builtin opt gdk_vmtrim = no
# builtin opt monet_prompt = >
@@ -17,21 +17,24 @@ stderr of test 'dbi` in directory 'clien
# builtin opt sql_debug = 0
# cmdline opt gdk_nr_threads = 0
# cmdline opt mapi_open = true
-# cmdline opt mapi_port = 37882
-# cmdline opt mapi_usock = /var/tmp/mtest-79204/.s.monetdb.37882
+# cmdline opt mapi_port = 36003
+# cmdline opt mapi_usock = /var/tmp/mtest-66827/.s.monetdb.36003
# cmdline opt monet_prompt =
# cmdline opt mal_listing = 2
-# cmdline opt gdk_dbpath =
/Users/hannes/monetdb-oct2014-install/var/MonetDB/mTests_clients_R
+# cmdline opt gdk_dbpath =
/Users/hannes/monetdb-install/var/MonetDB/mTests_clients_R
# cmdline opt mal_listing = 0
# cmdline opt embedded_r = yes
# cmdline opt gdk_debug = 536870922
-# 14:24:03 >
-# 14:24:03 > "R" "--vanilla" "--slave" "--args" "37882"
-# 14:24:03 >
+# 16:49:50 >
+# 16:49:50 > "R" "--vanilla" "--slave" "--args" "36003"
+# 16:49:50 >
+Identifier(s) Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species
contain reserved SQL characters and need to be quoted.
+Identifier(s) Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species
contain reserved SQL characters and need to be quoted.
+Identifier(s) year, month, day are reserved SQL keywords and need to be quoted.
-# 14:24:04 >
-# 14:24:04 > "Done."
-# 14:24:04 >
+# 16:49:52 >
+# 16:49:52 > "Done."
+# 16:49:52 >
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
@@ -1,20 +1,23 @@
stdout of test 'dbi` in directory 'clients/R` itself:
-# 14:24:02 >
-# 14:24:02 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=37882" "--set"
"mapi_usock=/var/tmp/mtest-79204/.s.monetdb.37882" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/Users/hannes/monetdb-oct2014-install/var/MonetDB/mTests_clients_R"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
-# 14:24:02 >
+# 16:49:50 >
+# 16:49:50 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36003" "--set"
"mapi_usock=/var/tmp/mtest-66827/.s.monetdb.36003" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/Users/hannes/monetdb-install/var/MonetDB/mTests_clients_R" "--set"
"mal_listing=0" "--set" "embedded_r=yes"
+# 16:49:50 >
-# MonetDB 5 server v11.19.0
+# MonetDB 5 server v11.22.0
# This is an unreleased version
-# Serving database 'mTests_clients_R', using 8 threads
-# Compiled for x86_64-apple-darwin14.0.0/64bit with 64bit OIDs dynamically
linked
+# Serving database 'mTests_clients_R', using 4 threads
+# Compiled for x86_64-apple-darwin14.3.0/64bit with 64bit OIDs and 128bit
integers dynamically linked
# Found 16.000 GiB available main-memory.
# Copyright (c) 1993-July 2008 CWI.
# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
# Visit http://www.monetdb.org/ for further information
-# Listening for connection requests on mapi:monetdb://wired1-11.cwi.nl:37882/
-# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-79204/.s.monetdb.37882
+# Listening for connection requests on
mapi:monetdb://dakar.da.cwi.nl.hhk.dk:36003/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-66827/.s.monetdb.36003
+# MonetDB/GIS module loaded
+# Start processing logs sql/sql_logs version 52200
+# Finished processing logs sql/sql_logs
# MonetDB/SQL module loaded
# MonetDB/R module loaded
@@ -28,7 +31,7 @@ Ready.
# loading sql script: 14_inet.sql
# loading sql script: 15_querylog.sql
# loading sql script: 16_tracelog.sql
-# loading sql script: 19_cluster.sql
+# loading sql script: 17_temporal.sql
# loading sql script: 20_vacuum.sql
# loading sql script: 21_dependency_functions.sql
# loading sql script: 22_clients.sql
@@ -36,19 +39,27 @@ Ready.
# loading sql script: 24_zorder.sql
# loading sql script: 25_debug.sql
# loading sql script: 26_sysmon.sql
+# loading sql script: 27_rejects.sql
# loading sql script: 39_analytics.sql
+# loading sql script: 39_analytics_hge.sql
+# loading sql script: 40_geom.sql
# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
# loading sql script: 41_md5sum.sql
# loading sql script: 45_uuid.sql
+# loading sql script: 46_gsl.sql
+# loading sql script: 51_sys_schema_extension.sql
# loading sql script: 75_storagemodel.sql
# loading sql script: 80_statistics.sql
# loading sql script: 80_udf.sql
+# loading sql script: 80_udf_hge.sql
# loading sql script: 90_generator.sql
+# loading sql script: 90_generator_hge.sql
# loading sql script: 99_system.sql
-# 14:24:03 >
-# 14:24:03 > "R" "--vanilla" "--slave" "--args" "37882"
-# 14:24:03 >
+# 16:49:50 >
+# 16:49:50 > "R" "--vanilla" "--slave" "--args" "36003"
+# 16:49:50 >
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
