Re: [sqlite] New word to replace "serverless"

[Donald Shepherd]

On Wed, 29 Jan 2020 at 12:26 am, Jose Isaias Cabrera 
wrote:

>
> R Smith, on Tuesday, January 28, 2020 06:39 AM, wrote...
> >
> > I do not have a great suggestion to add, but to observe that the best
> > suggestions I think are: NOT changing, (or if we have to) "Server-Free"
> > or "Localized".
>
> I agree with these, but localize is another buzz word for translation.


Internationalisation/translation is my first thought when I hear
localisation/localised, a buzzword that must be at least 15 years old given
when I first encountered it used like that.

Regards,
Donald Shepherd.

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

Re: [sqlite] New word to replace "serverless"

[Donald Shepherd]

On Tue, 28 Jan 2020 at 10:19 am, Richard Hipp  wrote:

> daemon-less?
> --
> D. Richard Hipp
> d...@sqlite.org


In-process? Same concept but defining it by what it is rather than what it
isn't.

Regards,
Donald Shepherd.

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

Re: [sqlite] Bug fixes only branch.

[Donald Shepherd]

On Tue, 14 Jan 2020 at 7:00 am, Donald Griggs  wrote:

> Hi, Syed,
>
> ===
> On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad 
> wrote:
>
> > We are at 3.14.2   Date : 2016-09-12
> >
> > how can i take latest stable branch which include only bug fixes . no new
> > features.
> >
> > Is there any way?
> > ==
>
>
> I may well not be understanding properly, but what motivates you to ask for
> this?
> Since the sqlite team spends so much effort to ensure backward
> compatibility, how bad would things be if you simply updated to the current
> stable release?
>
> The team does allow many features to be eliminated through conditional
> compilation if you are severely constrained in RAM.   Was RAM size the
> motivation?
>
> To provide versions which include only bug fixes from any arbitrary
> releasee, I should think the developers would, for every stable release,
> have to maintain a new fixes-only branch indefinitely -- and thus have to
> maintain dozens of branches.   Am I missing something?
>
> Kind regards,
>Donald
> ___


I can't speak to his exact scenario but having spent time in a very risk
averse work environment, I've experienced this kind of thinking.

The logic is almost always as a result of "we must have low bug counts
(true) so we need bug fixes (true) but new features introduce bugs (in
general true) therefore we don't want any new features".

In other words it's a result of the environment rather than a reflection of
SQLite.

Regards,
Donald Shepherd.

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

Re: [sqlite] Performance vs. memory trade-off question

[Donald Shepherd]

On Sun, 15 Dec 2019 at 1:40 am, Dominique Pellé 
wrote:

> Richard Hipp  wrote:
>
> > On 12/14/19, Dominique Pellé  wrote:
> > >
> > > I'm curious about what kind of change reduces memory per
> > > connection.
> > >
> >
> > The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
> >
> > I'll try to add more description later - busy with other things right
> > this moment.
>
> Ah, it about lookaside. I build with SQLITE_OMIT_LOOKASIDE
> so I suppose the mini-lookaside-take-2 branch will do nothing then
> in this case.
>
> Regards
> Dominique
> ___


Gut feel is that the divide will fall such that embedded use cases want the
memory and PC or equivalent use cases want the CPU.  I fall into the latter
so inevitably want the performance since 72k doesn't really move the needle
on our usage but we've found ourselves CPU bound a few times.

If that split on the usefulness is correct, is it reasonable to suggest
that since embedded cases will use switches to exclude everything they
don't need this should fall into that category?

I'm obviously just speculating on that front having not used it there
myself.

Regards,
Donald Shepherd.

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

Re: [sqlite] Klocwork static analysis report

[Donald Shepherd]

On Wed, 6 Nov 2019 at 22:21, Richard Hipp  wrote:

> On 11/6/19, Raitses, Alex  wrote:
> > Hello,
> > Please find attached Klocwork static analysis report for “C source code
> as
> > an amalgamation”, version 3.30.1 (sqlite3.c).
> > Can you please review the report attached and update which bugs can be
> > fixed.
> >
>
> (1) This mailing list strips attachments.
>
> (2) Klocworks does not find "bugs".  Rather, it finds warnings.  The
> overwhelming majority of warnings found by klocworks are
> false-positives.  I do not recall an occasion where klockworks found
> an actual bug in SQLite.  Mostly, klocworks warning list are just a
> distraction for the developers that take their time away from finding
> real bugs.  Please ignore klocworks, at it is not a useful tool for
> finding errors in SQLite.


Having done a triage of Klocwork issues on an earlier amalgamation (to
assuage organisational worry about open source), by far the majority were
null pointer warnings on code paths that could never be null.  It did not
inspire confidence.

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

Re: [sqlite] DEF CON (wasL A license plate of NULL)

[Donald Shepherd]

The difference, in Apple's case _very_ specifically, is that they sign the
majority (all the rest?  Unsure) of their files so that this style of
exploit fails.  Basically SQLite is being used to bypass an existing
Apple-specific security control.  Outside of the iOS world the
applicability is a lot lower (if any) as you detail.

Regards,
Donald Shepherd.

On Tue, 13 Aug 2019 at 06:14, Keith Medcalf  wrote:

>
> On Monday, 12 August, 2019 11:09, Simon Slavin 
> wrote:
>
> >Some interesting things are emerging from this year's DEF CON.  This
> >one is related to an issue we've often discussed here.  I hope you'll
> >indulge this slightly off-charter post.
> >
> >
> https://www.iheart.com/content/2019-08-12-clever-vanity-license-plate-backfires-on-man-winds-up-with-tons-of-tickets/
> >
>
> Perhaps more apropos is the following story from the Register, also
> originating at DEF CON:
>
> https://www.theregister.co.uk/2019/08/10/memory_corruption_sqlite/
>
> Although I would point out that the root problem is that the attacker
> already has access to the file in order to change it, and therefore already
> has presence on the machine.  This is really no different that saying that
> "if an attacker has access the filesystem to replace the login program,
> that the login program can be compromised".  In other words, much ado about
> nothing.  Solve the root issue (the inappropriate granting of access by
> some other method) and the issue is resolved.
>
> This (seems to me) falls into the class of "if you have SYSTEM (root, for
> the *nix crowd) authority" you can "exploit vulnerability X" to obtain
> SYSTEM authority.  Why on earth would you bother?  Sure, you can exploit
> the vulnerability but it gains you nothing that you do not already have.
> Perhaps I am just lazy but I see no point in engaging in extra work for no
> advantage (then again, maybe that is just the Control Systems background
> rearing its head).
>
> As a side note, if one ALREADY HAS access to a machine hosting a database,
> and ALREADY HAS access to be able to make arbitrary changes to the database
> file, then the same exploit can be carried out on just about ANY system
> running just about ANY database imaginable .. it is trivial to create a
> view which replaces a table and have that view "do things" that are other
> than what was intended by the original designer, and have the fact that the
> table was replaced by a view remain "hidden" from routine uses.  And in any
> case, why bother with all the rigamarole.  You can already copy the
> contents of the database or make changes directly, so why go to such great
> lengths to be able to achieve indirectly that which you can already do
> directly?  (Not to mention that there are already capabilities to monitor
> for this sort of thing via the authorizer).
>
> Granted, it is not usual to "ship around" SQLServer or DB2 databases or
> have those host "application file formats" quite like it is with SQLite3
> databases, but then, files (no matter the type) originating from
> untrustworthy third-parties should be, well, untrusted.  The same applies
> to files which have been accessed (and perhaps modified) by untrustworthy
> parties.  The root problem is the prior untrustworthy access -- fix that
> and the problem goes away.
>
> Conversely there is a great trend these days to "execute" data --
> thankfully something which SQLite3 does not do.  An application might, but
> that is an application problem and not a data problem.
>
> The only interesting thing is CVE-2015-7036, but I don't know if that was
> so much an SQLite3 issue, as it was an issue in the use of the tokenizer by
> Apple.  In either case, Apple fixed their bugs and SQLite3 was hardened
> against some inappropriate (unintended by the application developer) uses
> of the fts3_tokenizer() function.
>
> https://www.sqlite.org/releaselog/3_28_0.html
> Item 10
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] Floating point literals

[Donald Shepherd]

Thanks, that's an interesting wrinkle that I don't remember being raised in
previous discussions but if known it should be mentioned up front as
many/most use affinities.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:58, Keith Medcalf  wrote:

