refactor initialization
[pulsecounter.git] / DATABASE
1 Given that it is not going to be possible to write data to the sensor MCU,
2 we would need the database structure allowing for flexible adjustment of
3 the "initial value" of the counters.
4
5 1. As a new value for a counter arrives, if the new value is less than
6 the prevously recorded one, we automatically make an adjustment to make
7 the value continue to grow.
8
9 2. At some later time, we may need to change the adjustment value if some
10 pulses where lost.
11
12 3. There should be a way to introduce an adjustment for specified value
13 at specified time (optional).
14
15 4. Count events should be recorded "as is", to allow future changes of
16 processing procedure.
17
18 5. Database design should be optimised to a query that returns a set of
19 (corrected) values of the counter with the times of measurement, limited
20 by the interval of time.
21
22 6. Another (less frequent) query will be for the last recorded (corrected)
23 value of the counter with the time of measurement.
24
25 create table adjustment (timestamp datetime, value int);
26 create table counter    (timestamp datetime, value int);
27
28 insert into adjustment values (datetime('now'), 10);
29 insert into counter values (datetime('now'), 5);
30 insert into counter values (datetime('now'), 10);
31 insert into counter values (datetime('now'), 15);
32 insert into adjustment values (datetime('now'), 30);
33 insert into counter values (datetime('now'), 0);
34 insert into counter values (datetime('now'), 5);
35 insert into counter values (datetime('now'), 10);
36
37 select timestamp, value+adj as value from
38   (select c.timestamp timestamp, c.value value, 
39                 (select value from adjustment a
40                         where a.timestamp <= c.timestamp
41                         order by timestamp desc limit 1) adj from counter c
42   ) t;
43
44
45 sqlite: "SQL error: no such column: c.timestamp"
46
47 mysql:
48 +---------------------+-------+
49 | timestamp           | value |
50 +---------------------+-------+
51 | 2015-12-14 19:16:27 |    15 |
52 | 2015-12-14 19:16:31 |    20 |
53 | 2015-12-14 19:16:35 |    25 |
54 | 2015-12-14 19:16:44 |    30 |
55 | 2015-12-14 19:16:48 |    35 |
56 | 2015-12-14 19:16:51 |    40 |
57 +---------------------+-------+
58 6 rows in set (0.00 sec)
59
60 2015-12-20:
61
62 After some playing with it, I think it is a mistake. One of my assumed
63 goals was minimize carrying state along time. This is achieved in the
64 `counter` table, but not in `adjustment` table. Namely, when you need to
65 adjust before one or more later adjustments, you'd have to update the
66 later adjustment records. Advantage of this approach is that you need
67 to select just one row from the `adjustment` table in addition to the
68 row of the `counter` table. Disadvantage is as stated above, departure
69 from the "write only" principle.
70
71 So I am changing the design to this:
72
73
74 select timestamp, value+adj as value from
75   (select c.timestamp timestamp, c.value value, 
76                 (select sum(value) from adjustment a
77                         where a.timestamp <= c.timestamp
78                 ) adj from counter c
79   ) t;
80
81 so every row in the `adjustment` table contains incremental offset,
82 typically simply the last recorded value of the counter (unless some
83 events where lost while the sensor was down). Select is potentially
84 more expensive; given that I do not anticipate many adjustments this
85 should not be a problem for a single-user setup. For "industrial
86 size" setup, the problem is easily solved caching the result of the
87 "summing" select.
88