A database table management class that provides operations for data manipulation, schema validation, and table administration. This class combines database connectivity with data validation and bulk operations.
Details
The DBTable_v9 class is a 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, which ensure data integrity and schema compliance.
What the object creates in the database
One object creates three kinds of thing, and each carries its own name rule.
- The table
Named
table_name, in the schema thatdbconfignames.- The primary key constraint
Named
PK_plus the fully specified table name, with every.,[and]deleted. Schemaanonwith tableanon_datatherefore givesPK_anonanon_data. Two different tables can reach one name, because the rule deletes the separator. Schemaawith tablebcand schemaabwith tablecboth givePK_abc.- One index per entry in
indexes The names you write in
indexesare logical names. Each index reaches the database under a physical name of the formix_<slug>_<16 hexadecimal characters>, at most 63 characters. The name carries the table identity, so two tables in one schema that both declareind1get two indexes.csdb:::index_physical_name()returns the name for one table and one logical name.
The case of a constraint name on PostgreSQL
The source writes PK_, in upper case. PostgreSQL folds an unquoted
identifier to lower case, so the catalogue stores pk_. Measured on
the norsyss_data1 database on 2026-08-15: 92 lower case pk_
constraint names, and 0 upper case.
A DROP CONSTRAINT that quotes the source spelling therefore fails on
PostgreSQL. Write the name unquoted, or write it in lower case.
SQLite does not fold at all. It keeps PK_MixedCase exactly as the
source writes it, so the two backends disagree on one identifier.
The physical index name has no such trap. It is lower case already, so it reads the same in the source and in both catalogues.
See also
The introduction vignette,
vignette("csdb", package = "csdb"). It builds one of these on
SQLite and inserts the bundled
nor_covid19_cases_by_time_location dataset. It also shows two
tables that declare one logical index name.
DBConnection_v9 takes the same arguments as the
dbconfig list, and one is created here to hold the connection.
Other database classes:
DBConnection_v9
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,
dbconnection = NULL
)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.
dbconnectionAn existing
DBConnection_v9to use, or NULL. The object borrows a supplied connection and does not own it.disconnect()then does nothing, so the caller decides when the connection closes. The object creates and owns a connection when this argument is NULL. It is the last argument, because a subclass can forward the earlier seven positionally.
DBTable_v9$disconnect()
Disconnect from the database. This does nothing when the connection came
from the dbconnection argument, because the caller owns that
connection.
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 copy and paste for all your variables.
DBTable_v9$add_indexes()
Adds indexes to the database table from `self$indexes`. Creates each index in `self$indexes` exactly once, even when the table does not exist yet and this call is what creates it.
The names in `self$indexes` are logical names. Each index reaches the database under a physical name. That name carries the table identity. Two tables in one schema that declare the same logical name therefore ask for different index names.
After each create, the method reads the catalogue. It raises when the index is absent from this table, and when the index covers columns other than the declared ones.
That check is defined for SQLite and for PostgreSQL, and for no other backend. On any other backend the method creates each index and does NOT verify it.
DBTable_v9$drop_indexes()
Drops all indexes from the database table.
The method drops the physical name that `add_indexes()` created, for every logical name in `self$indexes`. An index that a legacy release created under the logical name is not dropped here.
DBTable_v9$confirm_indexes()
Confirms that the database holds every index declared in `self$indexes`, on this table, with the declared columns in the declared order.
The method never drops an index to reconcile. It takes one of four actions per declared index:
present with the declared columns: nothing.
absent: add it.
present with other columns: raise.
any index csdb did not name: ignore it.
The method reads an index definition on SQLite and on PostgreSQL only. On any other backend it checks the name alone, so it cannot see a change of columns.
Examples
# Creating the object opens no connection, and the field types are
# checked while it is created. These field types do not satisfy the
# csfmt_rts_data_v1 schema, so the constructor stops.
try(DBTable_v9$new(
dbconfig = list(driver = "PostgreSQL Unicode", server = "localhost"),
table_name = "my_data_table",
field_types = c("id" = "INTEGER"),
keys = "id",
validator_field_types = validator_field_types_csfmt_rts_data_v1
))
#> Error in initialize(...) : field_types not validated in my_data_table
# \donttest{
# A full cycle on SQLite, in a file that tempfile() names. SQLite needs
# no server, so this block runs anywhere. Name a driver of
# "ODBC Driver 17 for SQL Server" or "PostgreSQL Unicode" instead, and
# nothing else in the block changes.
db_config <- list(driver = "SQLite", db = tempfile(fileext = ".sqlite"))
# Indexes are named here, because add_indexes() takes no arguments and
# reads them from the object.
my_table <- DBTable_v9$new(
dbconfig = db_config,
table_name = "my_data_table",
field_types = c(
"id" = "INTEGER",
"name" = "TEXT",
"value" = "DOUBLE",
"date_created" = "DATE"
),
keys = "id",
indexes = list("ind1" = c("name", "date_created")),
validator_field_types = validator_field_types_blank,
validator_field_contents = validator_field_contents_blank
)
my_table$create_table()
#> Creating table my_data_table
#> Adding index ind1
# insert_data() and upsert_data() need a data.table.
my_table$insert_data(data.table::data.table(
id = 1:3,
name = c("Alice", "Bob", "Charlie"),
value = c(10.5, 20.3, 15.7),
date_created = as.Date("2023-01-01")
))
# tbl() returns a lazy dbplyr reference.
my_table$tbl() |>
dplyr::filter(value > 15) |>
dplyr::collect()
#> # A tibble: 2 × 4
#> id name value date_created
#> <int> <chr> <dbl> <date>
#> 1 2 Bob 20.3 2023-01-01
#> 2 3 Charlie 15.7 2023-01-01
# Add the indexes that were named above.
my_table$add_indexes()
#> Adding index ind1
my_table$upsert_data(data.table::data.table(
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$nrow()
#> [1] 4
my_table$disconnect()
# }