> False, as it depends on the application of affinity.  If you are storing
> the floating point value in a column that does not have an affinity (ie, no
> conversions are performed), then it is stored exactly (except for NaN).
> Application of affinity (ie, real) will cause the -0.0 to be stored as the
> integer 0 and thus the sign will be lost on retrieval (as well as the
> conversion of NaN to NULL).
>
> >>> import apsw
> >>> import math
> >>> db = apsw.Connection('')
> >>> db.execute('create table x(x)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=-0.0)
>
> >>> db.execute('drop table x');
> >>> db.execute('create table x(x real)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=0.0)
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Donald Shepherd
> >Sent: Wednesday, 31 July, 2019 16:50
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Floating point literals
> >
> >That's not correct, verified several times by my own testing and
> >re-verified on the recent discussion about -0.0 on this mailing list.
> >
> >If you store -0.0 as a double, it will be stored as an integer as a
> >space-saving mechanism.  That integer is 0.  When you retrieve the
> >value as
> >a double it will be 0.0.  The sign has been stripped.
> >
> >Regards,
> >Donald Shepherd.
> >
> >On Thu, 1 Aug 2019 at 08:47, Keith Medcalf 
> >wrote:
> >
> >>
> >> The -0.0 is only for conversion to text.  Otherwise -0.0 is
> >preserved both
> >> on input and output (including input text conversions).  It is only
> >the
> >> conversion of -0.0 TO text that drops the sign.  NaN becomes a NULL
> >(ie, a
> >> double is not stored, a NULL value is stored).  Everything else is
> >> preserved including Inf and -Inf.
> >>
> >> --
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> >> >Sent: Wednesday, 31 July, 2019 15:34
> >> >To: sqlite-users@mailinglists.sqlite.org
> >> >Subject: Re: [sqlite] Floating point literals
> >> >
> >> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
> >> >> I understand you can *retrieve* a non-quantized value using
> >> >sqlite3_column_double(), but I don't see a way to set one without
> >> >having to printf() the floating point value.
> >> >
> >> >sqlite3_bind_double
> >> >
> >> >> Can this be done using sqlite3_bind_* interfaces, or do they
> >> >quantize as well?
> >> >
> >> >Yes. No; except that I seem to recall it mentioned that NaN is
> >> >treated as SQL NULL, and negative zero is normalized to positive
> >> >zero.
> >> >
> >> >> The goal is to copy the straight 8-byte (or precision-extended
> >4-
> >> >byte) IEEE value into the column into the database (where the
> >column
> >> >is defined as a FLOAT) without having to build a SQL statement
> >that
> >> >has an obscene

Re: [sqlite] Floating point literals

[Donald Shepherd]

That's not correct, verified several times by my own testing and
re-verified on the recent discussion about -0.0 on this mailing list.

If you store -0.0 as a double, it will be stored as an integer as a
space-saving mechanism.  That integer is 0.  When you retrieve the value as
a double it will be 0.0.  The sign has been stripped.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:47, Keith Medcalf  wrote:

>
> The -0.0 is only for conversion to text.  Otherwise -0.0 is preserved both
> on input and output (including input text conversions).  It is only the
> conversion of -0.0 TO text that drops the sign.  NaN becomes a NULL (ie, a
> double is not stored, a NULL value is stored).  Everything else is
> preserved including Inf and -Inf.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> >Sent: Wednesday, 31 July, 2019 15:34
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: Re: [sqlite] Floating point literals
> >
> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
> >> I understand you can *retrieve* a non-quantized value using
> >sqlite3_column_double(), but I don't see a way to set one without
> >having to printf() the floating point value.
> >
> >sqlite3_bind_double
> >
> >> Can this be done using sqlite3_bind_* interfaces, or do they
> >quantize as well?
> >
> >Yes. No; except that I seem to recall it mentioned that NaN is
> >treated as SQL NULL, and negative zero is normalized to positive
> >zero.
> >
> >> The goal is to copy the straight 8-byte (or precision-extended 4-
> >byte) IEEE value into the column into the database (where the column
> >is defined as a FLOAT) without having to build a SQL statement that
> >has an obscene number of digits in each floating point field.
> >
> >That's precisely what bound parameters and sqlite3_bind_X functions
> >are for.
> >--
> >Igor Tandetnik
> >
> >
> >___
> >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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Donald Shepherd]

Plus (as Igor noted) -0.0 returns as 0.0.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:41, Keith Medcalf  wrote:

>
> sqlite3_bind_double and sqlite3_column_double will round trip IEEE
> floating point values EXCEPT for NaN.  NaN will be stored as a NULL.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Eric Reischer
> >Sent: Wednesday, 31 July, 2019 15:15
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Floating point literals
> >
> >Is there a way to pass binary representations of floating point
> >numbers to
> >a SQL query?  If sqlite's internal representation of floating point
> >numbers is 8-byte IEEE doubles, it would be convenient to be able to
> >pass
> >the literal value of a float or double to the underlying SQL parser
> >without suffering the quantization that occurs with printf()'ing
> >floating
> >point values.
> >
> >One way I've accomplished this in the past with other interfaces is
> >to
> >interpret a hex value as a binary literal that can be interpreted as
> >a raw
> >4-byte or 8-byte IEEE floating-point value (either via a union or
> >other
> >compiler trick).  I understand you can *retrieve* a non-quantized
> >value
> >using sqlite3_column_double(), but I don't see a way to set one
> >without
> >having to printf() the floating point value.
> >
> >Can this be done using sqlite3_bind_* interfaces, or do they quantize
> >as
> >well?  The documentation isn't clear on this.  The goal is to copy
> >the
> >straight 8-byte (or precision-extended 4-byte) IEEE value into the
> >column
> >into the database (where the column is defined as a FLOAT) without
> >having
> >to build a SQL statement that has an obscene number of digits in each
> >floating point field.
> >
> >Thanks in advance.
> >___
> >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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug when creating a table via select?

[Donald Shepherd]

Somewhat bizarrely only "BLOB" affinity doesn't make it from the original
table to the new table when using the "select" syntax to create the new
table.  Even items with aliased affinities (VARTEXT, or something that
defaults to NUMERIC) comes across as the base affinity but at least have an
affinity.

This is simple to reproduce:

sqlite> .version
SQLite 3.29.0 2019-07-10 17:32:03
fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
zlib version 1.2.11
gcc-5.2.0
sqlite> create table x(a int, b text, c real, d blob, e vartext, f garbage);
sqlite> pragma table_info(x);
0|a|int|0||0
1|b|text|0||0
2|c|real|0||0
3|d|blob|0||0
4|e|vartext|0||0
5|f|garbage|0||0
sqlite> create table y as select * from x;
sqlite> pragma table_info(y);
0|a|INT|0||0
1|b|TEXT|0||0
2|c|REAL|0||0
3|d||0||0
4|e|TEXT|0||0
5|f|NUM|0||0
sqlite> select * from sqlite_master;
table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f
garbage)
table|y|y|3|CREATE TABLE y(
  a INT,
  b TEXT,
  c REAL,
  d,
  e TEXT,
  f NUM
)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Donald Shepherd]

On Fri., 14 Jun. 2019, 7:43 am Keith Medcalf,  wrote:

> On Thursday, 13 June, 2019 15:21, Donald Shepherd <
> donald.sheph...@gmail.com> wrote:
>
> >On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp  wrote:
>
> >> On 6/13/19, Donald Shepherd  wrote:
>
> >>> Given there's been numerous comments to the effect that SQLite
> >>> now supports -0.0 storing and retrieval other than printing,
> >>> I'm curious which version this was implemented in as I wouldn't
> >>> mind removing my custom code when we move to a SQLite version
> >>> with this improvement.
>
> >> SQLite version 3.0.0 from 2004-06-17.
>
> >When I reported that testing on 3.8.7.2 stored -0.0 but retrieved
> >0.0, your comment on this list was that "SQLite converts integer
> >floating point values to actual integers for storage (because that
> >takes up less space on disk) and then converts back to double upon
> >retrieval. That round-trip would change -0.0 into +0.0."
>
> >I was wondering when this changed?
>
> >That doesn't seem like "supporting -0.0 storing and retrieval" since
> >3.0.0.0 if it was previously being modified to another value during
> >storage, losing the sign in the process.
>
> Actually, it depends on whether the database column has real affinity or
> not.  If it has no affinity then the value is not "converted".
>
> If you declare the database column thusly:
>
> create table x(x); -- column x has no affinity
>
> then storing a double -0.0 in the column either via the statement INSERT
> INTO X VALUES (-0.0); or through binding a double containing -0.0 as a
> parameter to INSERT INTO X VALUES (?) results in the double -0.0 being
> stored in the database and retrieved with column_double against the query
> SELECT X FROM X;
>
> However, if you declare the database column thusly:
>
> create table x(x real); -- column x has real affinity
>
> then all manner of attempting to store -0.0, 0, 0.0 results in
> column_double retrieving 0.0 (stripping the sign) since what is actually
> stored is the two's complement integer 0 which does not have a sign (or
> rather, the sign is +ve).
>

