Changes

Jump to: navigation, search

Database Query API

120 bytes added, 12:53, 30 January 2016
no edit summary
* TABLE-NAME - the name of the table. That would be "Person", Family", "Media", "Repository", "Place", "Note", "Source", "Citation", or "Tag"
* SELECT-LIST - a list of dot-separated field path strings from this object type (eg, "gramps_id", "primary_name.first_name", etc)
Optional arguments:
=== WHERE-EXPRESSION ===
The where expression WHERE-EXPRESSION must be in one of these four forms (tuples or lists allowed):
* None - no filter applied to data
=== ORDER-BY-LIST ===
The ORDER-BY-LIST is either None or is a list of dotted-field path strings paired with "ASC" (ascending) or "DESC"(descending).
Examples:
Barry, I0012
Barry, I0011
 
=== Joins ===
 
The database.select method can also do joins across primary objects in each of the WHERE, ORDER, or select fields. For example consider this request:
 
>>> db.select("Family", ["mother_handle.gramps_id"])
 
This will return the gramps_id's of the mothers of each family, if there is a mother.
 
You can join across multiple tables with more complex queries, such as:
 
>>> db.select("Family", ["mother_handle.event_ref_list.ref.gramps_id"])
 
That returns all of the Event gramps_ids for all events for the mother (Person) of all families, looking something like:
 
[{"gramps_id": ["E0001", "E0002"]},
{"gramps_id": "E0003"},
{"gramps_id": ["E0003", "E0001"]},
...
]
 
Note that these joins are done per record and are therefore not optimized---each requires another database access. In the future, these could be optimized via a SQL JOIN.
== Result ==
The dotted-field path strings are mapped to SQL names by replacing dots with two underscores.
 
=== Joins ===
 
The primary_object.get_field(field-name) (and thus db.select) can also do joins across primary objects. For example consider this request:
 
>>> family.get_field("mother_handle.gramps_id", db)
'I2345'
 
That is, mother_handle is a handle to a Person, but get_field() can get the joined data, if given the database as a second argument.
 
Thus, this also works:
 
>>> db.select("Family", ["mother_handle.gramps_id"])
 
and even more complex queries, such as:
 
>>> db.select("Family", ["mother_handle.event_ref_list.ref.gramps_id"])
 
That returns all of the Event gramps_ids for all events for the mother of all families, looking something like:
 
[{"gramps_id": ["E0001", "E0002"]},
{"gramps_id": "E0003"},
{"gramps_id": ["E0003", "E0001"]},
...
]
== Speed Tests ==

Navigation menu