Re: [sqlite] Multi-thread mode question

[Neo Anderson]

Thanks for your clarification.


> From: h...@scigames.at
> To: sqlite-users@sqlite.org
> Date: Mon, 9 Feb 2015 07:18:29 +
> Subject: Re: [sqlite] Multi-thread mode question
>
> In serialized mode, SQLite will acquire the mutex when it detects you are 
> "starting to use" the database handle (somewhere between entering 
> sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT 
> LETTING GO until the calling thread is "finished" (like when sqlite3_step 
> returns SQLITE_DONE or the thread calls sqlite3_reset or sqlite3_finalize).
>
> In multithread mode, you are taking over this responsibility; if you take 
> care, you may nest several selects from different threads into a single 
> transaction, but need to be aware of the fact that they will all commit or 
> rollback together.
>
> -Ursprüngliche Nachricht-
> Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
> Gesendet: Montag, 09. Februar 2015 06:34
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Multi-thread mode question
>
>> Does the application work if you configure SQLite to serialized mode?
> Yes. But I am confused why serialized mode works while multi-thread mode 
> always cause crashes because I also wrap calls around statement handle.
>
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment...
> Do I need to do this in serialized mode (suppose I use a single connection 
> across multiple threads)?
>
> 
>> Date: Sun, 8 Feb 2015 03:31:46 -0800
>> From: d3c...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multi-thread mode question
>>
>> it's better to use a connection per thread... the connection resource
>> isn't very big...
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment... if you do a execute and then start
>> stepping and getting values while another thread starts another
>> statement... that's 3 individual locks, but it doesn't lock the
>> context of the statement being used... it will lead to bizarre crashes
>> in the database; similar to double-releasing memory or delayed
>> reference of memory that has been released.
>>
>> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy  wrote:
>>
>>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>>
 The doc says:

 Multi-thread.
 In this mode, SQLite can be safely used by multiple threads provided
 that no single database connection is used simultaneously in two or
 more threads.

 I have a scenario that every sqlite3_calls around a single database
 connection is protected by a recursive mutex, but I have very
 strange runtime error in sqlite3.c and each time the error occurs at
 a different place.

 Does this mean the following statement is true:

 In muti-thead mode, a single database connection cannot be shared
 among threads even if any activity around the connection is protected by a 
 mutex.

>>>
>>> Not true.
>>>
>>> The only difference between multi-threaded and serialized mode is
>>> that, internally, every sqlite3_xxx() API call grabs a recursive
>>> mutex to prevent two threads from simultaneously accessing the database 
>>> handle structure.
>>> i.e. the same thing your code is doing externally.
>>>
>>> Note that calls on statement handles (i.e. sqlite3_step(),
>>> sqlite3_column_text() etc.) count as calls on the database handle
>>> that created them. So you need to protect them with the same mutex.
>>>
>>> Does the application work if you configure SQLite to serialized mode?
>>>
>>> Dan.
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> 

Re: [sqlite] equivalent for JOIN LATERAL

[Darren Duncan]

I recall that 
http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ shows 
how Pg 9.3's LATERAL join is useful in practice, as it lets you do in 
declarational SQL what you may have needed procedural code for before, in which 
case it is an improvement. -- Darren Duncan


On 2015-02-08 9:12 PM, James K. Lowden wrote:

On Sun, 8 Feb 2015 23:52:43 +0100
Big Stone  wrote:


I fall over this presentation of LATERAL, from postgresql guys.

Does it exist in SQLITE ?


Syntactically, no.  Functionally, in part.


If not, would it be possible too much effort ?


I'm guessing the answer is No because the prerequisites are missing.

Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views.  You think you'd like to be able to say,

SELECT S.*
FROM T join F(T.t) as S on T.t < S.x

where F is some function that produces a table for a scalar/row
input.

However, perfectly nothing new is really needed to express the idea:

SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

I suspect that new syntax like this is usually added to SQL for the
wrong reasons.

1.  Marketing.  Now with LATERAL added!
2.  User-imagined need, because don't know SQL
3.  Punt on query optimization, invent keyword as hint

In each case, they have added complexity without power.  The "improved"
system is harder to use and to develop.  But, hey, it's progress.

?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away?
? Antoine de Saint-Exupery

--jkl


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

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

oups ! Thank you Roger, I had forgot to post the link.

I got it via a tweet of Wes McKinney, one of the DataScience leader in the
Python World.

https://twitter.com/wesmckinn/status/564526251591733248
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-thread mode question

[Hick Gunter]

In serialized mode, SQLite will acquire the mutex when it detects you are 
"starting to use" the database handle (somewhere between entering 
sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT LETTING 
GO until the calling thread is "finished" (like when sqlite3_step returns 
SQLITE_DONE or the thread calls sqlite3_reset or sqlite3_finalize).

In multithread mode, you are taking over this responsibility; if you take care, 
you may nest several selects from different threads into a single transaction, 
but need to be aware of the fact that they will all commit or rollback together.

-Ursprüngliche Nachricht-
Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
Gesendet: Montag, 09. Februar 2015 06:34
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Multi-thread mode question

> Does the application work if you configure SQLite to serialized mode?
Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.

> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment...
Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?


> Date: Sun, 8 Feb 2015 03:31:46 -0800
> From: d3c...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multi-thread mode question
>
> it's better to use a connection per thread... the connection resource
> isn't very big...
> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment... if you do a execute and then start
> stepping and getting values while another thread starts another
> statement... that's 3 individual locks, but it doesn't lock the
> context of the statement being used... it will lead to bizarre crashes
> in the database; similar to double-releasing memory or delayed
> reference of memory that has been released.
>
> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy  wrote:
>
>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>
>>> The doc says:
>>>
>>> Multi-thread.
>>> In this mode, SQLite can be safely used by multiple threads provided
>>> that no single database connection is used simultaneously in two or
>>> more threads.
>>>
>>> I have a scenario that every sqlite3_calls around a single database
>>> connection is protected by a recursive mutex, but I have very
>>> strange runtime error in sqlite3.c and each time the error occurs at
>>> a different place.
>>>
>>> Does this mean the following statement is true:
>>>
>>> In muti-thead mode, a single database connection cannot be shared
>>> among threads even if any activity around the connection is protected by a 
>>> mutex.
>>>
>>
>> Not true.
>>
>> The only difference between multi-threaded and serialized mode is
>> that, internally, every sqlite3_xxx() API call grabs a recursive
>> mutex to prevent two threads from simultaneously accessing the database 
>> handle structure.
>> i.e. the same thing your code is doing externally.
>>
>> Note that calls on statement handles (i.e. sqlite3_step(),
>> sqlite3_column_text() etc.) count as calls on the database handle
>> that created them. So you need to protect them with the same mutex.
>>
>> Does the application work if you configure SQLite to serialized mode?
>>
>> Dan.
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Multi-thread mode question

[Keith Medcalf]

And those mutexes around statement usage apply the mutex based on the 
underlying connection, not the statement (which is irrelevant)?

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Neo Anderson
>Sent: Sunday, 8 February, 2015 22:34
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Multi-thread mode question
>
>> Does the application work if you configure SQLite to serialized mode?
>Yes. But I am confused why serialized mode works while multi-thread mode
>always cause crashes because I also wrap calls around statement handle.
>
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment...
>Do I need to do this in serialized mode (suppose I use a single
>connection across multiple threads)?
>
>
>> Date: Sun, 8 Feb 2015 03:31:46 -0800
>> From: d3c...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multi-thread mode question
>>
>> it's better to use a connection per thread... the connection resource
>isn't
>> very big...
>> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
>> lifetime of the statment... if you do a execute and then start stepping
>and
>> getting values while another thread starts another statement... that's
>3
>> individual locks, but it doesn't lock the context of the statement
>being
>> used... it will lead to bizarre crashes in the database; similar to
>> double-releasing memory or delayed reference of memory that has been
>> released.
>>
>> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy 
>wrote:
>>
>>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>>
 The doc says:

 Multi-thread.
 In this mode, SQLite can be safely used by multiple threads provided
>that
 no single database connection is used simultaneously in two or more
 threads.

 I have a scenario that every sqlite3_calls around a single database
 connection is protected by a recursive mutex, but I have very strange
 runtime error in sqlite3.c and each time the error occurs at a
>different
 place.

 Does this mean the following statement is true:

 In muti-thead mode, a single database connection cannot be shared
>among
 threads even if any activity around the connection is protected by a
>mutex.

>>>
>>> Not true.
>>>
>>> The only difference between multi-threaded and serialized mode is
>that,
>>> internally, every sqlite3_xxx() API call grabs a recursive mutex to
>prevent
>>> two threads from simultaneously accessing the database handle
>structure.
>>> i.e. the same thing your code is doing externally.
>>>
>>> Note that calls on statement handles (i.e. sqlite3_step(),
>>> sqlite3_column_text() etc.) count as calls on the database handle that
>>> created them. So you need to protect them with the same mutex.
>>>
>>> Does the application work if you configure SQLite to serialized mode?
>>>
>>> Dan.
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Multi-thread mode question

[Neo Anderson]

> Does the application work if you configure SQLite to serialized mode?
Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.

> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment...
Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?


> Date: Sun, 8 Feb 2015 03:31:46 -0800
> From: d3c...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multi-thread mode question
>
> it's better to use a connection per thread... the connection resource isn't
> very big...
> even if you wrap the sqlite3_ calls... you'll need to wrap the entire
> lifetime of the statment... if you do a execute and then start stepping and
> getting values while another thread starts another statement... that's 3
> individual locks, but it doesn't lock the context of the statement being
> used... it will lead to bizarre crashes in the database; similar to
> double-releasing memory or delayed reference of memory that has been
> released.
>
> On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy  wrote:
>
>> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>>
>>> The doc says:
>>>
>>> Multi-thread.
>>> In this mode, SQLite can be safely used by multiple threads provided that
>>> no single database connection is used simultaneously in two or more
>>> threads.
>>>
>>> I have a scenario that every sqlite3_calls around a single database
>>> connection is protected by a recursive mutex, but I have very strange
>>> runtime error in sqlite3.c and each time the error occurs at a different
>>> place.
>>>
>>> Does this mean the following statement is true:
>>>
>>> In muti-thead mode, a single database connection cannot be shared among
>>> threads even if any activity around the connection is protected by a mutex.
>>>
>>
>> Not true.
>>
>> The only difference between multi-threaded and serialized mode is that,
>> internally, every sqlite3_xxx() API call grabs a recursive mutex to prevent
>> two threads from simultaneously accessing the database handle structure.
>> i.e. the same thing your code is doing externally.
>>
>> Note that calls on statement handles (i.e. sqlite3_step(),
>> sqlite3_column_text() etc.) count as calls on the database handle that
>> created them. So you need to protect them with the same mutex.
>>
>> Does the application work if you configure SQLite to serialized mode?
>>
>> Dan.
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[James K. Lowden]

On Sun, 8 Feb 2015 23:52:43 +0100
Big Stone  wrote:

> I fall over this presentation of LATERAL, from postgresql guys.
> 
> Does it exist in SQLITE ?

Syntactically, no.  Functionally, in part.  

> If not, would it be possible too much effort ?

I'm guessing the answer is No because the prerequisites are missing.  

Something like LATERAL (or APPLY in SQL Server) arises around
table-valued functions, which really should be called parameterized
views.  You think you'd like to be able to say, 

SELECT S.*
FROM T join F(T.t) as S on T.t < S.x

where F is some function that produces a table for a scalar/row
input.  

However, perfectly nothing new is really needed to express the idea: 

SELECT S.*
FROM (select F(t) from T) as S
WHERE EXISTS (select 1 from T where S.x > T.t)

I suspect that new syntax like this is usually added to SQL for the
wrong reasons.  

1.  Marketing.  Now with LATERAL added!
2.  User-imagined need, because don't know SQL
3.  Punt on query optimization, invent keyword as hint

In each case, they have added complexity without power.  The "improved"
system is harder to use and to develop.  But, hey, it's progress.   

?Perfection is achieved not when there is nothing
left to add, but when there is nothing left to take away? 
? Antoine de Saint-Exupery

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

Re: [sqlite] equivalent for JOIN LATERAL

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/08/2015 03:32 PM, Simon Slavin wrote:
> For those like me who hadn't heard of it, here's a reference:

Here is a presentation referenced "Modern SQL in PostgreSQL", with
title "Still using Windows 3.1?  So why stick to SQL-92?"  Lots of
nice exposition and diagrams.  SQLite is also mentioned.

   http://www.slideshare.net/MarkusWinand/modern-sql

Seen on HackerNews:

   https://news.ycombinator.com/item?id=9018129

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTYB58ACgkQmOOfHg372QSipwCfTJa/M8UPBqQ1UwRmoxgTVSwU
wBoAn0I9HpwyQswDjLjCKjoY6IdTh4lu
=EOku
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[Simon Slavin]

On 8 Feb 2015, at 10:52pm, Big Stone  wrote:

> I fall over this presentation of LATERAL, from postgresql guys.
> (look at pages 1 to 16)

For those like me who hadn't heard of it, here's a reference:



and from the documentation, section 7.2.1.5 of



> Does it exist in SQLITE ?
> 
> If not, would it be possible [without] too much effort ?

I'm going to let other people answer the question.

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

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

If not, would it be possible "without" too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] equivalent for JOIN LATERAL

[Big Stone]

Hello,

I fall over this presentation of LATERAL, from postgresql guys.
(look at pages 1 to 16)

Does it exist in SQLITE ?

If not, would it be possible too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[James K. Lowden]

On Sun, 8 Feb 2015 09:57:54 -0500
Doug Currie  wrote:

> tonypdmtr  on SO
> posted a CTE solution; it is something like this, which works for me:
> 
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id
>union values (NULL, 0))
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as
> rank from tt as s where S_id is not NULL;
> 
> But my question remains, why is the UNION necessary in the  CTE?
> 
> why doesn't this work? ...
> 
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id)
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as
> rank from tt as s;

