R: Methods for 'DBTABLES' objects

DBTABLES-methods {pkgutils}

R Documentation

Methods for `DBTABLES` objects

Description

Return or check the supposed primary and foreign keys in a DBTABLES object, or show, traverse, combine, or split such objects.

Usage

  ## S4 method for signature 'DBTABLES,ANY,character,logical'
by(data,
    INDICES, FUN, ..., do_map = NULL, do_inline = FALSE, do_quote = '"',
    simplify) 
  ## S4 method for signature 'DBTABLES,ANY,character,missing'
by(data,
    INDICES, FUN, ..., do_map = NULL, do_inline = FALSE, do_quote = '"',
    simplify) 
  ## S4 method for signature 'DBTABLES,ANY,function,logical'
by(data,
    INDICES, FUN, ..., do_map = NULL, do_inline = FALSE, do_quote = '"',
    simplify) 
  ## S4 method for signature 'DBTABLES,ANY,function,missing'
by(data,
    INDICES, FUN, ..., do_map = NULL, do_inline = FALSE, do_quote = '"',
    simplify) 

  ## S4 method for signature 'DBTABLES'
c(x, ..., recursive = FALSE) 

  ## S4 method for signature 'DBTABLES'
fkeys(object) 

  ## S4 method for signature 'DBTABLES'
fkeys_valid(object) 

  ## S4 method for signature 'DBTABLES'
head(x) 

  ## S4 method for signature 'DBTABLES'
length(x) 

  ## S4 method for signature 'DBTABLES'
pkeys(object) 

  ## S4 method for signature 'DBTABLES'
pkeys_valid(object) 

  ## S4 method for signature 'DBTABLES'
show(object) 

  ## S4 method for signature 'DBTABLES,logical'
sort(x, decreasing) 
  ## S4 method for signature 'DBTABLES,missing'
sort(x, decreasing) 

  ## S4 method for signature 'DBTABLES,ANY,logical'
split(x, f, drop) 
  ## S4 method for signature 'DBTABLES,ANY,missing'
split(x, f, drop) 
  ## S4 method for signature 'DBTABLES,missing,logical'
split(x, f, drop) 
  ## S4 method for signature 'DBTABLES,missing,missing'
split(x, f, drop) 

  ## S4 method for signature 'DBTABLES'
summary(object) 

  ## S4 method for signature 'DBTABLES'
tail(x) 

  ## S4 method for signature 'DBTABLES'
update(object, start, drop = TRUE)

Arguments

`object`	`DBTABLES` object.
`x`	`DBTABLES` object.
`data`	`DBTABLES` object.
`decreasing`	Logical scalar. If missing, `FALSE` is used instead.
`start`	Numeric vector or `NULL`. If a numeric vector and named, the names must be the slot names; otherwise these are inserted, in order. Each value of `start` defines an new starting point (minimum) for the primary key in the respective table. `update` takes care that the foreign keys referring to this table are modified in the same way. If `NULL`, `start` is set to a vector that causes all output primary keys to start at 1. This can be used to revert a previous call of `update`.
`recursive`	Logical scalar passed from `c` as `drop` argument to `update`. Also causes `update` to be applied to `x` (and not only to the elements of `...`, if any).
`f`	Missing or (list of) factor(s) that fit(s) to the dimensions of the first table returned by. If missing, the primary key of this table is used.
`drop`	For `update`, a logical scalar that indicates whether or not the row names of all tables should be set to `NULL`. For `split`, `drop` is either missing or a logical scalar. `TRUE` is the default. If so, after splitting, `update` is applied to each `DBTABLES` object that is an element of the resulting list, with `start` set to `NULL` and `drop` set to `TRUE`.
`INDICES`	If `do_inline` is `FALSE`, a vector used for selecting a subset of the tables and modifying their order (which might not make sense, see `DBTABLES`). Otherwise usually a vector of primary keys for the first table. Selection of rows from subsequent tables works via the defined foreign keys. Thus, used for querying for data slots for a novel `DBTABLES` with `FUN`.
`FUN`	Function to be applied to each table in turn. If `do_inline` is `FALSE`, it should accept a character scalar (name of a table) a data frame (this table) and as the first two (unnamed) arguments. If `do_inline` is `TRUE` and `simplify` is `FALSE`, `FUN` should accept three first arguments (in addition to those in `...`, if any), in order: the name of a table (database table or data frame), the name of a column in that table, and a vector of numeric indexes to match that column. It should return a data frame with all matched rows. If `do_inline` is `TRUE` and `simplify` is `TRUE`, `FUN` should accept a character scalar (SQL statement) as single first argument in addition to those in `...`, and use this to retrieve columns from a database table. The generated SQL uses double-quoting for the table and column identifiers and single-quoting for the query values (starting with `INDICES`) unless they are numeric.
`...`	Objects of the same class as `x` for `c`, optional further arguments of `FUN` for `by`.
`do_map`	Optional vector for mapping the slot names in `data` before passing them to `FUN`.
`do_inline`	Logical scalar indicating how `FUN` should be used and which kind of return value should be produced. See `FUN` for details.
`do_quote`	Character scalar or function used for generating SQL identifiers. If a function, used directly; otherwise used as quote character (to be doubled within the character string).
`simplify`	Logical scalar. If `do_inline` is `FALSE`, this argument indicates whether SQL should be generated and passed to `FUN`, see above. If `do_inline` is `TRUE`, `simplify` determines the second argument passed to `FUN`: if `TRUE`, the name of the primary ID column of the database table is used, if `FALSE` the according data frame contained in `data` is passed. If missing, `do_inline` is used as default for `simplify`.

