Re: [sqlite] Seasonal syntax

[Rowan Worth]

On 14 December 2017 at 01:19, Warren Young  wrote:

> On Dec 12, 2017, at 10:24 AM, Simon Slavin  wrote:
> >
> >  Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour=‘nice’
>
> I think you mean
>
> SELECT name,address
> CASE behaviour
>   WHEN ‘nice' THEN
> hobbies
>   ELSE
> 'coal'
>   END
> FROM people
>

Surely "hobbies" should read something like:

(SELECT gift FROM ideas WHERE ideas.hobby IN (SELECT value FROM
json_each(hobbies)) ORDER BY random() LIMIT 1)

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

Re: [sqlite] Can a trigger recursively update a table?

[Shane Dev]

brilliant! - it works - thanks

On 14 December 2017 at 19:07, Clemens Ladisch  wrote:

> Shane Dev wrote:
> > On 14 December 2017 at 12:59, Clemens Ladisch 
> wrote:
> >> Shane Dev wrote:
> >>> Can we conclude there is no single CTE or other SQL statement which can
> >>> update a branch of the tree starting with a flexibly specified node?
> >>
> >> That should be possible when you enable recursive triggers:
> >>
> >>   begin
> >> update hierarchy set status = null where id = old.id;
> >> delete from vhierarchy where parent = old.id;
> >>   end;
> >
> > With your solution, how would you define the DELETE ON VHIERARCHY
> trigger?
>
> That is the trigger.
>
>
> Regards,
> Clemens
> ___
> 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] Is this a bug with expression evaluation?

[Clemens Ladisch]

Tony Papadimitriou wrote:
> I really don't know what the standard says, but here are two different
> opinions in implementation.
>
> MySQL example:

You know that the "SQL" in "MySQL" is actually the abbreviation of
"something quite loose"?  ;-)

Anyway, it appears even MySQL conforms to SQL-92 subclause 6.12:

| 1) If the data type of both operands of a dyadic arithmetic opera-
|tor is exact numeric, then the data type of the result is exact
|numeric, with precision and scale determined as follows:
|[...]
|d) The precision and scale of the result of division is
|   implementation-defined.


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

Re: [sqlite] Is this a bug with expression evaluation?

[Peter Da Silva]

On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> Just to remind you that if something is not documented it can change.  The 
> next version of SQLite might decide that 1 / 2 is 0.  So don’t write code 
> that depends on it.

I think it already does:

sqlite> select 1/2;
0
sqlite> 


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

Re: [sqlite] Is this a bug with expression evaluation?

[Richard Hipp]

On 12/14/17, Tony Papadimitriou  wrote:
>
> MySQL example:
> mysql> select 1/2;
> ++
> | 1/2|
> ++
> | 0.5000 |
> ++
> 1 row in set (0.13 sec)

MySQL is the only database engine that behaves this way.  All others
do integer arithmetic on integer values.

This is probably the reason that MySQL has the separate "DIV" operator
for integer division, whereas everybody else makes due with the
standard "/" operator.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[Simon Slavin]

On 14 Dec 2017, at 5:03pm, Tony Papadimitriou  wrote:

> SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
> no affinity. "
> It seems that 'no affinity' gets translated to integer affinity, then.

Just to remind you that if something is not documented it can change.  The next 
version of SQLite might decide that 1 / 2 is 0.  So don’t write code that 
depends on it.

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

Re: [sqlite] Can a trigger recursively update a table?

[Clemens Ladisch]

Shane Dev wrote:
> On 14 December 2017 at 12:59, Clemens Ladisch  wrote:
>> Shane Dev wrote:
>>> Can we conclude there is no single CTE or other SQL statement which can
>>> update a branch of the tree starting with a flexibly specified node?
>>
>> That should be possible when you enable recursive triggers:
>>
>>   begin
>> update hierarchy set status = null where id = old.id;
>> delete from vhierarchy where parent = old.id;
>>   end;
>
> With your solution, how would you define the DELETE ON VHIERARCHY trigger?

That is the trigger.


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

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

[Simon Slavin]

What you see is not a bug, it’s an annoying heritage of C syntax.  Might even 
precede C.  Here’s the problem:

select column1*(24/100);

And here’s what you’re meant to do for 24%:

select column1*(24.0/100.0);

Alternatively, the value in column1 should be real.  That should also work.

If your numbers are real, express them as real, using either a decimal point or 
exponent/mantissa format.  "24" means integer 24.  "24.0" means real 24.

