DBI package objects and functions in R6 rocker class

Nikolaus Pawlowski


DBI objects

rocker class encapsulates the DBI objects driver, connection and result. If required, these objects can be directly used with DBI functions. However, it is recommended to use this option with care! Direct usage of DBI functions, may disrupt proper function of rocker class. Many DBI functions are implemented in rocker class. Whenever possible, use the rocker class functions.

Prepare object

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$.drv # Empty driver
#> NULL
db$.con # Empty connection
#> NULL
db$.res # Empty result
#> NULL

DBIDriver-class

db$setupSQLite() # Setup SQLite database
#> Dctr | Driver load RSQLite
db$.drv # 'DBI' DBIDriver-class
#> <SQLiteDriver>
db$getInfoDrv() # 'rocker' class function
#> Dctr | Driver info 2.2.9 (driver.version), 3.37.0 (client.version)
#> $driver.version
#> [1] '2.2.9'
#> 
#> $client.version
#> [1] '3.37.0'
DBI::dbGetInfo(db$.drv) # Direct usage of 'DBI' function on 'rocker' class
#> $driver.version
#> [1] '2.2.9'
#> 
#> $client.version
#> [1] '3.37.0'
RSQLite::dbGetInfo(db$.drv) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $driver.version
#> [1] '2.2.9'
#> 
#> $client.version
#> [1] '3.37.0'

DBIConnection-class

db$connect() # Open connection
#> DCtr | Database connected
db$.con # 'DBI' DBIConnection-class
#> <SQLiteConnection>
#>   Path: :memory:
#>   Extensions: TRUE
db$getInfoCon() # 'rocker' class function
#> DCtr | Connection info 3.37.0 (db.version), :memory: (dbname), NA (username), NA (host), NA (port)
#> $db.version
#> [1] "3.37.0"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA
DBI::dbGetInfo(db$.con) # Direct usage of 'DBI' function on 'rocker' class
#> $db.version
#> [1] "3.37.0"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA
RSQLite::dbGetInfo(db$.con) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $db.version
#> [1] "3.37.0"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA

Prepare table

db$writeTable("mtcars", mtcars) # Create table for testing
#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32

DBIResult-class

db$sendQuery("SELECT * FROM mtcars;") # Send query
#> DCtR | Send query 21 characters
db$.res # 'DBI' DBIResult-class
#> <SQLiteResult>
#>   SQL  SELECT * FROM mtcars;
#>   ROWS Fetched: 0 [incomplete]
#>        Changed: 0
db$getInfoRes() # 'rocker' class function
#> DCtR | Result info SELECT * FROM mtcars; (statement), 0 (row.count), 0 (rows.affected), FALSE (has.completed)
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE
DBI::dbGetInfo(db$.res) # Direct usage of 'DBI' function on 'rocker' class
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE
RSQLite::dbGetInfo(db$.res) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE

Clean up

db$clearResult() # Clean up result
#> DCtr | Clear result
db$.res # Empty result
#> NULL
db$disconnect() # Close connection
#> Dctr | Database disconnected
db$.con # Empty connection
#> NULL
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite
db$.drv # Empty driver
#> NULL

DBI functions in rocker

Generally, rocker function names are related to DBI function names. In rocker functions, the leading db is removed.

In DBI most functions need to be supplied with a driver (drv), connection (conn) or result (res) object. In rocker, functions automatically access the corresponding objects (.drv, .con and .res) stored in the class.

DBI example

drv <- RSQLite::SQLite() # SQLite driver
DBI::dbCanConnect( # Test parameter
  drv = drv,
  dbname = ":memory:"
)
#> [1] TRUE
con <- DBI::dbConnect( # Open connection
  drv = drv,
  dbname = ":memory:"
)
DBI::dbWriteTable(con, "mtcars", mtcars) # Create table for testing
res <- DBI::dbSendQuery(con, "SELECT * FROM mtcars;") # Send query
output <- DBI::dbFetch(res) # Fetch result
DBI::dbClearResult(res) # Clean up result
DBI::dbDisconnect(con) # Close connection
DBI::dbUnloadDriver(drv) # Unload driver

rocker example

db <- rocker::newDB(verbose = FALSE) # New database handling object
db$setupDriver( # Setup SQLite database
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)
db$canConnect() # Test parameter
#> [1] TRUE
db$connect() # Open connection
db$writeTable("mtcars", mtcars) # Create table for testing
db$sendQuery("SELECT * FROM mtcars;") # Send query
output <- db$fetch() # Fetch result
db$clearResult() # Clean up result
db$disconnect() # Close connection
db$unloadDriver() # Reset database handling object

List of functions

rocker function Corresponding DBI function DBI object used Comment
initialize() none none
print() none none
setupDriver() none driver from appropriate package Usually, parameters provided to dbConnect() in DBI are provided to setupDriver() in rocker
setupPostgreSQL() none none RPostgres::Postgres() is used with rocker function setupDriver()
setupMariaDB() none none RMariaDB::MariaDB() is used with rocker function setupDriver()
setupSQLite() none none RSQLite::SQLite() is used with rocker function setupDriver()
unloadDriver() dbUnloadDriver() driver
canConnect() dbCanConnect() driver Usually, parameters provided to dbCanConnect() in DBI are provided to setupDriver() in rocker
connect() dbConnect() driver Usually, parameters provided to dbConnect() in DBI are provided to setupDriver() in rocker
disconnect() dbDisconnect() connection
sendQuery() dbSendQuery() connection
getQuery() Is not using dbGetQuery(), but has the same function connection Especially, combination of rocker functions sendQuery(), fetch() and clearResult()
sendStatement() dbSendStatement() connection
execute() Is not using dbExecute(), but has the same function connection Especially, combination of rocker functions sendStatement() and clearResult()
fetch() dbFetch() result
hasCompleted() dbHasCompleted() result
getRowsAffected() dbGetRowsAffected() result
getRowCount() dbGetRowCount() result
columnInfo() dbColumnInfo() result
getStatement() dbGetStatement() result
clearResult() dbClearResult() result
begin() dbBegin() connection
commit() dbCommit() connection
rollback() dbRollback() connection
getInfoDrv() dbGetInfo() driver
getInfoCon() dbGetInfo() connection
getInfoRes() dbGetInfo() result
isValidDrv() dbIsValid() driver
isValidCon() dbIsValid() connection
isValidRes() dbIsValid() result
createTable() dbCreateTable() connection
appendTable() dbAppendTable() connection
writeTable() dbWriteTable connection
readTable() dbReadTable connection
removeTable() dbRemoveTable() connection
existsTable() dbExistsTable() connection
listFields() dbListFields() connection
listObjects() dbListObjects() connection
listTables() dbListTables() connection