Changes

= Examples = The QueryGramplet in gramps-addons/trunk for gramps/master can nowSELECT, UPDATE, and DELETE. Some examples (keywords are showncapitalized, but the SQL parser is case-insensitive; fields that are capitalized are ''macros'', see below for more information): <pre>DELETE FROM person WHERE GIVEN == "Travis";

DELETE SELECT * FROM person WHERE primary_name.first_name == "Travis"LIMIT 10;

SELECT * from gramps_id, GIVEN, SURNAME FROM person;

SELECT gramps_id, primary_name.first_name, primary_name.surname_list.event_ref_list[0].surname from ref FROM person;

SELECT event_ref_list.0.ref FROM UPDATE personSET GIVEN="Gary" WHERE GIVEN == "Travis";

UPDATE SELECT gramps_id FROM person SET primary_name.first_name="Gary" where primary_name.first_name == "Travis"ROWNUM < 10;

SELECT gramps_id FROM person LIMIT 5; SELECT gramps_id FROM person LIMIT 20,30; SELECT gramps_id, father_handle.SURNAME, mother_handle.SURNAME from family; </pre>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 ageneric filter). The parser can be made more user friendly... it mayjust throw an error currently.

1) Most SQL clauses (UPDATE table, FROM table, SELECT ..., SETfield=value, ..., LIMIT ...) can appear in any position, any order

2) ...except the WHERE clause: it must be last; this is because theWHERE clause is not parsed, because:

3) The WHERE clause uses any valid Python expressions. (May need toimport It imports some libraries (such as random), to have ready any possibleexpression needed).

4) The SELECT fields currently use a dotted notation for listreferences* from person WHERE random. Use "event_ref_list.0" rather than "event_ref_list[0]"random() < .1;

5) JOINS are not necessary, because it automatically looks up allrelations through the handlesThis selects records where each has a 10% chance of being selected.

4) The SELECT fields use the bracketed notation for list references. Use "event_ref_list[0]". 5) JOINS are not necessary, because it automatically looks up all relations through the handles. In a SELECT, columns with multiple values in a list will appear as an outer-join with other values in the row. 6) UPDATE currently only works will work on the primary any field, through a joined object, not or on thejoined primary object. For example, you can't update the birth date of anevent through the person's referenced events. I think this can befixed.

8) Need to implement Implemented "LIMIT number; ", "LIMIT start, stop; ", and "WHERE ROWNUM < number" (ROWNUM can be used in any expression).

9) Field names are the actual names of the fields of thegramps.gen.lib objects, verbatim, no differences. You might need tolook up what you need... no help yet from this interface (although Iam working on defining a built-in Schema that could help)

11) Shortcut: you can use col[N] in the WHERE clause to reference acolumn selected. N is zero-based.

select SELECT gramps_id, private from FROM person WHERE not col[1];

That will select all people where private is not True. That would include None (non-existent record) and False values. To select only False values, use:  SELECT gramps_id, private FROM person WHERE col[1] == False; That will not select None values. 12) If an object doesn't match any selected field, it just doesn'tshow. For example, to find all of the people with at least two

select SELECT primary_name.surname_list.[1 from ] FROM person;

select SELECT gramps_id, primary_name.surname_list.[1 from ] FROM person where WHERE col[1]; 13) You do not need to reference a field before you can use it in the WHERE clause.  14) The semicolon is optional.

1315) In a SELECT (for speed reasons), you need to reference a fieldbefore you can use itBe careful selecting all fields from all records.. That is not necessary in the UPDATE or DELETEstatements. (The idea here is that SELECTS are done quite frequently,but UPDATES are done rarelycould take up a lot of memory, and it doesn't matter if those take alittle longer). Maybe we can relax this constraintbring down Gramps.

1416) The semicolon is optionalThis should be fairly fast, but it does call eval(). This might make things a little slower, but made the code much easier to write. And it does use the full power of python.

1517) Be careful selecting all fields from all records... that couldtake up a lot of memory, and bring down grampsYou can use parentheses in an "UPDATE table SET field=value" value.Something like:

16) This should be fairly fast, but it does call eval UPDATE table SET field=(field + 1). This mightmake things a little slower, but made the code much easier to write.And it does use the full power of python.;

17but that hasn't been well-tested. (Speaking of testing, there is a Vassilii-inspired unittest with the QueryQuickview... will add more there) You can use parens in an "UPDATE table SET field=value" value.Something like:

UPDATE table SET field=(field + 118);The primary_name... stuff is really long and verbose. See "shortcuts" above.

but 19) Fields that hasn't been well-tested. (Speaking contain other objects, or lists of testingobjects, there is aVassilii-inspired unittest with the QueryQuickviewwill show as dictionaries and lists of dictionaries.You can refine those fields by further specifying subparts. Maybe we should not show these, or show in another form.. will add morethere).

1820) The primary_nameIf a selected field does not exist in a record, then it will have a value of None... stuff is really long For example, if you are selecting those people that have a second surname on their primary name, and verbosethere are some people who do not have a second surname, it will appear as None. Maybe weneed some "virtual If all columns"are None, then the item will not be selected at all.

1921) Fields If you know that contain other objects, or lists of objects, only one value will showas dictionaries and lists of dictionaries. You can refine those fieldsby further specifying subparts. Maybe we should not show thesematch, orshow in another form..then a "LIMIT 1" may be a way to speed up the query.

<pre> $ select given_name, surname from people;

$ select * from sources;  $ select * from events;

$ select * from familiesevents;