I’ve had this behaviour bite me when I was doing some simple maths in C and 
divided by "4" instead of "4.0".  It took me numerous debugging statements and 
three or four hours to figure out what was wrong, and when I figured it out I 
was so annoyed I left work early.

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

Re: [sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

-Original Message- 
From: J. King


Someone please correct me if I'm wrong, but I believe it's mandated by the 
SQL standard that integer division is used when both operands are integers.


I really don't know what the standard says, but here are two different 
opinions in implementation.


MySQL example:
mysql> select 1/2;
++
| 1/2|
++
| 0.5000 |
++
1 row in set (0.13 sec)

PostgreSQL example:
psql=# select 1/2;
?column?
--
   0
(1 row)


Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column 
>affinity would do the rest.


SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
no affinity. "

It seems that 'no affinity' gets translated to integer affinity, then.

Is there a way to default to float?

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

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

[Hick Gunter]

This is well documented behaviour, see the explanation of affinity. See 
http://sqlite.org/datatype3.html#affinity

If you require floating point arithmetic, you must introduce REAL affinity, 
either by including a field with storage class REAL, a cast operation or a real 
literal value

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Tony Papadimitriou
Gesendet: Donnerstag, 14. Dezember 2017 17:36
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select 
column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[J. King]

Someone please correct me if I'm wrong, but I believe it's mandated by the SQL 
standard that integer division is used when both operands are integers. 

Your synthetic example doesn't use a fixed table, but if it did the easiest 
solution for you would probably be to define any columns where you need 
arbitrary precision as REAL rather than INTEGER, and SQLite column affinity 
would do the rest.

Otherwise, yes, I believe you would need to cast. 

On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou  wrote:
>I’ve noticed this (very annoying) behavior:
>
>select column1*(24/100) wrong from (values(100));
>
>Removing the parentheses yields the correct result:
>
>select column1*24/100 correct from (values(100));
>
>This obviously behaves like integer math is used and (24/100) gets
>truncated to zero.
>
>If I add a dot to either number (e.g., 24. or 100.) I get the correct
>result.
>But, with named fields, it’s not as easy as adding a dot:
>
>select column1*(column2/column3) wrong from (values(100,24,100));
>select column1*column2/column3 correct from (values(100,24,100));
>
>So, to get correct answer I have to use a cast for either field?
>
>select column1*(cast(column2 as float)/column3) correct from
>(values(100,24,100));
>
>In this example removing the parentheses is a simple solution.
>But if the expression was column1*(1+column2/column3) a cast is the
>only way?  (Hope not!)
>
>Anyway, if all this happens to be so by design, is there at least some
>way to default to float math rather than integer?
>
>Thanks.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug with expression evaluation?

[Marc L. Allen]

I just multiply by 1.0

Select column1*(column2 * 1.0 / column3)...

Removing the parentheses only provide the correct results in your example.  
It's still using integer math, it's just performing the multiply first, as per 
order of operations.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Thursday, December 14, 2017 11:36 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select 
column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug with expression evaluation?

[Tony Papadimitriou]

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100));
select column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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

Re: [sqlite] Can a trigger recursively update a table?

[Shane Dev]

Hi Clemens,

With your solution, how would you define the DELETE ON VHIERARCHY trigger?

On 14 December 2017 at 12:59, Clemens Ladisch  wrote:

> Shane Dev wrote:
> > Can we conclude there is no single CTE or other SQL statement which can
> > update a branch of the tree starting with a flexibly specified node?
>
> That should be possible when you enable recursive triggers:
>
>   begin
> update hierarchy set status = null where id = old.id;
> delete from vhierarchy where parent = old.id;
>   end;
>
>
> Regards,
> Clemens
> ___
> 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] DateTime kind stored as undefined

[Cezary H. Noweta]

I'm sorry -- the following post was sent to a private e-mail by an accident:

Hello,

On 2017-12-13 12:51, Michał Niegrzybowski wrote:
> I have a table which has a column of type DateTime in my code I insert
> there an actual UTC Date (which is not the same as my local time). When I
> want to gather previously added record, my record contains date in his
> DateTime column, but this DateTime is a localtime with kind specified to
> 'undefined' instead of 'UTC'.


I cannot reproduce the problem. Setting a format to ticks and a kind to 
UTC causes a storing/retrieving a valid UTC DateTime, which is stored as 
INTEGER. Could you provide your connection string?


-- best regards

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

Re: [sqlite] [EXTERNAL] Re: How to index data based on custom comparisons?

[Hick Gunter]

Select  from blob_index idx cross join data_table dt on 
(idx.rowid = dt.rowid) where ;

