Skip to contents

Changelog

Source: NEWS.md

healthdb (development version)

  • Behavior change: identify_row() (and define_case()/define_case_with_age(), which pass it through) with match = "like" or "start" now ignores case by default, via the new ignore_case argument. Codes in administrative data are often inconsistently cased, so a case-sensitive match silently misses records, e.g., match = "start" with vals = "f10" previously did not find “F10” on a data.frame. Both the values and the patterns are lower-cased before comparison, so the result no longer depends on the backend. Previously the behavior was case-sensitive for data.frames, and for remote tables it followed the database, its collation, and the ‘dbplyr’ version: ‘dbplyr’ 2.5.x translated str_like() to case-insensitive ILIKE on PostgreSQL and a bare, collation-dependent LIKE on SQL Server, while 2.6.0 pins both to case-sensitive (on SQL Server with an explicit COLLATE Latin1_General_100_CS_AS clause). Set ignore_case = FALSE for the previous case-sensitive behavior; on a large table it is also faster, as wrapping the column in LOWER() prevents an index on it from being used. Only the comparison is case-folded; the values in the output keep their original case. The verbose output now reports whether the match ignores case, and points to ignore_case = FALSE for speed when it does. The other match types are unchanged: “in” and “between” compare values as they are, and “regex” follows the supplied pattern (use "(?i)" for a case-insensitive one).

  • make_test_dat() with type = "database" no longer emits a “src_memdb() was deprecated in dbplyr 2.6.0” warning naming healthdb. The in-memory SQLite database it writes to is now opened directly through ‘DBI’/‘RSQLite’ and cached for the session, instead of going through the ‘dbplyr’ helpers, whose names differ across versions (src_memdb()/tbl_memdb() are deprecated as of 2.6.0 in favour of memdb(), which does not exist before it). The examples of execute_def() and fetch_var() likewise create their database table with DBI::dbConnect() plus dplyr::copy_to(). No change in behavior, and the minimum ‘dbplyr’ version is unchanged.

  • Fixed identify_row() with match = "glue_sql" failing on SQL Server with “Incorrect syntax near the keyword ‘AS’” under ‘dbplyr’ 2.6.0. The supplied SQL fragment is opaque to ‘dbplyr’, which since that version wraps it in CAST(... AS BIT) = 1 on SQL Server; T-SQL has no boolean value type, so a condition cannot be a CAST operand. The fragment is now wrapped in CASE WHEN ... THEN 1 ELSE 0 END = 1, which generates the same valid SQL on every backend and on both ‘dbplyr’ 2.5.x and 2.6.0.

  • pool_case() with output_lvl = "clnt" now also reports first_entry_date and first_entry_src, the mirror image of last_entry_date/last_entry_src: the date and source of the client’s earliest included record, whether or not it was a valid one. This is distinct from first_valid_date, which is the earliest record that counted towards the case definition and is therefore often later than the client’s very first record.

  • Fixed pool_case() with output_lvl = "clnt" reporting the wrong first_valid_src and last_entry_src on the data.frame backend. Both were taken from the first/last row of the client rather than from the row holding the first/last date, so they reported the first/last source in binding order (which is the same for every client) whenever the bound records were not already sorted by date. The source is now found by comparing dates on both backends, with ties broken by the alphabetical order of the source labels, matching what the database backend already produced. Records with a missing date no longer influence the result on either backend.

  • The return value of pool_case() is now documented column by column for both output_lvl options.

  • fetch_var() on a data.frame no longer relies on left_join() preserving the row order of its input when re-attaching the fetched variables. Matched values are now re-aligned by an internal row id, so results stay correct even when the join methods are overridden by a backend that does not guarantee row order (e.g., ‘duckplyr’ via methods_overwrite(), under which fetched values could previously be silently attached to the wrong rows). The one-to-one check is now based on the row ids, which is slightly stronger than the previous row-count check.

  • fetch_var() now errors informatively when the names of fetched variables clash with each other, with columns already in data, or with keys not used in their linkage. Previously such clashes were resolved silently by renaming (e.g., var.x/var.y or var...2), so an output column could carry a name that no longer meant what it appeared to; a variable sharing a name with an unused key was particularly deceptive, as data’s own column shadowed the fetched one.

  • fetch_var() now rejects any non-formula element in linkage up front (previously a single valid formula let malformed elements through to an obscure error), and tolerates duplicated key names after |.

  • collapse_episode() now removes records with a missing (NA) start or end date (with a warning reporting the count) before deriving episodes, consistent with restrict_date(). Previously a single missing end date silently merged every later record of that client into one episode on the data.frame backend (an NA propagated through cummax()), and the data.frame and database backends disagreed on the result. Both backends now drop such records and agree.

  • collapse_episode() on a data.frame now errors when a column would clash with the names it derives (epi_id, epi_no, epi_seq, epi_start_dt, epi_stop_dt, and internal helpers), matching the database method. Previously the data.frame method silently renamed the clashing column with an _og suffix.

  • collapse_episode()’s gap_overwrite default is now 99999 on every backend (the data.frame method previously declared Inf, which was dead via S3 dispatch and would have produced NA on the database method). No change for typical calls through the generic.

  • collapse_episode() on a zero-row data.frame no longer emits spurious “no non-missing arguments to min/max” warnings; it returns the empty result with the episode columns added.

  • New vignette “The logic behind if_date() and restrict_date()” (vignette("if_date_logic")) explaining the within rolling window, the shrinking-window search for the apart condition, how the search loop is unrolled into SQL window functions with metaprogramming, and how these relate to known techniques.

  • New vignette “Data wrangling helpers” (vignette("wrangling")) introducing report_n(), compute_duration(), lookup(), compute_comorbidity(), collapse_episode(), cut_period(), and if_date(), with guidance on keeping the work on the database for as long as possible.

  • The package options (healthdb.verbose, healthdb.force_proceed, and the new healthdb.check_con) are now documented in one place on the package help page; see ?healthdb. Also fixed a broken cross-reference and polished wording throughout the documentation.

  • Documented case sensitivity of matching: identify_row() “like”/“start” matching is case-sensitive for data.frames but follows the database for remote tables (e.g., case-insensitive ASCII LIKE on SQLite, case-sensitive on PostgreSQL, collation-dependent on SQL Server); compute_comorbidity() expects upper-case codes.

  • identify_row() now errors informatively when vals is empty (e.g., character(0) or NULL) instead of failing with an obscure message or silently returning no rows.

  • identify_row() is now also exported as identify_rows() (consistent with it returning multiple rows); both names work and are documented on the same page.

  • identify_row() with match = "like" or "start" on remote tables now generates a single WHERE ... LIKE ... OR ... LIKE ... clause instead of one sub-query per pattern combined with UNION. The new query is simpler and typically faster. Note a subtle behavior change: UNION removed duplicate rows, while the new query (like all other match types) keeps them; results differ only if the source table contains fully duplicated rows.

  • The database connection check (a SELECT 1 round trip) that runs at the start of every database-method function can now be turned off with options(healthdb.check_con = FALSE), which saves one round trip to the server per step in a long pipeline.

  • Fixed if_date() and restrict_date() rejecting the boundary case where apart * (n - 1) equals within as impossible. It is satisfiable: n dates with all gaps exactly apart span exactly apart * (n - 1) days, e.g., two dates exactly 30 days apart do satisfy apart = 30 with within = 30. Only apart * (n - 1) strictly greater than within is impossible now, and the database method raises the same error as the local method instead of silently returning no match.

  • Corrected the documentation of restrict_date()’s force_collect argument, which still described a pre-release version: the apart condition runs on the database (it does not require a local data frame), and force_collect = TRUE is only an opt-in fallback for the apart-plus-within case when the database does not permit temporary tables or the overlap join.

  • Fixed report_n() (and the report_on argument of exclude()) erroring with “Can’t coerce from a object to an integer” on database backends whose counts come back as 64-bit integers (e.g., PostgreSQL).

  • Fixed restrict_date() with both apart and within failing on PostgreSQL when within is passed as a plain number (e.g., within = 365, which is a double in R): the generated SQL added a numeric to a date (“date + 365.0”), which PostgreSQL rejects. within is now coerced to integer in this branch like in the others.

  • Fixed restrict_date() with apart on PostgreSQL: the generated SQL summed and coalesced boolean columns, which PostgreSQL rejects (“function sum(boolean) does not exist”); the indicators are now explicit integers. Also fixed the check_missing = TRUE count, which could report a garbled number of removed records (e.g., “2.47e-323”) or miss the warning entirely on some backends.

  • The test suite can now run against PostgreSQL (HEALTHDB_TEST_BACKEND=postgres plus the standard PG* connection variables) and SQL Server (HEALTHDB_TEST_BACKEND=sqlserver plus an ODBC connection string in HEALTHDB_TEST_ODBC), and does so on GitHub Actions via service containers, in addition to the default local SQLite.

  • Behavior change: compute_comorbidity() now also matches ICD-9 codes by prefix (consistent with the ICD-10 change below): codes in data are compared with each listed code at the code’s own length, as the codes in Quan et al. (2005) cover all their subdivisions, e.g., “428” (Congestive Heart Failure, 428.x) now captures “4280” and “42800”, which were previously missed by exact matching. This affects both “ICD-9-CM-5digits” and the full-code part of “ICD-9-CM-3digits”. ICD-9 scores may be higher than in previous versions. The prefix matching reproduces the reference SAS implementation, which compares every code with the SAS IN: (starts-with) operator; the package code lists were verified code-for-code against the MCHP SAS macros (see ?elix_codes for links).

  • New exported dataset elix_codes: the ICD codes defining the 31 Elixhauser comorbidity categories used by compute_comorbidity(), with the category labels, full names, and matching rules (prefix/exact) for all three supported ICD versions. compute_comorbidity() is now driven by this dataset internally; its interface and results are unchanged (verified against the previous implementation over every code in the lists).

  • The confirmation prompts in execute_def(), bind_source(), and report_n() now abort with an informative error in non-interactive sessions (e.g., Rscript, knitr) instead of silently proceeding (readline() returns “” when not interactive, which was treated as consent). Interactively, only an explicit “y”/“yes” answer proceeds now; any other answer cancels.

  • restrict_date() (data.frame method) with mode = “filter” no longer warns (“no non-missing arguments to max”) when no record is left from the previous steps.

  • Removed leftover commented-out code and browser() calls across the package; no functional change.

  • Behavior change: define_case_with_age() now applies the age restriction (age_range) before restrict_n() and restrict_date() instead of after, so that n_per_clnt, apart, and within are evaluated only on age-eligible records (matching definitions such as “two or more visits while aged 18-65”). Results may differ from previous versions for calls that combine age_range with n_per_clnt > 1, apart, or within: clients who met those count/time-span criteria only by counting out-of-range records are no longer included.

  • Fixed define_case_with_age() with birth_date erroring on database backends that lack a SQL translation for difftime(), most notably SQLite (the generated query contained an invalid difftime(..., 'days' AS units) and failed at collection). Age is now computed with backend-appropriate date arithmetic (DATEDIFF() on SQL Server, date subtraction on SQLite/PostgreSQL), so the birth_date path works across all supported backends. The age column path was unaffected.

  • define_case_with_age() age-calculation fixes: the age is now signed, so a record dated before its birth_date (a data error) yields a negative age and is excluded by age_range rather than being flipped to a positive age that could wrongly pass the filter; the internal temporary age column no longer overwrites an existing column of the same name in the input; and age_range now errors when its lower bound exceeds the upper bound instead of silently returning no records.

  • Fixed a bug in define_case() and define_case_with_age(): with keep = "last", the first occurrence of “min” anywhere in the internally generated code was string-replaced with “max”, which corrupted column names containing “min” (e.g., a clnt_id named “admin_id” became “admax_id” and caused an error).

  • Internal refactoring: restrict_date() (database method), define_case(), and define_case_with_age() no longer build queries by string-replacing names in deparsed code; column names are now injected as symbols with ‘rlang’. The generated SQL is unchanged, but column names that contain substrings such as “clnt_id”, “date_var”, “_i”, or “_x” no longer risk corrupting the query.

  • Behavior change: compute_comorbidity() now matches ICD-10 codes by prefix instead of exactly. Codes in data are truncated to their first 3 and 4 characters and compared with the 3-character (e.g., I50) and 4-character (e.g., E115) codes in Quan et al. (2005), respectively, which cover all their subdivisions. Previously, exact matching missed subdivisions of the listed codes, e.g., “I500” was not counted as Congestive Heart Failure (“I50” on the list), and “E1152” (ICD-10-CA) was not counted as Diabetes Complicated (“E115” on the list). ICD-10 scores may therefore be higher than in previous versions. Records with codes that belong to multiple categories in Quan et al. (e.g., I11.0 in both Congestive Heart Failure and Hypertension Complicated) are now counted in all matching categories.

  • Fixed a bug in the data.frame method of identify_row(): when the input contained columns named rid or incl, the function renamed those columns in the user’s original data frame by reference (e.g., rid became rid.og) and silently overwrote then dropped them from the output. Internal working columns are now given names guaranteed not to clash with the input, and the input is never modified.

