Build a SQLite database of clinical code look up and mapping tables

Source: R/lookups_and_mappings.R

Write the output from build_all_lkps_maps to a SQLite database.

all_lkps_maps_to_db(
  all_lkps_maps = build_all_lkps_maps(),
  db_path = "all_lkps_maps.db",
  overwrite = FALSE
)

Arguments

all_lkps_maps

A named list of look up and mapping tables, created by build_all_lkps_maps.

db_path

Where the database will be created. If an SQLite database file already exists here, then the lookup and mapping tables will be added to this. If NULL (default), then no database will be created/modified.

overwrite

If TRUE, overwrite tables in the database if they already exist. Default value is FALSE.

Value

Returns db_path invisibly

Examples

# build dummy all_lkps_maps resource (supressing warning messages)
all_lkps_maps_dummy <- build_all_lkps_maps_dummy()

# write to SQLite database file
db_path <- suppressMessages(
  all_lkps_maps_to_db(
    all_lkps_maps = all_lkps_maps_dummy,
    db_path = tempfile()
  )
)

# connect to SQLite database
con <- DBI::dbConnect(RSQLite::SQLite(), db_path)

# create named list of tbl_dbi objects
all_lkps_maps_dummy_db <- ukbwranglr::db_tables_to_list(con)

head(all_lkps_maps_dummy_db$icd10_lkp)
#> # Source:   SQL [6 x 13]
#> # Database: sqlite 3.38.5 [/tmp/RtmpJSGmQJ/filee4ac6e7932cd]
#>   .rowid ICD10_CODE ALT_CODE USAGE   USAGE_UK DESCRIPTION  MODIFIER_4 MODIFIER_5
#>    <int> <chr>      <chr>    <chr>   <chr>    <chr>        <chr>      <chr>     
#> 1      1 A00        A00      DEFAULT 3        Cholera      NA         NA        
#> 2      2 A00.0      A000     DEFAULT 3        Cholera due… NA         NA        
#> 3      3 A00.1      A001     DEFAULT 3        Cholera due… NA         NA        
#> 4      4 A00.9      A009     DEFAULT 3        Cholera, un… NA         NA        
#> 5      5 A01.0      A010     DEFAULT 3        Typhoid fev… NA         NA        
#> 6      6 A02        A02      DEFAULT 3        Other salmo… NA         NA        
#> # … with 5 more variables: QUALIFIERS <chr>, GENDER_MASK <chr>, MIN_AGE <chr>,
#> #   MAX_AGE <chr>, TREE_DESCRIPTION <chr>

# import to R with dplyr::collect()
dplyr::collect(all_lkps_maps_dummy_db$icd10_lkp)
#> # A tibble: 197 × 13
#>    .rowid ICD10_CODE ALT_CODE USAGE   USAGE_UK DESCRIPTION MODIFIER_4 MODIFIER_5
#>     <int> <chr>      <chr>    <chr>   <chr>    <chr>       <chr>      <chr>     
#>  1      1 A00        A00      DEFAULT 3        Cholera     NA         NA        
#>  2      2 A00.0      A000     DEFAULT 3        Cholera du… NA         NA        
#>  3      3 A00.1      A001     DEFAULT 3        Cholera du… NA         NA        
#>  4      4 A00.9      A009     DEFAULT 3        Cholera, u… NA         NA        
#>  5      5 A01.0      A010     DEFAULT 3        Typhoid fe… NA         NA        
#>  6      6 A02        A02      DEFAULT 3        Other salm… NA         NA        
#>  7      7 A02.0      A020     DEFAULT 3        Salmonella… NA         NA        
#>  8      8 A02.1      A021     DEFAULT 3        Salmonella… NA         NA        
#>  9      9 A02.2      A022     DEFAULT 3        Localized … NA         NA        
#> 10     10 A02.8      A028     DEFAULT 3        Other spec… NA         NA        
#> # … with 187 more rows, and 5 more variables: QUALIFIERS <chr>,
#> #   GENDER_MASK <chr>, MIN_AGE <chr>, MAX_AGE <chr>, TREE_DESCRIPTION <chr>