0.5.7

This release makes the evaluator (proxied) path much faster for restricted databases, fixes two PostgreSQL errors with boolean columns, and adds select_normalizer so that select values have the same types on every path.

What's Changed

Faster evaluation on raw data — @dsblank (#7)

• RawDataReader (opt-in). run_query and evaluate_where can read records, and the records they refer to, as raw data instead of building a Gramps object for each one. For a guest in gramps-web-api using RestrictedDb, this makes /query 1.3–13× faster on a 100k-person tree. The default ObjectReader reads through the database's object getters as before, so Gramps proxies and the addons work the same and run at the same speed.
• run_query_with_total returns (rows, total), with the number of matches counted in the same pass. The total ignores after and limit, like compile_count_query. Before, getting a total needed a second full pass.
• run_query no longer goes through GenericFilter. It takes a reader= argument and turns only the final page into objects.
• Related records are cached per query in a 10,000-entry LRU cache.

Behavior change: a related record that can't be read (hidden or missing) is no longer counted by any(...) or len(...). This now matches the SQL path. Before, any(...) and len(...) without a condition still counted a related record that the database hides.

Guest query, 100k-person tree	Before	After	Speedup
gender == Person.FEMALE	28.1 s	6.7 s	4.2×
primary_name.first_name == 'Mary'	27.8 s	4.8 s	5.8×
birth.date.sortval >= Date('Jan 1, 1950')	33.1 s	12.2 s	2.7×
Event like(place.title, '%Texas%')	53.9 s	4.1 s	13.2×

Boolean columns on PostgreSQL — @dsblank (#8)

• Booleans across a relationship. birth.private == False, birth.place.private, father.private and in [...] raised operator does not exist: integer = boolean. They now work, as a top-level private == False already did.
• Keyset cursors. An after cursor holding True/False for a sort column such as private raised integer > boolean. It now works.

These fixes also apply wherever where expressions are compiled to SQL, such as gramps-web-api's MatchesQuery filter rule and the GOQLFilter addon.

select_normalizer — @dsblank (#8)

select_normalizer(spec, select) returns a function that converts a row from compile_query to the values the evaluator path returns:

Selected value	SQLite	PostgreSQL	Normalized
flat boolean (private, birth.private)	1	1	True
JSON boolean (primary_name.private)	1	'true'	True
JSON number (birth.date.sortval)	2389036	'2389036'	2389036
object or list (birth.date)	JSON text	JSON text	parsed
string (primary_name.first_name)	'123'	'123'	'123'

Column types are looked up once when the normalizer is built. Values that already have the right type pass through unchanged.

Tests

811 tests. New tests check that SQLite, PostgreSQL and the evaluator agree on related boolean columns, boolean keyset cursors, and select value types. They also check that the total ignores limit and after, that private parts of visible records are matched as cleaned values with both readers, and that the LRU cache evicts in the right order. The PostgreSQL test helper now creates INTEGER columns as INTEGER. Copying every column as TEXT had hidden the boolean bugs.

Contributors

• @dsblank

Full Changelog: 0.5.6...0.5.7

0.5.6

This release fixes any() and len() nested inside another any(). These queries used to fail with an SQL error or return no rows. They now return the same rows on SQLite, PostgreSQL and the evaluator (proxied) path. No query that worked before changes behavior.

What's Changed

Nested any() and len() — @dsblank (#6)

• A collection inside a collection's condition. any(e for e in events if any(e.citations)) and any(e for e in events if e.place.title == 'Chicago') failed with no such column: event.json_data. They now compile.
• A list inside a list. any(r for r in event_ref_list if any(a for a in r.attribute_list if a.value == 'witness')) produced invalid SQL. Each nesting level now gets its own alias (je, je1, je2, …).
• A list on an array element. any(r for r in event_ref_list if any(r.citation_list)) checked the person's own citation_list instead of each event reference's, so it matched nobody. len() had the same problem. Both now check each element.
• The same collection nested in itself. any(p for p in enclosing_places if any(p.enclosing_places)) (a place whose encloser is itself enclosed) and any(a for a in associations if len(a.associations) > 1) matched nobody: the inner query read its own row instead of the outer one. Each nesting level now gets its own alias (place__target, place__target1, …).

Tests

New tests run each nested query as SQL and through the evaluator, and check that both return the expected rows. They cover crossing tables, nesting a collection in itself up to three levels, and lists inside lists. Set GOQL_TEST_POSTGRES_DSN to also run them on PostgreSQL. The tests copy their fixture into temporary tables and roll back, so they don't touch existing data.

Contributors

• @dsblank

Full Changelog: 0.5.5...0.5.6

0.5.5

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

• @DavidMStraub made their first contribution in #3 🎉
• @dsblank

Full Changelog: 0.5.4...0.5.5

0.5.4

What's Changed

• Apply ORDER BY's COLLATE to WHERE ordering comparisons too
• Prefer len()/any() over count()/exists() in where_expr docs

Full Changelog: 0.5.3...0.5.4

0.5.3

What's Changed

• Fix resolve_column_path rejecting bare names that are both a flat column and a relationship
• Add README cookbook examples for backlinks
• Fix Postgres 500 on boolean flat-column literals (e.g. private is False)
• Add len(path) -- array-length comparisons over an intra-record JSON array
• Unify any()/len() with exists()/count() -- one dispatch, two keyword generations
• Add Person.child_refs -- a self-linked collection, closing the "adopted" gap
• Refresh docs to lead with any()/len(), the canonical spellings
• Add end-to-end test for backlinks comprehension sugar
• Fix comprehension sugar to desugar into any()/len(), not exists()/count()
• Lock in more any()/len() comprehension equivalences as regression tests
• Reject a relationship hop before a collection name consistently

Full Changelog: 0.5.0...0.5.3

0.5.0

What's Changed

• Add backlinks: reverse-reference lookup via Gramps' own reference table by @dsblank in #2

Full Changelog: 0.4.1...0.5.0

0.3.4

Full Changelog: 0.3.3...0.3.4

0.3.3

Full Changelog: 0.2.1...0.3.3

0.2.1

Full Changelog: 0.2.0...0.2.1

0.2.0

What's Changed

• Add or/not to where_expr, fix a NULL-handling divergence, and start a roadmap by @dsblank in #1

New Contributors

• @dsblank made their first contribution in #1

Full Changelog: 0.1.1...0.2.0