I don't know the answer to your questions, except to say that IMO the
SO answer is incorrect for at least two reasons:

1.  Last I checked, SELECT in a column position in the SELECT clause as
in 

select foo (select ...)

is not permitted by the SQL standard.  

2.  In principle, adding data in a query to "make it work" is the wrong
approach.  Here "and S_id is not NULL" is needed to compensate for that
UNION.  

Why it's "needed", in the sense that you get "misuse of aggregate", I
don't know.  It looks like valid SQLite syntax to me.  But I like my
corrected syntax better:

with tt (S_id, total) as
   (select S_id, sum(ca1 +ca2 + exam) as total
   from t group by S_id)
select S.S_id, S.total, 1+count(lesser.total) as RANK
from tt as S
left join tt as lesser
on   S.total < lesser.total
group by S.S_id, S.total
order by S.total desc;
S_idtotal   RANK  
--  --  --
2   198 1 
4   198 1 
5   183 3 
3   165 4 
1   143 5 

because it produces the desired result using standard syntax.  I would
argue that's a clearer expression of the problem, too.  

I have a couple of efficiency questions for those who know:

1.  Is the left-join on a CTE apt to be more effecient than the version
that uses a correlated subquery in the SELECT clause?  

2.  Is there any performance difference between 

sum(ca1 +ca2 + exam) 
and 
sum(ca1) + sum(ca2) + sum(exam) 

