dbplyr 2.2.0

dbplyr 2.2.0

New features

• SQL formatting has been considerably improved with new wrapping and indenting.
  show_query() creates more readable queries by printing the keywords in blue
  (@mgirlich, #644). When possible dbplyr now uses SELECT * instead of
  explicitly selecting every column (@mgirlich).

• Added support for rows_insert(), rows_append(), rows_update(),
  rows_patch(), rows_upsert(), and rows_delete() (@mgirlich, #736).

• Added copy_inline() as a copy_to() equivalent that does not need write
  access (@mgirlich, #628).

• remote_query(), show_query(), compute() and collect() have an
  experimental cte argument. If TRUE the SQL query will use common table
  expressions instead of nested queries (@mgirlich, #638).

• New in_catalog(), which works like in_schema(), but allows creation of
  table identifiers consisting of three components: catalog, schema, name
  (#806, @krlmlr).

Improvements to SQL generation

• When possible, dbplyr now uses SELECT * instead of explicitly selecting
  every column (@mgirlich).

• New translation for cut() (@mgirlich, #697).

• Improved translations for specific backends:

  • as.Date() for Oracle (@mgirlich, #661).
  • case_when() with a final clause of the form TRUE ~ ... uses ELSE ...
    for SQLite (@mgirlich, #754).
  • day(), week(), isoweek(), and isoyear() for Postgres (@mgirlich, #675).
  • explain() for ROracle (@mgirlich).
  • fill() for SQL Server (#651, @mgirlich) and RPostgreSQL (@mgirlich).
  • quantile() for SQL Server (@mgirlich, #620).
  • str_flatten() for Redshift (@hdplsa, #804)
  • slice_sample() for MySQL/MariaDB and SQL Server (@mgirlich, #617).
  • union() for Hive (@mgirlich, #663).

• The backend function dbplyr_fill0() (used for databases that lack
  IGNORE NULLS support) now respects database specific translations
  (@rsund, #753).

• Calls of the form stringr::foo() or lubridate::foo() are now evaluated in
  the database, rather than locally (#197).

• Unary plus (e.g. db %>% filter(x == +1)) now works (@mgirlich, #674).

• is.na(), ifelse(), if_else(), case_when(), and if()
  generate slightly more compact SQL (@mgirlich, #738).

• if_else() now supports the missing argument (@mgirlich, #641).

• n() now respects the window frame (@mgirlich, #700).

• quantile() no longer errors when using the na.rm argument (@mgirlich, #600).

• remote_name() now returns a name in more cases where it makes sense
  (@mgirlich, #850).

• The partial evaluation code is now more aligned with dtplyr. This makes it
  easier to transfer bug fixes and new features from one package to the other.
  In this process the second argument of partial_eval() was changed to a lazy
  frame instead of a character vector of variables (@mgirlich, #766).
  Partially evaluated expressions with infix operations are now correctly
  translated. For example translate_sql(!!expr(2 - 1) * x) now works
  (@mgirlich, #634).

Minor improvements and bug fixes

• New pillar::tbl_format_header() method for lazy tables: Printing a lazy
  table where all rows are displayed also shows the exact number of rows in the
  header. The threshold is controlled by getOption("pillar.print_min"),
  with a default of 10 (#796, @krlmlr).

• The 1st edition extension mechanism is formally deprecated (#507).

• across(), if_any() and if_all() now defaults to .cols = everything()
  (@mgirlich, #760). If .fns is not provided if_any() and if_all() work
  like a parallel version of any()/any() (@mgirlich, #734).

• across(), if_any(), and if_all() can now translate evaluated lists
  and functions (@mgirlich, #796), and accept the name of a list of functions
  (@mgirlich, #817).

• Multiple across() calls in mutate() and transmute() can now access
  freshly created variables (@mgirlich, #802).

• add_count() now doesn't change the groups of the input (@mgirlich, #614).

• compute() can now handle when name is named by unnaming it first
  (@mgirlich, #623), and now works when temporary = TRUE for Oracle
  (@mgirlich, #621).

• distinct() now supports .keep_all = TRUE (@mgirlich, #756).

• expand() now works in DuckDB (@mgirlich, #712).

• explain() passes ... to methods (@mgirlich, #783), and
  works for Redshift (@mgirlich, #740).

• filter() throws an error if you supply a named argument (@mgirlich, #764).

• Joins disambiguates columns that only differ in case (@mgirlich, #702).
  New arguments x_as and y_as allow you to control the table alias
  used in SQL query (@mgirlich, #637). Joins with na_matches = "na" now work
  for DuckDB (@mgirlich, #704).

• mutate() and transmute() use named windows if a window definition is
  used at least twice and the backend supports named windows (@mgirlich, #624).

• mutate() now supports the arguments .keep, .before, and .after
  (@mgirlich, #802).

• na.rm = FALSE only warns once every 8 hours across all functions (#899).

• nesting() now supports the .name_repair argument (@mgirlich, #654).

• pivot_longer() can now pivot a column named name (@mgirlich, #692),
  can repair names (@mgirlich, #694), and can work with multiple names_from
  columns (@mgirlich, #693).

• pivot_wider(values_fn = ) and pivot_longer(values_transform = )
  can now be formulas (@mgirlich, #745).

• pivot_wider() now supports the arguments names_vary, names_expand, and
  unused_fn (@mgirlich, #774).

• remote_name() now returns a name in more cases where it makes sense
  (@mgirlich, #850).

• sql_random() is now exported.

• ungroup() removes variables in ... from grouping (@mgirlich, #689).

• transmute() now keeps grouping variables (@mgirlich, #802).