Thanks, that's an interesting wrinkle I might be able to use.

Regards,
Donald Shepherd.

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Donald Shepherd]

On Fri, 14 Jun 2019 at 07:13, Simon Slavin  wrote:

> On 13 Jun 2019, at 10:01pm, Donald Shepherd 
> wrote:
>
> > Given there's been numerous comments to the effect that SQLite now
> supports
> > -0.0 storing and retrieval other than printing, I'm curious which version
> > this was implemented in as I wouldn't mind removing my custom code when
> we
> > move to a SQLite version with this improvement.
>
> For auditing and probity reasons, I would store the values twice: once in
> a BLOB and another time in a REAL.  When you need to look at the value you
> can choose which one to look at depending on why you're looking at it.
>

Thanks to the very useful data affinity rules and flexibility in SQLite we
store a REAL if SQLite supports the value and a BLOB if it doesn't (off the
top of my head, this is -0.0 and the NaN values - based off testing on
3.8.7.2 which we haven't revisited, hence my current curiousity), and the
data type informs us as to whether we need to do a memory conversion from
binary data to floating point on the way back out.

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Donald Shepherd]

On Fri, 14 Jun 2019 at 7:11 am, Richard Hipp  wrote:

> On 6/13/19, Donald Shepherd  wrote:
> >
> > Given there's been numerous comments to the effect that SQLite now
> supports
> > -0.0 storing and retrieval other than printing, I'm curious which version
> > this was implemented in as I wouldn't mind removing my custom code when
> we
> > move to a SQLite version with this improvement.
>
> SQLite version 3.0.0 from 2004-06-17.


When I reported that testing on 3.8.7.2 stored -0.0 but retrieved 0.0, your
comment on this list was that "SQLite converts integer floating point
values to actual integers for storage (because that takes up less space on
disk) and then converts back to double upon retrieval. That round-trip
would change -0.0 into +0.0."  I was wondering when this changed?

That doesn't seem like "supporting -0.0 storing and retrieval" since
3.0.0.0 if it was previously being modified to another value during
storage, losing the sign in the process.

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Donald Shepherd]

On Fri, 14 Jun 2019 at 3:28 am, Doug  wrote:

> It occurs to me that if there are sqlite applications today requiring the
> functionality of -0.0, they have implemented it so that it works for their
> application. Perhaps, they accomplished that by adding a flag bit or by
> some other means.
>
> So if you do nothing about -0.0, you will break no existing applications.
> Granted, you are not adhering to IEEE-745.
>
> However, ...
>
> If you can find anyone who has implemented such an application (using
> -0.0), you could find out how they implemented it. To make sqlite adhere to
> IEEE-745, generate an sqlite application note suggesting possible ways an
> application might implement the functionality.


My application uses it as we are required to maintain a bit perfect copy of
customer data. As such we had to store it as a BLOB with extra logic around
converting it back to a C double on retrieval.

Given there's been numerous comments to the effect that SQLite now supports
-0.0 storing and retrieval other than printing, I'm curious which version
this was implemented in as I wouldn't mind removing my custom code when we
move to a SQLite version with this improvement.

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

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Donald Shepherd]

On Thu, 13 Jun 2019 at 01:28, Richard Hipp  wrote:

> On 6/12/19, Jonathan Brandmeyer  wrote:
> > IMO, when acting as a storage engine, SQLite should be good to the last
> > bit.
>
> That is already the case, and has been for 17 years.  The question at
> hand is what should SQLite do when the application asks it to convert
> a -0.0 value into text.  It is only the binary-to-text conversion
> routine that is at question here.  If you are reading back your
> database content using sqlite3_column_double(), you get back
> bit-for-bit exactly what you put in.
>

Strictly speaking this isn't true - if using the C interface to pass in
-0.0 it used to get returned as 0.0 due to the optimization where
"SQLite converts
integer floating point values to actual integers for storage (because that
takes up less space on disk) and then converts back to double upon
retrieval. That round-trip would change -0.0 into +0.0." (2015) That may
have changed in recent years as I haven't explicitly tested it once I
worked around this behaviour by storing some double values as BLOBs.

Similarly passing in a bit representation of NaNs will not get you
bit-for-bit exactly what you put in, as SQLite discards it on the return
trip (also from 2015, though this code is still present so I assume the
behaviour is the same):

sqlite3_bind_double calls sqlite3VdbeMemSetDouble which has a specific
check against NaN.  My assumption is that this is what results in NaNs not
round tripping and instead coming back out as SQLITE_NULL:

SQLITE_PRIVATE void sqlite3VdbeMemSetDouble(Mem *pMem, double val){
  sqlite3VdbeMemSetNull(pMem);
  if( !sqlite3IsNaN(val) ){
pMem->u.r = val;
pMem->flags = MEM_Real;
  }
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Donald Shepherd]

https://stackoverflow.com/questions/7261707/how-to-avoid-precompiled-headers


On Fri., 21 Dec. 2018, 1:02 pm zydeholic  To Donald Shepard,
> Sorry, I'm on digest and am having to just respond to my own messages for
> now.  Just told it to get me off of digest.
> Regarding PCH, VC++ seems to be forcing me into that situation (yes, I'm
> relatively new to C++ and the ins and outs of VS).
> If I don't include the pch.h in my files, it won't compile and comes back
> and asks me if I forgot them.
>
> So, any pointers on how to slide through that problem would be great.
> Thanks for your and everyone else's time.
>
>
>
>   From: zydeholic 
>  To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
>  Sent: Thursday, December 20, 2018 5:57 PM
>  Subject: Re: Need setup code for VC++ 2017 that will ACTUALLY COMPILE
>
> Trying again, importing the sqlite3.h and sqlite3.c files directly into
> the subdirectory created by VC++, and running:
>
> SeverityCodeDescriptionProjectFileLineSuppression
> State
> ErrorC1853'Debug\sqlite_try_3.pch' precompiled header file is from
> a previous version of the compiler, or the precompiled header is C++ and
> you are using it from C (or vice versa)sqlite_try_3
> c:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.c1
>
>
>   From: zydeholic 
>  To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
>  Sent: Thursday, December 20, 2018 5:34 PM
>  Subject: Re: Need setup code for VC++ 2017 that will ACTUALLY COMPILE
>
> Ok, sorry for my thrashing.  I get frustrated when I don't know what's
> happening, even when I follow directions from a website.  And I tried
> several things and couldn't remember what errors I got from what venture.
>
>
>
> To Scott Doctor,
> I pulled the two files into a C++ project, deleted the .cpp file, and
> compiled.
> It came back and said my PCH compiled header was from a previous project,
> or something like that.  I tried to save the message, but it got lost in
> the hubbub.
> So, I deleted the PCH.CPP file that was in the project.  I just rebuilt
> it, and got this message:
>
> ErrorC1010unexpected end of file while looking for precompiled
> header. Did you forget to add '#include "pch.h"' to your source?
> sqlite_cc:\sqlite\source code\sqlite-amalgamation-326\sqlite3.c
> 220536
>
> even though I have #include "pch.h" in both of my .c and .h files.
>
> Any pointers appreciated.  Thanks.
>
>
>   From: zydeholic 
>  To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
>  Sent: Thursday, December 20, 2018 2:41 PM
>  Subject: Need setup code for VC++ 2017 that will ACTUALLY COMPILE
>
> Hello folks,
> I looked through the last few months of posts in the archive, and no
> subject lines seemed to cover this.
> I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried
> code from a couple of websites.
> NOTHING seems to compile all the way through.
>
> I'm using Visual Studio 2017 C++.  I'm on a WIndows 10 machine.  64bit,
> but compiling 32 bit.
> I tried Code::Blocks and got different, but equally incomplete compiles.
>
> I've included  in the includes.  I've downloaded sqlite3.c and .h.
> Nothing seems to work.  Is there ANYWHERE that offers a step-by-step
> solution to this that WILL COMPILE all the way through.
> I'm sure this has been covered before, but I did not see a way to search
> the entire archives.  Any help appreciated.
> Thanks.
>
>
>
>
>
>
>
> ___
> 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] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Donald Shepherd]

PCH/precompiled headers are not related to SQLite.  You can try disabling
them or looking in how to fix them separately.

Regards,
Donald Shepherd.

On Fri, 21 Dec 2018 at 12:34, zydeholic  wrote:

> Ok, sorry for my thrashing.  I get frustrated when I don't know what's
> happening, even when I follow directions from a website.  And I tried
> several things and couldn't remember what errors I got from what venture.
>
>
>
> To Scott Doctor,
> I pulled the two files into a C++ project, deleted the .cpp file, and
> compiled.
> It came back and said my PCH compiled header was from a previous project,
> or something like that.  I tried to save the message, but it got lost in
> the hubbub.
> So, I deleted the PCH.CPP file that was in the project.  I just rebuilt
> it, and got this message:
>
> ErrorC1010unexpected end of file while looking for precompiled
> header. Did you forget to add '#include "pch.h"' to your source?
> sqlite_cc:\sqlite\source code\sqlite-amalgamation-326\sqlite3.c
> 220536
>
> even though I have #include "pch.h" in both of my .c and .h files.
>
> Any pointers appreciated.  Thanks.
>
>
>   From: zydeholic 
>  To: "sqlite-users@mailinglists.sqlite.org" <
> sqlite-users@mailinglists.sqlite.org>
>  Sent: Thursday, December 20, 2018 2:41 PM
>  Subject: Need setup code for VC++ 2017 that will ACTUALLY COMPILE
>
> Hello folks,
> I looked through the last few months of posts in the archive, and no
> subject lines seemed to cover this.
> I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried
> code from a couple of websites.
> NOTHING seems to compile all the way through.
>
> I'm using Visual Studio 2017 C++.  I'm on a WIndows 10 machine.  64bit,
> but compiling 32 bit.
> I tried Code::Blocks and got different, but equally incomplete compiles.
>
> I've included  in the includes.  I've downloaded sqlite3.c and .h.
> Nothing seems to work.  Is there ANYWHERE that offers a step-by-step
> solution to this that WILL COMPILE all the way through.
> I'm sure this has been covered before, but I did not see a way to search
> the entire archives.  Any help appreciated.
> Thanks.
>
>
>
> ___
> 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] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Donald Shepherd]

You'll need to provide more information, speaking as someone who compiles
the amalgamation off the SQLite website with VC++ 2017 on Windows 10 with
no issues.  SQLite is C code, not C++ code, but VC++ detects that based off
the file extension and compiles it as such.

Trying to use whatever "CPPSqlite3.cpp" is and adding C++ headers is not
likely to get you anywhere unless you follow up with whoever created those
files for assistance.

Regards,
Donald Shepherd.

On Fri, 21 Dec 2018 at 09:41, zydeholic  wrote:

> Hello folks,
> I looked through the last few months of posts in the archive, and no
> subject lines seemed to cover this.
> I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried
> code from a couple of websites.
> NOTHING seems to compile all the way through.
>
> I'm using Visual Studio 2017 C++.  I'm on a WIndows 10 machine.  64bit,
> but compiling 32 bit.
> I tried Code::Blocks and got different, but equally incomplete compiles.
>
> I've included  in the includes.  I've downloaded sqlite3.c and .h.
> Nothing seems to work.  Is there ANYWHERE that offers a step-by-step
> solution to this that WILL COMPILE all the way through.
> I'm sure this has been covered before, but I did not see a way to search
> the entire archives.  Any help appreciated.
> Thanks.
> ___
> 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] Regarding CoC

[Donald Shepherd]

It's disappointing that some are using discussion on a (perfectly
acceptable) CoC to turn it into an excuse to post "jokes" about other
people's beliefs, but whatever floats your boat.

Regards,
Donald Shepherd.

On Tue, 23 Oct 2018 at 07:05, Charles Leifer  wrote:

> I dig the new CoC (not that anyone's counting).
>
> I'll share my comment from HN:
>
> If the code of conduct angers you, stop and think -- how did you feel one
> minute before you read the CoC? Is the problem really the CoC, or is it
> your collection of beliefs that is causing the problem? Furthermore, are
> you even affected? Do you contribute bug reports or patches? Follow the
> SQLite mailing list? Is anything here designed to prevent you from
> continuing to do so?
>
> SQLite's author is a spiritual guy. There's nothing wrong with him
> borrowing from spiritual sources to describe his ideal for how he wants the
> SQLite community to conduct itself.
>
> ...
>
> Also, how can you tell if someone's an atheist?
>
> Haha, don't worry friend, they'll be sure to tell you.
>
> Keep up the amazing work, SQLite team. The good tree bears the good fruit,
> and man SQLite is some good fruit.
>
> On Mon, Oct 22, 2018 at 2:49 PM Petite Abeille 
> wrote:
>
> >
> >
> > > On Oct 22, 2018, at 9:43 PM, Rob Dixon  wrote:
> > >
> > > weird and antagonistic
> >
> > Thank you for the kind words of support. Your wisdom and insights will be
> > missed. Farewell Rob Dixon. Godspeed.
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CASE and NULL

[Donald Shepherd]

On Thu, 5 Jul 2018 at 16:45, Simon Slavin  wrote:

> On 5 Jul 2018, at 7:30am, Clemens Ladisch  wrote:
>
> > The expression "x = x" will fail for NULL, but succeed for everything
> > else.  So you can use that to implement a "not-NULL ELSE"
>
> Wow.  That has to be the most counter-intuitive feature of SQLite.  I
> understand why it works, but I still don't like it.  Thanks for posting it.
>
> Hmm.  Yes, "x != x" works too.  I forgot to check "x IS NOT x".
>

I've always thought of that as an SQL thing rather than an SQLite thing,
because SQL Server and PostgreSQL and MySQL all do the same (or at least so
I'm lead to believe in the last two cases).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does WAL mode's SQLITE_BUSY special circumstances invoke the busy handler?

[Donald Shepherd]

The documentation on WAL databases includes a section with caveats re:
SQLITE_BUSY, included below.  Do these invoke the busy handler (if
configured) or just return SQLITE_BUSY immediately?  Making a valiant
attempt to read the code leads me to believe it returns immediately without
involving the busy handler.

"Cases where a query against a WAL-mode database can return SQLITE_BUSY
include the following:

- If another database connection has the database mode open in exclusive
locking mode then all queries against the database will return SQLITE_BUSY.
Both Chrome and Firefox open their database files in exclusive locking
mode, so attempts to read Chrome or Firefox databases while the
applications are running will run into this problem, for example.

- When the last connection to a particular database is closing, that
connection will acquire an exclusive lock for a short time while it cleans
up the WAL and shared-memory files. If a second database tries to open and
query the database while the first connection is still in the middle of its
cleanup process, the second connection might get an SQLITE_BUSY error.

- If the last connection to a database crashed, then the first new
connection to open the database will start a recovery process. An exclusive
lock is held during recovery. So if a third database connection tries to
jump in and query while the second connection is running recovery, the
third connection will get an SQLITE_BUSY error."
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Wed, 9 May 2018 at 11:13 Simon Slavin <slav...@bigfraud.org> wrote:

> On 8 May 2018, at 1:12pm, Simon Slavin <slav...@bigfraud.org> wrote:
>
> > You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN
> IMMEDIATE depending on which you want.  Then do all the backup stuff, then
> COMMIT or ROLLBACK without having changed anything.
>
> On 9 May 2018, at 1:50am, Donald Shepherd <donald.sheph...@gmail.com>
> wrote:
>
> > Having just tested that (for alternate purposes), it doesn't work.  The
> > sqlite3_backup_step calls following a "BEGIN IMMEDIATE" instruction
> > returned SQLITE_LOCKED.
>
> Oops.  I assume you did the lock using the same connetion that was calling
> the backup API.  In which case I apologise for the incorrect information.
>
> Simon.
>

I did mean to mention that I made sure I ran it on the same connection, so
yes.

To be honest I expected it to work too so I already had it on my list of
test cases.

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

Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Tue, 8 May 2018 at 22:12 Simon Slavin <slav...@bigfraud.org> wrote:

> On 8 May 2018, at 10:56am, R Smith <ryansmit...@gmail.com> wrote:
>
> > Thank you for clarifying - but it is still my understanding that the DB
> is not locked (if only in WAL mode), so the backup API, even with -1,
> either must ignore changes, or restart. My proposed flag is to lock rather
> than restart or ignore.
>
> You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN
> IMMEDIATE depending on which you want.  Then do all the backup stuff, then
> COMMIT or ROLLBACK without having changed anything.
>
> Simon.
>

Having just tested that (for alternate purposes), it doesn't work.  The
sqlite3_backup_step calls following a "BEGIN IMMEDIATE" instruction
returned SQLITE_LOCKED.

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

Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Tue, 8 May 2018 at 19:23 R Smith <ryansmit...@gmail.com> wrote:

>
> On 2018/05/08 9:37 AM, Donald Shepherd wrote:
> > I've long assumed that when using the online backup API on a SQLite
> > database, other processes will not be able to write to the source
> database
> > for the duration of the sqlite3_backup_step call.  However under some
> > testing I've been performing, I've found that this doesn't appear to be
> the
> > case.  Instead writes are prevented for a very small subset of that time,
> > if at all.
> >
> > Is that the expected behaviour, or is there a flaw in my testing
> > somewhere?  What defines the subset of time if it is correct?
> >
> > I'm testing a WAL database if that affects it.
>
> Expected and documented indeed. The basic rule that backup abides by is
> this:
>
> "Copy the database in a completely wholesome state to the destination."
> "If the data changes (and it can) then restart the backup process from
> start on the new data state."
>
> This works well for 90% of cases, but care is to be exercised for a
> really big + busy database (where writes are likely within the period of
> backup), the backup can infinitely restart. If this is the case, the
> controlling software needs an intervening step - and I don't think it
> can be done with an immediate transaction either, because the backup too
> will wait for that, but this is not tested by me, I might be wrong.
>
> It would actually be real nice if the backup API had a parameter or flag
> like "sqlite3_lockduringbackup".
>

Given your description, if I copy all pages at once (sqlite3_backup_step
with a page count of -1) I take it the later writes will not be reflected
in the resultant database and that's the trade off for not locking across
the full sqlite3_backup_step execution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database locking with online backup API

[Donald Shepherd]

I've long assumed that when using the online backup API on a SQLite
database, other processes will not be able to write to the source database
for the duration of the sqlite3_backup_step call.  However under some
testing I've been performing, I've found that this doesn't appear to be the
case.  Instead writes are prevented for a very small subset of that time,
if at all.

Is that the expected behaviour, or is there a flaw in my testing
somewhere?  What defines the subset of time if it is correct?

I'm testing a WAL database if that affects it.

Thank you,
Donald Shepherd.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Donald Shepherd]

100s - we use it as part of the definition of some dynamically created
tables that are dependent on the shape of the data we are receiving so can
end up with a large number of them.
On Sat, 17 Mar 2018 at 5:57 am, Doug Currie  wrote:

> 0
> ___
> 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] Tutorials, books, video about SQLite

