In krlmlr/dm: Relational Data Models

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  echo = FALSE
)

For printing

> If you have more than one table, use dm.

## Create dm... ### from database: `dm_from_con()` wzxhzdk:1 wzxhzdk:2 ### from data frames: `dm(df1, df2, ...)` wzxhzdk:3 ### from dm: `dm(dm1, df1...)` wzxhzdk:4 wzxhzdk:5

---

### Add keys: `dm_add_pk()`, `dm_add_fk()` **Automatic** for MariaDB, SQL Server, Postgres, and others. #### Primary keys **Identify** potential primary keys: - `dm_enum_pk_candidates()`: columns, candidate, why. wzxhzdk:6 **Identify** potential foreign keys: - `dm_enum_fk_candidates()`: columns, candidate, why. wzxhzdk:7

::: {.important-div} ### dm objects: relational data models The dm package provides a **grammar of relational data models**. It helps maintain **referential integrity**. A dm behaves like a list of tables (data frames or lazy tables) capturing **relationships** between the tables. ::: ::: {.alert .alert-primary role="alert" style="display:grid;place-items:center;"} Shiny app: `dm_gui(dm = dm1)` wzxhzdk:8 :::

---

## Resize dm ### Select tables: `dm_select_tbl(dm1, ...)` wzxhzdk:9 ### Rename tables: `dm_rename_tbl(dm1, ...)` ### Select columns: `dm_select(dm1, table, ...)` Automatic update of dm meta-information and table relations. wzxhzdk:10 ### Rename columns: `dm_rename(dm1, table, ...)` ### Filter rows: `dm_filter(dm1, table = (pred))` Filter rows in the table where the condition is defined, but also all directly/indirectly connected tables. wzxhzdk:11

## Visualize dm: `dm_draw()` ### Control diagram level of detail: display... - Only keys (default): `dm_draw(view_type = "keys_only")`. - All variables: `dm_draw(view_type = "all")`. - Only table names: `dm_draw(view_type = "title_only")`. wzxhzdk:12 ### Control diagram scope To visualize fewer tables first use `dm_select_tbl()`. ### Control diagram colors: `dm_set_colors()` wzxhzdk:13

---

## Data checks ### `dm_examine_constraints()` tibble with information about which key constraints are met or violated. ### `dm_examine_cardinalities()` tibble with information about the cardinality of the foreign keys constraints. ### `check_key(df, col1)` returns an error if not an unique key. ### `check_subset(df1, df2)` returns an error if `df1` is not a subset of `df2`. ### `check_set_equality(df1, df2)` returns an error if `df1` and `df2` are not the same sets.

---

### Fix column names: `dm_disambiguate_cols()` `dm_disambiguate_cols(dm1)` ensures that all columns in a dm have unique names.

## Transform dm into tibble ### Wide tibble: Cascade joins with `dm_flatten_to_tbl()` Only direct neighbours: `dm_flatten_to_tbl()` wzxhzdk:14 All neighbours: `dm_flatten_to_tbl(.recursive = TRUE)` wzxhzdk:15 ### Single tibble dm: `dm_wrap_tbl()` Parent tables are packed — `dm_pack_tbl()`. Child tables are nested — `dm_nest_tbl()`. wzxhzdk:16 ### Retrieve one table of the dm: `pull_tbl()` wzxhzdk:17 wzxhzdk:18

## Mutate, create, analyze tables ### Method 1: deconstruct and reconstruct 1. `dm_get_tables(keyed = TRUE)`:
to keep information on primary and foreign keys). wzxhzdk:19 2. tidyverse pipeline on the table of interest. wzxhzdk:20 3. Optional: update the dm object: wzxhzdk:21 ### Method 2: zoom 1. `dm_zoom_to()`: Zoom on a table. wzxhzdk:22 2. tidyverse pipeline (`mutate()`, etc.). wzxhzdk:23 3. `dm_update_zoomed()` (replace) / `dm_insert_zoomed()` wzxhzdk:24

## Modify database source of a dm ### Export dm object to database: `copy_dm_to()` Need a **database connection — `DBI::dbConnect()`**. wzxhzdk:25 ## Insert, update or remove rows in a dm Methods: - `dm_rows_insert(dm1, dm2)`: adds new rows - `dm_rows_update(dm1, dm2)`: changes values in rows - `dm_rows_patch(dm1, dm2)`: fills in missing values - `dm_rows_upsert(dm1, dm2)`: adds new or changes rows - `dm_rows_delete(dm1, dm2)`: deletes rows wzxhzdk:26 wzxhzdk:27 wzxhzdk:28 wzxhzdk:29 A dm is immutable, except with - these functions AND - a mutable backend (database) AND - `in_place = TRUE`.

source(system.file("make-cheatsheet.R", package = "dm"))

krlmlr/dm documentation built on April 19, 2024, 5:23 p.m.