R: Database I/O for 'opm'

opm_dbput {opm}

R Documentation

Database I/O for opm

Description

Methods for inserting, querying and deleting OPMX objects into or from (SQL-based) relational databases. A common database scheme is assumed as defined in the auxiliary SQL files of this package (run opm_files in "sql" mode), but tables could be named differently, and columns could be added containing user-defined combinations of metadata.

Usage

  ## S4 method for signature 'ANY'
opm_dbcheck(conn, metadata = NULL,
    time.points = TRUE, wells = TRUE) 

  ## S4 method for signature 'MOPMX'
opm_dbclass(object) 
  ## S4 method for signature 'OPM'
opm_dbclass(object) 
  ## S4 method for signature 'OPMS'
opm_dbclass(object) 
  ## S4 method for signature 'integer'
opm_dbclass(object) 

  ## S4 method for signature 'character,ANY'
opm_dbclear(object, conn, map.tables = NULL, klass = "OPM_DB")
  ## S4 method for signature 'integer,DBIConnection'
opm_dbclear(object, conn, map.tables = NULL, klass = "OPM_DB")

  ## S4 method for signature 'character,DBIConnection'
opm_dbfind(object, conn, map.tables = NULL, klass = "OPM_DB")

  ## S4 method for signature 'character,ANY'
opm_dbget(object, conn,
    map.tables = NULL, include = 2L, klass = c(opm_dbclass(include), "MOPMX")) 
  ## S4 method for signature 'integer,DBIConnection'
opm_dbget(object, conn,
    map.tables = NULL, include = 2L, klass = c(opm_dbclass(include), "MOPMX")) 

  ## S4 method for signature 'ANY,ANY'
opm_dbnext(object, conn, map.tables = NULL)
  ## S4 method for signature 'DBTABLES,DBIConnection'
opm_dbnext(object, conn, map.tables = NULL)

  ## S4 method for signature 'ANY,ANY'
opm_dbput(object, conn, ...) 
  ## S4 method for signature 'DBTABLES,DBIConnection'
opm_dbput(object, conn,
    map.tables = NULL, start = opm_dbnext(object, conn, map.tables))

Arguments

`object`	`OPMX`, `MOPMX` or `OPM_DB` object, integer vector containing real or potential primary keys of a database table, or character scalar containing a partial SQL query (the part after the ‘WHERE’ keyword).
`conn`	Database connection object. Currently only `DBIConnection` objects from the DBI package are supported, but they allow for using any of the reverse dependencies of DBI in conjunction with opm and thus the majority of open SQL databases. For using `RODBC` objects as created by the RODBC package see the opmextra package.
`map.tables`	Passed as `do_map` argument to `by` from the pkgutils package. Necessary if table names that deviate from the defaults are to be used.
`klass`	Character vector indicating one or several class names. For PM data this argument should not be changed.
`include`	Integer scalar indicating whether aggregated data (1) or aggregated and discretised data (2) or neither (0) should be added to the result. The numeric method of `opm_dbnext` needs the same kind of `object` argument.
`start`	Integer vector determining the minimum primary keys to which those in `object` should be coerced. Necessary for appending to a database table without overwriting previously inserted data.
`metadata`	Empty or data frame with metadata to be added to the check object `vaas_4`. If a data frame, it must contain exactly 2 rows. Adding metadata makes only sense if according columns have been added to the database table for the plates; see the examples below. The original metadata from `vaas_4` are always removed.
`time.points`	Index of one to several time points. Selection speeds up database I/O during checking.
`wells`	Index of one to several wells. Selection speeds up database I/O during checking.
`...`	Optional arguments passed between the methods.

Details

The DBIConnection methods send table and column names are through make.db.names from the DBI package or its dependencies before including them into SQL queries, if any. As dictated by by from the pkgutils packages, this is done after applying map.tables.

opm_dbcheck attempts to insert, query and delete the first two plates from the object vaas_4 into the database. If everything is correctly set up, this should work without error unless these two plates from vaas_4 have already been inserted. If errors occur, it is up to the user to clean up the data base (as far as necessary).

Note that the deletion mechanism is based on ON DELETE CASCADE. To enable this in SQLite, PRAGMA foreign_keys = ON; has to be called each time a database is opened. See the according demo entry.

Value

The main functions are those for create, search, read and delete operations:

opm_dbput returns an integer vector containing the primary keys of the inserted plates.
opm_dbfind returns an integer vector containing the primary keys of the found plates.
opm_dbget returns a MOPMX object with one element per plate type.
opm_dbclear invisibly returns the result of dbGetQuery (which is usually NULL).

Regarding the helper functions, opm_dbnext returns an integer scalar that is suitable as start argument of opm_dbput, whereas opm_dbclass returns a character scalar with the name of the intermediary class (derived from OPM_DB) to be created for database I/O. These need not normally be called by an opm user.

For checking whether a database (connection) is correctly set up, opm_dbcheck is available, which returns a character vector whose elements are either ok or a description of the error that has occurred at that step of the checking process.

Examples

# The SQL files for generating the expected database tables. Tables can
# be renamed, but then an according 'map.tables' argument must be used.
opm_files("sql")

## [1] "/usr/local/lib/R/library/opm/auxiliary/001_rdbms_drop.sql"  
## [2] "/usr/local/lib/R/library/opm/auxiliary/002_rdbms_create.sql"
## [3] "/usr/local/lib/R/library/opm/auxiliary/003_rdbms_delete.sql"

# Usage examples are given in these demos. An according database must be
# made accessible beforehand.
if (interactive())
  demo(package = "opm")

[Package opm version 1.3.63 Index]