Classdm

Thedm class consists of a collection of tables andmetadata about the tables, such as

• the names of the tables
• the names of the columns of the tables
• the primary and foreign keys of the tables to link the tablestogether
• the data (either as data frames or as references to databasetables)

All tables in adm must be obtained from the same datasource; csv files and spreadsheets would need to be imported to dataframes in R.

Examples ofdm objects

There are currently three options available for creating adm object. The relevant functions for creatingdm objects are:

1. dm()
2. as_dm()
3. new_dm()
4. dm_from_con()

To illustrate these options, we will now create the samedm in several different ways. We can use the tables fromthe well-known {nycflights13} package.

library(nycflights13)library(dm)dm(airlines, airports, flights, planes, weather)

library(nycflights13)library(dm)empty_dm<-dm()empty_dmdm(empty_dm, airlines, airports, flights, planes, weather)

as_dm(list(airlines = airlines,airports = airports,flights = flights,planes = planes,weather = weather))

sqlite_con<- dbplyr::nycflights13_sqlite()flights_dm<-dm_from_con(sqlite_con)flights_dm

base_dm<-new_dm(list(airlines = airlines,airports = airports,flights = flights,planes = planes,weather = weather))base_dm

dm_validate(base_dm)

Access tables

We can get the list of tables withdm_get_tables() andthesrc object withdm_get_con().

In order to pull a specific table from adm, use:

flights_dm[["airports"]]

But how can we use {dm}-functions to manage the primary keys of thetables in adm object?

Primary keys ofdm objects

Some useful functions for managing primary key settings are:

1. dm_add_pk()
2. dm_get_all_pks()
3. dm_rm_pk()
4. dm_enum_pk_candidates()

If you created adm object according to the examples in“Examples ofdm objects”, your objectdoes not yet have any primary keys set. So let’s add one.

We use thenycflights13 tables,i.e. flights_dm from above.

dm_has_pk(flights_dm, airports)flights_dm_with_key<-dm_add_pk(flights_dm, airports, faa)flights_dm_with_key

Thedm now has a primary key:

dm_has_pk(flights_dm_with_key, airports)

To get an overview over all tables with primary keys, usedm_get_all_pks():

dm_get_all_pks(flights_dm_with_key)

Remove a primary key:

dm_rm_pk(flights_dm_with_key, airports)%>%dm_has_pk(airports)

If you still need to get to know your data better, and it is alreadyavailable in the form of adm object, you can use thedm_enum_pk_candidates() function in order to getinformation about which columns of the table are unique keys:

dm_enum_pk_candidates(flights_dm_with_key, airports)

Theflights table does not have any one-column primarykey candidates:

dm_enum_pk_candidates(flights_dm_with_key, flights)%>% dplyr::count(candidate)

dm_add_pk() has acheck argument. If set toTRUE, the function checks if the column of the table givenby the user is unique. For performance reasons, the default ischeck = FALSE. See also [dm_examine_constraints()] forchecking all constraints in adm.

try(dm_add_pk(flights_dm, airports, tzone,check =TRUE))

Foreign keys

Useful functions for managing foreign key relations include:

1. dm_add_fk()
2. dm_get_all_fks()
3. dm_rm_fk()
4. dm_enum_fk_candidates()

Now it gets (even more) interesting: we want to define relationsbetween different tables. With thedm_add_fk() function youcan define which column of which table points to another table’scolumn.

This is done by choosing a foreign key from one table that will pointto a primary key of another table. The primary key of the referred tablemust be set withdm_add_pk().dm_add_fk() willfind the primary key column of the referenced table by itself and makethe indicated column of the child table point to it.

flights_dm_with_key%>%dm_add_fk(flights, origin, airports)

This will throw an error:

try(  flights_dm%>%dm_add_fk(flights, origin, airports))

Let’s create adm object with a foreign key relation towork with later on:

flights_dm_with_fk<-dm_add_fk(flights_dm_with_key, flights, origin, airports)

What if we tried to add another foreign key relation fromflights toairports to the object? Columndest might work, since it also contains airport codes:

try(  flights_dm_with_fk%>%dm_add_fk(flights, dest, airports,check =TRUE))

Checks are opt-in and executed only ifcheck = TRUE. Youcan still add a foreign key with the defaultcheck = FALSE.See alsodm_examine_constraints() for checking allconstraints in adm.

Get an overview of all foreign key relationswithdm_get_all_fks():

dm_get_all_fks(dm_nycflights13(cycle =TRUE))

Remove foreign key relations withdm_rm_fk() (parametercolumns = NULL means that all relations will be removed,with a message):

try(  flights_dm_with_fk%>%dm_rm_fk(table = flights,column = dest,ref_table = airports)%>%dm_get_all_fks(c(flights, airports)))flights_dm_with_fk%>%dm_rm_fk(flights, origin, airports)%>%dm_get_all_fks(c(flights, airports))flights_dm_with_fk%>%dm_rm_fk(flights,columns =NULL, airports)%>%dm_get_all_fks(c(flights, airports))

Since the primary keys are defined in thedm object, youdo not usually need to provide the referenced column name ofref_table.

Another function for getting to know your data better(cf. dm_enum_pk_candidates() in“Primary keysofdm objects”) isdm_enum_fk_candidates(). Use it to get an overview overforeign key candidates that point from one table to another:

dm_enum_fk_candidates(flights_dm_with_key, weather, airports)