Garbage-collect useless management database entries #189

When using p≡p for Email for a while the management database becomes populated with an annoying quantity of garbage identities containing an address, possibly nonexisting or even syntactically invalid, and default values for every other field.

This is expected. In part it is spam, most mostly it is the MUA asking the engine for information about an address while the user is still typing, possibly with typing mistakes: there will be a lot of garbage entries for addresses which do not really exist. This is needed in order to quickly display a "colour" in the privacy bar.

I would like to delete entries with default values (non-p≡p-using, message format 0, no key).

Shall I keep entries with a non-default username, and every other field default? I would provisionally say yes.

There will be one Engine function: pEp_garbage_collect_database, with no parameter other than a session. This will be wrapped in adapters. Interactive applications can call it infrequently, in a specific background thread. It is also sensible to run at startup.

The purpose of this change is making debugging convenient and more deterministic, more than saving space.

Here is a working DML statement doing what is explained above:

PRAGMAforeign_keys=1;-- For my tests: enforce ON DELETE CASCADE in Identity
-- EXPLAIN QUERY PLAN
DELETEFROMPerson-- SELECT username FROM Person -- For tests
WHEREidIN(SELECTP.idFROMPersonPWHEREP.langISNULLANDP.commentISNULLANDP.main_key_idISNULLANDNOTP.is_pEp_userANDP.id='TOFU_'||P.usernameANDNOTEXISTS(SELECT*FROMIdentityIWHEREI.user_id=P.idAND(I.main_key_idISNOTNULLORI.username<>I.addressORI.commentISNOTNULLORI.flags<>0ORI.is_ownORI.pEp_version_major<>0ORenc_format<>0)));

Its performance should not be too terrible:

QUERY PLAN
|--SEARCH Person USING COVERING INDEX sqlite_autoindex_person_1 (id=?)
|--LIST SUBQUERY 2
| |--SCAN P
| `--CORRELATED SCALAR SUBQUERY 1
| `--SEARCH I USING INDEX identity_userid (user_id=?)
|--SCAN alternate_user_id
|--SCAN revocation_contact_list
|--SEARCH trust USING COVERING INDEX sqlite_autoindex_trust_1 (user_id=?)
`--SEARCH identity USING COVERING INDEX identity_userid (user_id=?)

and in practice I expect it to be even better than it looks here: the subquery will hardly ever be executed for nontrivial Persons, since in the outer query some of the clauses in AND will be found false for all nontrivial Persons, after garbage collection has been performed the first time.

The DML statement is designed in a conservative way, to keep any trivial Person as long as for the same person id there exists a non-trivial Identity. Current p≡p applications do not yet take advantage of this possibility, but this DML statement will not break them when they start doing so.

The statement above works well but some garbage entries survive with correct names and wrong addresses -- coming from intermediate editing states and typos when composing messages.

Example:

sqlite> SELECT * FROM Person WHERE username = 'Sva';
id username main_key_id lang comment is_pEp_user
----------------------------------- -------- ---------------------------------------- ---- ------- -----------
TOFU_sva@pep.foundation Sva DBA0A1A1001396838E3A3269DF2ED8AA4A3144AA 1 
TOFU_sva@pep-project.org Sva 0 
TOFU_sva@pep.-project.org Sva 0 
TOFU_sva@pep.f-project.org Sva 0 
TOFU_sva@pep.fo-project.org Sva 0 
TOFU_sva@pep.fou-project.org Sva 0 
TOFU_sva@pep.foun-project.org Sva 0 
TOFU_sva@pep.found-project.org Sva 0 
TOFU_sva@pep.founda-project.org Sva 0 
TOFU_sva@pep.foundat-project.org Sva 0 
TOFU_sva@pep.foundati-project.org Sva 0 
TOFU_sva@pep.foundatio-project.org Sva 0 
TOFU_sva@pep.foundation-project.org Sva 0 
TOFU_sva@pep Sva 0 
TOFU_sva@pep. Sva 0 
TOFU_sva@pep.f Sva 0 
TOFU_sva@pep.fo Sva 0 
TOFU_sva@pep.fou Sva 0 
TOFU_sva@pep.foun Sva 0 
TOFU_sva@pep.found Sva 0 
TOFU_sva@pep.founda Sva 0 
TOFU_sva@pep.foundat Sva 0 
TOFU_sva@pep.foundati Sva 0 
TOFU_sva@pep.foundatio Sva 0 

It is clear what happened here: at some point when composing a message I must have edited the address in question after it autocompleted, changing the domain from pep.foundation.org to pep-project.org and also making typing mistakes in the process. The Engine saw every intermediate stage of my editing as a separate Person; since all of these Persons have non-default usernames they are considered non-garbage entries by the SQL statement above.

Notice that the first entry is non-garbage in any case, because of the fields main_key_id and is_pEp_user.

I would like to delete entries where the only non-default information is the username. However I will speak with @fdik before doing so, as only he understands the full implications.

Solution from @fdik : add a last-modification-time column. Consider an entry garbage, even if there is a non-default name, if it is old enough (for example older than one year).

The application is partly to blame, says @fdik: it should use outgoing_message_rating_preview instead of outgoing_message_rating when the field is still being edited. Some garbage, I say, will still be generated.