[Donald Shepherd]

I've used "The Definitive Guide to SQLite" and have been pretty happy with
it.  I've probably learned more from this list and StackOverflow though.

On Thu, 10 Aug 2017 at 06:29 Lars Frederiksen  wrote:

> Thank you for all your advices concerning books about SQLite. I will have
> a closer look!
>
> Lars
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Nico Williams
> Sendt: 9. august 2017 22:19
> Til: SQLite mailing list
> Emne: Re: [sqlite] Tutorials, books, video about SQLite
>
> By far the best generic SQL book, IMO, is the O'Reilly "SQL" Pocket
> Guide.  It's very small and yet fairly comprehensive.  It covers Oracle,
> DB2, SQL Server, and MySQL.  But it's very general and brief, and
> everything it has to teach you is generally applicable to PostgreSQL and
> SQLite3.
>
> Nico
> --
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch for consideration: auto_vacuum slack.

[Donald Shepherd]

I use auto-vacuum in my application storage for work.  This was introduced
in about 2013.

The motivation was more political than anything though, as convincing some
as to the introduction requiring jumping through some pretty arbitrary
hoops.  Enabling auto-vacuum was one of those, to mitigate concerns that a
piece of software unused in the immediate development centre would chew up
resources needed by other more business critical processes.

On Tue, 14 Feb 2017 at 07:14 Richard Hipp  wrote:

> On 2/13/17, Scott Hess  wrote:
> >
> > Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
> > This setting allows client code to signal that auto_vacuum can leave
> pages
> > on the freelist until releasing them would allow a db size change.
>
> This makes me want to ask:  Is anybody still using auto_vacuum?  And
> if they are, should they be?
>
> Auto-vacuum was invented way back in 2004, for Motorola, who at the
> time was the worlds leading manufacturer of mobile phones.  This was
> during the heyday of flip-phones, before iPhone or Android.  The
> devices had very little flash memory - total storage capacity was
> measured in megabytes rather than gigabytes.  If storage ran low,
> Motorola wanted to be able to VACUUM the SQLite databases to make them
> smaller.  The problem there is VACUUM requires nearly 2x the size of
> the original database in temp storage, so if you are already low on
> space, VACUUM probably won't work.  The solution was auto-vacuum,
> which keeps the databases at near their minimum size at all times, at
> the cost of some extra database fragmentation, more I/O, and reduced
> performance.
>
> Fast foward 13 years (how long is that in internet-years?) and the
> situation has changed.  Does anybody really care anymore that a
> database file might have a few dozen pages on its freelist?  Or if
> they do care, does anybody lack the temp space sufficient to run a
> real VACUUM?  My impression is that these days people just want the
> database to run fast and with a minimum of I/O and are not overly
> concerned with a few extra freelist pages, which means that
> auto-vacuum should remain turned off.
>
> Scott:  The motivation for your patch seem to be to get auto-vacuum to
> run a little faster.  But if performance is your goal, why not just
> turn auto-vacuum off?  Or, failing that, set it to INCREMENTAL and
> then run "PRAGMA incremental_vacuum" when "PRAGMA freelist_count"
> reaches some threshold?
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Donald Shepherd]

Why don't you build it yourself as a 64 bit executable?

On Wed, 10 Aug 2016 at 00:31 Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> I would like to request a SQLite official 64-bit SQLite3.exe CLI (not DLL)
> be created.
>
> I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> reasoning for which why creating a 64-bit version is denied are "it does
> not make a real difference", "you can just use ram disks", etc., etc.
>
> Here is my plea...  I am using a set of complicated CTEs to crawl through
> a network (tree) to aggregate and calculate formulas.  I don't have
> exceptionally large datasets but my CTEs result in a ton of memory usage.
> The process works well from disk, in Windows, but using a smaller test
> sample I get about a 30% to 40% increase in processing time if I set the
> PRAGMA to temp_store = 2.  If I use a normal dataset, not a small test, I
> hit an approximate 2G limit and get a "out of memory" message, which I
> understand is due to SQLite3.exe being 32-bit.  I have found some 3rd party
> 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date
> and don't allow all functionality that I am using.  So, I do have a use
> case that requires 64-bit and I would see a significant increase in speed.
>
> As to RAM disks, I work in a corporate environment that locks down user
> rights which precludes me from distributing a tool that requires the
> creation of a tool that needs administrator rights.  I also, would like to
> avoid having to compile it myself; I am not a software engineer.
>
> Thanks for your consideration.
>
> Richard
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> 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] Locking semantics are broken?

[Donald Shepherd]

On Tue, 28 Jun 2016 at 19:17 Rowan Worth  wrote:

> On 28 June 2016 at 16:07, dandl  wrote:
>
> > > Do not use SQLite for concurrent access over a network connection.
> > Locking
> > > semantics are broken for most network filesystems, so you will have
> > > corruption issues that are no fault of SQLite.
> >
> > I have seen this comment made more than once on this list. Is there any
> > reliable evidence to support this for a Windows-based network?
> >
> > Disclosure: we wrote and maintain an ISAM-based multi-user database
> > product which relies on network locking. We have conducted exhaustive
> tests
> > over many years and in our opinion, locking and multi-user semantics on
> > Windows XP and later networks are reliable and free of errors, if
> performed
> > correctly by the client software.
> >
>
> I can't comment on windows sorry, but in-house we use sqlite databases
> shared between many unix clients via network file systems. Over the years
> we've used nfs3, nfs4, and lustre.
>
> These databases are subject to highly concurrent usage every working day.
> In the past two years we've had maybe one corruption issue which implicated
> the file system (client side logging suggested that four RESERVED locks
> were obtained concurrently).
>
>
> More commonly corruption has been the result of user/application
> misbehaviour:
>
> 1. Users copying databases while they're being updated (leaving them with a
> corrupt copy)
> 2. Users inadvertently symlinking/hardlinking database files
> 3. Our application inadvertently discarding sqlite's locks after backing up
> the database (thanks POSIX locking semantics)
>
>
> (3) was the main offender for us. Since figuring that out we've been left
> with a very robust environment - but not bullet proof as indicated above. I
> can imagine this kind of thing being sensitive to network/file system
> configuration, which is not easy to diagnose as a sysadmin let alone via
> email so in that sense I understand why network file systems are
> discouraged on the list (aside from the fact that sqlite and its
> database-level single user lock was not designed for networked concurrent
> usage).
>

