I noticed that sqliteDefaultBusyCallback() seems to depend directly on the OS
(behave differently based on SQLITE_OS_WIN||HAVE_USLEEP). Since the underlying
primitive, sqlite3OsSleep(), actually uses the VFS to sleep, and unixSleep()
also has a roundup to whole seconds when HAVE_USLEEP is not
> On Feb 25, 2020, at 6:12 AM, J Decker wrote:
>
> other than that; if space is really a concern, maybe a zip layer?
In my experience, the concern is more about speed than size. Given the raw
string/blob data from a SQLite column, and a specific property name/path, how
fast can you find its
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,
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:
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
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);
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));
>..
>
This strikes me as best solved in the programming language.
If a single set of data points is being acquired in real time, and you have a
programming language (or script) generating the INSERT commands, why not simply
keep the most recently inserted temperature in a variable ?
On the other
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
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
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
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
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
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
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
Tuesday, February 25, 2020, 3:00:09 PM, Luuk wrote:
[tests snipped]
> So, the index does not grow indefinitely
> On 25-2-2020 14:00, Graham Holden wrote:
>> It is an interesting problem. And the above is just guesswork... It would
>> be good to verify experimentally that the index really does
script:
#!/bin/bash
if [ ! -f test.db ] ; then sqlite3 test.db "CREATE VIRTUAL TABLE tab
USING fts5(x)"; fi
sqlite3 test.db ".import wikipedia tab"
a=$(sqlite3 test.db "SELECT count(*) FROM tab_data")
echo "# records after import: $a"
sqlite3 test.db "DELETE FROM tab"
a=$(sqlite3 test.db
On Fri, Feb 21, 2020 at 6:03 AM Richard Hipp wrote:
> On 2/21/20, Wout Mertens wrote:
> > The idea is that upon storing the JSON
> > data, the JSON1 extension parses it, extracts the layouts recursively,
> > stores them when they are not known yet, and then only stores the
> > values in the
This might be to do with how an FTS index works under the hood,
involving various levels of "b-tree" that grow as entries are added,
but aren't always shrunk when entries are deleted.
There were a bunch of emails on the list around 4th to the 13th May
2014: sample below from Dan Kennedy (one of
Dear sqlite users,
I noticed that when you delete lines from a FTS virtual table, somehow there is
some data remaining in the sqlite db, so that's it does not shrink much in size.
$ sqlite3 test.sql "CREATE VIRTUAL TABLE tab USING fts5(x)"
$ curl -s https://www.wikipedia.org | tr -cd
20 matches
Mail list logo