rocker – R6 database interface class wrapping DBI

rocker – R6 database interface class wrapping DBI
Installation
New rocker class object
Additional packages and database types
Database connection
Password storage
DBI objects
DBI functions in rocker
- List of functions
Transaction
Usage of S3 and R6 functions
Further help

rocker – R6 database interface class wrapping DBI

R6 class interface for handling relational database connections using DBI package as backend. The class allows handling of connections to e.g. PostgreSQL, MariaDB and SQLite. The purpose is having an intuitive object allowing straightforward handling of SQL databases.

Installation

Installation of current released version from CRAN

install.packages("rocker")

Installation of current development version from GitHub

install.packages("devtools")
devtools::install_github("nikolaus77/rocker")

New rocker class object

Create new rocker database handling object

Option 1

db <- rocker::newDB() # New database handling object
#> dctr | New object

Option 2

db <- rocker::rocker$new() # New database handling object
#> dctr | New object

Terminal output

Controlling terminal output

db <- rocker::newDB(verbose = TRUE) # New database handling object
#> dctr | New object
db$setupPostgreSQL()
#> Dctr | Driver load RPostgres
db$unloadDriver()
#> dctr | Driver unload RPostgres

db$verbose <- FALSE # Terminal output off
db$setupPostgreSQL()
db$unloadDriver()

db$verbose <- TRUE # Terminal output on (default)
db$setupPostgreSQL()
#> Dctr | Driver load RPostgres
db$unloadDriver()
#> dctr | Driver unload RPostgres

Structure of terminal output

Dctr | Driver load RSQLite
D                          = Driver     (D = loaded,    d = not set)
 c                         = Connection (C = opened,    c = closed)
  t                        = Transation (T = active,    t = no tranastion)
   r                       = Result     (R = available, r = no result)
       Driver load RSQLite = Message text

Optional object ID

Optionally, rocker object can be labeled with an ID. This can be helpful in case terminal output of multiple rocker objects need to be distinguished.

db1 <- rocker::newDB(id = "myDB 1") # New database handling object with ID
#> myDB 1 | dctr | New object id myDB 1
db2 <- rocker::newDB(id = "myDB 2") # New database handling object with ID
#> myDB 2 | dctr | New object id myDB 2
db1$setupPostgreSQL()
#> myDB 1 | Dctr | Driver load RPostgres
db2$setupMariaDB()
#> myDB 2 | Dctr | Driver load RMariaDB
db1$unloadDriver()
#> myDB 1 | dctr | Driver unload RPostgres
db2$unloadDriver()
#> myDB 2 | dctr | Driver unload RMariaDB

db1$id <- NULL # Remove ID
db1$setupSQLite()
#> Dctr | Driver load RSQLite
db1$unloadDriver()
#> dctr | Driver unload RSQLite

db1$id <- "newID 1" # Add new ID
db1$setupSQLite()
#> newID 1 | Dctr | Driver load RSQLite
db1$unloadDriver()
#> newID 1 | dctr | Driver unload RSQLite

Object properties

Object properties are stored in the info field and can be displayed by print function.

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupPostgreSQL()
#> Dctr | Driver load RPostgres
db$info
#> $package
#> [1] "RPostgres"
#> 
#> $host
#> [1] "127.0.0.1"
#> 
#> $port
#> [1] "5432"
#> 
#> $dbname
#> [1] "mydb"

db
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery null
#> database        
#>   package       RPostgres
#>   host          127.0.0.1
#>   port          5432
#>   dbname        mydb
#> status          
#>   driver        true
#>   connection    false
#>   transaction   false
#>   result        false

db$print()
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery null
#> database        
#>   package       RPostgres
#>   host          127.0.0.1
#>   port          5432
#>   dbname        mydb
#> status          
#>   driver        true
#>   connection    false
#>   transaction   false
#>   result        false

print(db)
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery null
#> database        
#>   package       RPostgres
#>   host          127.0.0.1
#>   port          5432
#>   dbname        mydb
#> status          
#>   driver        true
#>   connection    false
#>   transaction   false
#>   result        false

db$unloadDriver()
#> dctr | Driver unload RPostgres

