Changeset: a60c6a14fb1e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a60c6a14fb1e
Added Files:
clients/R/MonetDB.R/man/dbTransaction.Rd
clients/R/MonetDB.R/man/monetdb_queryinfo.Rd
clients/R/MonetDB.R/man/mq.Rd
Modified Files:
NT/monetdb_config.h.in
clients/R/MonetDB.R/DESCRIPTION
clients/R/MonetDB.R/NEWS
clients/R/MonetDB.R/R/monetdb.R
clients/R/db.tests/monetdb.test.R
clients/Tests/exports.stable.out
gdk/ChangeLog
gdk/gdk.h
gdk/gdk_aggr.c
gdk/gdk_align.c
gdk/gdk_bat.c
gdk/gdk_batop.c
gdk/gdk_bbp.c
gdk/gdk_bbp.h
gdk/gdk_calc.c
gdk/gdk_calc_compare.h
gdk/gdk_cross.c
gdk/gdk_delta.c
gdk/gdk_firstn.c
gdk/gdk_group.c
gdk/gdk_heap.c
gdk/gdk_join.c
gdk/gdk_join_legacy.c
gdk/gdk_logger.c
gdk/gdk_private.h
gdk/gdk_sample.c
gdk/gdk_search.c
gdk/gdk_select.c
gdk/gdk_setop.c
gdk/gdk_storage.c
gdk/gdk_tm.c
gdk/gdk_unique.c
gdk/gdk_utils.c
geom/monetdb5/geom.c
monetdb5/extras/rdf/rdf_shredder.c
monetdb5/extras/rdf/rdfalgebra.c
monetdb5/extras/sphinx/sphinx.c
monetdb5/mal/Tests/tst1012.mal
monetdb5/mal/Tests/tst1012.stable.out
monetdb5/mal/mal_authorize.c
monetdb5/mal/mal_profiler.c
monetdb5/mal/mal_type.h
monetdb5/modules/atoms/batxml.c
monetdb5/modules/atoms/json.c
monetdb5/modules/atoms/mtime.c
monetdb5/modules/atoms/str.c
monetdb5/modules/kernel/algebra.c
monetdb5/modules/kernel/array.c
monetdb5/modules/kernel/bat5.c
monetdb5/modules/kernel/bat5.h
monetdb5/modules/kernel/batcolor.c
monetdb5/modules/kernel/batmmath.c
monetdb5/modules/kernel/batstr.c
monetdb5/modules/kernel/counters.c
monetdb5/modules/kernel/microbenchmark.c
monetdb5/modules/kernel/status.c
monetdb5/modules/mal/Tests/inspect05.stable.out
monetdb5/modules/mal/batExtensions.c
monetdb5/modules/mal/batExtensions.h
monetdb5/modules/mal/batExtensions.mal
monetdb5/modules/mal/bbp.c
monetdb5/modules/mal/clients.c
monetdb5/modules/mal/cluster.c
monetdb5/modules/mal/inspect.c
monetdb5/modules/mal/mal_mapi.c
monetdb5/modules/mal/manifold.c
monetdb5/modules/mal/mat.c
monetdb5/modules/mal/mdb.c
monetdb5/modules/mal/mkey.c
monetdb5/modules/mal/pcre.c
monetdb5/modules/mal/querylog.c
monetdb5/modules/mal/sysmon.c
monetdb5/modules/mal/tablet.c
monetdb5/modules/mal/tokenizer.c
monetdb5/modules/mal/txtsim.c
monetdb5/modules/mal/zorder.c
monetdb5/optimizer/opt_pipes.c
monetdb5/optimizer/opt_statistics.c
monetdb5/tests/gdkTests/Tests/TMsubcommit.mal
monetdb5/tests/gdkTests/Tests/TMsubcommit.stable.out
monetdb5/tests/gdkTests/Tests/bat_insert.mal
monetdb5/tests/gdkTests/Tests/bat_insert.stable.out
monetdb5/tests/gdkTests/Tests/reload.mal
monetdb5/tests/gdkTests/Tests/str_heap.mal
monetdb5/tests/gdkTests/Tests/str_heap.stable.out
monetdb5/tools/Tests/mserver5--help.stable.err
sql/backends/monet5/LSST/lsst.c
sql/backends/monet5/UDF/udf.c
sql/backends/monet5/datacell/basket.c
sql/backends/monet5/datacell/dcoperator.c
sql/backends/monet5/datacell/emitter.c
sql/backends/monet5/datacell/petrinet.c
sql/backends/monet5/datacell/receptor.c
sql/backends/monet5/gsl/gsl.c
sql/backends/monet5/sql.c
sql/backends/monet5/sql_bat2time.c
sql/backends/monet5/sql_cast.c
sql/backends/monet5/sql_cast_impl_down_from_flt.h
sql/backends/monet5/sql_cast_impl_down_from_int.h
sql/backends/monet5/sql_cast_impl_up_to_flt.h
sql/backends/monet5/sql_cast_impl_up_to_int.h
sql/backends/monet5/sql_fround.c
sql/backends/monet5/sql_fround_impl.h
sql/backends/monet5/sql_optimizer.c
sql/backends/monet5/sql_round.c
sql/backends/monet5/sql_round_impl.h
sql/backends/monet5/vaults/fits.c
sql/backends/monet5/vaults/mseed.c
sql/storage/bat/bat_logger.c
sql/storage/bat/bat_storage.c
sql/storage/bat/bat_utils.c
sql/storage/bat/bat_utils.h
sql/storage/restrict/restrict_storage.c
testing/Mtest.py.in
tools/mserver/mserver5.1.in
tools/mserver/mserver5.c
Branch: transaction-replication
Log Message:
Merge with default branch.
Manually merge gdk_logger.c
diffs (truncated from 8103 to 300 lines):
diff --git a/NT/monetdb_config.h.in b/NT/monetdb_config.h.in
--- a/NT/monetdb_config.h.in
+++ b/NT/monetdb_config.h.in
@@ -745,9 +745,12 @@
#undef fstat
#endif
#define fstat _fstat64
-#ifndef S_ISREG /* in case this file wasn't included first */
+#ifndef S_ISREG
#define S_ISREG(mode) (((mode) & _S_IFMT) == _S_IFREG)
#endif
+#ifndef S_ISDIR
+#define S_ISDIR(mode) (((mode) & _S_IFMT) == _S_IFDIR)
+#endif
/* Define to the sub-directory in which libtool stores uninstalled libraries.
*/
diff --git a/clients/R/MonetDB.R/DESCRIPTION b/clients/R/MonetDB.R/DESCRIPTION
--- a/clients/R/MonetDB.R/DESCRIPTION
+++ b/clients/R/MonetDB.R/DESCRIPTION
@@ -1,6 +1,6 @@
Package: MonetDB.R
-Version: 0.9.3
-Date: 2013-06-12
+Version: 0.9.4
+Date: 2013-07-14
Title: Connect MonetDB to R
Authors@R: c(person("Hannes Muehleisen", role = c("aut", "cre"),email =
"[email protected]"),
person("Thomas Lumley", role = "ctb"),
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,3 +1,6 @@
+0.9.4
+- dbWriteTable overhaul (thanks, Anthony)
+
0.9.3
- Remove trailing slashes for monetdb.program.path parameter for
monetdb.server.setup (Thanks, Anthony!)
- fixed monetdbd.liststatus (Thanks, Bart)
diff --git a/clients/R/MonetDB.R/R/monetdb.R b/clients/R/MonetDB.R/R/monetdb.R
--- a/clients/R/MonetDB.R/R/monetdb.R
+++ b/clients/R/MonetDB.R/R/monetdb.R
@@ -21,7 +21,7 @@ MonetR <- MonetDB <- MonetDBR <- MonetDB
setMethod("dbGetInfo", "MonetDBDriver", def=function(dbObj, ...)
list(name="MonetDBDriver",
- driver.version="0.9.3",
+ driver.version="0.9.4",
DBI.version="0.2-7",
client.version=NA,
max.connections=NA)
@@ -168,17 +168,17 @@ if (is.null(getGeneric("dbTransaction"))
standardGeneric("dbTransaction"))
setMethod("dbTransaction", signature(conn="MonetDBConnection"),
def=function(conn, ...) {
- dbSendQuery(conn, "start transaction")
+ dbSendQuery(conn, "START TRANSACTION")
invisible(TRUE)
})
setMethod("dbCommit", "MonetDBConnection", def=function(conn, ...) {
- dbSendQuery(conn, "commit")
+ dbSendQuery(conn, "COMMIT")
invisible(TRUE)
})
setMethod("dbRollback", "MonetDBConnection", def=function(conn, ...) {
- dbSendQuery(conn, "rollback")
+ dbSendQuery(conn, "ROLLBACK")
invisible(TRUE)
})
@@ -275,9 +275,8 @@ setMethod("dbSendQuery", signature(conn=
# adapted from RMonetDB, very useful...
-setMethod("dbWriteTable", "MonetDBConnection", def=function(conn, name, value,
overwrite=TRUE,
+setMethod("dbWriteTable", "MonetDBConnection", def=function(conn, name, value,
overwrite=FALSE, append=FALSE, insert=FALSE,
...) {
-
if (is.vector(value) && !is.list(value)) value <- data.frame(x=value)
if (length(value)<1) stop("value must have at least one column")
if (is.null(names(value))) names(value) <- paste("V", 1:length(value),
sep='')
@@ -286,32 +285,41 @@ setMethod("dbWriteTable", "MonetDBConnec
} else {
if (!is.data.frame(value)) value <- as.data.frame(value)
}
- fts <- sapply(value, dbDataType, dbObj=conn)
-
+ if (overwrite && append) {
+ stop("Setting both overwrite and append to true makes no sense.")
+ }
+ qname <- make.db.names(conn, name, allow.keywords=FALSE)
if (dbExistsTable(conn, name)) {
if (overwrite) dbRemoveTable(conn, name)
- else stop("Table `", name, "' already exists")
+ if (!overwrite && !append) stop("Table '", name, "' already exists. Set
overwrite=TRUE if you want to remove
+ the existing table. Set append=TRUE if you would like to add the new
data to the existing table.")
}
-
- fdef <- paste(make.db.names(conn, names(value), allow.keywords=FALSE), fts,
collapse=', ')
- qname <- make.db.names(conn, name, allow.keywords=FALSE)
- ct <- paste("CREATE TABLE ", qname, " (", fdef, ")", sep= '')
- dbSendUpdate(conn, ct)
-
+ if (!dbExistsTable(conn, name)) {
+ fts <- sapply(value, dbDataType, dbObj=conn)
+ fdef <- paste(make.db.names(conn, names(value), allow.keywords=FALSE),
fts, collapse=', ')
+ ct <- paste("CREATE TABLE ", qname, " (", fdef, ")", sep= '')
+ dbSendUpdate(conn, ct)
+ }
if (length(value[[1]])) {
- inss <- paste("INSERT INTO ", qname, " VALUES(", paste(rep("?",
length(value)), collapse=', '),
+ if (insert) {
+ inss <- paste("INSERT INTO ", qname, " VALUES (", paste(rep("?",
length(value)), collapse=', '),
")", sep='')
- .mapiRequest(conn, "Xauto_commit 0")
- for (j in 1:length(value[[1]])) dbSendUpdate(conn, inss,
list=as.list(value[j, ]))
- dbSendQuery(conn, "COMMIT")
- .mapiRequest(conn, "Xauto_commit 1")
+ dbTransaction(conn)
+ for (j in 1:length(value[[1]])) dbSendUpdate(conn, inss,
list=as.list(value[j, ]))
+ dbCommit(conn)
+ } else {
+ tmp <- tempfile(fileext = ".csv")
+ write.table(value, tmp, sep = ",", quote = TRUE,row.names = FALSE,
col.names = FALSE,na="")
+ dbSendQuery(conn, paste0("COPY ",format(nrow(value), scientific=FALSE),"
RECORDS INTO ", qname,
+ " FROM '", tmp, "' USING DELIMITERS ',','\\n','\"' NULL AS '' LOCKED"))
+ file.remove(tmp)
+ }
}
return(invisible(TRUE))
})
setMethod("dbDataType", signature(dbObj="MonetDBConnection", obj = "ANY"), def
= function(dbObj,
obj, ...) {
-
if (is.logical(obj)) "BOOLEAN"
else if (is.integer(obj)) "INTEGER"
else if (is.numeric(obj)) "DOUBLE PRECISION"
diff --git a/clients/R/MonetDB.R/man/dbTransaction.Rd
b/clients/R/MonetDB.R/man/dbTransaction.Rd
new file mode 100644
--- /dev/null
+++ b/clients/R/MonetDB.R/man/dbTransaction.Rd
@@ -0,0 +1,37 @@
+\name{dbTransaction}
+\alias{dbTransaction}
+\alias{dbTransaction,MonetDBConnection-method}
+\alias{dbCommit,MonetDBConnection-method}
+\alias{dbRollback,MonetDBConnection-method}
+
+\title{
+ Create, commit or abort a database transaction.
+}
+\description{
+\code{dbTransaction} is used to switch the data from the normal auto-commiting
mode into transactional mode. Here, changes to the database will not be
permanent until \code{dbCommit} is called. If the changes are not to be kept
around, you can use \code{dbRollback} to undo all the changes since
\code{dbTransaction} was called.
+
+}
+\usage{
+ dbTransaction(conn, ...)
+}
+\arguments{
+ \item{conn}{A MonetDB.R database connection. Created using
\code{\link[DBI]{dbConnect}}
+ with the \code{\link[MonetDB.R]{MonetDB.R}} database driver.}
+ \item{...}{Future use.}
+ }
+\value{
+ Returns \code{TRUE} if the transaction command was successful.
+}
+\examples{
+\dontrun{
+conn <- dbConnect(MonetDB.R(), "monetdb://localhost/acs")
+dbSendUpdate(conn, "CREATE TABLE foo(a INT,b VARCHAR(100))")
+dbTransaction(conn)
+dbSendUpdate(conn, "INSERT INTO foo VALUES(?,?)", 42, "bar")
+dbCommit(conn)
+dbTransaction(conn)
+dbSendUpdate(conn, "INSERT INTO foo VALUES(?,?)", 43, "bar")
+dbRollback(conn)
+
+# only 42 will be in table foo
+}}
diff --git a/clients/R/MonetDB.R/man/monetdb_queryinfo.Rd
b/clients/R/MonetDB.R/man/monetdb_queryinfo.Rd
new file mode 100644
--- /dev/null
+++ b/clients/R/MonetDB.R/man/monetdb_queryinfo.Rd
@@ -0,0 +1,31 @@
+\name{monetdb_queryinfo}
+\alias{monetdb_queryinfo}
+
+\title{
+ Get information about the result set of a query without actually executing
it. This is mainly needed for \code{dplyr} compatibility.
+}
+\description{
+ \code{monetdb_queryinfo(...)} is used to get the expected result set
structure (# rows, # columns, column names, column types etc.) without actually
running the query.
+}
+\usage{
+monetdb_queryinfo(conn, query)
+}
+\arguments{
+ \item{conn}{Database name}
+ \item{query}{SQL SELECT query to get information about}
+ }
+\value{
+ Environment with various entries, e.g.
+ \itemize{
+ \item \code{cols} -- number of columns
+ \item \code{rows} -- number of rows
+ \item \code{types} -- vector of column type from database (e.g. "VARCHAR" or
"INT")
+ \item \code{names} -- vector of column names
+ \item \code{tables} -- vector of table names
+ }
+}
+
+\examples{
+\dontrun{
+ monetdb_queryinfo("demo","SELECT 1")
+}}
diff --git a/clients/R/MonetDB.R/man/mq.Rd b/clients/R/MonetDB.R/man/mq.Rd
new file mode 100644
--- /dev/null
+++ b/clients/R/MonetDB.R/man/mq.Rd
@@ -0,0 +1,29 @@
+\name{mq}
+\alias{mq}
+
+\title{
+ Connect to a database, run a single SELECT query, and disconnect again.
+}
+\description{
+ \code{mq(...)} provides a short way to connect to a MonetDB database, run a
single SELECT query, and disconnect again.
+}
+\usage{
+mq(dbname, query, ...)
+}
+\arguments{
+ \item{dbname}{Database name}
+ \item{query}{SQL SELECT query to run}
+ \item{...}{Other options for \code{\link[DBI]{dbConnect}}}
+ }
+\value{
+ Returns a data frame that contains the result of the passed query or an
error if something went wrong.
+}
+
+\seealso{
+ \code{\link[DBI]{dbConnect}}
+ \code{\link[MonetDB.R]{mc}}
+}
+\examples{
+\dontrun{
+ mq("demo","SELECT 1")
+}}
diff --git a/clients/R/db.tests/monetdb.test.R
b/clients/R/db.tests/monetdb.test.R
--- a/clients/R/db.tests/monetdb.test.R
+++ b/clients/R/db.tests/monetdb.test.R
@@ -83,7 +83,44 @@ stopifnot(identical(dim(iris),dim(iris3)
stopifnot(identical(dbListFields(con,"monetdbtest"),c("sepal_length","sepal_width","petal_length","petal_width","species")))
dbRemoveTable(con,"monetdbtest")
stopifnot(identical(dbExistsTable(con,"monetdbtest"),FALSE))
+# test dbWriteTable
+conn <- con
+tname <- "mtcars"
+tsize <- function(conn,tname)
+ as.integer(dbGetQuery(conn,paste0("SELECT COUNT(*) FROM ",tname))[[1]])
+
+# clean up
+if (dbExistsTable(conn,tname))
+ dbRemoveTable(conn,tname)
+
+# table does not exist, append=F, overwrite=F, this should work
+dbWriteTable(conn,tname,mtcars,append=F,overwrite=F)
+stopifnot(dbExistsTable(conn,tname))
+stopifnot(identical(nrow(mtcars),tsize(conn,tname)))
+
+# these should throw errors
+errorThrown <- F
+tryCatch(dbWriteTable(conn,tname,mtcars,append=F,overwrite=F),error=function(e){errorThrown
<<- T})
+stopifnot(errorThrown)
+
+errorThrown <- F
+tryCatch(dbWriteTable(conn,tname,mtcars,overwrite=T,append=T),error=function(e){errorThrown
<<- T})
+stopifnot(errorThrown)
+
+# this should be fine
+dbWriteTable(conn,tname,mtcars,append=F,overwrite=T)
+stopifnot(dbExistsTable(conn,tname))
+stopifnot(identical(nrow(mtcars),tsize(conn,tname)))
+
+# append to existing table
+dbWriteTable(conn,tname,mtcars,append=T,overwrite=F)
+stopifnot(identical(as.integer(2*nrow(mtcars)),tsize(conn,tname)))
+dbRemoveTable(conn,tname)
+
+dbRemoveTable(conn,tname)
+dbWriteTable(conn,tname,mtcars,append=F,overwrite=F,insert=T)
+dbRemoveTable(conn,tname)
#thrice to catch null pointer errors
stopifnot(identical(dbDisconnect(con),TRUE))
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
