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
