Skip to content

0.5.5

Choose a tag to compare

@dsblank dsblank released this 11 Oct 14:17
· 29 commits to main since this release

This release makes PostgreSQL behave the same as SQLite, so the same query gives the same rows in the same order on either database.

⚠️ Behavior change: backslash in like() patterns

like() now treats \ as an escape character on every backend: \% matches a literal %, \_ a literal _, and \\ a literal backslash. PostgreSQL already worked this way. On SQLite and the evaluator (proxied) path, \ used to be an ordinary character, so existing patterns that contain one may now match different rows:

  • A \ before another character now escapes it. like(path, r'C:\Users\%') no longer matches C:\Users\...; write r'C:\\Users\\%' instead.
  • A pattern ending in a lone \ is now rejected with a QueryError (a 422 from gramps-web-api). Write r'%\\' instead of '%\\'.

In a where_expr, string literals follow Python's escaping first, so use a raw string or double every backslash again. The substring form of in ('C:\Users' in path) is unaffected.

What's Changed

Case-insensitive like() and substring in on PostgreSQL — @DavidMStraub (#3)

SQLite's LIKE ignores case, and PostgreSQL's doesn't, so like(surname, 'smi%') matched "Smith" on SQLite but not on PostgreSQL. Both like() and substring in now use ILIKE on PostgreSQL. Backslash escaping and case folding are now consistent across SQLite, PostgreSQL and the evaluator. (SQLite still only folds case for ASCII letters, so É/é differ there.) Follow-up by @dsblank validates like() patterns in one place and adds lone-backslash tests.

NULL ordering on PostgreSQL — @dsblank (#5, fixes #4)

PostgreSQL sorts NULL last in ASC and first in DESC, the opposite of SQLite, which the keyset pager assumes. Sorting by a field with missing values could order rows differently and drop rows when paging. PostgreSQL queries now emit ASC NULLS FIRST / DESC NULLS LAST. On a 7,262-person tree sorted by birth.place.title, paging now returns all 7,262 rows in both directions (previously 1,903 ascending and 5,359 descending).

Contributors

Full Changelog: 0.5.4...0.5.5