Assuming that the rowid of the blob_index is generated from and identical to 
the rowid of the data table

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lifepillar
Gesendet: Donnerstag, 14. Dezember 2017 13:52
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] How to index data based on custom comparisons?

On 14/12/2017 13:14, Richard Hipp wrote:
> On 12/14/17, Lifepillar  wrote:
>
>> I am not familiar with virtual tables yet, but I see that they are
>> used, for example, to implement Rtree indexes. Would it be feasible
>> to implement my own index structure as a virtual table and use it to
>> index a blob column in a standard table (or even just in the virtual
>> table itself)?
>
> That would be complicated.

So, it is possible :)

> A different idea.  Suppose you have two new UDFs:
>
> ieee754dec(X):  Converts IEEE754-binary number X into IEEE754-decimal.
> In other words it takes a "double" input and returns a "blob" output.
>
> ieee754bin(Y):  Converts IEEE754-decimal blob Y and converts it into
> IEEE754-binary.
>
> Both routines are approximate because most IEEE754-binary values do
> not have an exact equivalent IEEE754-decimal representation and vice
> versa.  Your UDFs would need to find something very close.
>
> Given these routines, you could then index your IEEE754-decimal
> columns by doing an index on an expression using the new iee754bin()
> function.

Thanks, that's another possibility to consider, although one typically uses 
decimal values when exactness is needed; using your scheme requires some care, 
I think, for example not to miss matches because of approximations or to filter 
spurious matches away.

Somehow, I wish SQLite had something like PostgreSQL GiST indexes... ;)

Life.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to index data based on custom comparisons?

[Lifepillar]

On 14/12/2017 13:14, Richard Hipp wrote:

On 12/14/17, Lifepillar  wrote:


I am not familiar with virtual tables yet, but I see that they are used,
for example, to implement Rtree indexes. Would it be feasible to
implement my own index structure as a virtual table and use it to index
a blob column in a standard table (or even just in the virtual table
itself)?


That would be complicated.


So, it is possible :)


A different idea.  Suppose you have two new UDFs:

ieee754dec(X):  Converts IEEE754-binary number X into IEEE754-decimal.
In other words it takes a "double" input and returns a "blob" output.

ieee754bin(Y):  Converts IEEE754-decimal blob Y and converts it into
IEEE754-binary.

Both routines are approximate because most IEEE754-binary values do
not have an exact equivalent IEEE754-decimal representation and vice
versa.  Your UDFs would need to find something very close.

Given these routines, you could then index your IEEE754-decimal
columns by doing an index on an expression using the new iee754bin()
function.


Thanks, that's another possibility to consider, although one typically
uses decimal values when exactness is needed; using your scheme
requires some care, I think, for example not to miss matches because of
approximations or to filter spurious matches away.

Somehow, I wish SQLite had something like PostgreSQL GiST indexes... ;)

Life.

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

Re: [sqlite] Does sqlite have official development testing tool?

[J Decker]

On Thu, Dec 14, 2017 at 4:19 AM, advancenOO 
wrote:

> Hello Richard,
>
> I hope to run some tests by myself and I think TCL tests in your link are
> what I want.
> There are so many .tcl and .test in Sqlite source tree.
> Could someone share what commands I need to run to start all TCL tests?
>
> the 'make test' target uses a program 'testfixture' which is passed a tcl
script.  You can re-run indicidual tests with './testfixture tests/'
although that's the majority of the tests, some of the tests are
'fuzzcheck' which is another program built by the makefile.
make test does a good majority of the tests... there's also make fulltest
which takes many hours to complete but does 10x more tests.


> Thanks.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] How to index data based on custom comparisons?

[Lifepillar]

On 13/12/2017 22:20, Simon Slavin wrote:


On 13 Dec 2017, at 8:34pm, Lifepillar  wrote:


But, (correct me if
I am wrong), if I index the blob column directly, comparisons are
based on memcpy(), which in my case is not what I want. Is it
possible to create an index that somehow uses a custom comparison
function instead? E.g., I have a deccmp(x,y) function that returns
-1 if xy. Can I define an index based on
that?


As Dr H wrote, it can’t be done.  Either store a normalised (numeric) version 
of the number, or store both the BLOB and a normalised version.


Thanks for the suggestion. Storing a normalized version side by side 
with the unnormalized number would work, but it would double the used 
space.  Storing just the normalized number would lose information, though.


Life.

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

Re: [sqlite] Does sqlite have official development testing tool?

[Richard Hipp]

On 12/14/17, advancenOO  wrote:
> Hello Richard,
>
> I hope to run some tests by myself and I think TCL tests in your link are
> what I want.
> There are so many .tcl and .test in Sqlite source tree.
> Could someone share what commands I need to run to start all TCL tests?
>