I would expect the left join is faster than a correlated subquery, and
that fewer aggregates is better than more. 

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

Re: [sqlite] Bug in SQLite FLOAT values

[Keith Medcalf]

A double precision floating point value contains about 14.5 to 16 digits of 
precision WHEN CONVERTED FROM BINARY TO DECIMAL TEXT.  However, the double 
precision number is merely the closest binary approximation of a value as can 
be encoded in BINARY (base 2) format.  Exact DEMAL (base 10) values cannot be 
represented in binary (base 2) floating point.  When you "round off" a floating 
point value to contain, as you put it, merely 6 digits of precision, what you 
are doing is throwing away some approximation data and making the value a less 
precise approximation of your diddled value -- you make it impossible to access 
the original value or to repeat the rerounding and achieve the same result.  
You are converting a value which is the best approximation of the value to a 
worse approximation of a value.  That you only want to see 6 digits of 
displayed resolution is a display problem, not a value storage and retrieval 
problem.

You are doing the equivalent of taking a beautiful 4096x2048 32-bit CMYK image 
and compressing it to a 16 KByte JPEG of postage stamp size (which it does by 
throwing away information such that it becomes permanently inaccessible and 
permanently irretrievable).  When you then "blow up" the JPEG back to the 
original size you are greeted by pixelated eye-bleed causing crap that bears no 
resemblance whatsoever to the original data.  The same applies to non-lossless 
audio compression formats as well (nay, all digital audio formats, truth be 
told).  Compressing an analogue signal into a 128kbit mpeg stream (or any other 
non-lossless format) permanently destroys data content, making it impossible to 
retrieve the original data (and in the case of audio, leaving only crap that a 
tone deaf moron using "cheap tinkle" audio equipment could stand -- it makes 
the rest of us bleed out our ears).

