Adding Business Logic in the Database layer

[dsblank]

Gramps has always had a firm line separating so-called "business logic" from the database layer. What do I mean? Take for example this method to get a person's parent's handles:

    def get_parent_handles(self, person_handle, first_only=True):
        """
        Return handles of all parents of a person across their parent families.
        """
        person = self.get_raw_person_data(person_handle)
        if person is None:
            return []
        families = person.parent_family_list
        if first_only and families:
            families = [families[0]]
        result = []
        for fam_handle in families:
            family = self.get_raw_family_data(fam_handle)
            if family is None:
                continue
            if family.father_handle:
                result.append(family.father_handle)
            if family.mother_handle:
                result.append(family.mother_handle)
        return result

This takes a bit of processing, looking in different places to find all of the parent handles. But it is all Python, and doesn't depend on the database except for standard method calls.

Normally, we would say that this has no business being in the database layer because is completely database independent. That is, we only put database access methods in the database. Like get_person_from_gramps_id and all of the rest of the database methods.

But what if the get_parent_handles() method could be implemented at the database level, for example in SQL? For example:

    def get_parent_handles(self, person_handle, first_only=True):
        """
        Return handles of all parents of a person across their parent families.
        """
        if first_only:
            fam_handle = self._json_first_scalar(
                "SELECT json_data FROM person WHERE handle = ?",
                "parent_family_list",
            )
            self.dbapi.execute(
                "SELECT f.father_handle, f.mother_handle "
                "FROM family f "
                f"WHERE f.handle = {fam_handle}",
                [person_handle],
            )
        else:
            pf = self._json_expand_scalar_array(
                "p.json_data", "parent_family_list", "pf"
            )
            self.dbapi.execute(
                "SELECT f.father_handle, f.mother_handle "
                f"FROM person p, {pf} "
                "JOIN family f ON f.handle = pf.value "
                "WHERE p.handle = ?",
                [person_handle],
            )
        return [h for row in self.dbapi.fetchall() for h in row if h]

This does what the Python did, but can be executed much faster in SQL. I haven't found many candidates (yet) that are helpful in making gramps faster and more efficient. But I did find two:

• get_parent_handles() — returns all parent handles for a person across their parent families.
• get_child_handles() — returns all child handles for a person across their own families.

These are implemented and used in the new Graph Algorithms PRs:

1. Graph algorithms #2111
2. Extend #2111: add database methods, migrate remaining filter rules, add tests, fix report traversal bugs #2395

There is one fly in the ointment: these SQL methods can't be used if there is a database proxy (like living or private) in use. So they have to fallback to the python implementation (or implement specialize SQL).

Is it worth moving these from SQL to Python? In my tests, I find different ancestor and descendent algorithms are anywhere from about 5 to 9 times faster. So, yes, in my opinion is is worth it.

[emyoulation]

The business logic that would be a set "proximity" lookups for a Person or a Family.

find all the Families where:
1) the Person is a Partner or Offspring.
2) any Family members is a Partner or Offspring

And an option to find a list of particular secondary object types under those, no matter how deep.

So, for a by a Person events (and their Spouse-level family events) or a Family.

1. all the events
2. all the referenced Places (and their 'enclosing' Places)
3. all the referenced Citations
4. all the Addresses
5. all the Attributes
6. all the tags
7. all the Notes
8. all the Gallery object
9. all the Associations
10. all the Names

Because efficiently finding such subsets as a prefilter can reduce what an un-optimized Custom Filter reviews from tens of thousands of objects to mere dozens.

[dsblank]

@emyoulation, sorry, but to qualify as a candidate for inclusion in the database layer, the code would need to:

1. Be able to be written in Python (the base implementation)
2. Be able to be written in SQL
3. (2) has to be substantially faster than (1)

SQL isn't magic, and in some case could be slower than Python (a complex recursive query, for example).

find all the Families where:

1. the Person is a Partner or Offspring.
2. any Family members is a Partner or Offspring

I'm not sure that that qualifies, but it certainly could take advantage of the two methods described above!

[dsblank]

Another criteria:

4. It is used in Gramps and its inclusion would make parts substantially faster