Value

pkeys yields a character vector with the (expected) name of the primary key column for each table. fkeys returns a matrix that describes the (expected) relations between the tables.

fkeys_valid and pkeys_valid return TRUE if successful and a character vector describing the problems otherwise. These functions can thus be used as validity argument of setClass.

summary creates an S3 object that nicely prints to the screen (and is used by show).

length returns the number of rows of the data frame in the slot defined by the first entry of fkeys.

head and tail return the minimum and maximum available primary key, respectively, for all contained tables.

sort sorts all tables by their primary keys and returns an object of the same class.

update and c return an object of the same class than object and x, respectively. c runs update on all objects in ... to yield overall unique IDs and then runs rbind on all tables.

split uses f for splitting the first table returned by pkeys and then proceeds by accordingly splitting the tables that refer to it. c can be used to get the original object back.

by returns the result of FUN or INDICES as a list or other kind of object, depending on simplify, if do_inline is TRUE.

If do_inline is FALSE, by creates a novel DBTABLES object. It starts with passing INDICES as indexes (optionally within an SQL statement) to FUN, which should yield a data frame to be inserted as first table. Further INDICES arguments are generated using the information returned by fkeys to fill the object. Child classes might need to define a prototype with data frames that might be empty but already contain the column naming that defines the cross references.

Examples

# example class, with 'results' referring to 'experiments'
setClass("myclass",
  contains = "DBTABLES",
  slots = c(experiments = "data.frame", results = "data.frame"))

x <- new("myclass",
  experiments = data.frame(id = 3:1, researcher = "Jane Doe"),
  results = data.frame(id = 1:5, experiment_id = c(1L, 3L, 2L, 1L, 3L),
    type = c("A", "B", "A", "B", "A"), value = runif(5)))

summary(x)

## An object of class 'myclass'.
## Number of rows in first table: 3
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

length(x) # not the number of slots

## [1] 3

pkeys(x)

## experiments     results 
##        "id"        "id"

fkeys(x) # NA entries are used for table without foreign keys

##      from.tbl      from.col        to.tbl        to.col
## [1,] "experiments" NA              NA            "id"  
## [2,] "results"     "experiment_id" "experiments" "id"

# conduct some checks
stopifnot(fkeys_valid(x), pkeys_valid(x), length(x) == 3)
stopifnot(anyNA(fkeys(x)), !all(is.na(fkeys(x))))

# originally the primary keys are not in order here
(y <- sort(x))

## An object of class 'myclass'.
## Number of rows in first table: 3
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

slot(y, "experiments")

##   id researcher
## 3  1   Jane Doe
## 2  2   Jane Doe
## 1  3   Jane Doe

