From: Eugene Crosser Date: Fri, 8 Jan 2016 08:02:36 +0000 (+0300) Subject: Notes on using sqlite3 X-Git-Url: http://www.average.org/gitweb/?p=pulsecounter.git;a=commitdiff_plain;h=5faa8ead391b2fc6c07a10ca1bbeea1b6690f8d8;hp=0afe015d8ca980e92c5c13baf4aebab5f3ca8c82 Notes on using sqlite3 --- diff --git a/DATABASE b/DATABASE index b498c2a..a4bc213 100644 --- a/DATABASE +++ b/DATABASE @@ -57,6 +57,14 @@ mysql: +---------------------+-------+ 6 rows in set (0.00 sec) +2016-01-08: + +Hey, turns out my default sqlite verstion 2.8.17 cannot handle this, +but sqlite 3 works like a charm. +[#include test.sql] +I may be replacing the backend. Requires different linux/dbstore.c +and web/query.cgi rewritten to use HDBC.sqlite3. + 2015-12-20: After some playing with it, I think it is a mistake. One of my assumed diff --git a/test.sql b/test.sql new file mode 100644 index 0000000..f4e929e --- /dev/null +++ b/test.sql @@ -0,0 +1,21 @@ +drop table if exists adjustment; +drop table if exists counter; +create table adjustment (timestamp datetime, value int); +create table counter (timestamp datetime, value int); + +insert into adjustment values ('2016-01-07 00:00:00', 10); +insert into counter values ('2016-01-07 00:00:01', 5); +insert into counter values ('2016-01-07 00:00:02', 10); +insert into counter values ('2016-01-07 00:00:03', 15); +insert into adjustment values ('2016-01-07 00:00:04', 30); +insert into counter values ('2016-01-07 00:00:05', 0); +insert into counter values ('2016-01-07 00:00:06', 5); +insert into counter values ('2016-01-07 00:00:07', 10); + +select timestamp, value+adj as value from + (select c.timestamp timestamp, c.value value, + (select value from adjustment a + where a.timestamp <= c.timestamp + order by timestamp desc limit 1) adj from counter c + ) t; +