So too is "diddling" with double precision floating point numbers causing 
irretrievable and irreperable damage to the data values contained in them.

You are confusing the "data value" (in this case the floating point value) with 
the display representation for biots (text strings in base-10 decimal 
representation).  Once converted, you are throwing away information which you 
will never be able to recover ever again.  Store and work with ONLY the double 
value that is provided to you without any tinkering.  If you BIOTs require 
displaying only six digits of precision, then display it to them that way.  Do 
not confuse BIOT display and BIOT input requirements with the internal 
representation of the data inside a digital computer system.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Abdul Aziz
>Sent: Saturday, 7 February, 2015 07:22
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>OK, understood, thanks but can anyone explain me I was creating db of
>different sensors, I used same methods, but in fields which were FLOAT
>were
>filling with junk values (after 6 decimal places, see in SENSOR_1) why?
>even I was cutting it to 6 decimal places (as shown previously, was then
>again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
>in different table with same methods, then was getting correct
>(formatted,
>upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
>please have a look...
>
>Thank you very much :)
>
>On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik 
>wrote:
>
>> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>>
>>> Thanks for replybut I am now using VARCHARS, then how this is
>working?
>>> not generating any errors?
>>>
>>
>> When Tim said "Read this", he meant it. http://www.sqlite.org/
>> datatype3.html answers your questions (but only if you read it).
>> --
>> Igor Tandetnik
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>



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

Re: [sqlite] ordinary CTE containing sum()

[Darko Volaric]

I'm interested in this too (since I rely on it working). According to the
documentation this should be perfectly legal and seems like an arbitrary
limitation (or a bug). It says:

"An ordinary common table expression works as if it were a view that exists
for the duration of a single statement."

But it's not the case here.

On Sun, Feb 8, 2015 at 6:57 AM, Doug Currie  wrote:

> >
> > > In response to this SO question:
> > >
> > >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> > >
> > > I tried to formulate a query without temp tables using an ordinary
> > > CTE, but received an error "misuse of aggregate: sum()".
> >
>
> tonypdmtr  on SO posted
> a
> CTE solution; it is something like this, which works for me:
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id
>union values (NULL, 0))
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s where S_id is not NULL;
>
> But my question remains, why is the UNION necessary in the  CTE?
>
> why doesn't this work? ...
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id)
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s;
>
> e
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Simon Slavin]

