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
(
key text 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
(
key text 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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 25 February, 2020 13:15
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Fwd: inserting new data only
>
>
>On Tuesday, 25 February, 2020 12:23, Przemek Klosowski
><przemek.klosowski+sql...@gmail.com> wrote:
>
>>On Tue, Feb 25, 2020 at 1:18 PM Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>> create table data
>>> (
>>> key text 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
>(
> key text 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
>(
> key text primary key,
> data integer not null
>)
>without rowid;
>
>insert into data select '10:32' as key, 12 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));
>insert into data select '10:35' as key, 15 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));
>insert into data select '10:37' as key, 15 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));
>insert into data select '10:39' as key, 13 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));
>insert into data select '10:43' as key, 13 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));
>insert into data select '10:46' as key, 18 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));
>
>select * from data;
>10:32|12
>10:35|15
>10:39|13
>10:46|18
>
>insert into data select '10:32' as key, 12 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));
>insert into data select '10:35' as key, 15 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));
>insert into data select '10:37' as key, 15 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));
>insert into data select '10:39' as key, 13 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));
>insert into data select '10:43' as key, 13 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));
>insert into data select '10:46' as key, 18 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));
>
>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.
>
>
>
>_______________________________________________
>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
