[sqlite] How to set SQLITE_THREADSAFE=2 through the configure arguments?

[Yuri]

Documentation suggests that  -DSQLITE_THREADSAFE has 3 valid values: 0, 
1, 2. https://www.sqlite.org/threadsafe.html


But configure script appears to only be able to set the values 0 and 1 
with --enable-threadsafe. How to set -DSQLITE_THREADSAFE=2? I think, 
configure script is missing this ability.



Yuri

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

Re: [sqlite] How to configure size of shared-cache in SQLite?

[Jens Alfke]

> On Mar 10, 2017, at 3:32 PM, Simon Slavin  wrote:
> 
> Two different patterns of use.  One is that the different threads/processes 
> usually care about different rows (maybe in different tables).  In that case, 
> shared cache is of very little benefit.  The other is when different 
> threads/processes usually update the same parts of the files.  In that case 
> sharing cache can provide a great improvement in throughput.

This is the latter case — the connections would be in a pool for threads to 
use. So each connection will be making a random subset of the queries.
(My understanding is that the SQLite package for .NET already works this way, 
though I haven’t looked at it myself.)

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

Re: [sqlite] How to configure size of shared-cache in SQLite?

[Simon Slavin]

On 10 Mar 2017, at 9:34pm, Keith Medcalf  wrote:

> You mean physical reads?  I suppose this would be possible, as long as the 
> working set of all your read queries are able to fit in the cache 
> simultaneously.  If not, you are likely to get more cache thrash with the 
> cache being shared then if it is not shared since you are using the same 
> cache for all connections, rather one per connection that will contain only 
> the working set for the queries processed on that connection.

Two different patterns of use.  One is that the different threads/processes 
usually care about different rows (maybe in different tables).  In that case, 
shared cache is of very little benefit.  The other is when different 
threads/processes usually update the same parts of the files.  In that case 
sharing cache can provide a great improvement in throughput.

Modified for SQLite, of course, because almost every modification modifies the 
beginning of the database file and the beginning of the journal file.

But yes, as Keith points out, there’s no way to know which optimization(s) will 
benefit your particular setup without trying them.  And you shouldn’t waste a 
lot of time on doing anything non-standard unless a vanilla setup is too slow.  
You are not trying to provide the fastest possible program; you are trying to 
provide a program which is fast enough.

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

Re: [sqlite] How to configure size of shared-cache in SQLite?

[Simon Slavin]

On 10 Mar 2017, at 9:34pm, Keith Medcalf  wrote:

> You mean physical reads?  I suppose this would be possible, as long as the 
> working set of all your read queries are able to fit in the cache 
> simultaneously.  If not, you are likely to get more cache thrash with the 
> cache being shared then if it is not shared since you are using the same 
> cache for all connections, rather one per connection that will contain only 
> the working set for the queries processed on that connection.

Two different patterns of use.  One is that the different threads/processes 
usually care about different rows (maybe in different tables).  In that case, 
shared cache is of very little benefit.  The other is when different 
threads/processes usually update the same parts of the files.  In that case 
sharing cache can provide a great improvement in throughput.

Modified for SQLite, of course, because almost every modification modifies the 
beginning of the database file and the beginning of the journal file.

But yes, as Keith points out, there’s no way to know which optimization(s) will 
benefit your particular setup without trying them.  And you shouldn’t waste a 
lot of time on doing anything non-standard unless a vanilla setup is too slow.  
You are not trying to provide the fastest possible program; you are trying to 
provide a program which is fast enough.

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

Re: [sqlite] How to configure size of shared-cache in SQLite?

[Keith Medcalf]

On Friday, 10 March, 2017 10:57, Jens Alfke  wrote:
>> On Mar 9, 2017, at 7:55 AM, Keith Medcalf  wrote:

>> Why are you using SHARED_CACHE since it does not sound like you have
>> memory constraints on cache size, which is the primary (only) reason you
>> would ever want to use shared cache since the penalties for doing so are
>> significant.

> What about for reducing I/O? If the cache is shared, presumably the number
> of read calls will be correspondingly reduced.