On 7 Feb 2015, at 2:22pm, Abdul Aziz  wrote:

> but in fields which were FLOAT were
> filling with junk values (after 6 decimal places, see in SENSOR_1) why?

Take a look at what happens when you try to write 1/13th in decimal:

0.076923076923076923076923076923076923076923076923076923.

What about 7/9ths:

0.77.

And PI:

3.1415926535897932384626443323279502. (I forget the rest)

There's no way to write those numbers precisely in decimal.  The first repeats 
after the sixth place.  The second repeats after the first place.  The third 
never repeats, but also never stops.  Similarly there's no way to write some 
numbers precisely in binary.  So if I tell you you had to write the number 
using just digits and a decimal point, you can't do it.  Similarly, if you 
convert some numbers into binary format and back (just 0 and 1 and a decimal 
point) you don't get back precisely the number you put in.

Fields defined as FLOAT (in your Android API) have their values stored in 
binary format.  So storing a number in that field involves converting it to 
binary.  If, instead, you define your fields as text no conversion takes place 
because there's no need to turn the number into binary format.

(Above explanation simplified with respect to affinities and deep maths for 
simplicity.)

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

[sqlite] SQLite Toolbox - free Visual Studio extension

[Erik Ejlskov Jensen]

I have recently released this free VS addin, also Works with the free VS 2013 
Community edition.


Blog post: 
http://erikej.blogspot.dk/2014/08/sqlite-toolbox-40-visual-guide-of.html


Channel 9 video: 
http://channel9.msdn.com/Shows/Visual-Studio-Toolbox/SQL-Server-Compact-and-SQLite-Toolbox
 






Sendt fra Surface
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite FLOAT values

[Abdul Aziz]

