Wrapping and unwrapping with compound keys

[krlmlr]

library(conflicted)
library(dm)

schemata <- tibble::tibble(
  catalog_name = character(0),
  schema_name = character(0),
)
tables <- tibble::tibble(
  table_catalog = character(0),
  table_schema = character(0),
  table_name = character(0),
  table_type = character(0),
)
columns <- tibble::tibble(
  table_catalog = character(0),
  table_schema = character(0),
  table_name = character(0),
  column_name = character(0),
  ordinal_position = structure(numeric(0), class = "integer64"),
  column_default = character(0),
  is_nullable = character(0),
)
table_constraints <- tibble::tibble(
  constraint_catalog = character(0),
  constraint_schema = character(0),
  constraint_name = character(0),
  table_catalog = character(0),
  table_schema = character(0),
  table_name = character(0),
  constraint_type = character(0),
)
key_column_usage <- tibble::tibble(
  constraint_catalog = character(0),
  constraint_schema = character(0),
  constraint_name = character(0),
  table_catalog = character(0),
  table_schema = character(0),
  table_name = character(0),
  column_name = character(0),
  ordinal_position = structure(numeric(0), class = "integer64"),
)
constraint_column_usage <- tibble::tibble(
  table_catalog = character(0),
  table_schema = character(0),
  table_name = character(0),
  column_name = character(0),
  constraint_catalog = character(0),
  constraint_schema = character(0),
  constraint_name = character(0),
  ordinal_position = structure(numeric(0), class = "integer64"),
)

meta_simple <- dm::dm(
  schemata,
  tables,
  columns,
  table_constraints,
  key_column_usage,
  constraint_column_usage,
) %>%
  dm::dm_add_pk(schemata, c(catalog_name, schema_name)) %>%
  dm::dm_add_pk(tables, c(table_catalog, table_schema, table_name)) %>%
  dm::dm_add_pk(columns, c(table_catalog, table_schema, table_name, column_name)) %>%
  dm::dm_add_pk(table_constraints, c(constraint_catalog, constraint_schema, constraint_name)) %>%
  dm::dm_add_pk(key_column_usage, c(constraint_catalog, constraint_schema, constraint_name, ordinal_position)) %>%
  dm::dm_add_pk(constraint_column_usage, c(constraint_catalog, constraint_schema, constraint_name, ordinal_position)) %>%
  dm::dm_add_fk(tables, c(table_catalog, table_schema), schemata) %>%
  dm::dm_add_fk(columns, c(table_catalog, table_schema, table_name), tables) %>%
  dm::dm_add_fk(table_constraints, c(table_catalog, table_schema, table_name), tables) %>%
  dm::dm_add_fk(key_column_usage, c(constraint_catalog, constraint_schema, constraint_name), table_constraints) %>%
  dm::dm_add_fk(constraint_column_usage, c(constraint_catalog, constraint_schema, constraint_name, ordinal_position), key_column_usage) %>%
  dm::dm_set_colors(`#0000FFFF` = schemata) %>%
  dm::dm_set_colors(`#A52A2AFF` = tables) %>%
  dm::dm_set_colors(`#A52A2AFF` = columns) %>%
  dm::dm_set_colors(`#008B00FF` = table_constraints) %>%
  dm::dm_set_colors(`#FFA500FF` = key_column_usage) %>%
  dm::dm_set_colors(`#FFA500FF` = constraint_column_usage)

meta_simple %>%
  dm_draw(rankdir = "TB")

meta_simple %>%
  dm_wrap_tbl(schemata)
#> ── Metadata ────────────────────────────────────────────────────────────────────
#> Tables: `schemata`
#> Columns: 3
#> Primary keys: 1
#> Foreign keys: 0

meta_simple %>%
  dm_wrap_tbl(schemata) %>%
  dm_unwrap_tbl(meta_simple)
#> Error in `chr_as_locations()`:
#> ! Can't subset columns that don't exist.
#> ✖ Column `table_name` doesn't exist.

^{Created on 2022-06-28 by the reprex package (v2.0.1)}