This PR has
1. The `select_from` table methods, with parameters what, where,… order_by, page, and page_size
2. The Python-expression string to SQL translator (QueryBuilder)
3. An autocomplete text entry for writing Python expression strings (for user)
4. Example filter enhancements showing how to use `select_from` table methods (for developer)
5. A Where filter to use `select_from` table expressions (for user)
6. Example Where filter on sidebar Person view (for user)
7. Python type hints on Gramps objects for autocomplete and SQL generation
The main motivation for the PR is to tap into the reduced time required for processing filters in Gramps. This is made possible by the Filter Optimizer introduced in Gramps 6.0. If a filter has a matches handles in `Filter.selected_handles` then those handles can be used to eliminate (in certain circumstances) full-table sweeps in Python.
Testing on this PR using a family tree database with 100k people. Here are some comparisons of the filter rules updated in this PR (calling this PR Gramps 7.0):
| Test Name | Gramps 6.0 Average Time (seconds) | Gramps 7.0 Average Time (seconds)
|-----------|-----------------------------------|----------------------------------|
| person.PeoplePrivate | 4.035897 | 0.783568 |
| person.Disconnected | 3.666995 | 0.418769 |
| person.PersonWithIncompleteEvent | 8.817965 | 1.871430 |
## 1. The select_from Methods
This PR adds the following methods:
- `select_from_citation(what, where, order_by=, env, page, page_size)`
- `select_from_event(what, where, order_by=, env, page, page_size)`
- `select_from_family(what, where, order_by=, env, page, page_size)`
- `select_from_media(what, where, order_by=, env, page, page_size)`
- `select_from_note(what, where, order_by=, env, page, page_size)`
- `select_from_person(what, where, order_by=, env, page, page_size)`
- `select_from_place(what, where, order_by=, env, page, page_size)`
- `select_from_repository(what, where, order_by=, env, page, page_size)`
- `select_from_source(what, where, order_by=, env, page, page_size)`
- `select_from_tag(what, where, order_by=, env, page, page_size)`
These methods are implemented for DB-API based databases (for postgresql and sqlite). This allows fast access to SQL using the JSON representations added in Gramps 6.0.
NOTE: there is always protection (`db.can_use_fast_selects()`) so that these are not used where:
* there is a filter in place
* the methods are not implemented
Full design plans and examples here: https://www.gramps-project.org/wiki/index.php/GEPS_047:_Select_data_from_SQL_API#Design_Goals
Each method can specify:
- what - what is returned from the methods. By default, this is the matching objects, but can also be just particular field (such as `person.handle`, or `person.gender`)
- where - the condition on what to match (such as `person.gender == Person.MALE`)
- order_by - the sorted order in which to return the items
Each of what, where, and order_by is written by a Python expression as strings (especially useful in passing items from the GUI or Web interfaces).
### 1.1 Examples
The full set of examples include demonstrations of JOINs, arrays, and list comprehension.
```python
# Get all people that are a father in a family:
for p_handle, f_handle in db.select_from_person(
what=["person.handle", "family.handle"],
where="person.handle == family.father_handle"
):
...
```
## 2. Python-expression string to SQL translator
The QueryBuilder converts Python expression strings to SQL in two phases. First, the QueryParser uses Python's ast.parse() to parse the string into an AST, then recursively walks the AST nodes to build an intermediate query model of Expression objects (e.g., CompareExpression, AttributeExpression, BoolOpExpression). This model represents the expression structure without SQL-specific details.
The SQLGenerator then visits these Expression objects and emits SQL. Each expression type has a generator method (e.g., _generate_compare(), _generate_attribute()) that maps Python operations to SQL. For example, attribute access like person.name becomes JSON extraction functions, comparisons like == and != become SQL operators, and boolean operations like and/or become SQL AND/OR clauses. The generator also handles dialect differences between SQLite and PostgreSQL, ensuring the SQL is valid for the target database.
The QueryBuilder and related abstractions are backed by a large number of tests in order to make it powerful, flexible, and robust.
There is a README with lots of examples of Python expression conversions.
## 3. autocomplete text entry
The autocomplete is very good at giving suggestions based on the specific Python expressions available in the QueryBuilder. It uses Python objects, and jedi, for type identification.
<img width="1200" height="676" alt="image" src="https://github.com/user-attachments/assets/5689115a-3077-4dcc-bc54-e0f36f114fd0" />
## 4. Example filter enhancements
The PR contains a variety of examples of conversion of the previous code to the new `select_from` methods.
## 5. A Where filter
<img width="1200" height="676" alt="image" src="https://github.com/user-attachments/assets/8344968d-a1ec-4b8e-becc-c47e438d4f04" />
<img width="727" height="51" alt="image" src="https://github.com/user-attachments/assets/f5685851-2ee4-48bb-a8c7-2e4d4b65529f" />
## 6. Example Where filter on sidebar Person view
<img width="740" height="293" alt="image" src="https://github.com/user-attachments/assets/99b111d7-024f-4627-918c-0e2e2032e110" />
## Summary
The QueryBuilder and select methods are the main parts of this PR, with the surrounding components as support.
Disclaimer: This PR was written by me, Cursor (variety of models), and Copilot.