Re: [sqlite] Fwd: inserting new data only

2020-02-26 Thread Jose Isaias Cabrera

James K. Lowden, on Wednesday, February 26, 2020 11:55 AM, wrote...
>
> On Tue, 25 Feb 2020 12:02:24 -0500
> Przemek Klosowski  wrote:
>
> > and I want to avoid storing repetitive data, so that the database
> > should contain
> > 10:32  12
> > 10:35  15
> > 10:39  13
> > 10:46  18
> > where only the earliest time with the unchanging value is stored.
>
> Be careful what you wish for.  Usually "avoid storing" is a proxy for
> some kind of intended use.  Unless it's infeasible, it's usually better
> to store everything, verbatim, as it arrives.  Then you can present it
> however you like, with nothing lost.

Fresh real-life-work experience...
I was tasked to take data from a system and report on it, so, as I looked
over the data I saw multiple entries, so I asked the client do you want to
know if a set of data is repeated? "NO," said the client, "I only care for
what new and fresh."  Are you sure, I can see that there are repeated
entries, and I can see different places where they are coming from, and
how often, and... "NO!,  I only care for what new and fresh."
When I was going to show the reporting tool, she said, "By the way, this
has how many times repeated records are coming in, and from where,
correct?" I knew this was going to happened, so I had left a way to
quickly make a few changes, re-imported the data, and was able to save
the world.  So, as James said, "I would keep everything and just show
what you need."  Maybe someday you want to know how many
repeated entries happen in an hour, day, etc.  Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-26 Thread Przemek Klosowski
On Wed, Feb 26, 2020 at 11:56 AM James K. Lowden
 wrote:
> > and I want to avoid storing repetitive data, so that the database
> > should contain
> > [...]
> > only the earliest time with the unchanging value is stored.
>
> Be careful what you wish for.  Usually "avoid storing" is a proxy for
> some kind of intended use.  Unless it's infeasible, it's usually better
> to store everything, verbatim, as it arrives.  Then you can present it
> however you like, with nothing lost.

A lot of process measurement instrumentation returns some nominal
values, for instance firmware version. Do we store it or not? Storing
it every second is silly, but if we drop it, we wouldn't be able to
for instance audit if the statistics changed in some subtle way
because of a firmware bug. This 'store only changed values' is
intended for situations like that.
Another suitable candidate for that treatment might be a status, for
instance 'on battery' value for a UPS monitoring system. I can't think
of a scenario where storing it every time would be better.
The only disadvantage I can think of would be incidental, like
monitoring the monitoring system itself: a long period of 'no data'
could be caused by a monitoring failure as well as by the value not
changing; but if that is a concern, I think it would be better to
store a single heartbeat rather than possibly multiple unchanging
values.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-26 Thread James K. Lowden
On Tue, 25 Feb 2020 12:02:24 -0500
Przemek Klosowski  wrote:

> and I want to avoid storing repetitive data, so that the database
> should contain 
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.

Be careful what you wish for.  Usually "avoid storing" is a proxy for
some kind of intended use.  Unless it's infeasible, it's usually better
to store everything, verbatim, as it arrives.  Then you can present it
however you like, with nothing lost.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Keith Medcalf

I keep forgetting that the mix/max optimization is not applied at the same time 
as retrieving other data from a table, so if you actually want to optimize the 
generated plan, you need to use the following trigger:

create trigger data_insert before insert on data
begin
select raise(ABORT, 'Out of order insert')
 where julianday(new.key) <= coalesce((select julianday(max(key)) from 
data), -1);
select raise(IGNORE)
 where new.data IS (select data from data order by key desc limit 1);
end;