OK, understood, thanks but can anyone explain me I was creating db of
different sensors, I used same methods, but in fields which were FLOAT were
filling with junk values (after 6 decimal places, see in SENSOR_1) why?
even I was cutting it to 6 decimal places (as shown previously, was then
again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
in different table with same methods, then was getting correct (formatted,
upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
please have a look...

Thank you very much :)

On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik  wrote:

> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>
>> Thanks for replybut I am now using VARCHARS, then how this is working?
>> not generating any errors?
>>
>
> When Tim said "Read this", he meant it. http://www.sqlite.org/
> datatype3.html answers your questions (but only if you read it).
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.5.0 of DB Browser for SQLite released

[justin]

It turned out there were a few important bugs in 3.5.0, so now there's
a v3.5.1 with fixes for them.

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.1

Hope that's useful to people. :)

+ Justin


On 2015-01-31 18:46, jus...@postgresql.org wrote:

Hi all,

We've just released v3.5.0 of DB Browser for SQLite:

  https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.0

This release has a fair amount of bug fixes (eg in CVS import), plus
several usability tweaks, and now supports Simplified Chinese.

This is the first release to support encrypted databases too, via
SQLCipher.  Only available in the non-Windows builds for now (eg
OSX, Linux, etc).

Windows and MacOS X binaries are available from the above URL.

Linux, FreeBSD, OS/2 users will need to compile it themselves (pretty
easy) for now, until ports/packages/etc become available.

Hope that's helpful for people. :)

Regards and best wishes,

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




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

Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

>
> > In response to this SO question:
> >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> >
> > I tried to formulate a query without temp tables using an ordinary
> > CTE, but received an error "misuse of aggregate: sum()".
>

tonypdmtr  on SO posted a
CTE solution; it is something like this, which works for me:

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id
   union values (NULL, 0))
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s where S_id is not NULL;

But my question remains, why is the UNION necessary in the  CTE?

why doesn't this work? ...

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id)
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s;

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

Re: [sqlite] Multi-thread mode question

[J Decker]

it's better to use a connection per thread... the connection resource isn't
very big...
even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment... if you do a execute and then start stepping and
getting values while another thread starts another statement... that's 3
individual locks, but it doesn't lock the context of the statement being
used... it will lead to bizarre crashes in the database; similar to
double-releasing memory or delayed reference of memory that has been
released.

On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy  wrote:

> On 02/08/2015 04:30 PM, Neo Anderson wrote:
>
>> The doc says:
>>
>> Multi-thread.
>> In this mode, SQLite can be safely used by multiple threads provided that
>> no single database connection is used simultaneously in two or more
>> threads.
>>
>> I have a scenario that every sqlite3_calls around a single database
>> connection is protected by a recursive mutex, but I have very strange
>> runtime error in sqlite3.c and each time the error occurs at a different
>> place.
>>
>> Does this mean the following statement is true:
>>
>> In muti-thead mode, a single database connection cannot be shared among
>> threads even if any activity around the connection is protected by a mutex.
>>
>
> Not true.
>
> The only difference between multi-threaded and serialized mode is that,
> internally, every sqlite3_xxx() API call grabs a recursive mutex to prevent
> two threads from simultaneously accessing the database handle structure.
> i.e. the same thing your code is doing externally.
>
> Note that calls on statement handles (i.e. sqlite3_step(),
> sqlite3_column_text() etc.) count as calls on the database handle that
> created them. So you need to protect them with the same mutex.
>
> Does the application work if you configure SQLite to serialized mode?
>
> Dan.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-thread mode question

[Dan Kennedy]

On 02/08/2015 04:30 PM, Neo Anderson wrote:

The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.

I have a scenario that every sqlite3_calls around a single database connection 
is protected by a recursive mutex, but I have very strange runtime error in 
sqlite3.c and each time the error occurs at a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared among threads 
even if any activity around the connection is protected by a mutex.


Not true.

The only difference between multi-threaded and serialized mode is that, 
internally, every sqlite3_xxx() API call grabs a recursive mutex to 
prevent two threads from simultaneously accessing the database handle 
structure. i.e. the same thing your code is doing externally.


Note that calls on statement handles (i.e. sqlite3_step(), 
sqlite3_column_text() etc.) count as calls on the database handle that 
created them. So you need to protect them with the same mutex.


Does the application work if you configure SQLite to serialized mode?

Dan.


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

[sqlite] Multi-thread mode question

[Neo Anderson]

The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.

I have a scenario that every sqlite3_calls around a single database connection 
is protected by a recursive mutex, but I have very strange runtime error in 
sqlite3.c and each time the error occurs at a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared among threads 
even if any activity around the connection is protected by a mutex.

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