Open main menu

Gramps β

Changes

Addon:QueryGramplet

784 bytes added, 18:10, 17 August 2015
→‎Examples
{{Third-party plugin}}
 
The Query Gramplet takes SQL-like queries and produces a Quick View.
= Examples =
The QueryGramplet in gramps-addons/trunk for gramps/master Gramps 5.0 can now SELECT, UPDATE, and DELETE. Some examples (keywords are showncapitalized, but the SQL parser is case-insensitive; fields that are capitalized are ''macros''and must be capitalized, see below for more information): <pre>DELETE FROM person WHERE GIVEN == "Travis"; SELECT * FROM person LIMIT 10;
DELETE SELECT gramps_id, GIVEN, SURNAME FROM person WHERE GIVEN == "Travis";
SELECT * event_ref_list[0].ref FROM person LIMIT 10;
SELECT gramps_id, UPDATE person SET GIVEN, SURNAME FROM person="Gary" WHERE GIVEN == "Travis";
SELECT event_ref_list[0].ref gramps_id FROM personwhere ROWNUM < 10;
UPDATE SELECT gramps_id FROM person SET GIVEN="Gary" WHERE GIVEN == "Travis"LIMIT 5;
SELECT gramps_id FROM person where ROWNUM < 10LIMIT 20,30;
SELECT gramps_id FROM person LIMIT 5, father_handle.SURNAME, mother_handle.SURNAME from family;
SELECT UPDATE gramps_id SET tag_list = Tag("Betty") FROM person LIMIT 20,30WHERE "Betty" in primary_name.first_name;
</pre> Hints: * You may want to do a general SELECT gramps_idfirst ("SELECT * FROM table")--- that will show you the names of fields* The query will automatically outer-join tables (use FLAT to not join)* Assigning to a list will append onto it* Use Tag("name") to lookup or create a new tag* Use Date(year[, month[, day]]) to create a date* Use TODAY for a date create for today* You have access to these libraries/functions: _ (for translations), re, random, father_handle.SURNAMEdb (database) Other options: * FLAT - do not create extra rows via a JOIN* EXPAND - do automatic JOINs* RAW - no extra processing* NORAW - follow handles, mother_handle.SURNAME from family; etc
This API is made possible through the generic struct/json interface. It is very little code, because it relies on these generic structures. It should be able to be made solid enough to expose to users (say as a generic filter). The parser can be made more user friendly... it may just throw an error currently.
Here is the grammar for the subset of SQL supported. The SELECT, UPDATE, DELETE, and LIMIT clauses may be in any order. The WHERE clause (if used) must be last.
<pre>
SELECT expr1 [as var1][, expr2 [as var2], ...] FROM table [LIMIT number1[, number2]] [WHERE expression];
SELECT UPDATE table SET field1=expr1 [as var1][, field2=expr2 [as var2], ...] FROM table [LIMIT number1[, number2]] [WHERE expression];
UPDATE DELETE FROM table SET field1=expr1[, field2=expr2, ...] [LIMIT number1[, number2]] [WHERE expression];
DELETE FROM table [LIMIT number1[, number2]] [WHERE expression];  ... [FLAT | EXPAND] ...  ... [RAW | NORAW] ...
... [RAW | NORAW] ...
</pre>
'''table''' is one of:
Examples:
<pre> SELECT gramps_id, primary_name.surname_list.surname FROM person WHERE any([re.match("Sm.*th", name) for name in col[1]]);</pre>
Searches all primary_name surnames to find names that start with "Sm" and end in "th". col[1] is primary_name.surname_list.surname, which is a list of surnames.
[[Image:QuerySmith.png|thumb|left|400px]]
{{-}}
<pre> $ select given_name, surname from people;
$ select * from sources;  $ select * from events;
$ select * from familiesevents;
$ select * from families;
</pre>
[[Category:Plugins]]
[[Category:Developers/General]]
[[Category:Reports]]
[[Category:Gramplets]]
[[Category:Views]]
Retrieved from "https://gramps-project.org/wiki/index.php/Special:MobileDiff/47785...57484"