Skip to contents

Decode a vector of foreign keys according to foreign key parent table

decode_fk_keys.Rd

In a database, there are foreign key tables containing fk values (usually strings) and fk keys (usually integers). This function converts a vector of fk keys (integers, v_key) into a vector fk values (strings) according to the database at conn.

Usage

decode_fk_keys(
  v_key,
  fk_table_name,
  conn,
  schema = schema_from_conn(conn),
  fk_parent_tables = get_all_fk_tables(conn = conn, schema = schema, collect = TRUE),
  pk_suffix = PFUPipelineTools::key_col_info$pk_suffix
)

Arguments

v_key

A vector of fk keys (typically integers) to be converted to fk values (typically strings).

fk_table_name

The name of the foreign key table in conn that contains the mapping from fk values to fk keys.

conn

A connection to the CL-PFU database. Needed only if fk_parent_tables is not provided.

schema

The data model (dm object) for the database in conn. Default is dm_from_con(conn, learn_keys = TRUE). Needed only if fk_parent_tables is not provided. See details.

fk_parent_tables

A named list of all parent tables for the foreign keys in db_table_name. See details.

pk_suffix

A string that gives the suffix for primary key columns. Default is PFUPipelineTools::key_col_info$pk_suffix.

Value

v_key decoded according to fk_parent_tables.

Details

schema is a data model (dm object) for the database in conn. Its default value (schema_from_conn(conn)) extracts the data model for the database at conn automatically. However, if the caller already has the data model, supplying it in the schema argument will save time.

fk_parent_tables is a named list of tables, one of which (the one named fk_table_name) contains the foreign keys v. fk_parent_tables is treated as a store from which foreign key tables are retrieved by name when needed. The default value (which calls get_all_fk_tables() with collect = TRUE) retrieves all possible foreign key parent tables from conn, potentially a time-consuming process. For speed, pre-compute all foreign key parent tables once (via get_all_fk_tables(collect = TRUE)) and pass the list to the fk_parent_tables argument of this function.

Examples

fk_parent_tables <- list(
  Country = tibble::tribble(~CountryID, ~Country,
                            1, "USA",
                            2, "ZAF",
                            3, "GHA"))
decode_fk_keys(c(1, 1, 3),
               fk_table_name = "Country",
               fk_parent_tables = fk_parent_tables)
#> [1] "USA" "USA" "GHA"