search

gilestrolab / ethoscope

a platform from monitoring animal behaviour in real time from a raspberry pi
http://lab.gilest.ro/ethoscope/
GNU General Public License v3.0
17 stars 25 forks source link

Problem in the database when using the append function. #139

Open ggilestro opened 3 years ago

ggilestro commented 3 years ago

Commit https://github.com/gilestrolab/ethoscope/commit/7853b445f03cc9875f17a7ab2ea8a73c2db98fb6 created an issue. When data are appended to an existing database, the variable t restarts from 0 and that creates a wrong series in the column.

A workaround for existing databases is to open the db file and issue the following SQL:

UPDATE ROI_1 SET t = t + ( SELECT MAX(t) from ROI_1 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_1));
UPDATE ROI_2 SET t = t + ( SELECT MAX(t) from ROI_2 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_2)); 
UPDATE ROI_3 SET t = t + ( SELECT MAX(t) from ROI_3 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_3)); 
UPDATE ROI_4 SET t = t + ( SELECT MAX(t) from ROI_4 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_4)); 
UPDATE ROI_5 SET t = t + ( SELECT MAX(t) from ROI_5 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_5)); 
UPDATE ROI_6 SET t = t + ( SELECT MAX(t) from ROI_6 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_6)); 
UPDATE ROI_7 SET t = t + ( SELECT MAX(t) from ROI_7 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_7)); 
UPDATE ROI_8 SET t = t + ( SELECT MAX(t) from ROI_8 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_8)); 
UPDATE ROI_9 SET t = t + ( SELECT MAX(t) from ROI_9 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_9)); 
UPDATE ROI_10 SET t = t + ( SELECT MAX(t) from ROI_10 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_10)); 
UPDATE ROI_11 SET t = t + ( SELECT MAX(t) from ROI_11 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_11)); 
UPDATE ROI_12 SET t = t + ( SELECT MAX(t) from ROI_12 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_12)); 
UPDATE ROI_13 SET t = t + ( SELECT MAX(t) from ROI_13 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_13)); 
UPDATE ROI_14 SET t = t + ( SELECT MAX(t) from ROI_14 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_14)); 
UPDATE ROI_15 SET t = t + ( SELECT MAX(t) from ROI_15 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_15)); 
UPDATE ROI_16 SET t = t + ( SELECT MAX(t) from ROI_16 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_16)); 
UPDATE ROI_17 SET t = t + ( SELECT MAX(t) from ROI_17 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_17)); 
UPDATE ROI_18 SET t = t + ( SELECT MAX(t) from ROI_18 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_18)); 
UPDATE ROI_19 SET t = t + ( SELECT MAX(t) from ROI_19 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_19)); 
UPDATE ROI_20 SET t = t + ( SELECT MAX(t) from ROI_20 ) WHERE id > (SELECT id from (SELECT id, MAX(t) from ROI_20));

However, this needs to be fixed in the code by inheriting and maintaining the same t0 . We also need to add a new metadata table with a different name (such as metadata_1) rather than simply overwrite the content of the metadata table.

ggilestro commented 3 years ago

The script above will not work if the t value is largest at the end of the experiment, for instance in cases like the one below. image

What we need to identify is the first peak, which is the only point where id.t < id.t+1 with the following script:

SELECT t FROM (
    SELECT
        g1.id,
        g1.t,
        (g2.t - g1.t) as delta
    FROM
        ROI_1 g1
            INNER JOIN
        ROI_1 g2 on g2.id = g1.id + 1
    WHERE
        delta < 0
)

Which in one row becomes

UPDATE ROI_1 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_1 g1 INNER JOIN ROI_1 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_1 g1 INNER JOIN ROI_1 g2 on g2.id = g1.id + 1 WHERE delta < 0));

And this solves the issue image

So the full sequence to use is the following:

UPDATE ROI_1 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_1 g1 INNER JOIN ROI_1 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_1 g1 INNER JOIN ROI_1 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_2 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_2 g1 INNER JOIN ROI_2 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_2 g1 INNER JOIN ROI_2 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_3 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_3 g1 INNER JOIN ROI_3 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_3 g1 INNER JOIN ROI_3 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_4 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_4 g1 INNER JOIN ROI_4 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_4 g1 INNER JOIN ROI_4 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_5 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_5 g1 INNER JOIN ROI_5 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_5 g1 INNER JOIN ROI_5 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_6 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_6 g1 INNER JOIN ROI_6 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_6 g1 INNER JOIN ROI_6 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_7 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_7 g1 INNER JOIN ROI_7 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_7 g1 INNER JOIN ROI_7 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_8 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_8 g1 INNER JOIN ROI_8 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_8 g1 INNER JOIN ROI_8 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_9 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_9 g1 INNER JOIN ROI_9 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_9 g1 INNER JOIN ROI_9 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_10 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_10 g1 INNER JOIN ROI_10 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_10 g1 INNER JOIN ROI_10 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_11 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_11 g1 INNER JOIN ROI_11 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_11 g1 INNER JOIN ROI_11 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_12 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_12 g1 INNER JOIN ROI_12 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_12 g1 INNER JOIN ROI_12 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_13 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_13 g1 INNER JOIN ROI_13 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_13 g1 INNER JOIN ROI_13 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_14 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_14 g1 INNER JOIN ROI_14 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_14 g1 INNER JOIN ROI_14 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_15 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_15 g1 INNER JOIN ROI_15 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_15 g1 INNER JOIN ROI_15 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_16 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_16 g1 INNER JOIN ROI_16 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_16 g1 INNER JOIN ROI_16 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_17 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_17 g1 INNER JOIN ROI_17 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_17 g1 INNER JOIN ROI_17 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_18 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_18 g1 INNER JOIN ROI_18 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_18 g1 INNER JOIN ROI_18 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_19 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_19 g1 INNER JOIN ROI_19 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_19 g1 INNER JOIN ROI_19 g2 on g2.id = g1.id + 1 WHERE delta < 0));
UPDATE ROI_20 SET t = t + (SELECT FIT from (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_20 g1 INNER JOIN ROI_20 g2 on g2.id = g1.id + 1 WHERE delta < 0)) WHERE id > (SELECT FID FROM (SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM ROI_20 g1 INNER JOIN ROI_20 g2 on g2.id = g1.id + 1 WHERE delta < 0));