[metrics-bugs] #31071 [Metrics/ExoneraTor]: Add a notice if we're missing data for a lookup

[Tor Bug Tracker & Wiki]

#31071: Add a notice if we're missing data for a lookup
--------------------------------+------------------------------
 Reporter:  karsten             |          Owner:  metrics-team
     Type:  enhancement         |         Status:  assigned
 Priority:  Medium              |      Milestone:
Component:  Metrics/ExoneraTor  |        Version:
 Severity:  Normal              |     Resolution:
 Keywords:                      |  Actual Points:
Parent ID:                      |         Points:
 Reviewer:  irl                 |        Sponsor:
--------------------------------+------------------------------

Comment (by karsten):

 I started looking into implementing this enhancement.

 First thing is that we'll have to define criteria for putting out a
 warning that we might be lacking exit scanner information. I think that
 experiencing an 18+ hour gap of exit scan results would be reason enough
 to emit a warning. This would apply to the requested day minus 2 days and
 plus 1 day.

 What we could do is extend `search_by_date_address24()` by another `UNION`
 to include a query like this:

 {{{
 SELECT DATE(scanned) AS date, NULL AS fingerprint_base64,
       DATE_TRUNC('hour', scanned) AS scanned, NULL AS exitaddress,
       NULL AS validafter, NULL AS nickname, NULL AS exit, NULL AS
 oraddress
   FROM exitlistentry_exitaddress
   WHERE DATE(scanned)
   BETWEEN '2013-03-12' AND '2013-03-15' -- requested date in this example:
 '2013-03-14'
   GROUP BY 1, 3;
 }}}

 The response would contain up to 4 * 24 = 96 rows for each hour of exit
 scan results during the four days around the requested date. We could then
 check for gaps that are 18 hours or longer and print out a warning if we
 found something.

 This query is really fast, because it uses the
 `exitlistentry_exitaddress_date_scanned_fingerprint_id` index. I don't
 expect major performance drawbacks from adding this query to
 `search_by_date_address24()`.

 Next steps:
  - Provide a new schema file `src/main/sql/exonerator3.sql` with the
 updated `search_by_date_address24` function.
  - Update `QueryServlet.java` to parse the new query reponse by checking
 whether there were exit scanner results for all four days in question.
  - Extend `QueryResponse.java` to include this information.
  - Update `ExoneraTorServlet.java` to display this information.
  - Prepare the new warning message for translation.

 Still leaving this assigned to metrics-team, as I'm not planning to
 implement this in the next couple days.

 Feedback appreciated!

--
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/31071#comment:6>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online