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_<slug>_<16 hexadecimal characters>, at most 63 characters. The rule is the index rule below, with the prefixpk_and the logical namepk. The table identity is the table name on SQL Server, andschema.tableon PostgreSQL. Two different tables get two names. Schemaawith tablebcand schemaabwith tablectherefore no longer share a name.csdb:::pk_physical_name()returns the name for one table. SQLite gives the key no name.- 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
Every constraint name and every index name is lower case. PostgreSQL folds
an unquoted identifier to lower case, and SQLite and SQL Server do not.
A lower case name therefore reads the same in the source and in every
catalogue. A DROP CONSTRAINT can quote the name or leave it
unquoted, and it finds the constraint both ways.
A table that an earlier csdb release created keeps its old constraint name,
PK_ plus the table name with every ., [ and ]
deleted. PostgreSQL stores that name in lower case. csdb does not rename
it.
Indexes on a table that exists
An existing table gets its missing declared indexes on first use. The first
call that reaches the table looks for the physical name of each declared
index on that table. It creates each index that is absent, and nothing
else. Later calls on the same object do no index work, and a direct call
to create_table() is one of them. After remove_table() or
keep_rows_where(), the next create_table() call checks again.
Each new R process, and each new object, checks again.
A failed create gives a warning that names the table and the index. The call then continues with its read or write.
An index under an old name stays. A table that a release before 2026.10.4
indexed as ind1 then holds both ind1 and the new physical
name. csdb does not drop the old index.
The check reads pg_indexes on PostgreSQL and sqlite_master on
SQLite. On SQL Server one statement reads sys.indexes and creates
the index when it is absent. On any other backend csdb checks nothing.
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$create_table()
Create the database table. When the table exists and its fields differ
from field_types, this drops the table with all its rows and
creates it again.
The fields differ when the names or their order differ, as
check_fields_match() compares them. Do not call this on a table
whose rows you need, unless field_types matches the table.
You rarely call this yourself. connect(), tbl() and 12
other public methods call it once per object, through the private
method lazy_creation_of_table(). 14 of the 22 public methods
therefore can drop the table.
When the table exists and its fields match, this creates each declared index that is missing. See the section "Indexes on a table that exists".
On PostgreSQL, a field type DOUBLE creates a double precision
column. A real column declared DOUBLE in a table that exists
counts as matching. It becomes double precision with one
ALTER TABLE, and the table keeps its rows. See the section
"PostgreSQL double precision" of DBConnection_v9.
DBTable_v9$insert_data()
Inserts data into the database table.
Usage
DBTable_v9$insert_data(
newdata,
confirm_insert_via_nrow = FALSE,
verbose = TRUE,
load_timeout = 3600
)Arguments
newdataThe data to insert.
confirm_insert_via_nrowIf TRUE, a PostgreSQL or SQL Server load that stops on a duplicate key is upserted once instead. Otherwise the method counts the rows after the insert, and upserts when the table holds fewer rows than
newdata. It stops when the table still holds fewer rows thannewdataafter the upsert.verboseBoolean.
load_timeoutThe longest time in seconds that one load MAY run. On SQL Server, csdb then kills
bcpand stops with an error of classcsdb_load_ambiguous, and it does not retry the load. On PostgreSQL, see the section "PostgreSQL loads" ofDBConnection_v9. The default of 3600 s is the 60 minutes after which Airflow kills a norsyss task.
Details
For SQL Server and MySQL, the method writes newdata to a new load file in tempdir() for each call.
csdb deletes that file when the load returns, also after a failed load.
A write that fails partway leaves the file in tempdir(). PostgreSQL and SQLite write no file.
Called without file, the internal load methods default to a new file from tempfile().
They delete that default file in every case, also after a write that fails partway.
DBTable_v9$upsert_data()
Upserts data into the database table.
Usage
DBTable_v9$upsert_data(
newdata,
drop_indexes = names(self$indexes),
verbose = TRUE,
load_timeout = 3600
)Arguments
newdataThe data to insert.
drop_indexesA vector of the indexes to drop before the upsert (can increase performance).
verboseBoolean.
load_timeoutThe longest time in seconds that one load MAY run. On SQL Server, csdb then kills
bcpand stops with an error of classcsdb_load_ambiguous, and it does not retry the load. On PostgreSQL, see the section "PostgreSQL loads" ofDBConnection_v9. The default of 3600 s is the 60 minutes after which Airflow kills a norsyss task.
Details
The load file follows the same rules as in insert_data(): a new file in tempdir() for each call, deleted when the load returns.
DBTable_v9$replace_all_rows()
Replaces every row of the database table with newdata, in one
transaction. When a statement fails, the table keeps its old rows.
The rows go through DBI::dbAppendTable(), not through bcp or
the JSON load. Use this method for small tables.
The method stops before any statement unless newdata has each
name in names(field_types) once, and no other column.
A change of field_types still drops and creates the table again
first, through create_table(). The first call after a schema
change can therefore start from an empty table.
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,
load_timeout = 3600
)Arguments
newdataThe data to insert.
drop_indexesA vector of the indexes to drop before the upsert (can increase performance).
verboseBoolean.
load_timeoutThe longest time in seconds that one load MAY run. On SQL Server, csdb then kills
bcpand stops with an error of classcsdb_load_ambiguous, and it does not retry the load. On PostgreSQL, see the section "PostgreSQL loads" ofDBConnection_v9. The default of 3600 s is the 60 minutes after which Airflow kills a norsyss task.
DBTable_v9$drop_all_rows_and_then_insert_data()
Drops all rows in the database table and then inserts data.
Usage
DBTable_v9$drop_all_rows_and_then_insert_data(
newdata,
confirm_insert_via_nrow = FALSE,
verbose = TRUE,
load_timeout = 3600
)Arguments
newdataThe data to insert.
confirm_insert_via_nrowIf TRUE, a PostgreSQL or SQL Server load that stops on a duplicate key is upserted once instead. Otherwise the method counts the rows after the insert, and upserts when the table holds fewer rows than
newdata. It stops when the table still holds fewer rows thannewdataafter the upsert.verboseBoolean.
load_timeoutThe longest time in seconds that one load MAY run. On SQL Server, csdb then kills
bcpand stops with an error of classcsdb_load_ambiguous, and it does not retry the load. On PostgreSQL, see the section "PostgreSQL loads" ofDBConnection_v9. The default of 3600 s is the 60 minutes after which Airflow kills a norsyss task.
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
#> Added missing index ind1 to table my_data_table
#> Adding index ind1
#> NULL
# 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()
# }