NEWS.md
pack_join()
works correctly if name
is the same as an existing column in either table. In some cases a column is overwritten, this is consistent with nest_join()
behavior (#864, #865).dm_insert_zoomed()
uses the color from the zoomed table for the new table (#750, #863).copy_dm_to()
consumes less memory and is faster when writing to SQL Server (#855).copy_dm_to()
shows progress bars again (#850, #855).dbAppendTable()
for zero-row tables (#847).dm_wrap_tbl()
, dm_unwrap_tbl()
, dm_nest_tbl()
, dm_unnest_tbl()
, dm_pack_tbl()
and dm_unpack_tbl()
(#595, #733, #737).dm_examine_cardinality()
(#264, #735).pack_join()
generic and method for data frames, the same to tidyr::pack()
as dplyr::nest_join()
is to tidyr::nest()
(#721, #722).dm_pixarfilms()
is exported and gains a consistent = FALSE
argument; if TRUE
the data is modified so that all referential constraints are satisfied (#703, #707, #708, @erictleung).db_schema_...()
functions no longer pro-actively check for schema existence (#672, #815, #771).db_schema_list.Microsoft SQL Server
no longer ignoring schemas for which the owner cannot be found (#815, #771).copy_dm_to()
works with DuckDB again, the SQL statements to define the tables and indexes are now created by us (#701, #709).dm_pixarfilms()
creates a dm object with data from the {pixarfilms} package (#600, @erictleung).check_cardinality_0_1()
, check_cardinality_0_n()
, check_cardinality_1_1()
, check_cardinality_1_n()
, and examine_cardinality()
now support compound keys (#524).check_subset()
and check_set_equality()
support compound keys (#523).dm_paste()
adds the on_delete
argument to dm_add_fk()
(#673).dm_disambiguate_cols()
also disambiguates columns used in keys, to support correct disambiguation for compound keys (#662).dm_disambiguate_cols()
now emits the source code equivalent of a renaming operation (#684).dm_examine_constraints()
uses backticks to surround table names (#687).decompose_table()
now avoids creating NA
values in the key column (#580).dm_draw()
works with empty tables (#585).mutate()
, transmute()
, distinct()
and summarize()
now support dplyr::across()
and extra arguments (#640).relocate()
now works on zoomed dm
objects (#666).dm_add_fk()
gains on_delete
argument which copy_dm_to()
picks up and translates to an ON DELETE CASCADE
or ON DELETE NO ACTION
specification for the foreign key (#649).dm_copy_to()
defines foreign keys during table creation, for all databases except DuckDB. Tables are created in topological order (#658). For cyclic relationship graphs, table creation is attempted in the original order and may fail (#664).waldo::compare()
shows better output for dm objects (#642).dm_paste()
output uses trailing commas in the dm::dm()
and tibble::tibble()
calls, and sorts column attributes by name, for better modularity (#641).db_schema_create()
, db_schema_drop()
, db_schema_exists()
and db_schema_list()
replace the corresponding sql_schema_*()
functions, the latter are soft-deprecated (#670). The connection argument to db_schema_*()
is called con
, not dest
(#668).copy_dm_to()
and sql_create_schema()
no longer actively check for schema existence (#644, #660).OUTPUT
clause for SQL Server (#647).sql_rows_delete()
with returning
argument for SQL Server (#645).rows_insert()
, rows_update()
and rows_delete()
gain returning
argument. In combination with in_place = TRUE
this argument makes the newly inserted rows accessible via get_returning_rows()
after the operation completes (#593, @mgirlich).rows_patch()
for DBI connections (#610, @mgirlich).NO ACTION
instead of CASCADE
in foreign key constraints to permit self-references.dm_from_src()
supports pool::Pool
objects (#599, @moodymudskipper).dm_rows_update()
and related functions for dm objects with tables without primary key (#592).glimpse()
is implemented for dm
objects (#605).rows_insert()
, rows_update()
and rows_delete()
(#617, @mgirlich).dm_zoom_to()
for dm
objects with an empty table (#626, @moodymudskipper).dm
objects in some corner cases (#596).sql_schema_list()
supports pool::Pool
objects (#633, @brancengregory).check_suggested()
everywhere (#572, @moodymudskipper).dm
objects (#597).rows_truncate()
in interactive mode (#588).rows_delete()
for databases (#589).dm_examine_constraints()
, dm_rows_insert()
and related, copy_dm_to()
and collect.dm()
show progress bars in interactive mode via the progress package. The new progress = NA
argument controls the behavior (#262, @moodymudskipper).copy_dm_to()
gains a copy_to
argument to support other ways of copying data to the database (#582).dm_get_all_fks()
returns a data frame with a parent_key_cols
instead of a parent_pk_cols
column (introduced in dm 0.2.0), to reflect the fact that a foreign key no longer necessarily points to a primary key (#562).*_pk()
and *_fk()
functions now verify that the dots are actually empty (#536).dm_get_pk()
is deprecated in favor of dm_get_all_pks()
(#561).dm_has_fk()
and dm_get_fk()
are deprecated in favor of dm_get_all_fks()
(#561).dm_add_fk()
gains ref_columns
argument that supports creating foreign keys to non-primary keys (#402).dm_get_all_pks()
gains table
argument for filtering the returned primary keys (#560).dm_get_all_fks()
gains parent_table
argument for filtering the returned foreign keys (#560).dm_rm_fk()
gains an optional ref_columns
argument. This function now supports removal of multiple foreign keys filtered by parent or child table or columns, with a message (#559).dm_rm_pk()
gains columns
argument and allows filtering by columns and by tables or removing all primary keys. The rm_referencing_fks
argument has been deprecated in favor of the new fail_fk
argument (#558).dm_get_all_fks()
has been optimized for speed and no longer sorts the keys (#560)..rds
file (#402)."dm_v1"
class from dm objects again, this would have made every S3 dispatch more costly. Relying on an internal "version"
attribute instead (#547).dm_get_src()
tbl.dm()
, src_tbls.dm()
, copy_to.dm()
. These functions have better alternatives and use the notion of a “data source” which is being phased out of dplyr (#527).*_pk()
and *_fk()
functions gain an ellipsis argument that comes before check
, force
and rm_referencing_fks
arguments (#520).dm_add_pk()
and dm_add_fk()
support compound keys via the c()
notation, e.g. dm_add_pk(dm, table, c(col1, col2))
. dm_nycflights13()
returns a data model with compound keys by default. Use compound = FALSE
to return the data model from dm v0.1.13 or earlier (#3).dm_get_all_fks()
includes parent_pk_cols
column that describes the primary key columns of the parent table (#335).dm_from_src()
supports the schema
argument also for MariaDB and MySQL databases (#516)."dm_v1"
in addition to "dm"
, to allow backward-compatible changes of the internal format (#521).dm_examine_constraints()
and other check functions count the number of rows that violate constraints for primary and foreign keys (#335).copy_dm_to(set_key_constraints = FALSE)
downgrades unique indexes to regular indexes (#335).rows_truncate()
implemented for data frames (#335).dm_enum_fk_candidates()
enumerates column in the order they apper in the table (#335).dm_draw()
gains column_types
argument, if TRUE
the column type is shown for each displayed column (#444, @samssann).copy_dm_to()
gains schema
argument (#432).dm_from_src()
gains dbname
argument for MSSQL (#472).rows_update()
when multiple columns are updated (#488, @samssann).enum_fk_candidates()
now only checks distinct values, this improves performance for large tables. As a consequence, only the number of distinct values is reported for mismatches, not the number of mismatching rows/entries (#494).?dm_zoom_to
(#403).dm_from_src()
gains dbname
argument for MSSQL (#472).count()
and tally()
for dplyr 1.0.3 compatibility (#475).dm_from_src()
for MSSQL when learn_keys = FALSE
(#427).expect_snapshot()
everywhere (#456).vignette("howto-dm-copy", package = "dm")
and vignette("howto-dm-rows", package = "dm")
discuss updating data on the database. In part derived from vignette("howto-dm-db", package = "dm")
(#411, @jawond).dm_mutate_tbl()
(#448).dm_financial()
falls back to db-edu.pacha.dev if relational.fit.cvut.cz is unavailable (#446, @pachamaltese).R CMD check
.dm_from_src()
accepts schema
argument for MSSQL databases (#367).dm_get_src()
returns NULL
for local data sources (#394).copy_dm_to()
gives a deprecation message (#395).copy_dm_to()
gives a better error message for bad table_names
(#397).dm
objects with local data sources no longer show the “Table source” part in the output.dm_bind()
for binding two or more ‘dm’ objects together (#417).dm_financial()
no longer prints message about learn_keys = FALSE
.dm_rows_update()
and related functions now use the primary keys defined in x
for establishing matching rows.withCallingHandlers()
where appropriate (#422)..dm
and .zoomed_dm
methods (#300).dm_financial()
are not run if connection can’t be established (#418).dm_paste()
generates self-contained code (#401).rows_truncate()
for databases.collect()
works on a zoomed dm, with a message.dm_add_pk(check = TRUE)
gives a better error message.rows_insert()
works if column names consist of SQL keywords (#409).src_sqlite()
in examples (#372).New dm_rows_insert()
, dm_rows_update()
, dm_rows_patch()
, dm_rows_upsert()
, dm_rows_delete()
and dm_rows_truncate()
, calling the corresponding rows_*()
method for every table (#319).
New rows_truncate()
(#319).
Added rows_insert()
and rows_update()
methods for SQLite, Postgres, MariaDB and MSSQL (#319).
Missing arguments now give a better error message (#388).
copy_dm_to()
also accepts a function as the table_names
argument. The unique_table_names()
argument is deprecated (#80).
dm_from_src()
now works for databases other than Postgres and MSSQL (#288), gives a warning if tables cannot be accessed with table_name = NULL
(#348), and gains learn_keys
argument to control querying of primary and foreign keys from the database (#340).dm_examine_constraints()
now prints a different message if a dm has no constraints defined."zoomed_dm"
: head()
, tail()
, pull()
, group_data()
, group_indices()
, group_vars()
, group_keys()
and groups()
(#236, #203).dm_paste()
supports writing colors and the table definition via the new options
argument. The definition can be written to a file via the new path
argument. The select
argument is soft-deprecated (#218, #302).dm_add_tbl()
uses rlang::list2()
internally, now accepts :=
to specify table names.dm_ptype()
(#301).dm_financial()
and dm_financial_sqlite()
.check_key()
now also works on a zoomed dm.dm_add_pk()
, dm_rm_pk()
, dm_add_fk()
and dm_rm_fk()
are now stricter when keys exists or when attempting to remove keys that don’t exist. A more relaxed mode of operation may be added later (#214).examine_cardinality()
, dm_examine_constraints()
and enum_pk_candidates()
now work for columns named n
.dm_set_key_constraints()
(and by extension dm_copy_to(set_key_constraints = TRUE)
) now quote identifiers for the SQL that creates foreign keys on the database.collect()
gives a better error message when called on a "zoomed_dm"
(#294).check_subset()
gives a clean error message if the tables are complex expressions.dm_from_src(schema = "...")
works on Postgres if search_path
is not set on the connection.compute.zoomed_dm()
no longer throws an error.dm_nycflights13(subset = TRUE)
memoizes subset and also reduces the size of the weather
table.format.dm()
.dm_examine_constraints()
formats the problems nicely.dm_from_src()
for Postgres through the new schema
and table_type
arguments (#256).filter.zoomed_dm()
no longer sets the filter.examine_()
functions never throw an error (#238).dm_zoom_to()
, dm_insert_zoomed()
, dm_update_zoomed()
and dm_discard_zoomed()
; check_()
-> examine_()
; dm_get_filter()
-> dm_get_filters()
; dm_from_src()
+ dm_learn_from_db()
-> dm_from_src()
(#233).$.zoomed_dm()
, [.zoomed_dm()
, [[.zoomed_dm()
, length.zoomed_dm()
, names.zoomed_dm()
, tbl_vars.zoomed_dm()
(#199, #216).as.list()
methods (#213).dm_examine_constraints()
and other key-related functions for compound keys (#239).R CMD check
with dev versions of dependencies.cdm_
prefix to dm_
. The old names are still available (#117).pull_tbl()
extracts a single table from a dm
(#206).dm_apply_filters_to_tbl()
that applies filters in related tables to a table, similar to dm_apply_filters()
; tbl()
, $
and [[
no longer apply filter conditions defined in related tables (#161).dm_paste()
(#160).check_cardinality()
returns the nature of the relationship between parent_table$pk_col
and child_table$fk_col
(#15).check_key()
no longer maps empty selection list to all columns.check_key()
supports tidyselect (#188).dm_rm_tbl()
supports tidyselect (#127).decompose_table()
uses tidyselect (#194).copy_to()
for dm
objects (#129).ref_table
column in dm_check_constraints()
(#178).str()
shows simplified views (#123).validate_dm()
(#173).dm_rm_fk()
(#175).check_key()
for databases (#208).cdm_filter()
and filter.zoomed_dm()
apply the filter instantly, the expression is recorded only for display purposes and for terminating the search for filtered tables in cdm_apply_filters()
. This now allows using a variety of operations on filtered dm
objects (#124).dimnames()
, colnames()
, dim()
, distinct()
, arrange()
, slice()
, separate()
and unite()
implemented for zoomed dm-s (#130).cdm_flatten_to_tbl()
(#147).slice.zoomed_dm()
: user decides in arg .keep_pk
if PK column is tracked or not (#152).enum_pk_candidates()
works with zoomed dm-s (#156).enum_fk_candidates()
(#156).cdm_insert_zoomed_tbl()
and cdm_add_tbl()
, defaulting to renaming of old and new tables when adding tables with duplicate names (#132).dm()
is akin to tibble()
, dm_from_src()
works like dm()
did previously, new_dm()
only accepts a list of tables and no longer validates, validate_dm()
checks internal consistency (#69).compute.dm()
applies filters and calls compute()
on all tables (#135).dm
(#100).zoomed_dm
(#131).cdm_select_tbl()
works again when multiple foreign keys are defined between two tables (#122).dm
. Zooming to a table vie cdm_zoom_to_tbl()
creates a zoomed dm
on which the {dplyr} verbs can be applied. The resulting table can be put back into the dm
with cdm_update_zoomed_tbl()
(overwriting the original table) or cdm_insert_zoomed_tbl()
(creating a new table), respectively (#89).cdm_select_to_tbl()
removes foreign key constraints if the corresponding columns are removed."dm"
using cdm_select_tbl()
(#108).dm
objects via dm()
and new_dm()
(#96).cdm_flatten_to_tbl()
now flattens all immediate neighbors by default (#95).cdm_add_tbl()
and cdm_rm_tbl()
(#90).cdm_get_con()
(#84).dm
object is defined using a nested tibble, one row per table (#57).cdm_enum_pk_candidates()
and cdm_enum_fk_candidates()
both show candidates first (#85).cdm_flatten_to_tbl()
works only in the immediate neighborhood (#75).cdm_squash_to_tbl()
implements recursive flattening for left, inner and full join (#75).cdm_check_constraints()
to check referential integrity of a dm
(#56).cdm_copy_to()
gains table_names
argument (#79).check_key()
now deals correctly with named column lists (#83).cdm_add_pk()
with a missing column.cdm_enum_fk_candidates()
checks for class compatibility implicitly via left_join()
.cdm_enum_fk_candidates()
contains a more detailed entry in column why if no error & no candidate (percentage of mismatched vals etc.).cdm_join_to_tbl()
and cdm_flatten_to_tbl()
in the presence of cycles or disconnected tables (#74).src
component from dm (#38).cdm_get_src()
for local dm always returns a src based on .GlobalEnv
.cdm_flatten()
gains ...
argument to specify which tables to include. Currently, all tables must form a connected subtree rooted at start
. Disambiguation of column names now happens after selecting relevant tables. The resulting SQL query is more efficient for inner and outer joins if filtering is applied. Flattening with a right_join
with more than two tables is not well-defined and gives an error (#62).cdm_disambiguate_cols()
.cdm_flatten_to_tbl()
disambiguates only the necessary columns.enum_pk_candidates()
for character data.cdm_add_pk()
and cdm_add_fk()
no longer check data integrity by default.join
argument is a function, to avoid surprises when the caller passes data.cdm_copy_to()
works correctly with filtered dm
objects.cdm_apply_filters()
actually resets the filter conditions.cdm_draw()
no longer supports the table_names
argument, use cdm_select_tbl()
.dm
to a database now creates indexes for all primary and foreign keys.cdm_nrow()
returns named list (#49).cdm_semi_join()
.cdm_find_conn_tbls()
and the all_connected
argument to cdm_select()
(#35).cdm_set_key_constraints()
.cdm_select()
to cdm_select_tbl()
, now uses {tidyselect}.cdm_nycflights13()
now has cycle = FALSE
as default.cdm_check_for_*()
to cdm_enum_*()
.cdm_filter()
only records the filtering operation, the filter is applied only when querying a table via tbl()
or when calling compute()
or the new cdm_apply_filters()
(#32).cdm_flatten_to_tbl()
flattens a dm
to a wide table with starting from a specified table (#13). Rename cdm_join_tbl()
to cdm_join_to_tbl()
.cdm_disambiguate_cols()
(#40).cdm_rename()
(#41) and cdm_select()
(#50) for renaming and selecting columns of dm
tables.length.dm()
and length<-.dm()
(#53).$
, [[
, [
, names()
, str()
and length()
now implemented for dm objects (read-only).enum_pk_candidates()
.browse_docs()
opens the pkgdown website (#36).as_dm()
now also accepts a list of remote tables (#30).cdm_rename_tbl()
and cdm_select_tbl()
(#14).cdm_enum_fk_candidates()
and cdm_enum_pk_candidates()
contain a why
column that explains the reasons for rejection in a human-readable form (#12).create_graph_from_dm()
no longer fails in the presence of cycles (#10).cdm_filter()
no longer requires a primary key.decompose_table()
adds the new column in the table to the end.tbl()
now fails if the table is not part of the data model.nse_function()
replaces h()
for marking functions as NSE to avoid R CMD check warnings.NOT NULL
constraints are set at creation of the table. This removes the necessity to store column types.Initial GitHub release.
dm
objects and basic functions:dm()
new_dm()
validate_dm()
cdm_get_src()
cdm_get_tables()
cdm_get_data_model()
is_dm()
as_dm()
cdm_add_pk()
cdm_has_pk()
cdm_get_pk()
cdm_get_all_pks()
cdm_rm_pk()
cdm_check_for_pk_candidates()
cdm_add_fk()
cdm_has_fk()
cdm_get_fk()
cdm_get_all_fks()
cdm_rm_fk()
cdm_check_for_fk_candidates()
cdm_filter()
cdm_semi_join()
cdm_nrow()
cdm_copy_to()
cdm_set_key_constraints()
cdm_learn_from_db()
cdm_is_referenced()
cdm_get_referencing_tables()
cdm_select()
cdm_find_conn_tbls()
cdm_nycflights13()
cdm_rename_table()
cdm_rename_tables()