Allowing SQL queries for fast access

[dsblank]

Ever since Gramps 5.0 allowed a SQL backend, I've been thinking about how we can take advantage of direct, fast SQL access.

I have proposed a few ideas in the last few years on how to do this, but these proposals have all been a commitment to a particular (and opinionated) interface that didn't have universal appeal with all of the Gramps developers. And I appreciate that! We needed an API that could be implemented in a variety of styles.

So I kept working and finally I have what I hope will be accepted. The issue is that, in Gramps, filters are implemented in Python (called "rules"), and require a pass over the entire database (for example, over all people, or all families, etc). In Gramps 6.0, @stevenyoungs and I created a uniform method taking advantage of sets of filters. However that still requires at the root at least one pass over all of the data, and in some cases many more.

So what is the solution? Here is the proposal: allow database backends to register their own implementations of rules that can override the Python code. The API looks like:

class IsMale(BasePersonRule):
    def prepare(self, original, db, user):
        self.selected_handles: set[str] = set(
            db.select_from_person(
                what="person.handle",
                where="person.gender == Person.MALE",
            )
        )

db.register_rule_override(
    ("person", "isMale"),
    isMale,
) 

This code says that when a filter wants to use isMale rule (the rule that selects only the males in the person table) it will instead use the fast_prepare and fast_apply_to_one functions. For a SQL-based database, these functions would of course use SQL queries to get the data. This reduces the time required substantially.

The PR to allow this is small---a couple hundred lines. Then all that is needed is a database backend that registers rule overrides. And databases are plugins in Gramps, so the code can easily live outside the core codebase (at least for now).

Are you interested in Gramps filters running much much faster? Do you want to see what the override looks like in SQL? Do you have questions, comments, or feedback?

[dsblank]

What kinds of speed improvements can you expect? The SQL selection can be done quickly, but there 2 other PRs that make it very fast:

100k People, isMale Filter	Time (seconds)
Gramps 6.1	30
O(n**2) Fix #2180	13
Above PR #2177 + isMale override	8
Above PR + Skip-loop-PR #2160	2.77

In this example (just checking which of the 100k people are male) makes Gramps 10x faster.

[dsblank]

@Nick-Hall this shows the importance of the bug fix and other PRs (will require some conflict merges, unless you want me to combine all three into one PR).