You mean physical reads?  I suppose this would be possible, as long as the 
working set of all your read queries are able to fit in the cache 
simultaneously.  If not, you are likely to get more cache thrash with the cache 
being shared then if it is not shared since you are using the same cache for 
all connections, rather one per connection that will contain only the working 
set for the queries processed on that connection.
 
> I’m considering using a shared cache to cut down on I/O, as well as
> memory. If I use a pool of connections for read-only queries from multiple
> threads, it seems like a win to have them share a cache. 

Maybe.  It depends if the overhead of managing a shared cache (and the possible 
thrashing of that cache) exceeds the cost of not using a shared cache.  That 
will depend on your workload.  It is easy enough to experiment, however, since 
you only change one parameter to enable shared cache for the connections in the 
pool.

> I’d use a separate connection with its own cache for writes. Does that make 
> sense?

Yes.  However, each write will of course invalidate the cache used by the other 
connections (whether shared or not).  Though if it is WAL, I would suppose the 
cache would be invalidated only after a checkpoint ... but I am not certain of 
this.




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

Re: [sqlite] Why isn't my time formatting working?

[Jens Alfke]

> On Mar 8, 2017, at 12:52 PM, R Smith  wrote:
> 
>> Interestingly I rarely see dates stored in ISO8601 format/text
> 
> Because every programmer is a self-proclaimed optimization genius!

In this case it often makes sense to optimize in advance. In multiple 
situations over the years I’ve seen date-string parsing be a major bottleneck, 
in operations like database indexing and file reading. It’s surprisingly 
expensive; some of that is due to handling the weirdnesses of human date 
systems, but a lot seems to be because the typical functions have to handle 
arbitrary formats and decipher the format string as well as the input. (I’ve 
found you can do a lot better with a function that’s hardcoded to parse a 
specific date format.)

> If speed/space isn't critical, I always advise ISO8601 dates, typically 
> stored (in SQLite anyway) in a NUMERIC typed column.

I basically agree, it’s just that the speed seems to be critical more often 
than one would think :)

At least some date formats, including ISO-8601 with times in UTC, have the 
feature that you can compare dates as strings without having to parse them. 
That makes sorting by date a lot faster.

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

Re: [sqlite] SQLIte crash in sqlite3_db_release_memory

[Richard Hipp]

On 3/10/17, Anthrathodiyil, Sabeel (S.)  wrote:
> Hi,
> I am facing a crash while invoking "sqlite3_db_release_memory" the crash is
> from pcache1RemoveFromHash.  SQLite 3.7.10 is running on ARM A5 with
> Freescale MQX as OS.
>
> Any probable reasons in term of the SQLite operations that are done wrong or
> out of order?

The usual reason for this is that some other part of your application
has corrupted the heap and SQLite has stumbled over the damage.

I also observe the SQLite 3.7.10 is over 5 years old.  There have been
67 subsequent releases.  Version 3.17.0 use less than half the CPU
cycles as 3.7.10 and is completely backwards compatible.
-- 
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] SQLIte crash in sqlite3_db_release_memory

[Simon Slavin]

On 10 Mar 2017, at 2:05pm, Anthrathodiyil, Sabeel (S.)  
wrote:

> Stack trace is as below
> --
> pcache1RemoveFromHash
> pcache1EnforceMaxPage
> pcache1Shrink
> sqlite3PcacheShrink
> 
> The sequence of SQLite operations being followed which lead to crash is in 
> the following order
> prepare_sql
> step_sql
> sqlite3_db_release_memory
> sqlite3_finalize
> 
> Any probable reasons in term of the SQLite operations that are done wrong or 
> out of order?

Please check to see that the parameter you’re passing to 
sqlite3_db_release_memory() is the connection, not the statement.

If that’s not the problem, then for debugging purposes can you try reversing 
the order of the last two steps ?  Finalize the statement before you release 
memory for the connection.

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

[sqlite] SQLIte crash in sqlite3_db_release_memory

[Anthrathodiyil, Sabeel (S.)]

Hi,
I am facing a crash while invoking "sqlite3_db_release_memory" the crash is 
from pcache1RemoveFromHash.  SQLite 3.7.10 is running on ARM A5 with Freescale 
MQX as OS.

