Database transactions with R6 rocker class

Nikolaus Pawlowski

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