Good day everyone!
I need several custom sql-queries like this:
SELECT
person.gid,
name.first_name,
GROUP_CONCAT(surname.surname, ', ') AS all_surnames,
joined_table_birth.date_text AS birth,
joined_table_death.date_text AS death
FROM link AS link_person_name
INNER JOIN person ON person.handle = link_person_name.from_handle AND link_person_name.from_type = 'person'
INNER JOIN name ON name.handle = link_person_name.to_handle AND link_person_name.to_type = 'name'
INNER JOIN link AS link_name_surname ON name.handle = link_name_surname.from_handle AND link_name_surname.from_type = 'name'
INNER JOIN surname ON surname.handle = link_name_surname.to_handle AND link_name_surname.to_type = 'surname'
LEFT JOIN (
SELECT
event.handle AS handle,
GROUP_CONCAT(date.text, ', ') AS date_text
FROM event
LEFT JOIN link AS link_event_date ON event.handle = link_event_date.from_handle AND link_event_date.from_type = 'event'
LEFT JOIN date ON link_event_date.to_handle = date.handle AND link_event_date.to_type = 'date'
GROUP BY event.handle
) AS joined_table_birth ON person.birth_ref_handle = joined_table_birth.handle
LEFT JOIN (
SELECT
event.handle AS handle,
GROUP_CONCAT(date.text, ', ') AS date_text
FROM event
LEFT JOIN link AS link_event_date ON event.handle = link_event_date.from_handle AND link_event_date.from_type = 'event'
LEFT JOIN date ON link_event_date.to_handle = date.handle AND link_event_date.to_type = 'date'
GROUP BY event.handle
) AS joined_table_death ON person.death_ref_handle = joined_table_death.handle
GROUP BY person.gid, name.first_name
HAVING all_surnames LIKE '%Đинин%';
This is really difficult job to understand relations between the tables. Ive spent about 2 hrs to build the query above.
Does anyone have a schema or a diagram of the relationships between the tables? Please share your knowledge or resources if you have anything useful. Please, pay attention, I mean the db after exporting via sqlite tool.
Iâve found something similar what I need Gramps Data Model - Gramps
But I think this is original DB, not sqllite.
I suggest that you look into https://sqlitebrowser.org/ and use it to examine the Gramps database. I suspect that you will find you cannot do what I think you are trying to do (as I donât really understand SQL). The problem is that Gramps stores most of the interesting data in blobs, not as components of SQL tables. The current data model is still Gramps Data Model - Gramps it has not been changed lately.
You might also look into the Sqlite addon, as it creates an SQL database that may be more useful for your purposes.
I use dbeaver-ce under ubuntu. I think this is the same or very similar with https://sqlitebrowser.org/
I even tried draw relationships between the tables in automatical mode via dbeaver-ce, but it shows no relationships, no foreign keys on the sqllite level. So I must research it, build some queries to understand how really tables are related, which tables are pivotâŚ
This is not about sqllite: Gramps Data Model - Gramps unfortunatelly.
And finally, I think I already use âSqlite addonâ. It makes possible make such exports, or not?
No, I didnât work with views yet.
First of all I need return parents as a separate column to my SQL query above.
I need understand relationships between person and itâs parents and then extract parentsâ surnames like above.
Join the family table to the child_ref table on the handle field. The father_handle and mother_handle are available in the family table, and the ref field in the child_ref table points to the children.
Then link the father_handle, mother_handle and ref fields to the handle field in the person table.
You can get the names from the name and surname tables.
If youâre talking about the original SQLite Gramps DB found in the grampsdb directory, your transaction wonât work. This SQLlite DB is only an emulation of the BSDDB engine. Each record (in each TABLE) contains an opaque BLOB object which is the serialised Python object, plus a few fields used for quick indexing the record.
Your query may be valid for the SQLite DB exported by the SQLite Import/Export add-on, but I didnât check it. Then use some program to peek into the exported DB, like Sqliteman under Linux. Youâll get a list of the TABLEs and all COLUMNs in each table. You can also look at the sqlite_master information block where the CREATE commands for all tables are stored.