@DavidMStraub, and other gramps-web developers, I’ve been looking at what gramps-web can do differently from gramps-desktop in terms of speed. Specifically, the /get endpoint does what you currently have to do: get all of the primary objects (Person, for example) and then do the filtering, and ordering, and then select a page full to render. Very expensive!
But the prospect of JSON in the database makes a big difference (if we are willing to make some small sacrifices).
To make a big difference, we want to select only a page of data from the DB. But we can’t do that until we have the data in sorted order. Can we get all of the data for a page in sorted order without going to Python objects? Yes!
The Person data is probably the hardest, but is possible. The birth and death representations make this a little tricky. Here is a pure SQL selection query that is sortable, and can get just a page of data:
SELECT json_data->>"$.gramps_id" as "Gramps ID",
json_data->>"$.primary_name.surname_list[0].surname" as Surname,
json_data->>"$.primary_name.first_name" as "Given Name",
birth_date->>"$.dateval" as "Birth Date",
death_date->>"$.dateval" as "Death Date",
json_data->>"$.change" as "Last changed"
FROM (SELECT person.json_data,
birth_event_ref,
birth_json_data->>"$.date" as birth_date,
death_event_ref,
death_json_data->>"$.date" as death_date
FROM (SELECT person.json_data,
birth.value as birth_event_ref,
death.value as death_event_ref
FROM person,
json_each(person.json_data, "$.event_ref_list") as birth,
json_each(person.json_data, "$.event_ref_list") as death
WHERE person.birth_ref_index = birth.key and person.death_ref_index = death.key) as person
LEFT JOIN (SELECT handle,
json_data as birth_json_data
FROM event) as birth_event
ON birth_event.handle = birth_event_ref->>"$.ref"
LEFT JOIN (SELECT handle,
json_data as death_json_data
FROM event) as death_event
ON death_event.handle = death_event_ref->>"$.ref"
)
;
My SQL is a little rusty, but I think that is correct. The data looks correct:
(Once we have the data selected, then we can do more expensive operations on the objects, such as getting the full name, date formats, etc.)
What are the compromises? Well, we’ll have to do some additional work to get the ordering of surnames to exactly match the possibilities in gramps-desktop. I think just the unicode sorting in SQLite works as expected. We can add some support for more complex surnames, if needed.
With this approach, we should be able to handle databases with millions of rows.