dm 0.3.0

Features

• Implement glimpse() for zoomed_df (@IndrajeetPatil, #1003, #1161).

• Remove message about automated key selection with the select argument in joins on zoomed_df (@IndrajeetPatil, #1113, #1176).

• dm_from_con(learn_keys = TRUE) works for MariaDB (#1106, #1123, #1169, @maelle), and for compound keys in Postgres (#342, #1006, #1016) and SQL Server (#342).

• New json_pack_join(), json_nest_join(), json_pack() and json_nest(), similar to pack_join(), dplyr::nest_join(), tidyr::pack() and tidyr::nest(), but create character columns (#917, #918, #973, #974).

• nest_join() and pack_join() support zoomed_df objects (#1119, @IndrajeetPatil).

API

• Marked stable functions as stable, in particular dm() and related functions (#1032, #1040).

• Remove own rows_*() implementation for lazy tables, they are now available in dbplyr >= 2.2.0 (#912, #1024, #1028).

• Deprecate dm_join_to_tbl(), dm_is_referenced() and dm_get_referencing_tables() (#1038).

• New dm_validate() replaces now deprecated validate_dm() (#1033).

• dm_get_con() and dm_get_filters() use dm as argument name (#1034, #1036).

• Mark ... in dm_flatten_to_tbl() as experimental (#1037).

• Add ellipses to dm_disambiguate_cols(), dm_draw(), dm_examine_constraints(), dm_nycflights13() and dm_pixarfilms() (#1035).

• New dm_from_con(), soft-deprecated dm_from_src() (#1014, #1018, #1044).

• Moved pack_join() arguments past the ellipsis for consistency (#920, #921).

Bug fixes

• Compatibility fix for writing to SQL Server tables with dbplyr >= 2.2.0.

Documentation

• The pkgdown site now uses BS5 for greater readability (#1067, @maelle).

• Better message for dm_rows_...() functions if the in_place argument is missing (@IndrajeetPatil, #414, #1160).

• Better message for learning error (#1081).

• Greatly improved consistency, content, and language across all articles (@IndrajeetPatil, #1056, #1132, #1157, #1166, #1079, #1082, #1098, #1100, #1101, #1103, #1112, #1120, #1158, #1175).

• Tweaks of intro vignette and README (#1066, #1075, @maelle).

• Document glimpse() S3 method for dm (@IndrajeetPatil, #1121).

• Update credentials to fallback databases for dm_financial() hosted on pacha.dev (#916, @pachadotdev), also used now for vignettes (#1118) and in dm_from_con() example (#993).

• Update license year (#1029).

Internal

• Switch to duckdb as default database backend (#1179).

• Test duckdb and MariaDB on GHA (#1091, #1136).

dm 0.2.8

Features

• 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).
• Messages that suggest the installation of optional packages are shown only once per session (#852).
• 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).

Bug fixes

• Remove extra spaces in output when examining constraints with compound keys (#868).
• Fix column tracking for foreign keys (#856, #857).
• copy_dm_to() shows progress bars again (#850, #855).
• Progress bars use the console width (#853).
• Avoid calling dbAppendTable() for zero-row tables (#847).

Internal

• Require rlang 1.0.1 (#840).

dm 0.2.7

Features

• New dm_wrap_tbl(), dm_unwrap_tbl(), dm_nest_tbl(), dm_unnest_tbl(), dm_pack_tbl() and dm_unpack_tbl() (#595, #733, #737).
• New dm_examine_cardinality() (#264, #735).
• New 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).

Bug fixes

• 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).

Internal

• Establish compatibility with rlang 1.0.0 (#756).
• Simplify database checks on GitHub Actions (#712).

dm 0.2.6

Features

• New 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).

Bug fixes

• decompose_table() now avoids creating NA values in the key column (#580).
• dm_draw() works with empty tables (#585).

Internal

• Fix compatibility with dplyr 1.0.8 (#698).

dm 0.2.5

Features

• mutate(), transmute(), distinct() and summarize() now support dplyr::across() and extra arguments (#640).
• Key tracking for the first three verbs is less strict and based on name equality (#663).
• 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).

Breaking changes

• New 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).

Bug fixes

• copy_dm_to() and sql_create_schema() no longer actively check for schema existence (#644, #660).
• Add newline after OUTPUT clause for SQL Server (#647).
• Fix sql_rows_delete() with returning argument for SQL Server (#645).

Internal

• Remove method only needed for RSQLite < 2.2.8, add warning if loaded RSQLite version is <= 2.2.8 (#632).
• Adapt MSSQL tests to testthat update (#648).

dm 0.2.4

Features

• 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).
• Implement rows_patch() for DBI connections (#610, @mgirlich).
• Use NO ACTION instead of CASCADE in foreign key constraints to permit self-references.
• dm_from_src() supports pool::Pool objects (#599, @moodymudskipper).
• Better error message for dm_rows_update() and related functions for dm objects with tables without primary key (#592).
• glimpse() is implemented for dm objects (#605).
• Support DuckDB in rows_insert(), rows_update() and rows_delete() (#617, @mgirlich).

Bug fixes

• Fix dm_zoom_to() for dm objects with an empty table (#626, @moodymudskipper).
• Avoid generating invalid dm objects in some corner cases (#596).

Internal

• sql_schema_list() supports pool::Pool objects (#633, @brancengregory).
• Establish compatibility with pillar 1.6.2, vctrs > 0.3.8 and rlang > 0.4.11 (#613).
• Use check_suggested() everywhere (#572, @moodymudskipper).
• Add CI run for validating all new dm objects (#597).

dm 0.2.3

Bug fixes

• Fix rows_truncate() in interactive mode (#588).

Features

• Implement rows_delete() for databases (#589).

Internal

• Skip examples that might require internet access on non-CI platforms.

dm 0.2.2

Features

• 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).

Internal

• Always run database tests on sqlite for df source.
• Establish compatibility with testthat > 3.0.2 (#566, @moodymudskipper).

dm 0.2.1

Breaking changes

• 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).

Features

• 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).
• dm operations are now slightly faster overall.

Internal

• The internal data structure for a dm object has changed to accommodate foreign keys to other columns than the primary key. An upgrade message is shown when working with a dm object from an earlier version, e.g. if it was loaded from a cache or an .rds file (#402).
• Drop "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 0.2.0

Breaking changes

• Deprecate 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).

Features

• 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 objects now inherit from "dm_v1" in addition to "dm", to allow backward-compatible changes of the internal format (#521).
• Use hack to create compound primary keys on the database (#522).
• 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).