slot(y, "results")

##   id experiment_id type     value
## 1  1             1    A 0.9828689
## 2  2             3    B 0.5160012
## 3  3             2    A 0.5577777
## 4  4             1    B 0.8962137
## 5  5             3    A 0.1567336

stopifnot(!identical(x, y))

# get first and last primary keys for each table
head(x)

## experiments     results 
##           1           1

tail(x)

## experiments     results 
##           3           5

stopifnot(head(x) == 1, tail(x) >= head(x))

# modify the primary keys
start <- 3:4 # one value per table
y <- update(x, start)
head(y)

## experiments     results 
##           3           4

tail(y)

## experiments     results 
##           5           8

stopifnot(head(y) == start, tail(y) > start)
stopifnot(fkeys_valid(y), pkeys_valid(y)) # must still be OK
(y <- update(y, NULL))

## An object of class 'myclass'.
## Number of rows in first table: 3
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

head(y)

## experiments     results 
##           1           1

tail(y)

## experiments     results 
##           3           5

stopifnot(head(y) == 1, tail(y) > 1)
stopifnot(fkeys_valid(y), pkeys_valid(y))

# split the data
(y <- split(x))

## [[1]]
## An object of class 'myclass'.
## Number of rows in first table: 1
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id
## 
## [[2]]
## An object of class 'myclass'.
## Number of rows in first table: 1
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id
## 
## [[3]]
## An object of class 'myclass'.
## Number of rows in first table: 1
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

stopifnot(sapply(y, length) == 1)
stopifnot(sapply(y, fkeys_valid), sapply(y, pkeys_valid))

# combine data again
(y <- do.call(c, y))

## An object of class 'myclass'.
## Number of rows in first table: 3
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

stopifnot(length(y) == length(x), class(x) == class(y))
stopifnot(fkeys_valid(y), pkeys_valid(y))
## ids are not necessarily the same than before but still OK

# traverse the object
(y <- by(x, TRUE, function(a, b) is.data.frame(b), simplify = FALSE))

## experiments     results 
##        TRUE        TRUE

stopifnot(unlist(y), !is.null(names(y)))
(z <- by(x, 2:1, function(a, b) is.character(a), simplify = FALSE))

##     results experiments 
##        TRUE        TRUE

stopifnot(unlist(z), names(z) == rev(names(y))) # other order
(z <- by(x, 1:2, function(a, b) a, simplify = FALSE,
  do_map = c(experiments = "A", results = "B"))) # with renaming

##   A   B 
## "A" "B"

stopifnot(unlist(z) == c("A", "B")) # new names passed as 2nd argument to FUN

# to illustrate by() in inline mode, we use a function that simply yields
# the already present slots
col_fun <- function(items, tbl, col, idx) {
  tmp <- slot(items, tbl)
  tmp[tmp[, col] %in% idx, , drop = FALSE]
}

(y <- by(x, slot(x, "experiments")[, "id"], col_fun, items = x,
  do_inline = TRUE, simplify = FALSE))

## An object of class 'myclass'.
## Number of rows in first table: 3
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

stopifnot(identical(y, x))

# select only a subset of the indexes
(y <- by(x, c(2L, 1L), col_fun, items = x, do_inline = TRUE,
  simplify = FALSE))

## An object of class 'myclass'.
## Number of rows in first table: 2
## Defined cross-references between tables:
## experiments.<MISSING>
##                       <MISSING>.id
## results.experiment_id
##                       experiments.id

stopifnot(length(y) == 2)

# try a mapping that does not work
(y <- try(by(x, c(2L, 1L), col_fun, items = x, simplify = FALSE,
  do_inline = TRUE, do_map = c(results = "notthere")), silent = TRUE))

## [1] "Error in slot(items, tbl) : \n  no slot of name \"notthere\" for this object of class \"myclass\"\n"
## attr(,"class")
## [1] "try-error"
## attr(,"condition")
## <simpleError in slot(items, tbl): no slot of name "notthere" for this object of class "myclass">

stopifnot(inherits(y, "try-error"))
## note that non-matching names would be silently ignored

[Package pkgutils version 0.7.63 Index]