R6 Class representing a database table with advanced data management capabilities
Source:R/r6_DBTable_v9.R
DBTable_v9.RdA comprehensive database table management class that provides high-level operations for data manipulation, schema validation, and table administration. This class combines database connectivity with data validation and efficient bulk operations.
Details
The DBTable_v9 class is a sophisticated database table abstraction that provides:
Core functionality:
Table creation and schema management
Data insertion with bulk loading capabilities
Upsert operations (insert or update)
Index management (creation, deletion)
Data validation through customizable validators
Integration with dplyr for data queries
Advanced features:
Automatic table creation based on field specifications
Schema validation with custom validator functions
Efficient bulk data loading using database-specific methods
Index optimization for query performance
Cross-database compatibility (SQL Server, PostgreSQL)
Data validation: The class supports custom validation functions for both field types and data contents, ensuring data integrity and schema compliance.
Public fields
dbconnectionDatabase connection.
dbconfigConfiguration details of the database.
table_nameName of the table in the database.
table_name_short_for_mssql_fully_specified_for_postgresFully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).
table_name_short_for_mssql_fully_specified_for_postgres_textFully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).
table_name_fully_specifiedFully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).
table_name_fully_specified_textFully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]) as a text string.
field_typesThe types of each column in the database table (INTEGER, DOUBLE, TEXT, BOOLEAN, DATE, DATETIME).
field_types_with_lengthThe same as
field_typesbut with(100)added to the end of all TEXT fields.keysThe combination of variables that uniquely identify each row in the database.
keys_with_lengthThe same as
keysbut with(100)added to the end of all TEXT fields.indexesA named list of vectors (generally "ind1", "ind2", etc.) that improves the speed of data retrieval operations on a database table.
validator_field_contentsA function that validates the data before it is inserted into the database.
load_folderA temporary folder that is used to write data to before inserting into the database.
censorsA named list of censors.
Methods
DBTable_v9$new()
Create a new DBTable_v9 object.
Usage
DBTable_v9$new(
dbconfig,
table_name,
field_types,
keys,
indexes = NULL,
validator_field_types = validator_field_types_blank,
validator_field_contents = validator_field_contents_blank
)Arguments
dbconfigConfiguration details of the database (driver, server, port, db, schema, user, password, trusted_connection, sslmode, role_create_table).
table_nameName of the table in the database.
field_typesThe types of each column in the database table (INTEGER, DOUBLE, TEXT, BOOLEAN, DATE, DATETIME).
keysThe combination of these variables uniquely identifies each row of data in the table.
indexesA named list of vectors (generally "ind1", "ind2", etc.) that improves the speed of data retrieval operations on a database table.
validator_field_typesA function that validates the
field_typesbefore the DB schema is created.validator_field_contentsA function that validates the data before it is inserted into the database.
DBTable_v9$upsert_data()
Upserts data into the database table
Usage
DBTable_v9$upsert_data(
newdata,
drop_indexes = names(self$indexes),
verbose = TRUE
)DBTable_v9$drop_all_rows_and_then_upsert_data()
Drops all rows in the database table and then upserts data.
Usage
DBTable_v9$drop_all_rows_and_then_upsert_data(
newdata,
drop_indexes = names(self$indexes),
verbose = TRUE
)DBTable_v9$drop_all_rows_and_then_insert_data()
Drops all rows in the database table and then inserts data.
DBTable_v9$print_dplyr_select()
Prints a template dplyr::select call that you can easily copy/paste for all your variables.
DBTable_v9$confirm_indexes()
Confirms that the names and number of indexes in the database are the same as in the R code. Does not confirm the contents of the indexes!
Examples
if (FALSE) { # \dontrun{
# Create database connection
db_config <- list(
driver = "ODBC Driver 17 for SQL Server",
server = "localhost",
db = "mydb",
user = "myuser",
password = "mypass"
)
# Define table schema
field_types <- c(
"id" = "INTEGER",
"name" = "TEXT",
"value" = "DOUBLE",
"date_created" = "DATE"
)
# Create table object
my_table <- DBTable_v9$new(
dbconfig = db_config,
table_name = "my_data_table",
field_types = field_types,
keys = c("id"),
validator_field_types = validator_field_types_blank,
validator_field_contents = validator_field_contents_blank
)
# Create table in database
my_table$create_table()
# Insert data
sample_data <- data.frame(
id = 1:3,
name = c("Alice", "Bob", "Charlie"),
value = c(10.5, 20.3, 15.7),
date_created = as.Date("2023-01-01")
)
my_table$insert_data(sample_data)
# Query data using dplyr
result <- my_table$tbl() |>
dplyr::filter(value > 15) |>
dplyr::collect()
# Add indexes for performance
my_table$add_indexes(c("name", "date_created"))
# Upsert (insert or update) data
new_data <- data.frame(
id = 2:4,
name = c("Bob_Updated", "Charlie", "David"),
value = c(25.0, 15.7, 30.2),
date_created = as.Date("2023-01-02")
)
my_table$upsert_data(new_data)
} # }