make test

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

Re: [sqlite] Does sqlite have official development testing tool?

[advancenOO]

Hello Richard,

I hope to run some tests by myself and I think TCL tests in your link are
what I want.
There are so many .tcl and .test in Sqlite source tree.
Could someone share what commands I need to run to start all TCL tests? 

Thanks.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the timing to truncate WAL file to journal_size_limit

[Richard Hipp]

On 12/14/17, advancenOO  wrote:
> I noticed that,
> “The journal_size_limit pragma may be used to limit the size of WAL files
> left in the file-system after transactions or checkpoints. Each time a WAL
> file resets, SQLite compares the size of the WAL file left in the
> file-system to the size limit.”
>
> But I think only when the first transaction commits AFTER a checkpoint will
> WAL file truncate to the limit. As the src code is,
> if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ).
>
> Which means WAL file may still consume a large amount of space after
> checkpoints, unless a new transaction commits. Is that right?

Correct.  The reset happens on the next transaction commit.

There is also "PRAGMA wal_checkpoint(TRUNCATE);"

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

Re: [sqlite] How to index data based on custom comparisons?

[Richard Hipp]

On 12/14/17, Lifepillar  wrote:

> I am not familiar with virtual tables yet, but I see that they are used,
> for example, to implement Rtree indexes. Would it be feasible to
> implement my own index structure as a virtual table and use it to index
> a blob column in a standard table (or even just in the virtual table
> itself)?

That would be complicated.

A different idea.  Suppose you have two new UDFs:

ieee754dec(X):  Converts IEEE754-binary number X into IEEE754-decimal.
In other words it takes a "double" input and returns a "blob" output.

ieee754bin(Y):  Converts IEEE754-decimal blob Y and converts it into
IEEE754-binary.

Both routines are approximate because most IEEE754-binary values do
not have an exact equivalent IEEE754-decimal representation and vice
versa.  Your UDFs would need to find something very close.

Given these routines, you could then index your IEEE754-decimal
columns by doing an index on an expression using the new iee754bin()
function.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[Clemens Ladisch]

Shane Dev wrote:
> Can we conclude there is no single CTE or other SQL statement which can
> update a branch of the tree starting with a flexibly specified node?

That should be possible when you enable recursive triggers:

  begin
update hierarchy set status = null where id = old.id;
delete from vhierarchy where parent = old.id;
  end;


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

Re: [sqlite] How to index data based on custom comparisons?

[Lifepillar]

On 14/12/2017 00:02, Keith Medcalf wrote:


On Wednesday, 13 December, 2017 13:35, Lifepillar  
wrote:


I am implementing an extension for manipulating IEEE754 decimal
numbers. Numbers are stored as blobs using a standard encoding.
Numbers that are mathematically equal may have different
representations, (e.g., 1.0 may have mantissa 10 and exponent -1
while 1.00 may have mantissa 100 and exponent -2).


You have stated something that is impossible, or at least self-contradictory.  Unless, of course, 
you are talking about the "decimal" formats of IEEE754-2008 and not the standard (far 
more common) "binary" formats.


Yes, I am talking about decimal IEEE754-2008 format, not binary. I 
thought this would be clear from my use of the word "decimal" and from 
my example.



On the other hand however if you do NOT need binary64 at all, then there was a minor 
change discussed a while back by someone else where you can "change" the 
default floating-point number format from binary64 to decimal64 and then compile your own 
custom version of SQLite3 ...


Thanks for the tip! I will search for that discussion, although I would 
rather not modify SQlite3 source code if I can find another solution.


I am not familiar with virtual tables yet, but I see that they are used, 
for example, to implement Rtree indexes. Would it be feasible to 
implement my own index structure as a virtual table and use it to index
a blob column in a standard table (or even just in the virtual table 
itself)? I mean, would the optimizer be able to take advantage of such 
an index?


(Sorry if my questions sound naive, I'm still pretty new to SQLite.)

Life.

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

[sqlite] the timing to truncate WAL file to journal_size_limit

[advancenOO]

I noticed that, 
“The journal_size_limit pragma may be used to limit the size of WAL files
left in the file-system after transactions or checkpoints. Each time a WAL
file resets, SQLite compares the size of the WAL file left in the
file-system to the size limit.”

But I think only when the first transaction commits AFTER a checkpoint will
WAL file truncate to the limit. As the src code is, 
if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ). 

Which means WAL file may still consume a large amount of space after
checkpoints, unless a new transaction commits. Is that right?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users