Connection validation – Is the earlier opened database connection still open?

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupSQLite()
#> Dctr | Driver load RSQLite
db$print()
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery null
#> database        
#>   package       RSQLite
#>   dbname        :memory:
#> status          
#>   driver        true
#>   connection    false
#>   transaction   false
#>   result        false

During connection setup, a validateQuery is looked up automatically.

db$connect()
#> DCtr | Database connected
db$print()
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery SELECT 1
#> database        
#>   package       RSQLite
#>   dbname        :memory:
#> status          
#>   driver        true
#>   connection    true
#>   transaction   false
#>   result        false

Discovered validateQuery

db$validateQuery
#> [1] "SELECT 1"

Validate connection

db$validateCon()
#> DCtr | Connection valid true
#> [1] TRUE

If required, validateQuery can be defined manually.

db$validateQuery <- "SELECT 2"
db$validateCon()
#> DCtr | Connection valid true
#> [1] TRUE
db$print()
#> object          
#>   id            null
#>   verbose       true
#>   validateQuery SELECT 2
#> database        
#>   package       RSQLite
#>   dbname        :memory:
#> status          
#>   driver        true
#>   connection    true
#>   transaction   false
#>   result        false

Clean up

db$disconnect()
#> Dctr | Database disconnected
db$validateCon()
#> Dctr | Connection valid false
#> [1] FALSE
db$unloadDriver()
#> dctr | Driver unload RSQLite

Additional packages and database types

The listed packages are required for some functions of rocker.

RSQLite package

RSQLite package for handling of SQLite database connections. It is required for the setupSQLite() function of rocker class.

install.packages("RSQLite")

Setup database

Option 1

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupSQLite( # Setup SQLite database
  dbname = ":memory:"
)
#> Dctr | Driver load RSQLite
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite

Option 2

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupDriver( # Setup SQLite database
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)
#> Dctr | Driver load RSQLite
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite

RPostgres package

RPostgres package for handling of PostgreSQL database connections. It is required for the setupPostgreSQL() function of rocker class.

install.packages("RPostgres")

Setup database

Option 1

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupPostgreSQL( # Setup PostgreSQL database
  host = "127.0.0.1", port = "5432", dbname = "mydb",
  user = "postgres", password = "password"
)
#> Dctr | Driver load RPostgres
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RPostgres

Option 2

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupDriver( # Setup PostgreSQL database
  drv = RPostgres::Postgres(),
  host = "127.0.0.1", port = "5432", dbname = "mydb",
  user = "postgres", password = "password"
)
#> Dctr | Driver load RPostgres
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RPostgres

RMariaDB package

RMariaDB package for handling of MariaDB and MySQL database connections. It is required for the setupMariaDB() function of rocker class.

install.packages("RMariaDB")

Setup database

Option 1

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupMariaDB( # Setup MariaDB database
  host = "127.0.0.1", port = "3306", dbname = "mydb",
  user = "root", password = "password"
)
#> Dctr | Driver load RMariaDB
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RMariaDB

Option 2

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupDriver( # Setup MariaDB database
  drv = RMariaDB::MariaDB(),
  host = "127.0.0.1", port = "3306", dbname = "mydb",
  user = "root", password = "password"
)
#> Dctr | Driver load RMariaDB
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RMariaDB

crayon package

The crayon package is required for colored terminal output. If missing terminal output is monochrome.

install.packages("crayon")

Database connection

There are different ways to open a connection and to get data.

Prepare database with a table

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupSQLite(dbname = tempfile()) # Setup SQLite database
#> Dctr | Driver load RSQLite
db$connect() # Open connection
#> DCtr | Database connected
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
db$disconnect() # Close connection
#> Dctr | Database disconnected

Example 1

Get query with automatic connection / disconnection

output <- db$getQuery("SELECT * FROM mtcars;") # Get query
#> DCtr | Database connected 
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result 
#> Dctr | Database disconnected

Example 2

Get query with manual connection / disconnection

db$connect() # Open connection
#> DCtr | Database connected
output1 <- db$getQuery("SELECT * FROM mtcars;") # Get query 1
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
output2 <- db$getQuery("SELECT * FROM mtcars;", 15) # Get query 2
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows 15 -> Received 15 rows, 11 columns, 3416 bytes 
#> DCtR | Rows fetched 15 
#> DCtR | Has completed no 
#> DCtR | Fetch rows 15 -> Received 15 rows, 11 columns, 3416 bytes 
#> DCtR | Rows fetched 30 
#> DCtR | Has completed no 
#> DCtR | Fetch rows 15 -> Received 2 rows, 11 columns, 2184 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$disconnect() # Close connection
#> Dctr | Database disconnected

