Re: [sqlite] notify all processes of database modification

2020-02-09 Thread Rowan Worth
On Mon, 10 Feb 2020 at 12:53, Simon Slavin wrote: > On 10 Feb 2020, at 4:26am, Rowan Worth wrote: > > > See also PRAGMA data_version when it comes to polling the DB, the return > value of which changes when another process modifies the DB. IIRC the > implementation of this depends on a value in

Re: [sqlite] notify all processes of database modification

2020-02-09 Thread Simon Slavin
On 10 Feb 2020, at 4:26am, Rowan Worth wrote: > See also PRAGMA data_version when it comes to polling the DB, the return > value of which changes when another process modifies the DB. IIRC the > implementation of this depends on a value in the DB header page, so it may be > sufficient to only

Re: [sqlite] How to group this?

2020-02-09 Thread Rowan Worth
On Mon, 10 Feb 2020 at 11:12, Richard Damon wrote: > On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308

Re: [sqlite] notify all processes of database modification

2020-02-09 Thread Rowan Worth
On Sat, 8 Feb 2020 at 04:02, Jens Alfke wrote: > > On Feb 7, 2020, at 6:23 AM, Kees Nuyt wrote: > > > > Anyway, SQLite doesn't have such a mechanism by itself. > > Maybe inotify is useful to you : > > > > https://en.wikipedia.org/wiki/Inotify < > https://en.wikipedia.org/wiki/Inotify> > >

Re: [sqlite] How to group this?

2020-02-09 Thread Richard Damon
On 2/9/20 7:24 PM, Bart Smissaert wrote: ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563

Re: [sqlite] How to group this?

2020-02-09 Thread Keith Medcalf
That's good, but this not screw up later userid/date if an entry is AWOL. WITH systolic AS ( select userid, date, rank() over (partition by userid, date order by id) as rank, reading from pressure

Re: [sqlite] How to group this?

2020-02-09 Thread Keith Medcalf
select id, entry_date, max(case when term == 'Systolic' then reading end) as Systolic, max(case when term == 'Diastolic' then reading end) as Diastolic from the_table group by id, entry_date ; should be select id, entry_date, max(case

Re: [sqlite] How to group this?

2020-02-09 Thread Keith Medcalf
select id, entry_date, max(case when term == 'Systolic' then reading end) as Systolic, max(case when term == 'Diastolic' then reading end) as Diastolic from the_table group by id, entry_date ; If you want to make sure you have both terms for a given

Re: [sqlite] How to group this?

2020-02-09 Thread Simon Slavin
On 10 Feb 2020, at 1:25am, no...@null.net wrote: > Here is one way that appears to generate the correct result. Another way: create two VIEWs, one for systolic, one for diasystolic. Index both VIEWs on (id, date), then JOIN ON id AND date. If you want to, you could use this to make a third

Re: [sqlite] How to group this?

2020-02-09 Thread nomad
On Mon Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > I should get: > > 127/81 > 132/82 > 141/85 > 143/94 > > What should be the SQL to group like this? Here is one way that appears to generate the correct result. CREATE TABLE pressure( id INTEGER PRIMARY KEY,

[sqlite] "INSERT INTO table AS alias" is invalid inside triggers

2020-02-09 Thread nomad
I suspect I have found a parsing error in SQLite 3.30.1. Given the following: CREATE TABLE t1(a INTEGER PRIMARY KEY); The following statement is accepted by the parser: INSERT INTO t1 AS original (a) VALUES(1) ON CONFLICT DO NOTHING; However if I wrap it inside a trigger:

[sqlite] How to group this?

2020-02-09 Thread Bart Smissaert
ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563 1308 27/Jun/2013 Systolic 143 701564 1308