Driver
dbGetInfo("DBIDriver") (deprecated)
Return value of dbGetInfo has necessary elements. Repeated loading, instantiation, and unloading
Repeated load, instantiation, and unload of package in a new R session. Connection
Construction: dbConnect("DBIDriver") and dbDisconnect("DBIConnection", "ANY")
Can connect and disconnect, connection object inherits from
"DBIConnection".
Repeated disconnect throws warning. dbDataType("DBIConnection", "ANY")
SQL Data types exist for all basic R data types. dbDataType() does not
throw an error and returns a nonempty atomic character dbGetInfo("DBIConnection") (deprecated)
Return value of dbGetInfo has necessary elements Stress tests
Open 50 simultaneous connections
Open and close 50 connections
Repeated load, instantiation, connection, disconnection, and unload of
package in a new R session. Result
Construction: dbSendQuery("DBIConnection") and dbClearResult("DBIResult")
Can issue trivial query, result object inherits from "DBIResult".
Return value, currently tests that the return value is always
TRUE, and that an attempt to close a closed result set issues a
warning.
Leaving a result open when closing a connection gives a warning.
Can issue a command query that creates a table, inserts a row, and
deletes it; the result sets for these query always have "completed"
status.
Issuing an invalid query throws error (but no warnings, e.g. related to
pending results, are thrown). dbFetch("DBIResult") and dbHasCompleted("DBIResult")
Single-value queries can be fetched.
Multi-row single-column queries can be fetched.
Multi-row queries can be fetched progressively.
If more rows than available are fetched, the result is returned in full
but no warning is issued.
If zero rows are fetched, the result is still fully typed.
If less rows than available are fetched, the result is returned in full
but no warning is issued.
Side-effect-only queries (without return value) can be fetched.
Fetching from a closed result set raises an error.
Querying a disconnected connection throws error. dbGetQuery("DBIConnection", "ANY")
Single-value queries can be read with dbGetQuery
Multi-row single-column queries can be read with dbGetQuery.
Empty single-column queries can be read with
DBI::dbGetQuery(). Not all SQL dialects support the query
used here.
Single-row multi-column queries can be read with dbGetQuery.
Multi-row multi-column queries can be read with dbGetQuery.
Empty multi-column queries can be read with
DBI::dbGetQuery(). Not all SQL dialects support the query
used here. Create table with data type
SQL Data types exist for all basic R data types, and the engine can
process them.
SQL data type for factor is the same as for character. Data roundtrip
Data conversion from SQL to R: integer
Data conversion from SQL to R: integer with typed NULL values.
Data conversion from SQL to R: integer with typed NULL values
in the first row.
Data conversion from SQL to R: numeric.
Data conversion from SQL to R: numeric with typed NULL values.
Data conversion from SQL to R: numeric with typed NULL values
in the first row.
Data conversion from SQL to R: logical. Optional, conflict with the
data_logical_int test.
Data conversion from SQL to R: logical with typed NULL values.
Data conversion from SQL to R: logical with typed NULL values
in the first row
Data conversion from SQL to R: logical (as integers). Optional,
conflict with the data_logical test.
Data conversion from SQL to R: logical (as integers) with typed NULL
values.
Data conversion from SQL to R: logical (as integers) with typed NULL
values
in the first row.
Data conversion from SQL to R: A NULL value is returned as NA.
Data conversion from SQL to R: 64-bit integers.
Data conversion from SQL to R: 64-bit integers with typed NULL values.
Data conversion from SQL to R: 64-bit integers with typed NULL values
in the first row.
Data conversion from SQL to R: character.
Data conversion from SQL to R: character with typed NULL values.
Data conversion from SQL to R: character with typed NULL values
in the first row.
Data conversion from SQL to R: raw. Not all SQL dialects support the
syntax of the query used here.
Data conversion from SQL to R: raw with typed NULL values.
Data conversion from SQL to R: raw with typed NULL values
in the first row.
Data conversion from SQL to R: date, returned as integer with class.
Data conversion from SQL to R: date with typed NULL values.
Data conversion from SQL to R: date with typed NULL values
in the first row.
Data conversion from SQL to R: time.
Data conversion from SQL to R: time with typed NULL values.
Data conversion from SQL to R: time with typed NULL values
in the first row.
Data conversion from SQL to R: time (using alternative syntax with
parentheses for specifying time literals).
Data conversion from SQL to R: time (using alternative syntax with
parentheses for specifying time literals) with typed NULL values.
Data conversion from SQL to R: time (using alternative syntax with
parentheses for specifying time literals) with typed NULL values
in the first row.
Data conversion from SQL to R: timestamp.
Data conversion from SQL to R: timestamp with typed NULL values.
Data conversion from SQL to R: timestamp with typed NULL values
in the first row.
Data conversion from SQL to R: timestamp with time zone.
Data conversion from SQL to R: timestamp with time zone with typed NULL
values.
Data conversion from SQL to R: timestamp with time zone with typed NULL
values
in the first row.
Data conversion: timestamp (alternative syntax with parentheses
for specifying timestamp literals).
Data conversion: timestamp (alternative syntax with parentheses
for specifying timestamp literals) with typed NULL values.
Data conversion: timestamp (alternative syntax with parentheses
for specifying timestamp literals) with typed NULL values
in the first row. SQL
dbQuoteString("DBIConnection")
Can quote strings, and create strings that contain quotes and spaces.
Can quote more than one string at once by passing a character vector. dbQuoteIdentifier("DBIConnection")
Can quote identifiers that consist of letters only.
Can quote identifiers with special characters, and create identifiers
that contain quotes and spaces.
Character vectors are treated as a single qualified identifier. dbReadTable("DBIConnection") and dbWriteTable("DBIConnection")
Can write the datasets::iris data as a table to the
database, but won't overwrite by default.
Can read the datasets::iris data from a database table.
Can write the datasets::iris data as a table to the
database, will overwrite if asked.
Can write the datasets::iris data as a table to the
database, will append if asked.
Cannot append to nonexisting table.
Can write the datasets::iris data as a temporary table to
the database, the table is not available in a second connection and is
gone after reconnecting.
A new table is visible in a second connection. Roundtrip tests
Can create tables with keywords as table and column names.
Can create tables with quotes, commas, and spaces in column names and
data.
Can create tables with integer columns.
Can create tables with numeric columns.
Can create tables with numeric columns that contain special values such
as Inf and NaN.
Can create tables with logical columns.
Can create tables with logical columns, returned as integer.
Can create tables with NULL values.
Can create tables with 64-bit columns.
Can create tables with character columns.
Can create tables with factor columns.
Can create tables with raw columns.
Can create tables with date columns.
Can create tables with timestamp columns.
Can create tables with row names. dbListTables("DBIConnection")
Can list the tables in the database, adding and removing tables affects
the list. Can also check existence of a table. dbListFields("DBIConnection")
Can list the fields for a table in the database. Meta
dbIsValid("DBIConnection")
Only an open connection is valid. dbIsValid("DBIResult")
Only an open result set is valid. dbGetStatement("DBIResult")
SQL query can be retrieved from the result. dbColumnInfo("DBIResult")
Column information is correct. dbGetRowCount("DBIResult")
Row count information is correct. dbGetRowsAffected("DBIResult")
Information on affected rows is correct. dbGetInfo("DBIResult") (deprecated)
Return value of dbGetInfo has necessary elements Parametrised queries and statements
dbBind("DBIResult")
Empty binding with check of
return value.
Binding of integer values raises an
error if connection is closed.
Binding of integer values with check of
return value.
Binding of integer values with too many
values.
Binding of integer values with too few
values.
Binding of integer values, repeated.
Binding of integer values with wrong names.
Binding of integer values.
Binding of numeric values.
Binding of logical values.
Binding of logical values (coerced to integer).
Binding of NULL values.
Binding of character values.
Binding of date values.
Binding of POSIXct timestamp values.
Binding of POSIXlt timestamp values.
Binding of raw values.
Binding of statements.
Repeated binding of statements. dbBind("DBIResult")
Binding of multi-row integer values.
Binding of multi-row integer values with zero rows.
Binding of multi-row integer values with unequal length.
Binding of multi-row statements. Transactions
dbBegin("DBIConnection") and dbRollback("DBIConnection")
Filler dbWithTransaction("DBIConnection") and dbBreak()
Filler Full compliance
All of DBI
The package defines three classes that implement the required methods.
All methods have an ellipsis ... in their formals. Read-only access
Writing to the database fails. (You might need to set up a separate
test context just for this test.)