Example 3

Function getQuery() is a combination of functions sendQuery(), fetch() and clearResult().

db$connect() # Open connection
#> DCtr | Database connected
db$sendQuery("SELECT * FROM mtcars;") # Send query
#> DCtR | Send query 21 characters
output <- db$fetch() # Fetch result
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
db$clearResult() # Clean up result
#> DCtr | Clear result
db$disconnect() # Close connection
#> Dctr | Database disconnected

Clean up

db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite

Password storage

Some efforts were undertaken to encrypt and to protect the password in the private area of the class. The class stores the password hidden and inaccessible. Please let me know, in case you discover a way how to access the password!

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupDriver( # Setup PostgreSQL database with stored password (password and user are hidden - default behavior)
  RPostgres::Postgres(),
  host = "127.0.0.1", port = "5432", dbname = "mydb",
  user = "postgres", password = "password",
  protect = c("password", "user")
)
#> Dctr | Driver load RPostgres

db$connect() # Open connection 1; Password is stored in the class and does not need to be provided.
#> DCtr | Database connected
output1 <- db$getQuery("SELECT * FROM mtcars;") # Get query 1
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$disconnect() # Close connection 1
#> Dctr | Database disconnected

db$connect() # Open connection 2; Password is stored in the class and does not need to be provided.
#> DCtr | Database connected
output2 <- db$getQuery("SELECT * FROM mtcars;") # Get query 2
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$disconnect() # Close connection 2
#> Dctr | Database disconnected

db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RPostgres

In case you do not want to store the password in the class, you will need to provide it each time a connection is opened.

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupDriver( # Setup PostgreSQL database without stored password
  RPostgres::Postgres(),
  host = "127.0.0.1", port = "5432", dbname = "mydb",
  user = "postgres"
)
#> Dctr | Driver load RPostgres

db$connect(password = "password") # Open connection 1; Password needs to be provided.
#> DCtr | Database connected
output1 <- db$getQuery("SELECT * FROM mtcars;") # Get query 1
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$disconnect() # Close connection 1
#> Dctr | Database disconnected

db$connect(password = "password") # Open connection 2; Password needs to be provided.
#> DCtr | Database connected
output2 <- db$getQuery("SELECT * FROM mtcars;") # Get query 2
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$disconnect() # Close connection 2
#> Dctr | Database disconnected

db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RPostgres

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

Transaction

Setup database and a table with 32 rows.

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupSQLite() # Setup SQLite database
#> Dctr | Driver load RSQLite
db$connect() # Open connection
#> DCtr | Database connected
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
output <- db$getQuery("SELECT * FROM mtcars;") # Get query -> 32 rows
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes 
#> DCtR | Rows fetched 32 
#> DCtR | Has completed yes 
#> DCtr | Clear result
db$transaction # Transaction indicator
#> [1] FALSE

Starting with a table with 32 rows, begin transaction 1. Delete 15 rows and commit transaction. Operations results in a table with 17 rows.

db$begin() # Start transaction 1
#> DCTr | Transaction begin
db$transaction # Transaction indicator
#> [1] TRUE
AFFECTED <- db$execute("DELETE FROM mtcars WHERE gear = 3;") # Modify table -> 15 rows
#> DCTR | Send statement 34 characters 
#> DCTR | Rows affected 15 
#> DCTr | Clear result
db$commit() # Commit transaction 1
#> DCtr | Transaction commit
db$transaction # Transaction indicator
#> [1] FALSE
output <- db$getQuery("SELECT * FROM mtcars;") # Get query -> 17 rows
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 17 rows, 11 columns, 3504 bytes 
#> DCtR | Rows fetched 17 
#> DCtR | Has completed yes 
#> DCtr | Clear result

Starting with a table with 17 rows, begin transaction 2. Delete 5 rows and rollback transaction. Operations results in a table with 17 rows.