The query optimizer is smart enough to recognize that max(key) when used by 
itself and an index is available on key, that the index can be used to locate 
the row containing the max key (it is the last one and there is no need to look 
any further).  However, if you select max(key), data from table then the 
optimization is not applied and a scan of the whole table is done looking for 
the max(key) even though there is a suitable index, and you have to specify the 
index and how to use it and that you only need 1st result.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 25 February, 2020 14:44
>To: SQLite mailing list 
>Subject: Re: [sqlite] Fwd: inserting new data only
>
>
>If you are going to do it in all in one insert statement rather than
>using a before trigger to throw an error (ie, you want to silently ignore
>out-of-order inserts) then the following is slightly more efficient since
>the query planner appears to materialize the search so only does it once:
>
>with old(key, data)
>  as (
>  select coalesce(julianday(max(key)), -1),
> data
>from data
> ),
> new(key, data)
>  as (
>values (?, ?)
> )
>insert into data
> select new.key, new.data
>   from new, old
>  where new.data IS NOT old.data
>and julianday(new.key) > old.key;
>
>However, without the trigger the database will not enforce the
>monotonicity of the insert timestamps ...
>
>Note that you could do the whole thing in before trigger's which would
>mean you just use a regular old insert and the triggers do all the work,
>and then the database would entirely enforce its own integrity and rules
>... no matter who or what was trying to insert records ...
>
>create table data
>(
>keytext primary key,
>data   integer not null
>)
>without rowid;
>
>create trigger data_prohibit_oo_inserts before insert on data
>  when julianday(new.key) <= (select julianday(max(key)) from data)
>begin
>select raise(ABORT, 'Out of order insert');
>end;
>
>create trigger data_prohibit_duplicates before insert on data
>  when new.data IS (select data from (select max(key), data from data))
>begin
>select raise(IGNORE);
>end;
>
>
>-- insert into data values (?, ?);
>
>insert into data values ('10:32', 12);
>insert into data values ('10:35', 15);
>insert into data values ('10:37', 15);
>insert into data values ('10:39', 13);
>insert into data values ('10:43', 13);
>insert into data values ('10:46', 18);
>
>select * from data;
>10:32|12
>10:35|15
>10:39|13
>10:46|18
>
>insert into data values ('10:32', 12);
>Error: near line 33: Out of order insert
>insert into data values ('10:35', 15);
>Error: near line 34: Out of order insert
>insert into data values ('10:37', 15);
>Error: near line 35: Out of order insert
>insert into data values ('10:39', 13);
>Error: near line 36: Out of order insert
>insert into data values ('10:43', 13);
>Error: near line 37: Out of order insert
>insert into data values ('10:46', 18);
>
>select * from data;
>10:32|12
>10:35|15
>10:39|13
>10:46|18
>
>You could even do that with just one before trigger ...
>
>create table data
>(
>keytext primary key,
>data   integer not null
>)
>without rowid;
>
>create trigger data_insert before insert on data
>begin
>select raise(ABORT, 'Out of order insert')
> where julianday(new.key) <= coalesce((select julianday(max(key))
>from data), -1);
>select raise(IGNORE)
> where new.data IS (select data from (select max(key), data from
>data));
>end;
>
>-- insert into data values (?, ?);
>
>insert into data values ('10:32', 12);
>insert into data values ('10:35', 15);
>insert into data values ('10:37', 15);
>insert into data values ('10:39', 13);
>insert into data values ('10:43

Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Keith Medcalf

If you are going to do it in all in one insert statement rather than using a 
before trigger to throw an error (ie, you want to silently ignore out-of-order 
inserts) then the following is slightly more efficient since the query planner 
appears to materialize the search so only does it once:

with old(key, data)
  as (
  select coalesce(julianday(max(key)), -1),
 data
from data
 ),
 new(key, data)
  as (
values (?, ?)
 )
insert into data
 select new.key, new.data
   from new, old
  where new.data IS NOT old.data
and julianday(new.key) > old.key;

However, without the trigger the database will not enforce the monotonicity of 
the insert timestamps ...

Note that you could do the whole thing in before trigger's which would mean you 
just use a regular old insert and the triggers do all the work, and then the 
database would entirely enforce its own integrity and rules ... no matter who 
or what was trying to insert records ...

create table data
(
keytext primary key,
data   integer not null
)
without rowid;

create trigger data_prohibit_oo_inserts before insert on data
  when julianday(new.key) <= (select julianday(max(key)) from data)
begin
select raise(ABORT, 'Out of order insert');
end;

create trigger data_prohibit_duplicates before insert on data
  when new.data IS (select data from (select max(key), data from data))
begin
select raise(IGNORE);
end;


-- insert into data values (?, ?);

insert into data values ('10:32', 12);
insert into data values ('10:35', 15);
insert into data values ('10:37', 15);
insert into data values ('10:39', 13);
insert into data values ('10:43', 13);
insert into data values ('10:46', 18);

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

insert into data values ('10:32', 12);
Error: near line 33: Out of order insert
insert into data values ('10:35', 15);
Error: near line 34: Out of order insert
insert into data values ('10:37', 15);
Error: near line 35: Out of order insert
insert into data values ('10:39', 13);
Error: near line 36: Out of order insert
insert into data values ('10:43', 13);
Error: near line 37: Out of order insert
insert into data values ('10:46', 18);

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

You could even do that with just one before trigger ...

create table data
(
keytext primary key,
data   integer not null
)
without rowid;

create trigger data_insert before insert on data
begin
select raise(ABORT, 'Out of order insert')
 where julianday(new.key) <= coalesce((select julianday(max(key)) from 
data), -1);
select raise(IGNORE)
 where new.data IS (select data from (select max(key), data from data));
end;

-- insert into data values (?, ?);

insert into data values ('10:32', 12);
insert into data values ('10:35', 15);
insert into data values ('10:37', 15);
insert into data values ('10:39', 13);
insert into data values ('10:43', 13);
insert into data values ('10:46', 18);

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

insert into data values ('10:32', 12);
Error: near line 28: Out of order insert
insert into data values ('10:35', 15);
Error: near line 29: Out of order insert
insert into data values ('10:37', 15);
Error: near line 30: Out of order insert
insert into data values ('10:39', 13);
Error: near line 31: Out of order insert
insert into data values ('10:43', 13);
Error: near line 32: Out of order insert
insert into data values ('10:46', 18);
Error: near line 33: Out of order insert

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 25 February, 2020 13:15
>To: SQLite mailing list 
>Subject: Re: [sqlite] Fwd: inserting new data only
>
>
>On Tuesday, 25 February, 2020 12:23, Przemek Klosowski
> wrote:
>
>>On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf 
>wrote:
>
>>> create table data
>>> (
>>> keytext primary key,
>>> data   integer not null
>>> )
>>> without rowid;
>>>
>>> -- insert into data select ?, ? as value where value IS NOT (select
>>data from (select max(key), data from data));
>>>..
>>> Constraints:
>>>
>>> (1) Will only work for appending data (new key > all keys in table)
>>> (2) Types of key and data are immaterial as long as you are only
>>inserting (appending) new keys.
>
>>Awesome---exactly what's nee

Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Keith Medcalf

On Tuesday, 25 February, 2020 12:23, Przemek Klosowski 
 wrote:

>On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf  wrote:

>> create table data
>> (
>> keytext primary key,
>> data   integer not null
>> )
>> without rowid;
>>
>> -- insert into data select ?, ? as value where value IS NOT (select
>data from (select max(key), data from data));
>>..
>> Constraints:
>>
>> (1) Will only work for appending data (new key > all keys in table)
>> (2) Types of key and data are immaterial as long as you are only
>inserting (appending) new keys.

>Awesome---exactly what's needed.
>The monotonicity of the time key variable is assured by how the data
>is collected---but  is there a way to express that in sqlite?
>create table data (
>  key text primary key check
>(julianday(key) > julianday(select max(key) from data),
>  data integer not null);

You cannot do this with a CHECK constraint since check constraints cannot 
execute select statements (check constraints should be table invariant -- 
meaning that they must return the same result no matter what data is in the 
table or other tables, so only act to validate the data on the current row).  

This would be a case for a before insert trigger to prohibit the insert before 
it is performed (an after trigger would also work, but that would fire after 
the row is already inserted and would work by doing a statement rollback to 
delete the inserted row, so you want to avoid the row insertion completely, and 
the way to do that is with a before trigger):

create trigger data_prohibit_oo_inserts before insert on data 
  when julianday(new.key) <= (select julianday(max(key)) from data)
begin
select raise(ABORT, 'Out of order insert');
end;

This means a lookup and check after the record insertion is computed, if a 
record is to be inserted, but the btree will already be in memory and will have 
already traversed to the last entry, so this will consume CPU only, and very 
little at that.

create table data
(
keytext primary key,
data   integer not null
)
without rowid;

create trigger data_prohibit_oo_inserts before insert on data
  when julianday(new.key) <= (select julianday(max(key)) from data)
begin
select raise(ABORT, 'Out of order insert');
end;


-- insert into data select ?, ? as value where value != (select value from 
(select max(key), value from data));

insert into data select '10:32', 12 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:35', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:37', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:39', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:43', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:46', 18 as value where value IS NOT (select data 
from (select max(key), data from data));

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

insert into data select '10:32', 12 as value where value IS NOT (select data 
from (select max(key), data from data));
Error: near line 27: Out of order insert
insert into data select '10:35', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
Error: near line 28: Out of order insert
insert into data select '10:37', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
Error: near line 29: Out of order insert
insert into data select '10:39', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
Error: near line 30: Out of order insert
insert into data select '10:43', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
Error: near line 31: Out of order insert
insert into data select '10:46', 18 as value where value IS NOT (select data 
from (select max(key), data from data));

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

You could also change the insert like this:

insert into data 
 select ? as key, ? as value 
  where value IS NOT (select data from (select max(key), data from data))
and (julianday(key) > coalesce((select julianday(max(key)) from 
data),-1));

but that would just silently ignore the error rather than raising an error.  
(You need the coalesce because the "select julianday(max(key)) from data" could 
be null and it has to be non-null for the > expression to return a not null 
result (anything > null returns null which is FALSE in a where clause and NOT 
would not help since "NOT (anything > null)" is still null or false.  The 
trigger does not have this problem because a NULL result means the trigger does 
not fire.  Of course, changing the raise(ABORT ...) in the trigger to raise 
(IGNORE) achieves the same result.

create table data
(
keytext p

Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread David Raymond
Awesome---exactly what's needed.
The monotonicity of the time key variable is assured by how the data
is collected---but  is there a way to express that in sqlite?
create table data (
  key text primary key check
(julianday(key) > julianday(select max(key) from data),
  data integer not null);


That would/should be done in a trigger, and not a check constraint. A check 
constraint is only supposed to be something which will _always_ be true about 
that one and only record, and which only needs the contents of that 1 record to 
determine, and not something that might change depending on... anything else.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Przemek Klosowski
On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf  wrote:
> create table data
> (
> keytext primary key,
> data   integer not null
> )
> without rowid;
>
> -- insert into data select (?, ? as value where value IS NOT (select data 
> from (select max(key), data from data));
>..
> Constraints:
>
> (1) Will only work for appending data (new key > all keys in table)
> (2) Types of key and data are immaterial as long as you are only inserting 
> (appending) new keys.

Awesome---exactly what's needed.
The monotonicity of the time key variable is assured by how the data
is collected---but  is there a way to express that in sqlite?
create table data (
  key text primary key check
(julianday(key) > julianday(select max(key) from data),
  data integer not null);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Keith Medcalf

Note that this will work for discrete data from a sensor but will not properly 
historize continuous data.  That is, if what you are historizing is process 
data such as a temperature, this will not permit you to re-create the original 
engineering data.  For that you need to allow the last duplicate to be stored 
and also store the computed slope to prior with each append -- in that case 
triggers would be the only way to do it.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: Keith Medcalf 
>Sent: Tuesday, 25 February, 2020 11:18
>To: 'SQLite mailing list' 
>Subject: RE: [sqlite] Fwd: inserting new data only
>
>
>create table data
>(
>keytext primary key,
>data   integer not null
>)
>without rowid;
>
>-- insert into data select (?, ? as value where value IS NOT (select data
>from (select max(key), data from data));
>
>insert into data select '10:32', 12 as value where value IS NOT (select
>data from (select max(key), data from data));
>insert into data select '10:35', 15 as value where value IS NOT (select
>data from (select max(key), data from data));
>insert into data select '10:37', 15 as value where value IS NOT (select
>data from (select max(key), data from data));
>insert into data select '10:39', 13 as value where value IS NOT (select
>data from (select max(key), data from data));
>insert into data select '10:43', 13 as value where value IS NOT (select
>data from (select max(key), data from data));
>insert into data select '10:46', 18 as value where value IS NOT (select
>data from (select max(key), data from data));
>
>select * from data;
>10:32|12
>10:35|15
>10:39|13
>10:46|18
>
>Constraints:
>
>(1) Will only work for appending data (new key > all keys in table)
>(2) Types of key and data are immaterial as long as you are only
>inserting (appending) new keys.
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users  On
>>Behalf Of Przemek Klosowski
>>Sent: Tuesday, 25 February, 2020 10:02
>>To: SQLite mailing list 
>>Subject: [sqlite] Fwd: inserting new data only
>>
>>I am storing time series data arriving from a sensor into (time,value)
>>records, like so:
>>10:32  12
>>10:35  15
>>10:37  15
>>10:39  13
>>10:43  13
>>10:46  18
>>
>>and I want to avoid storing repetitive data, so that the database should
>>contain
>>10:32  12
>>10:35  15
>>10:39  13
>>10:46  18
>>where only the earliest time with the unchanging value is stored.
>>
>>I don't see how INSERT could be conditional on e.g.  value != (select
>>value from tbl order by time descending limit 1), so I thought I'd use
>>triggers. The only way I could think of was to delete the new
>>duplicate record after it has been inserted:
>>
>>create trigger cull after insert on tbl when
>> (select value-lead(value) over (order by time desc) from a limit 1) = 0
>>begin
>>   delete from a where time like new.time;
>>end;
>>
>>Is there a simpler way?
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Keith Medcalf

create table data
(
keytext primary key,
data   integer not null
)
without rowid;

-- insert into data select (?, ? as value where value IS NOT (select data from 
(select max(key), data from data));

insert into data select '10:32', 12 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:35', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:37', 15 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:39', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:43', 13 as value where value IS NOT (select data 
from (select max(key), data from data));
insert into data select '10:46', 18 as value where value IS NOT (select data 
from (select max(key), data from data));

select * from data;
10:32|12
10:35|15
10:39|13
10:46|18

Constraints:

(1) Will only work for appending data (new key > all keys in table)
(2) Types of key and data are immaterial as long as you are only inserting 
(appending) new keys.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Przemek Klosowski
>Sent: Tuesday, 25 February, 2020 10:02
>To: SQLite mailing list 
>Subject: [sqlite] Fwd: inserting new data only
>
>I am storing time series data arriving from a sensor into (time,value)
>records, like so:
>10:32  12
>10:35  15
>10:37  15
>10:39  13
>10:43  13
>10:46  18
>
>and I want to avoid storing repetitive data, so that the database should
>contain
>10:32  12
>10:35  15
>10:39  13
>10:46  18
>where only the earliest time with the unchanging value is stored.
>
>I don't see how INSERT could be conditional on e.g.  value != (select
>value from tbl order by time descending limit 1), so I thought I'd use
>triggers. The only way I could think of was to delete the new
>duplicate record after it has been inserted:
>
>create trigger cull after insert on tbl when
> (select value-lead(value) over (order by time desc) from a limit 1) = 0
>begin
>   delete from a where time like new.time;
>end;
>
>Is there a simpler way?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Przemek Klosowski
On Tue, Feb 25, 2020 at 1:03 PM John McKown
 wrote:
> > I am storing time series data arriving from a sensor into (time,value)
> > records, like so:
> > 10:32  12
> > 10:35  15
> > 10:37  15
> > 10:39  13
> > 10:43  13
> > 10:46  18
> >
> > and I want to avoid storing repetitive data, so that the database should
> > contain
> > 10:32  12
> > 10:35  15
> > 10:39  13
> > 10:46  18
> > where only the earliest time with the unchanging value is stored.
...
> Why not:
>
> CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY);
>
> You can't INSERT duplicate numbers into the "VALUE" column, it will fail.

This won't work here because the same value COULD reappear later:
12 15 15 13 13 18 15
needs to be registered as 12 15 13 18 15
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread Przemek Klosowski
On Tue, Feb 25, 2020 at 12:22 PM David Raymond  wrote:
>
> A before trigger which uses the raise function would stop it from getting 
> inserted in the first place.
>
> create trigger cull
> before insert on tbl
> when new.value = (select value from tbl order by time desc limit 1)
> begin
> select raise(ignore);
> end;
>
> Or if you want it to actually return an error to let you know what happened 
> you could make it
> select raise(abort, 'Repeated entry');

Ah, that's cool---I missed the 'ignore' possibility and thought that
the before trigger can't prevent the subsequent insert.

>
>
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Przemek Klosowski
> Sent: Tuesday, February 25, 2020 12:02 PM
> To: SQLite mailing list 
> Subject: [sqlite] Fwd: inserting new data only
>
> I am storing time series data arriving from a sensor into (time,value)
> records, like so:
> 10:32  12
> 10:35  15
> 10:37  15
> 10:39  13
> 10:43  13
> 10:46  18
>
> and I want to avoid storing repetitive data, so that the database should 
> contain
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.
>
> I don't see how INSERT could be conditional on e.g.  value != (select
> value from tbl order by time descending limit 1), so I thought I'd use
> triggers. The only way I could think of was to delete the new
> duplicate record after it has been inserted:
>
> create trigger cull after insert on tbl when
>  (select value-lead(value) over (order by time desc) from a limit 1) = 0
> begin
>delete from a where time like new.time;
> end;
>
> Is there a simpler way?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread John McKown
On Tue, Feb 25, 2020 at 11:03 AM Przemek Klosowski <
przemek.klosowski+sql...@gmail.com> wrote:

> I am storing time series data arriving from a sensor into (time,value)
> records, like so:
> 10:32  12
> 10:35  15
> 10:37  15
> 10:39  13
> 10:43  13
> 10:46  18
>
> and I want to avoid storing repetitive data, so that the database should
> contain
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.
>
> I don't see how INSERT could be conditional on e.g.  value != (select
> value from tbl order by time descending limit 1), so I thought I'd use
> triggers. The only way I could think of was to delete the new
> duplicate record after it has been inserted:
>
> create trigger cull after insert on tbl when
>  (select value-lead(value) over (order by time desc) from a limit 1) = 0
> begin
>delete from a where time like new.time;
> end;
>
> Is there a simpler way?
> ___
>


Why not:

CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY);

You can't INSERT duplicate numbers into the "VALUE" column, it will fail.


-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Fwd: inserting new data only

2020-02-25 Thread David Raymond
A before trigger which uses the raise function would stop it from getting 
inserted in the first place. 

create trigger cull
before insert on tbl
when new.value = (select value from tbl order by time desc limit 1)
begin
select raise(ignore);
end;

Or if you want it to actually return an error to let you know what happened you 
could make it
select raise(abort, 'Repeated entry');



-Original Message-
From: sqlite-users  On Behalf Of 
Przemek Klosowski
Sent: Tuesday, February 25, 2020 12:02 PM
To: SQLite mailing list 
Subject: [sqlite] Fwd: inserting new data only

I am storing time series data arriving from a sensor into (time,value)
records, like so:
10:32  12
10:35  15
10:37  15
10:39  13
10:43  13
10:46  18

and I want to avoid storing repetitive data, so that the database should contain
10:32  12
10:35  15
10:39  13
10:46  18
where only the earliest time with the unchanging value is stored.

I don't see how INSERT could be conditional on e.g.  value != (select
value from tbl order by time descending limit 1), so I thought I'd use
triggers. The only way I could think of was to delete the new
duplicate record after it has been inserted:

create trigger cull after insert on tbl when
 (select value-lead(value) over (order by time desc) from a limit 1) = 0
begin
   delete from a where time like new.time;
end;

Is there a simpler way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users