healthdb 0.5.1

CRAN release: 2026-05-26

  • Updated identify_row() to accommodate changes in upcoming ‘dbplyr’ release - Thanks the ‘dbplyr’ devs for the fix!

  • fixed an error caused by changes in ‘data.table’

healthdb 0.5.0

CRAN release: 2026-01-08

Bug fix

  • Fixed a bug in the apart feature in restrict_date() caused by ‘dbplyr’ 2.5.1 update where the input order of the SQL translation for difftime() was flipped.

Improvements

  • Added a new define function with age restriction - define_case_with_age()

healthdb 0.4.1

CRAN release: 2025-04-05

Improvements

  • Functions that take remote table as input now checks whether connection to database is working properly.

healthdb 0.4.0

CRAN release: 2025-02-28

Improvements

  • fetch_var() documentation now indicates that it does not accept input from a pipe.
  • collapse_episode() now works for remote table input.
  • pool_case() now provides the source for the first valid and the latest records for linkage back to the source data.

healthdb 0.3.1

CRAN release: 2024-05-23

Bug fixes

  • Fixed a critical bug introduced by the new code that cleans the window order remaining in database output. The problem is related to this issue Inability to remove window_order() leads to weird, unpredictable results.

  • bind_source() will bind correctly if every variable name argument is a single string (i.e., selecting variables with common names only).

  • Fixed a bug in pool_case() when every source had all the flags, the code that fills missing flag == 1 would trigger an error.