db$begin() # Start transaction 2
#> DCTr | Transaction begin
db$transaction # Transaction indicator
#> [1] TRUE
AFFECTED <- db$execute("DELETE FROM mtcars WHERE gear = 5;") # Modify table -> 5 rows
#> DCTR | Send statement 34 characters 
#> DCTR | Rows affected 5 
#> DCTr | Clear result
output <- db$getQuery("SELECT * FROM mtcars;") # Get query -> 12 rows
#> DCTR | Send query 21 characters 
#> DCTR | Fetch rows all -> Received 12 rows, 11 columns, 3416 bytes 
#> DCTR | Rows fetched 12 
#> DCTR | Has completed yes 
#> DCTr | Clear result
db$rollback() # Rollback transaction 2
#> DCtr | Transaction rollback
db$transaction # Transaction indicator
#> [1] FALSE
output <- db$getQuery("SELECT * FROM mtcars;") # Get query -> 17 rows
#> DCtR | Send query 21 characters 
#> DCtR | Fetch rows all -> Received 17 rows, 11 columns, 3504 bytes 
#> DCtR | Rows fetched 17 
#> DCtR | Has completed yes 
#> DCtr | Clear result

Clean up

db$disconnect() # Close connection
#> Dctr | Database disconnected
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite

Usage of S3 and R6 functions

Although rocker is a R6 class, functions can be also accesses in classical S3 way.

S3 example

library(rocker)
db <- newDB()
#> dctr | New object
setupDriver(db, drv = RSQLite::SQLite(), dbname = ":memory:")
#> Dctr | Driver load RSQLite
connect(db)
#> DCtr | Database connected
writeTable(db, "mtcars", mtcars)
#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32
sendQuery(db, "SELECT * FROM mtcars;")
#> DCtR | Send query 21 characters
output <- fetch(db)
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
clearResult(db)
#> DCtr | Clear result
disconnect(db)
#> Dctr | Database disconnected
unloadDriver(db)
#> dctr | Driver unload RSQLite

R6 example

db <- rocker::newDB()
#> dctr | New object
db$setupDriver(drv = RSQLite::SQLite(), dbname = ":memory:")
#> Dctr | Driver load RSQLite
db$connect()
#> DCtr | Database connected
db$writeTable("mtcars", mtcars)
#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32
db$sendQuery("SELECT * FROM mtcars;")
#> DCtR | Send query 21 characters
output <- db$fetch()
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
db$clearResult()
#> DCtr | Clear result
db$disconnect()
#> Dctr | Database disconnected
db$unloadDriver()
#> dctr | Driver unload RSQLite

Further help

Please read the documentation of rocker class.

help("rocker-package")
help("rocker-R6-class")
help("rocker-S3-functions")
help("rocker-README")

Reading of DBI package documentation is recommended.

CRAN
GitHub

Name		Name	Last commit message	Last commit date
Latest commit History 69 Commits
.github		.github
R		R
man		man
tests		tests
vignettes		vignettes
.Rbuildignore		.Rbuildignore
.gitignore		.gitignore
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
DESCRIPTION		DESCRIPTION
LICENSE		LICENSE
LICENSE.md		LICENSE.md
NAMESPACE		NAMESPACE
NEWS.md		NEWS.md
README.Rmd		README.Rmd
README.md		README.md
cran-comments.md		cran-comments.md
rocker.Rproj		rocker.Rproj

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Licenses found

Repository files navigation

rocker – R6 database interface class wrapping DBI

Installation

New rocker class object

Additional packages and database types

RSQLite package

RPostgres package

RMariaDB package

crayon package

Database connection

Password storage

DBI objects

DBIDriver-class

DBIConnection-class

DBIResult-class

DBI functions in rocker

List of functions

Transaction

Usage of S3 and R6 functions

Further help

About

Licenses found

Releases 4

Languages

License

Licenses found

nikolaus77/rocker

Folders and files

Latest commit

History

Repository files navigation

rocker – R6 database interface class wrapping DBI

Installation

New rocker class object

Additional packages and database types

RSQLite package

RPostgres package

RMariaDB package

crayon package

Database connection

Password storage

DBI objects

DBIDriver-class

DBIConnection-class

DBIResult-class

DBI functions in rocker

List of functions

Transaction

Usage of S3 and R6 functions

Further help

About

Topics

Resources

License

Licenses found

Code of conduct

Stars

Watchers

Forks

Releases 4

Languages