R6 Class representing a DB schema/table

The fundamental way to communicate with database tables.

Details

This class is a representation of a database table. It is the way that you can access data (e.g. `tbl()`), manipulate data (e.g. `insert_data`, `upsert_data`), and manipulate structural aspects of the database table (e.g. `add_indexes`, `drop_indexes`).

Public fields

dbconnection

Database connection.

dbconfig

Configuration details of the database.

table_name

Name of the table in the database.

table_name_short_for_mssql_fully_specified_for_postgres

Fully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).

table_name_short_for_mssql_fully_specified_for_postgres_text

Fully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).

table_name_fully_specified

Fully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]).

table_name_fully_specified_text

Fully specified name of the table in the database (e.g. \[db\].\[dbo\].\[table_name\]) as a text string.

field_types

The types of each column in the database table (INTEGER, DOUBLE, TEXT, BOOLEAN, DATE, DATETIME).

field_types_with_length

The same as field_types but with (100) added to the end of all TEXT fields.

keys

The combination of variables that uniquely identify each row in the database.

keys_with_length

The same as keys but with (100) added to the end of all TEXT fields.

indexes

A named list of vectors (generally "ind1", "ind2", etc.) that improves the speed of data retrieval operations on a database table.

validator_field_contents

A function that validates the data before it is inserted into the database.

load_folder

A temporary folder that is used to write data to before inserting into the database.

censors

A named list of censors.

Methods

Public methods

• DBTable_v9$new()

• DBTable_v9$print()

• DBTable_v9$connect()

• DBTable_v9$disconnect()

• DBTable_v9$table_exists()

• DBTable_v9$create_table()

• DBTable_v9$remove_table()

• DBTable_v9$insert_data()

• DBTable_v9$upsert_data()

• DBTable_v9$drop_all_rows()

• DBTable_v9$drop_rows_where()

• DBTable_v9$keep_rows_where()

• DBTable_v9$drop_all_rows_and_then_upsert_data()

• DBTable_v9$drop_all_rows_and_then_insert_data()

• DBTable_v9$tbl()

• DBTable_v9$print_dplyr_select()

• DBTable_v9$add_indexes()

• DBTable_v9$drop_indexes()

• DBTable_v9$confirm_indexes()

• DBTable_v9$nrow()

• DBTable_v9$info()

• DBTable_v9$clone()

---

Method 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

dbconfig

Configuration details of the database (driver, server, port, db, schema, user, password, trusted_connection, sslmode).

table_name

Name of the table in the database.

field_types

The types of each column in the database table (INTEGER, DOUBLE, TEXT, BOOLEAN, DATE, DATETIME).

keys

The combination of these variables uniquely identifies each row of data in the table.

indexes

A named list of vectors (generally "ind1", "ind2", etc.) that improves the speed of data retrieval operations on a database table.

validator_field_types

A function that validates the field_types before the DB schema is created.

validator_field_contents

A function that validates the data before it is inserted into the database.

Returns

A new `DBTable_v9` object.

---

Method print()

Class-specific print function.

Usage

DBTable_v9$print(...)

Arguments

...

Not in use.

---

Method connect()

Connect from the database

Usage

DBTable_v9$connect()

---

Method disconnect()

Disconnect from the database

Usage

DBTable_v9$disconnect()

---

Method table_exists()

Does the table exist

Usage

DBTable_v9$table_exists()

---

Method create_table()

Create the database table

Usage

DBTable_v9$create_table()

---

Method remove_table()

Drop the database table

Usage

DBTable_v9$remove_table()

---

Method insert_data()

Inserts data

Usage

DBTable_v9$insert_data(
  newdata,
  confirm_insert_via_nrow = FALSE,
  verbose = TRUE
)

Arguments

newdata

The data to insert.

confirm_insert_via_nrow

Checks nrow() before insert and after insert. If nrow() has not increased sufficiently, then attempt an upsert.

verbose

Boolean. Inserts data into the database table

---

Method upsert_data()

Upserts data into the database table

Usage

DBTable_v9$upsert_data(
  newdata,
  drop_indexes = names(self$indexes),
  verbose = TRUE
)

Arguments

newdata

The data to insert.

drop_indexes

A vector containing the indexes to be dropped before upserting (can increase performance).

verbose

Boolean.

---

Method drop_all_rows()

Drops all rows in the database table

Usage

DBTable_v9$drop_all_rows()

---

Method drop_rows_where()

Drops rows in the database table according to the SQL condition.

Usage

DBTable_v9$drop_rows_where(condition)

Arguments

condition

SQL text condition.

---

Method keep_rows_where()

Keeps rows in the database table according to the SQL condition.

Usage

DBTable_v9$keep_rows_where(condition)

Arguments

condition

SQL text condition.

---

Method 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
)

Arguments

newdata

The data to insert.

drop_indexes

A vector containing the indexes to be dropped before upserting (can increase performance).

verbose

Boolean.

---

Method 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
)

Arguments

newdata

The data to insert.

confirm_insert_via_nrow

Checks nrow() before insert and after insert. If nrow() has not increased sufficiently, then attempt an upsert.

verbose

Boolean.

---

Method tbl()

Provides access to the database table via dplyr::tbl.

Usage

DBTable_v9$tbl()

---

Method print_dplyr_select()

Prints a template dplyr::select call that you can easily copy/paste for all your variables.

Usage

DBTable_v9$print_dplyr_select()

---

Method add_indexes()

Adds indexes to the database table from `self$indexes`

Usage

DBTable_v9$add_indexes()

---

Method drop_indexes()

Drops all indees from the database table

Usage

DBTable_v9$drop_indexes()

---

Method 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!

Usage

DBTable_v9$confirm_indexes()

---

Method nrow()

Gets the number of rows in the database table

Usage

DBTable_v9$nrow(use_count = FALSE)

Arguments

use_count

If true, then uses the count command, which is slow but accurate. If false, then uses summary statistics, which is fast but inaccurate.

---

Method info()

Gets the information about the database table

Usage

DBTable_v9$info()

---

Method clone()

The objects of this class are cloneable with this method.

Usage

DBTable_v9$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.