[sqlite] sqliteDefaultBusyCallback and HAVE_USLEEP

2020-02-25 Thread Peter Kolbus
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

Re: [sqlite] more efficient JSON encoding: idle musing

2020-02-25 Thread Jens Alfke
> 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

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,

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:

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

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);

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)); >.. >

Re: [sqlite] inserting new data only

2020-02-25 Thread Simon Slavin
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

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

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

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

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

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

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

[sqlite] Fwd: inserting new data only

2020-02-25 Thread Przemek Klosowski
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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

2020-02-25 Thread Graham Holden
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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

2020-02-25 Thread Luuk
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

Re: [sqlite] more efficient JSON encoding: idle musing

2020-02-25 Thread J Decker
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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

2020-02-25 Thread Graham Holden
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

[sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

2020-02-25 Thread Matt Kloss
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