We are Windows-based and the only corruptions we've seen is naive copying
of an in-use database (#1 in your list) plus bugs such as crashes when
transferring the database between servers resulting in an incomplete copy.
Neither have been the responsibility of SQLite and we've generally
mitigated both by tool and usage improvement as best we can.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest way to add many simple rows to a table?

[Donald Shepherd]

On Fri, 27 May 2016 at 00:16 Eric Grange  wrote:

> Hi,
>
> I am looking for the fastest way to insert many rows to a simple table.
>
> By "simple" I mean a relation table (with just a couple integer/key fields)
> or even a single-column (temp table used for filtering as an alternative to
> a "in" literal), but I want to insert hundreds of thousandths of them as
> fast as possible.
>
> The fastest I have found so far is to use an insert from a select with
> json_each() used to provide the data.
> Using multiple "insert into", even within a prepared statement within a
> transaction is quite slower.
>
> The json_each approach would satisfy my need, but I am curious is there is
> another approach that could be suggested? (without involving a custom
> function)
>
> Eric
>

http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
has
a useful comparison of various techniques.  I used its comparison when
developing our product using SQLite with large numbers of records getting
inserted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Is Not Support Uint64?

[Donald Shepherd]

It doesn't support unsigned integers natively, but most of the time it will
effectively upconvert to a larger signed integer.  In the case where it's
greater than the maximum int8 it obviously cannot upconvert, so uses a
floating point to approximate.

https://www.sqlite.org/datatype3.html

If you want a uint8, you can do this manually by converting the value to
int8 for SQLite insertion and then converting it again on the way out, i.e.
remembering that the signed bit is actually part of the number.
Alternatively storing it as text will be lossless, if less efficient.


On Mon, 16 May 2016 at 14:16 sanhua.zh  wrote:

> I insert a value 18446744072708321492 as a integer into the table.
> In C code,sqlite3_column_type tells me is a type of 2, which is float
> point value.
> And I usesqlite3_column_double to get it. It returns18446744072708321280,
> which is already lost theaccuracy.
>
>
> Is SQLite not support Uint64 value, which bigger than the max of Int64?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Article about pointer abuse in SQLite

[Donald Shepherd]

On Wed, 23 Mar 2016 12:59 am Adam Devita  wrote:

>
> This discussion on the nature of undefined behaviour code is
> interesting.  I don't know the reasoning, but it seems that VS6 often
> initialized things to 0xcd in debug mode and (usually) had memory
> uninitialized to 0x00 when complied in Release (perhaps 0x00 just
> happens to be what was on the stack or heap).  I presume this wasn't
> just to make people suffer  when things don't work the same in debug
> vs release mode.
>

It's not uncommon for compilers to initialise variables to definitely bad
values in debug mode to help find these kinds of bugs.

However if you were getting 00s in VC++ you were getting lucky and would
probably continue to get lucky until an unexpected stack allocation changed
the usual location, after which all bets are off (speaking from VC++
experience and VC++6 specifically).

>

[sqlite] Changing the default page_size in 3.12.0

[Donald Shepherd]

On Sat, 5 Mar 2016 at 09:19 Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/03/16 07:48, Richard Hipp wrote:
> > The tip of trunk (3.12.0 alpha) changes the default page size for
> > new database file from 1024 to 4096 bytes. ... This seems like a
> > potentially disruptive change, so I want to give you, the user
> > community, plenty of time to consider the consequences and
> > potentially talk me out of it.
>
> Can I talk you into it instead :-)  My standard boilerplate for new
> databases is to set the page size to 4,096 bytes, and to turn on WAL.
>
> Roger
>

We've headed the same way.  4,096 significantly reduced the size of our
databases when we switched to it some time ago.

[sqlite] Performance comparison between SQLite and SQL Server?

[Donald Shepherd]

They're intended for fundamentally different uses.  It's like asking what's
more energy efficient for cooking dinner - a wok or an oven.

On Mon, 15 Feb 2016 at 14:21  wrote:

> Hi,
>
> I am just curious whether there is a performance comparison between SQLite
> and SQL Server? Surely SQL Server will perform better on huge database with
> thousands of tables(more than 10GB size). But whether SQLite will perform
> better on smaller database such as one database with one table that is less
> than 1GB?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Donald Shepherd]

On Tue, 3 Mar 2015 at 03:01 Simon Slavin  wrote:

>
> On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:
>
> > Every database I?ve every used starts SQL parameter indexes from 1.  I?m
> not sure it is part of the SQL standard, but it is more or less the defacto
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I hope the SQLite library does something appropriate if you try to bind to
> parameter 0.  It would seem to be an excellent avenue for a security bug if
> nothing tests for it.
>

SQLITE_RANGE <https://www.sqlite.org/rescode.html#range> is returned if the
parameter index is out of range.

https://www.sqlite.org/c3ref/bind_blob.html

I can't confirm that 100% off the top of my head but I'm uncoordinated
enough to repeatedly confuse the bind and column value API calls and use
0-based indices for both and haven't noticed any really untoward behaviour
(beyond my code not working and requiring fixing).

Regards,
Donald Shepherd.

[sqlite] Backup API and WAL

[Donald Shepherd]

On Mon Feb 23 2015 at 1:41:31 PM Simon Slavin  wrote:

>
> On 22 Feb 2015, at 11:15pm, Donald Shepherd 
> wrote:
>
> > If I use the backup API to create a copy of an SQLite database that uses
> > Write-Ahead Logging, will the resulting copy reflect the contents of both
> > the base database file and the -wal file?
>
> Until a transaction is COMMITted, it's not part of the database.  Because
> the program could decide to ROLLBACK instead.
>

My understanding of WAL is that even if a transaction is COMMITted it will
sit in the -wal file until a checkpoint occurs, i.e. by default when the
-wal file reaches 4 MB.  As a result that transaction will only be present
in the copied SQLite database if the backup API takes into account the
contents of both files.

Further reading led me to a reference stating that under the covers the
backup API is treated the same as any other reader, so I take that to mean
that it looks at both files and compiles the copy from the two.

[sqlite] Backup API and WAL

[Donald Shepherd]

If I use the backup API to create a copy of an SQLite database that uses
Write-Ahead Logging, will the resulting copy reflect the contents of both
the base database file and the -wal file?

Regards,
Donald Shepherd.

[sqlite] Getting the current value of busy_timeout

[Donald Shepherd]

Is there a way to get (not set) the current value of busy_timeout when
using an SQLite version older than 3.7.15 and the addition of "PRAGMA
busy_timeout;"?

http://www.sqlite.org/releaselog/3_7_15.html

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

On Tue Feb 03 2015 at 12:23:29 PM James K. Lowden 
wrote:

> On Sun, 1 Feb 2015 02:13:15 +0100
> Stephan Beal  wrote:
>
> > On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin 
> > wrote:
> >
> > > So, having established that NaN and -0 do not make the round trip
> > > from a C variable through a database and back into a C variable ...
> > > at least I think we have ...
> >
> > If you're assuming C89 (which sqlite3 is, by and large), it's a
> > technical fact that there is no standard representation of either
> > negative zero, NaN, or Infinity. Any such support would be
> > non-C-standard.
>
> As you know, C quite intentionally does not define bit-patterns for any
> numeric type.  It doesn't specify endianism, twos-complement negatives,
> or IEEE floating point format.  It doesn't even specify the number of
> bits used.  That is part of what makes C code portable.
>
> IEEE floating point is implemented in hardware.  The format has been
> universally adopted for floating-point units for 25 years or so.  There
> are processors that lack floating point support, but I've never heard
> of one that provides floating point in some other format.
>
> The compiler is free to decide what the bit pattern for
>
> double x = 1.2;
>
> would be.  As a practical matter, normally the obvious choice would be
> to implement IEEE format and rely the processor's floating point
> support.  But that's not an obligation; it's implementation-defined.
>
> SQLite's documentation states that REAL is "stored as an 8-byte IEEE
> floating point number".  I suspect that's actually an incidental
> by-product of being compiled exclusively on modern machines, all of
> which use IEEE floating point (if any).  I suspect a more accurate (but
> obscure) description would be "stored in the format of a C double as
> implemented by the compiler that compiled SQLite (normally IEEE 754)".
>
> If the following are not true, they should be, and we should understand
> why not:
>
> 1.  For storage and retrieval as REAL with binary bindings, SQLite
> simply copies the bits to and from the database.  There's no reason the
> 64 bits presented to the database can't be kept and returned on
> demand.
>
> 2.  For interpretation -- sorting, SQL computation, user-defined
> functions -- SQLite again relies on the compiler and perhaps math
> routines in the standard library.  Want to know how rounding works, or
> how your single-precision variable is widened to double-precision?
> RTFM!
>
> I mentioned collation before, but I don't think SQLite need have any
> rule about e.g. "how to sort NaN".  The processor surely has a rule for
> comparing NaNs.  The compiler will cause the processor to make the
> comparison and report the result, which SQLite can use without further
> assessment.
>
> It was alleged earlier that denormal numbers, -0, and NaN cannot be
> bound and stored to a column with REAL affinity.  If that's true,
> SQLite is interpreting the values or applying rules that afaik aren't
> explicitly stated and aren't strictly necessary.
>