Stack trace is as below
--
pcache1RemoveFromHash
pcache1EnforceMaxPage
pcache1Shrink
sqlite3PcacheShrink

The sequence of SQLite operations being followed which lead to crash is in the 
following order
prepare_sql
step_sql
sqlite3_db_release_memory
sqlite3_finalize

Any probable reasons in term of the SQLite operations that are done wrong or 
out of order?

Thanks,
Sabeel



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

Re: [sqlite] How to configure size of shared-cache in SQLite?

[Jens Alfke]

> On Mar 9, 2017, at 7:55 AM, Keith Medcalf  wrote:
> 
> Why are you using SHARED_CACHE since it does not sound like you have memory 
> constraints on cache size, which is the primary (only) reason you would ever 
> want to use shared cache since the penalties for doing so are significant.


What about for reducing I/O? If the cache is shared, presumably the number of 
read calls will be correspondingly reduced.

I’m considering using a shared cache to cut down on I/O, as well as memory. If 
I use a pool of connections for read-only queries from multiple threads, it 
seems like a win to have them share a cache. I’d use a separate connection with 
its own cache for writes. Does that make sense?

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

Re: [sqlite] sqlite3 feature or regression

[Nelson, Erik - 2]

Thanks for taking the time to send in the report and thanks to all who 
investigated it.  The robust discussion demonstrates the passion of the sqlite 
community, and I enjoy thinking about the various points as they are made.

From: [Vermes Mátyás ]
Sent: Mar 10, 2017 7:37 AM
To: [SQLite mailing list ]
Subject: [Re: [sqlite] sqlite3 feature or regression]

Thanks. Naturally I had experimented with several versions of the program, and 
saw that any ordering makes the  new feature/error disappear. You can see it if 
you read the comment at the bottom of my original script. But my purpose was 
the opposite: demonstrate the regression. I am not interested in this thing. I 
do not have applications based on SQLite. Simply I think that this 
feature/error cannot be left in its current state, because this is in 
contradiction with the axiom, that the result of a query must not depend from 
the existence of an index. I wanted to help you with this bug report, but I 
cannot do more for that.

> As Dan already observed, the problem results because you are modifying
> an index in the middle of a scan of that index, thereby messing up the
> scan.  Don't do that.  The safest approach is to run the query to
> completion, then go back and start the loop over UPDATEs.
>
> If you add "ORDER BY +rowid" to the query, that forces the query to
> run to completion first and then go through the sorter, before you get
> any results back, which solves the problem.

--
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Hick Gunter]

Perhaps an analogy will help: Imagine you are given a piano. Pressing keys on 
the piano will cause the corresponding tones to be played. If you hit the keys 
on the piano with a hammer, then this too will cause tones to be played; 
however, it will also most likely cause mechanical failure (i.e. no more tones 
played) of the piano over time, because it is not designed to be used in that 
manner. This is no fault of the piano or it's maker, even without a US 
disclaimer stating that keys on the piano may only be pressed within certain 
limits.

Likewise, updating the index you are scanning with (including the default rowid 
index) constitutes breach of design parameters, and not only in SQLite but also 
in many other indexed storage systems.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Vermes Mátyás
Gesendet: Donnerstag, 09. März 2017 15:43
An: SQLite mailing list 
Betreff: Re: [sqlite] sqlite3 feature or regression

Thanks. Naturally I had experimented with several versions of the program, and 
saw that any ordering makes the  new feature/error disappear. You can see it if 
you read the comment at the bottom of my original script. But my purpose was 
the opposite: demonstrate the regression. I am not interested in this thing. I 
do not have applications based on SQLite. Simply I think that this 
feature/error cannot be left in its current state, because this is in 
contradiction with the axiom, that the result of a query must not depend from 
the existence of an index. I wanted to help you with this bug report, but I 
cannot do more for that.

> As Dan already observed, the problem results because you are modifying
> an index in the middle of a scan of that index, thereby messing up the
> scan.  Don't do that.  The safest approach is to run the query to
> completion, then go back and start the loop over UPDATEs.
>
> If you add "ORDER BY +rowid" to the query, that forces the query to
> run to completion first and then go through the sorter, before you get
> any results back, which solves the problem.