healthdb 0.3.0

CRAN release: 2024-05-15

Improvements

  • fetch_var() will accept database input, but it will not prevent one-to-many joins because the checking could be slow on remote database.

  • All functions that accept database input now will NOT sort the output to avoid generating the ‘ORDER BY’ clause which may trigger a warning if the output was chained with subsequent operations.

  • restrict_date() will not check missing date unless the new check_missing argument was set to TRUE. The checking time may be not negligible for large database; the user should opt in for it.

Bug fixes

  • Chaining pool_case() with dplyr::compute() to create table in database should no longer give errors or warnings related to window order.

healthdb 0.2.0

CRAN release: 2024-05-05

Improvements

  • The apart argument in restrict_date() is now functional for both data.frame and database input.

  • restrict_date() will give warning if there are missing entries in date_var. The description of dup.rm has been re-written to clarify it is applicable only when apart is absent.

  • pool_case() now outputs both the number of raw (raw_in_src) and valid (valid_in_src) entries in each source.

  • List element of execute_def() output is named by definition and source labels.

  • Most of the verbose message, except data summaries, will be sent as message instead of text output, so that they can be suppressed via message = FALSE in Rmarkdown.

Bug fixes

  • Fixed if_date() gave output shorter than the input when detail and dup.rm are both TRUE. if_date() now does not assume the dates are sorted and returns elements in the original unsorted order.
  • Fixed restrict_date() for remote table might miss cases with duplicated dates when n > 2.