sqlite3_bind_double calls sqlite3VdbeMemSetDouble which has a specific
check against NaN.  My assumption is that this is what results in NaNs not
round tripping and instead coming back out as SQLITE_NULL:

SQLITE_PRIVATE void sqlite3VdbeMemSetDouble(Mem *pMem, double val){
  sqlite3VdbeMemSetNull(pMem);
  if( !sqlite3IsNaN(val) ){
pMem->u.r = val;
pMem->flags = MEM_Real;
  }
}

Richard answered on -0.0, i.e. it's a side effect of an optimization:

> SQLite converts integer floating point values to actual
> integers for storage (because that takes up less space on disk) and
> then converts back to double upon retrieval. That round-trip would
> change -0.0 into +0.0.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and
a value of 0.

On Thu Jan 29 2015 at 8:56:35 PM RSmith  wrote:

>
> On 2015/01/29 05:05, James K. Lowden wrote:
> > There's no reason to think, if the data are provided in binary form,
> that they won't be returned in the identical form absent an
> > explicit conversion. If that's not so, I'd sure like to know why. I'm
> faintly surprised NaNs can't be stored, too. Why should
> > SQLlite interpret them if they're bound to a double?
>
> Indeed, which is what all the posts have been saying more or less in terms
> of round-tripping all but NaNs.
>
> In the case of NaN though, there are two defined NaNs, namely qNaN and
> sNan which both means the same but the sNaN will cause an
> exception even at hardware level by merely passing through any register,
> which I believe is its intended purpose. This means that in
> order for software to work correctly, it should never let an sNaN pass
> through untouched, it should produce an error so all parties
> are savvy to the wrongness that just occured (Even if the software in use
> is not specifically checking for NaN, an sNaN should still
> cause an exception from lower down).
>
> qNaN should pass through and round-trip same as any other float. I'm not
> sure how SQLite handles either of these NaNs, but am now
> quite interested to know.
>
> ___
> 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] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

I'm still not convinced whether it's the behaviour causing my problem, but
it does look like negative zero is another special case:

SQLite version 3.8.7.2 2014-11-18 20:57:56
Enter ".help" for usage hints.
sqlite> create table datatable2 (doublevalue real);
sqlite> insert into datatable2 values(-0.0);
sqlite> select * from datatable2;
0.0

When inserting it through the API I get the same results, i.e. the signed
bit looks like it gets stripped so technically the value retrieved is not
the bitwise equivalent of what's written in even though it is the logical
equivalent (-0.0 == 0.0).

On Thu Jan 29 2015 at 10:13:55 AM Donald Shepherd <donald.sheph...@gmail.com>
wrote:

> Thanks for the reassurances.  I have a case where differences in doubles
> would explain what I'm seeing but I have no evidence that it is the case
> (evidence compilation is still underway), hence my attempt to plumb the
> depths of the list's knowledge to see if there was any known edge cases to
> be aware of (other than the NaN one I'd already run into). :)
>
> On Thu Jan 29 2015 at 10:10:35 AM Simon Slavin <slav...@bigfraud.org>
> wrote:
>
>>
>> On 28 Jan 2015, at 10:47pm, Donald Shepherd <donald.sheph...@gmail.com>
>> wrote:
>>
>> > This is a bit of a speculative question related to a problem I'm having
>> -
>> > are there legal values of a C++ double that would get truncated when
>> > written into and read from an SQLite database?
>>
>> In theory there should be no problem here.
>>
>> C doubles have 15 to 16 digits of precision.
>>
>> In SQLite databases, numbers which can't be stored as integers are stored
>> as IEEE 754-2008 64-bit floating point numbers, sometimes known as
>> 'binary64'.  These give 15 to 17 digits of precision.
>>
>> My understanding is that it is possible to store every distinct C double
>> value as a distinct binary64 value.
>>
>> If it's the conversion that worries you, you can read the SQLite source
>> code to find the programming used to encode and decode numbers into this
>> format.  I am not competent to read that source code and tell you
>> definitely that it works for all C double values.
>>
>> Simon.
>> ___
>> 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] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

Thanks for the reassurances.  I have a case where differences in doubles
would explain what I'm seeing but I have no evidence that it is the case
(evidence compilation is still underway), hence my attempt to plumb the
depths of the list's knowledge to see if there was any known edge cases to
be aware of (other than the NaN one I'd already run into). :)

On Thu Jan 29 2015 at 10:10:35 AM Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 28 Jan 2015, at 10:47pm, Donald Shepherd <donald.sheph...@gmail.com>
> wrote:
>
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> In theory there should be no problem here.
>
> C doubles have 15 to 16 digits of precision.
>
> In SQLite databases, numbers which can't be stored as integers are stored
> as IEEE 754-2008 64-bit floating point numbers, sometimes known as
> 'binary64'.  These give 15 to 17 digits of precision.
>
> My understanding is that it is possible to store every distinct C double
> value as a distinct binary64 value.
>
> If it's the conversion that worries you, you can read the SQLite source
> code to find the programming used to encode and decode numbers into this
> format.  I am not competent to read that source code and tell you
> definitely that it works for all C double values.
>
> Simon.
> ___
> 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] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.

On Thu Jan 29 2015 at 9:57:14 AM Igor Tandetnik <i...@tandetnik.org> wrote:

> On 1/28/2015 5:47 PM, Donald Shepherd wrote:
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> Written into and read from how, exactly? Do they, say, round-trip
> through a string representation at any point?
>
> I'm pretty sure that, if you put a value into a column with correct
> affinity using sqlite3_bind_double, you'd get the exact same value out
> using sqlite3_column_double (signalling NaN possibly excepted).
> --
> 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

[sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

This is a bit of a speculative question related to a problem I'm having -
are there legal values of a C++ double that would get truncated when
written into and read from an SQLite database?  The column is specified as
having REAL affinity though I gather that shouldn't matter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

[Donald Shepherd]

Let's see if I remember my notes from work ok at home:

- Units are seconds.
- IIRC user time is time spent in SQLite code, sys time is time spent in
system (OS) calls.  Both can vary from run to run and (at least in my
testing) sys time tends to vary based off system usage.

If you want the best "real" time results, a later version of the command
line also includes a "real" time that represents actual time elapsed but it
also has updated query planning IIRC.

On Mon Dec 15 2014 at 8:12:27 PM Simon Slavin  wrote:

> Okay.  I used '.timer on' in the shell tool.  SQLite 3.7.13, if it
> matters.  Here are two sample lines I got in response to different INSERT
> ... SELECT commands:
>
> CPU Time: user 880.710398 sys 353.260288
>
> CPU Time: user 5073.001124 sys 11609.266484
>
> The two commands were issued one after another on a computer which was
> otherwise idle.
>
> Question 1: What are the units ?
>
> Question 2: I would have expected consistency in that user time was always
> greater than system time.  Or perhaps the other way around.  Why is a
> different one greater for the two examples ?
>
> Simon.
> ___
> 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] 50% faster than 3.7.17

[Donald Shepherd]

Are any of these improvements specifically in the area of the online backup
API, or are they more in the general running of SQLite?

On 20 September 2014 11:14, Richard Hipp  wrote:

> The latest SQLite 3.8.7 alpha version (available on the download page
> http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release
> from 16 months ago.  That is to say, it does 50% more work using the same
> number of CPU cycles.
>
> This performance gain is over and above the query planner improvements that
> have also been made.  We are constantly looking for new ways to run queries
> and adding those ways into the query planner.  For example, in the previous
> release, we added a new way to evaluate IN operators with non-constant
> right-hand-sides that was reported on this mailing list to make some
> queries run 5 times faster.
>
> The 50% faster number above is not about better query plans.  This is 50%
> faster at the low-level grunt work of moving bits on and off disk and
> search b-trees.  We have achieved this by incorporating hundreds of
> micro-optimizations.  Each micro-optimization might improve the performance
> by as little as 0.05%.  If we get one that improves performance by 0.25%,
> that is considered a huge win.  Each of these optimizations is unmeasurable
> on a real-world system (we have to use cachegrind to get repeatable
> run-times) but if you do enough of them, they add up.
>
> A full 10% of the performance gain has come since the previous release.
> There have been a lot of changes.  All our tests pass, and we still have
> 100% branch test coverage, so we are confident that we didn't break too
> much.  But your testing is an important part of our quality process.
> Please download a source archive or a DLL and give the latest alpha a
> whirl, and let us know if you encounter any problems.
>
> P.S.:  Measurements were done using the "speedtest1 --size 5" workload on
> Ubuntu 10.13 and gcc 4.8.1 with -Os.  YMMV.  Version 3.7.17 requires
> 1432835574 CPU cycles and the 3.8.7 alpha requires just 953861485 CPU
> cycles, as measured by cachegrind.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Handling Timezones

[Donald Shepherd]

Actually what Rob and I were pointing out was that the chances of showing
up in Taiwan when you're in Tennessee is actually quite high in a corporate
environment - he gets moved from the UK to Germany, I get moved from
Australia to Phoenix, AZ, my wife gets moved from Australia to Switzerland
and that's just a random sample.  It's not uncommon at all for large
companies to route traffic through a single gateway, and as a result using
geolocation to detect timezones is very prone to problems if people want to
access a site from inside a large company, whereas using client-based logic
avoids this.


On 2 August 2014 09:27, Stephen Chrzanowski  wrote:

> I understand that with routing and such, you can end up outside where you
> really are (With my IP, I'm shown just outside of Toronto when I'm actually
> two hours out), but the chances of showing up in Taiwan when you're in
> Tennessee is doubtful.  The point of the matter is that you'll get real
> time data in regards to where the user might be located and from there,
> you'll get a general idea on when a good time to call is.
>
> There are also bounce VPNs which would make it look like I'm in Texas when
> I'm in Toronto.  Depending on how I route my traffic here, I can be
> anywhere in the world.
>
>
> On Fri, Aug 1, 2014 at 5:51 AM, Will Fong  wrote:
>
> > Hi everyone,
> >
> > Wow, such great responses! So my background is not with this type of
> > development, so I never really thought about these types of problems
> > before. Thank you all for the help!
> >
> > -will
> > ___
> > 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] Handling Timezones

[Donald Shepherd]

Speaking as someone who's work routes their internet traffic through a
gateway in Phoenix, AZ despite being based in Australia, guessing time
zones based off IP location is a lot more prone to error than detecting it
based off the client.


On 31 July 2014 17:54, Stephen Chrzanowski  wrote:

> Looking back at the clarification of what the OP wanted to do, I've got
> this to recommend;
>
> If your users are talking to your server via the internet and not via a VPN
> connection, instead of relying on what time zone your users browser is
> giving you, look at what IP they're calling in from and do an IP to
> geographical look up to find out where they are.  From there you'd be able
> to catalog a 'best time of contact' based on what the Geolocation service
> gives you.
>
> Doing a quick google search on "ip to geo" I found these two:
> http://www.iplocation.net/ and http://www.geoiptool.com/
>
> By the looks of it, for a modest yearly fee, you'd be able to download a
> database of IPs to locations and you'd be able to get time zone information
> right from there.
> ___
> 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] Handling Timezones

[Donald Shepherd]

You can represent time zones as integers by using minutes.  Examples: +600
for AEST, +330 for IST, -480 for PST.  No string manipulation is needed,
but depending on what or if you're using libraries, you may need extra
steps in there for convert those values into a representation supported by
the library.


On 30 July 2014 10:47, Will Fong  wrote:

> Hi,
>
> On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin 
> wrote:
> > Store their timezones in the format "[+-]HH:MM" and apply them by
> appending that text to any dates they provide.  See the "Time Strings"
> section of
>
> I can store each user's timezone setting as "[+-]HH:MM".  But I can
> only apply that to GMT values. So when I'm reading from the database,
> it's a trivial operation.
>
> However, if a user specifies a datetime, I would have to provide the
> reverse of that value to convert the user time into GMT. It would be a
> bit easier (yet still messy) if the timezone was just an integer, then
> I could just "*-1". But the ":MM" seems to make it a messy string
> operation.
>
> Is this the only option? It seems like there would have been a
> "better" way to handle this.
>
> Thanks,
> -will
> ___
> 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] Variable number of parameters in a prepared statement's IN clause

[Donald Shepherd]

Thanks all for the suggestions.  They're a great help.


On 21 July 2014 11:06, Keith Medcalf  wrote:

>
>
> >Is it possible to have a variable number of parameters in an IN clause in
> >a prepared statement, i.e. "select * from Table where Col1 in
> >(?,?,?,...);"?
>
> >Or do I need a constant number of parameters in there to be able to re-
> >use the prepared statement?
>
> The closest solution would be to create a table/temporary table and
> populate it with your value list.  You can then use a statement like:
>
> SELECT  FROM  WHERE  IN 
>
> This assumes that your  has only one column, it is the correct
> type.
>
> If your list is only integers take a look at test_intarray.c in the full
> source.  It implements a virtual table that maps from a C array to a
> virtual table.  With minimal changes this would work for doubles as well.
>  Since it would be your own custom extension virtual table, you could make
> it work with whatever weird data structure and data you required.
>
>
>
>
> ___
> 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] Variable number of parameters in a prepared statement's IN clause

[Donald Shepherd]

Is it possible to have a variable number of parameters in an IN clause in a
prepared statement, i.e. "select * from Table where Col1 in (?,?,?,...);"?
Or do I need a constant number of parameters in there to be able to re-use
the prepared statement?

Thanks for any help,
Donald Shepherd.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using WHERE clauses with REAL data

[Donald Shepherd]

It's a nice idea but that's just some sample values generated by an
emulator.  I've compromised and am using round() to limit it to a few
digits after the decimal when doing the comparison.


On 4 March 2014 21:27, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 4 Mar 2014, at 4:14am, Donald Shepherd <donald.sheph...@gmail.com>
> wrote:
>
> > It appears that using equals on floating point (REAL) data in WHERE
> clauses
> > doesn't necessarily work, presumably because of rounding errors - see
> below
> > for an example.  Is this the case?  Do I need to use BETWEEN instead of =
> > as I expect to be the case?
>
> A glance at those numbers suggests that their units are precise to 1/100th
> of a unit.  The proper reply to your question is that you should be storing
> and manipulating those values multiplied by 100, using integer arithmetic
> and INTEGER columns in your database.  This will mean you never have to
> worry about rounding or slack.
>
> Simon.
> ___
> 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] Using WHERE clauses with REAL data

[Donald Shepherd]

Thanks for the quick response.  That was what I was expecting.

Regards,
Donald.


On 4 March 2014 15:20, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Mar 3, 2014 at 11:14 PM, Donald Shepherd
> <donald.sheph...@gmail.com>wrote:
>
> > It appears that using equals on floating point (REAL) data in WHERE
> clauses
> > doesn't necessarily work, presumably because of rounding errors - see
> below
> > for an example.  Is this the case?  Do I need to use BETWEEN instead of =
> > as I expect to be the case?
> >
>
> Never (well, almost never) use == with floating point numbers.  This rule
> applies to all systems, not just SQLite.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Using WHERE clauses with REAL data

[Donald Shepherd]

It appears that using equals on floating point (REAL) data in WHERE clauses
doesn't necessarily work, presumably because of rounding errors - see below
for an example.  Is this the case?  Do I need to use BETWEEN instead of =
as I expect to be the case?

Thanks,
Donald.

sqlite> select * from table2 where Id='METER' and Type=0 and Pointer=15 and
"Date"=30414.0 and "Time"=141954.0;

RowId|Id|Type|Pointer|Date|Time|3|4|etc

31|*METER*|*0*|*15*|*30414.0*|*141954.0*|*3.4504768372*
|4.5594277954|5.6707629395|6.7896185303|7.888664856|8
.8961853027|9.0122888184|10.118855591|11.225422363|12.341525879|13.448092651|14.564196167|15.670762939|1.0
32|*METER*|*0*|*15*|*30414.0*|*141954.0*|*3.4504768372*
|4.5594277954|5.6707629395|6.7896185303|7.888664856|8
.8961853027|9.0122888184|10.118855591|11.225422363|12.341525879|13.448092651|14.564196167|15.670762939|1.0
sqlite> select * from table2 where Id='METER' and Type=0 and Pointer=15 and
"Date"=30414.0 and "Time"=141954.0 and "3"=3.4504768372;


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