--
Vermes Mátyás   
___
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
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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why isn't my time formatting working?

[Will Parsons]

On Wednesday,  8 Mar 2017  3:40 PM -0500, Paul Sanderson wrote:
> The vast majority of dates I see in SQLite databases are unix epoch integer
   ^
> times (seconds since 1/1/1980) with unix milli seconds a close second.
  ^
> Efficient to store, sort and do date arithmetic on but need to be converted
> to display.
>
> I also see unix nano seconds, 100 nano seconds, windows filetimes, chrome
> dates and NSDates/MacAbsolute very regularly.

I don't know a about "chrome dates" or "NSDates/MacAbsolute", but the
others are *time* formats, not dates.  Sure, one can use a time format
to represent a date (presumably by using midnight to represent the
date), but then you should probably add a constraint to database
allowing only multiples of 86400 seconds in the field.

Perhaps this may seem a bit of a quibble, but dates are a conceptually
distinct from timestamps.

> Interestingly I rarely see dates stored in ISO8601 format/text

I don't know about that - I certainly do.

> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 8 March 2017 at 20:17, David Raymond  wrote:
>
>> Correct. The ISO strings are the de-facto standard since that's what all
>> the date and time functions take in.
>> http://www.sqlite.org/lang_datefunc.html
>>
>> "The strftime() routine returns the date formatted according to the format
>> string specified as the first argument."

-- 
Will

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

Re: [sqlite] sqlite3 feature or regression

[Vermes Mátyás]

Thanks. Naturally I had experimented with several versions of the program, and 
saw that any ordering makes the  new feature/error disappear. You can see it if 
you read the comment at the bottom of my original script. But my purpose was 
the opposite: demonstrate the regression. I am not interested in this thing. I 
do not have applications based on SQLite. Simply I think that this 
feature/error cannot be left in its current state, because this is in 
contradiction with the axiom, that the result of a query must not depend from 
the existence of an index. I wanted to help you with this bug report, but I 
cannot do more for that.

> As Dan already observed, the problem results because you are modifying
> an index in the middle of a scan of that index, thereby messing up the
> scan.  Don't do that.  The safest approach is to run the query to
> completion, then go back and start the loop over UPDATEs.
> 
> If you add "ORDER BY +rowid" to the query, that forces the query to
> run to completion first and then go through the sorter, before you get
> any results back, which solves the problem.

-- 
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Vermes Mátyás]

> With 3.11.0, the scan is probably using the index instead of the table 

In this case the phenomena would be a new "feature". Unfortunately this would 
contradict to the axiom, that the result of a query must be independent of the 
existence of the indices.
-- 
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] More built-in functions for basic math

[Richard Hipp]

On 3/10/17, Dominique Devienne  wrote:
>
> PS: The latter would be better than nothing (I mean in compiled ready to
> use form),
> if the former doesn't happen, as seems likely given the lack of response
> from DRH.
> (via the ML or the SQLite Fossil Timeline)

The SQLite developers have a private chatroom on which this topic is
being discussed.

-- 
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] More built-in functions for basic math

[Dominique Devienne]

On Fri, Mar 10, 2017 at 12:29 PM, Stephan Buchert 
wrote:

> There is extension-functions.c in
> http://sqlite.org/contrib/download/
> [...]

Is the suggestion to have sqrt, sin, cos, stdev, ... built into sqlite
> standalone, or to provide a more a obvious way to access the library
> functions on systems where these are available?
>

The former. I even linked to that .c in the original post. --DD

PS: The latter would be better than nothing (I mean in compiled ready to
use form),
if the former doesn't happen, as seems likely given the lack of response
from DRH.
(via the ML or the SQLite Fossil Timeline)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More built-in functions for basic math

[Stephan Buchert]

There is extension-functions.c in

http://sqlite.org/contrib/download/

which I (and probably others) use, works well, gets via my .sqliterc always
loaded.

This extension-functions.c is a bit hard to find, probably because of its
not so descriptive name. It is not standalone, rather it links to libm etc.
Is the suggestion to have sqrt, sin, cos, stdev, ... built into sqlite
standalone, or to provide a more a obvious way to access the library
functions on systems where these are available?

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