Re: [sqlite] Deterministic random sampling via SELECT

[Doug Currie]

On Thu, Nov 7, 2019 at 4:23 PM Richard Damon 
wrote:

>
> One thought would be to generate a ‘hash’ from part of the record, maybe
> the record ID, and select records based on that value. The simplest would
> be something like id%100 == 0 would get you 1% of the records. That
> admittedly isn’t that random.
>
> Put the ID through a linear congruential generator, something like
>
> mod(a * Id + b, c) % 100 == 0
>
> And you will pretty well scramble the selection
>
>
Yes, and if a, b, and c come from a randomization table, they can be
modified to obtain a different pseudo-random set.

e
___
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?

[Doug Currie]

On Fri, Jun 14, 2019 at 7:16 AM R Smith  wrote:

>
> What I was objecting to, is claiming (in service of suggesting the
> use-case for -0.0), [...]
>
> I'll be happy to eat my words if someone can produce a mathematical
> paper that argued for the inclusion of -0.0 in IEEE754 to serve a
> mathematical concept. It's a fault, not a feature.
>

David Goldberg's classic paper "What Every Computer Scientist Should Know
About Floating-Point Arithmetic" has a section on this topic, 2.2.3 Slgned
Zero, with a few use cases.

W. Kahan's early papers on standardizing floating point uses the term
"affine mode" to describe when signed zeros and infinities matter (as
opposed to "projective mode").
E.g.,
ON A PROPOSED FLOATING-POINT STANDARD
W. Kahan
University of California, Berkeley
J. Palmer
INTEL Corporation, Aloha, Oregon
October 1, 1979

e
___
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?

[Doug Currie]

>
> I do not know if this is the result case in any of the programming
> languages, but in Mathematical terms that is just not true.
>

The related IEEE 754 rules are described here:
https://en.wikipedia.org/wiki/Signed_zero

e
___
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?

[Doug Currie]

>
>
> Except by the rules of IEEE (as I understand them)
>
> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"
>

Except that 0.0 is also an approximation to zero, not "true zero."

Consider that 1/-0.0 is -inf whereas 1/0.0 is +int

e
___
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?

[Doug Currie]

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

Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor

[Doug Currie]

On Fri, Mar 2, 2018 at 2:46 PM, Obrien, John J  wrote:

> [...]
>
> To summarize, my question is regarding what direction I should ask the
> hardware vendor to take. Does it make sense for them to spend time
> optimizing the SAM4S for SQLite or should we consider another approach?
>

John, try web searching for "flash key value store." Look for one that
supports SD Card or eMMC from ARM Cortex-M4 (or -A5).

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Doug Currie]

Cezary is correct,

NULL is not equal to NULL, though NULL is NULL.

sqlite> select NULL IS NULL;

1

sqlite> select NULL = NULL;


sqlite>


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

Re: [sqlite] Simple web query tool

[Doug Currie]

On Wed, Feb 1, 2017 at 11:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.


While I wouldn't call it extremely simple, the Fossil source has a
"translate" tool that supports embedding SQLite queries and HTML generation
inline with C source code for a cgi program.

Description from: http://fossil-scm.org/index.html/artifact/33b65539a12abd07

** SYNOPSIS:
**
** Input lines that begin with the "@" character are translated into
** either cgi_printf() statements or string literals and the
** translated code is written on standard output.
**
** The problem this program is attempt to solve is as follows:  When
** writing CGI programs in C, we typically want to output a lot of HTML
** text to standard output.  In pure C code, this involves doing a
** printf() with a big string containing all that text.  But we have
** to insert special codes (ex: \n and \") for many common characters,
** which interferes with the readability of the HTML.
**
** This tool allows us to put raw HTML, without the special codes, in
** the middle of a C program.  This program then translates the text
** into standard C by inserting all necessary backslashes and other
** punctuation.
**
** Enhancement #1:
**
** If the last non-whitespace character prior to the first "@" of a
** @-block is "=" or "," then the @-block is a string literal initializer
** rather than text that is to be output via cgi_printf().  Render it
** as such.
**
** Enhancement #2:
**
** Comments of the form:  "|* @-comment: CC" (where "|" is really "/")
** cause CC to become a comment character for the @-substitution.
** Typical values for CC are "--" (for SQL text) or "#" (for Tcl script)
** or "//" (for C++ code).  Lines of subsequent @-blocks that begin with
** CC are omitted from the output.

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Doug Currie]

On Sat, Jan 7, 2017 at 9:27 AM, Gan Uesli Starling  wrote:

> So I'm trying to accumulate data for state/prov inside of USA, Canada and
> Mexico, and country for the rest of the world.
>
> Since country and state from the same update are factors in incrementing
> each of four tables,
>

You should ask yourself why you have four tables instead of one table. This
(poor database normalization) is the root cause of the problem.

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

Re: [sqlite] Datatype for prices (1,500)

[Doug Currie]

On Wed, Nov 30, 2016 at 5:25 PM, Nico Williams 
wrote:

> On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote:
> >
> > [...] every RDMS "should" implement Decimal Floating Point.
>
> You could argue that every programming language needs that.  What makes
> SQL more special than the others in this regard?
>

The SQL standard (at least SQL92) specifies an exact numeric type that uses
decimal precision for rounding. Most other programming languages don't.

There are reasons we use IEEE754: it's fixed-sized, it's built-in pretty
> much everywhere, and it's usually implemented in hardware, so it's fast.


The IEEE754-2008 standard includes both base 2 (binary) and base 10
(decimal) numbers. E.g., decimal64, decimal128, as well as binary64
(typical C double) and binary32 (typical C float). There are few hardware
implementations of decimal floats; modern processors that have it include
IBM System Z and POWER6.

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

Re: [sqlite] Bug: SQLite's include guards are reserved identifiers

[Doug Currie]

On Sat, Jul 9, 2016 at 12:05 PM, Keith Medcalf  wrote:

>
> [...] Most API headers do the same thing.  Even the standard library does
> it, in most compilers.  [...]


Sure, that's why they're reserved! So user code and the C compiler's
library implementation don't clash. The C standard reserves those
identifiers for itself (or future versions of itself) to use.

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

[sqlite] File Locking Status

[Doug Currie]

> Is it possible to tell if SQLite has a database file locked?  Not through
> OS tools, but from System.Data.SQlite?


 If you can execute

BEGIN EXCLUSIVE TRANSACTION

and get back SQLITE_OK

then there were no locks on the database.

Of course you will then need to ROLLBACK.

Note that this will only work well if you have no busy timeout or bust
handler set.

e

[sqlite] Are BLOB stored inline with the rest of the record fields?

[Doug Currie]

On Wed, Mar 2, 2016 at 4:42 PM, R Smith  wrote:
>
>
> Personally, unless your space is constrained, I would simply save the
> numbers as strings, perhaps Hex or BCD with leading chars and convert as
> needed. This would sort correctly without tricks and not do much worse for
> space. (Base64 would be even better space-wise but won't sort correct).
>

There is an encoding defined in RFC 4648 "Base-N Encodings" that does
preserve sort order; it is called Base 32 Encoding with Extended Hex
Alphabet. I would think the Base64 alphabet could be rearranged to have the
same property.

e

[sqlite] insert in sqlite (returning value for inserted row)

[Doug Currie]

Suraj,

Don't use the same database connection in multiple threads. Each thread
should use its own connection. Then last insert rowid is predictable.

e


On Wed, Apr 1, 2015 at 1:10 PM, Kumar Suraj  wrote:

> Hi Richard.. this wont work for me due to following reason.
>
> If a separate thread performs a new INSERT
>  on the same database connection
> while the sqlite3_last_insert_rowid()
>  function is running
> and thus changes the last insert rowid
> , then the value
> returned by sqlite3_last_insert_rowid()
>  is unpredictable and
> might not equal either the old or the new last insert rowid
> .
>
> On Tue, Mar 31, 2015 at 6:23 PM, Richard Hipp  wrote:
>
> > https://www.sqlite.org/c3ref/last_insert_rowid.html
> >
> > On Tue, Mar 31, 2015 at 9:19 PM, Kumar Suraj 
> wrote:
> >
> > > Hi
> > >
> > > I am using sqlite C interface for inserting data in the table. The
> > primary
> > > key is a 64 bit integer which i need to auto-increment and get
> populated
> > > automatically as we do not provide that value in insert statement. Is
> > there
> > > a way i can get the autoincremented value for each row inserted when
> > ever i
> > > execute my insert.
> > >
> > > -Suraj
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] unreached code in sqlite3.c?

[Doug Currie]

On Thu, Feb 12, 2015 at 1:35 PM, R.Smith  wrote:

>
> Now one could argue the warning should not be issued for it, or some
> warnings are fine as information. Personally I prefer zero unneeded
> warnings/clutter but that's just my pedantism.
>

My pedantism is to prefer the warning since it might catch cases where the
code inadvertently neglects to define TERM_VNULL at all.

It's easy enough to fix if you want 0 to be a valid value for TERM_VNULL:

#if TERM_VNULL
 if( pTerm->wtFlags & TERM_VNULL ) continue;
#endif

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

Re: [sqlite] unreached code in sqlite3.c?

[Doug Currie]

Well, if TERM_VNULL is 0, then the code is truly unreachable, so I wouldn't
call it a compiler bug.

e


On Thu, Feb 12, 2015 at 9:26 AM, Richard Hipp  wrote:

> Dan is right.  I think I'd calling this a clang bug.
> On Feb 12, 2015 9:06 AM, "Dan Kennedy"  wrote:
>
> > On 02/12/2015 09:02 PM, Jens Miltner wrote:
> >
> >> Hi,
> >>
> >> I'm getting the following two warnings when compiling sqlite3.c with the
> >> latest clang tools:
> >>
> >>  sqlite3.c:116769:39: warning: code will never be executed
> >>> [-Wunreachable-code]
> >>>  if( pTerm->wtFlags & TERM_VNULL ) continue;
> >>>^~~~
> >>> sqlite3.c:116716:39: warning: code will never be executed
> >>> [-Wunreachable-code]
> >>>  if( pTerm->wtFlags & TERM_VNULL ) continue;
> >>>^~~~
> >>> 2 warnings generated.
> >>>
> >>>  (This is for SQLite version 3.8.8.2).
> >>
> >>
> >>  From the code, I don't immediately see why the compiler would think
> this
> >> code will never be executed, so I thought I'd bring it up with you guys.
> >>
> >
> >
> > Unless you have defined SQLITE_ENABLE_STAT4 (or STAT3), TERM_VNULL is
> > defined as 0:
> >
> >   http://www.sqlite.org/src/artifact/d3633e9b59210324?ln=273-277
> >
> > Dan.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

Thank you, Richard. It works for me now.

e


On Mon, Feb 9, 2015 at 1:30 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 2/7/15, Doug Currie <doug.cur...@gmail.com> wrote:
> > In response to this SO question:
> >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> >
> > I tried to formulate a query without temp tables using an ordinary CTE,
> but
> > received an error "misuse of aggregate: sum()".
> >
>
> Possibly fixed on trunk now.  Please test and confirm.
>
> --
> 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] ordinary CTE containing sum()

[Doug Currie]

For those interested in the initial "misuse of aggregate" issue of this
thread, there is now a ticket:

http://www.sqlite.org/src/tktview?name=2f7170d73b

e


On Mon, Feb 9, 2015 at 9:19 AM, Keith Medcalf  wrote:

>
> Not exactly since aggregates are implemented as functions.
>
> In the case of sum(a + b + c) you have the overhead of one aggregate setup
> call, one call per row (after the three additions are done) and one
> finalizer call to retrieve the aggregate and release the context.
>
> In the case of sum(a) + sum(b) + sum(c) you have three initializer calls
> being made to set up three different aggregate contexts.  Then on each row
> you call the increment function three times for three different contexts,
> then after the aggregate is complete you make three calls to finalize the
> three aggregates and release their contexts, then add up the sum.
>
> The number of additions is the same, but the latter (multiplicity of
> aggregate contexts) adds significantly to the size of the code path.
>
> This may be on the order of only a couple thousand instructions per row,
> but it is a couple *more* thousands of instructions per row than the former
> sum(a + b + c) case.
>
> This will not be significant where you are dealing with 10 rows, but when
> you have thousands or millions of rows it is quite significant.  It will
> also use more energy and concomitantly increase the temperature of the CPU,
> thus contributing to global warming.
>
> ---
> Theory is when you know everything but nothing works.  Practice is when
> everything works but no one knows why.  Sometimes theory and practice are
> combined:  nothing works and no one knows why.
>
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of R.Smith
> >Sent: Monday, 9 February, 2015 04:51
> >To: sqlite-users@sqlite.org
> >Subject: Re: [sqlite] ordinary CTE containing sum()
> >
> >
> >On 2/8/2015 10:23 PM, James K. Lowden wrote:
> >>
> >> I have a couple of efficiency questions for those who know:
> >>
> >> 1.  Is the left-join on a CTE apt to be more effecient than the version
> >> that uses a correlated subquery in the SELECT clause?
> >
> >I'm guessing it matters in some DBs but from testing it seems much the
> >same in SQLite.
> >
> >>
> >> 2.  Is there any performance difference between
> >>
> >>  sum(ca1 +ca2 + exam)
> >> and
> >>  sum(ca1) + sum(ca2) + sum(exam)
> >>
> >> I would expect the left join is faster than a correlated subquery, and
> >> that fewer aggregates is better than more.
> >
> >Now this is easy to check but the answer is simple too - I know it looks
> >in SQL terms like something more complicated is taking place, but in
> >reality it's all the same, consider that it is just like asking which of
> >these are faster:
> >
> >(1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
> >--  OR --
> >(1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)
> >
> >Count the plusses, they are the same. The difference to the
> >processor/engine (ultimately) of your two queries are merely order of
> >adding, but no difference to addition operations or amount of function
> >calls. (Unless "adding" by itself is a significantly different/slower
> >operation when done inside the aggregate function than outside it, but
> >that would fit somewhere between devious and insane).
> >
> >IF you could somehow get rid of the loop or change the compound
> >iteration count it might have a viable effect, but that is not the case
> >here.
> >
> >
> >___
> >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] ordinary CTE containing sum()

[Doug Currie]

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

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

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

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

why doesn't this work? ...

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

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

[sqlite] ordinary CTE containing sum()

[Doug Currie]

In response to this SO question:

http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table

I tried to formulate a query without temp tables using an ordinary CTE, but
received an error "misuse of aggregate: sum()".

This works:

sqlite> with tt (S_id, total) as
   ...>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   ...> from t group by S_id)
   ...> select * from tt ;
1|143
2|198
3|165
4|198
5|183

but with the same CTE this fails, even though the select statement after
the CTE works with an equivalent temporary table:

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

Any suggestions?

Thanks.

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

Re: [sqlite] decoding a bitmask

[Doug Currie]

>
> The query is on a visits table from a google chrome history database. The
> query seems to work OK if a single bit is set, but fails (a blank string is
> returned) when multiple bits are set. Any ideas why?
>

It's because none of the WHEN 0x... cases, except 0xC0...,  have multiple
bits set. The approach you've chosen requires enumerating all the possible
combinations (all 2^5 of them in this case). You are better off with one of
the other suggested approaches by Richard Hipp or RSmith.

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

>
> > Here's an analogy: a sequence of decimal digits is unsigned; it only
> > becomes negative when you put a "-" in front of it.
> >
> > Why shouldn't hex work the same way? (to eliminate the discombobulating
> > segment)
> >
>
> Because then you would not be able to write (in hex) a 64-bit bitmap that
> had the most significant bit set.
>

Ah, you want convenience! You could write -0x8000, but that
does become a hassle.

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

> Why are hex literals interpreted as signed at all? You could simply
> > consider all hex literals as unsigned values. If you need a negative
> value,
> > prefix it with the - operator, e.g., -0x77.
> >
> > With this approach (a) there is no discombobulating segment, (b) all 64
> bit
> > bit-masks are supported, and (c) the gradual overflow to double makes
> > sense.
>
>
> Because SQLite only supports signed integers internally.  If hex literals
> must be unsigned, that limits them to 63 bits.
>

Here's an analogy: a sequence of decimal digits is unsigned; it only
becomes negative when you put a "-" in front of it.

Why shouldn't hex work the same way? (to eliminate the discombobulating
segment)

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

> There is this range of negative
> values smack in the middle of an otherwise uniformly increasing sequence of
> positive numbers.  That negative range seems discombobulating.


Why are hex literals interpreted as signed at all? You could simply
consider all hex literals as unsigned values. If you need a negative value,
prefix it with the - operator, e.g., -0x77.

With this approach (a) there is no discombobulating segment, (b) all 64 bit
bit-masks are supported, and (c) the gradual overflow to double makes sense.

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

Re: [sqlite] Concrete example of corruption

[Doug Currie]

On Dec 5, 2013, at 8:55 PM, Warren Young  wrote:

> On 12/5/2013 17:00, Scott Robison wrote:
>> Might there be a way to implement a custom VFS for Mac to deal with this?
> 
> Wouldn't it be a lot simpler to just put the DB file into a Mac package (i.e. 
> directory) so the associated WAL and whatever other files get created in the 
> package, too?

Yes, I was wondering the same thing...

https://developer.apple.com/library/mac/documentation/CoreFoundation/Conceptual/CFBundles/DocumentPackages/DocumentPackages.html

e


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

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Doug Currie]

On Nov 24, 2013, at 6:47 AM, Alek Paunov  wrote:
> 
> BTW, I see the term "deterministic" in the SQL99 BNFs:
> …
> but different in PostgreSQL ("immutable", "stable", etc):


There is value in compatibility, but those adjectives are awful. In computer 
science we have referential transparency

http://en.wikipedia.org/wiki/Referential_transparency_(computer_science)

and pure functions

http://en.wikipedia.org/wiki/Pure_function

e


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

Re: [sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

[Doug Currie]

Paul Bainter wrote:

> >
> > Not sure what happened to this post previously, so I guess I'll try it
> > again with some additional information
>

GMail considered these messages spam for some reason. Check your spam
folder.

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

Re: [sqlite] Hints for the query planner

[Doug Currie]

On Sep 10, 2013, at 6:23 PM, Scott Robison  wrote:

> I think I prefer something along the lines of "unlikely" or "likely". The
> problem with a term like "selective" (at least in my brain) is that it
> doesn't imply (for the single argument version) in what way it is being
> selective.
> 
> If a negative form of the magic function is used ("unlikely", "seldom",
> etc) I would suggest considering inverting the optional second parameter.
> In other words, 0.05 would become 0.95. In my opinion, that reads better:
> "unlikely(COLUMN LIKE '%pattern%', 0.95)" reads "it is unlikely the
> expression will be true 95% of the time".
> 
> In like fashion, a positive form of the magic function would keep the
> current meaning of the optional second parameter.

This is the best suggestion. The pseudo-function names do not change the 
meaning of the query, and they are more clear with regard to the optional 
numeric argument.

e

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

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Doug Currie]

> I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain 
compatibility with some legacy systems. It is long past time that it should 
have changed to use sqlite3_prepare_v2().

Running Richard's example with sqlite3_prepare_v2 changed to sqlite3_prepare 
gives this output:

first step returns 101
second step returns 1
error message = SQL logic error or missing database
finalize returns 19

This doesn't match the output of lsqlite3 because the wrapper tries to be 
helpful, and when the second step fails, it calls sqlite_reset to get the error 
code. The equivalent C code is:


#include 
#include "sqlite3.h"
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(":memory:", );
  sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);

  //sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, , 0);
  sqlite3_prepare(db, "insert into t(x) values(?)", -1, , 0);

  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf("first step returns %d\n", rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf("second step returns %d\n", rc);
  printf("error message = %s\n", sqlite3_errmsg(db));
  

  if (rc == SQLITE_ERROR)
  {
rc = sqlite3_reset(pStmt);
printf("second step's reset returns %d\n", rc);
printf("error message = %s\n", sqlite3_errmsg(db));
  }

  rc = sqlite3_finalize(pStmt);
  printf("finalize returns %d\n", rc);
  sqlite3_close(db);
  return 0;
}



That prints

first step returns 101
second step returns 1
error message = SQL logic error or missing database
second step's reset returns 19
error message = column x is not unique
finalize returns 0

which matches the output from the Lua script.

The next version of lsqlite3 will use the recommended sqlite3_prepare_v2() API.

e

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Doug Currie]

On Mar 7, 2013, at 11:07 AM, Ryan Johnson  wrote:
> 
> That does leave the question of what to do with cast ('1.0' as integer), 
> though. Without the prefix-based matching that would now return NULL rather 
> than 1, even though cast(1.0 as integer) would still return 1. Then again, 
> disallowing all floats might be better than the current practice of returning 
> 1 from a cast of both '1e-10' and '1e10' (the real->integer casts do the 
> right thing, as does assignment to a column with integer affinity).

Would

  cast(cast(x as real) as integer)

do what you want?

e

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

Re: [sqlite] Mac development question

[Doug Currie]

On Oct 23, 2012, at 4:58 PM, Igor Korot  wrote:

> 1. I know on Mac I need to build an application bundle. Where do I
> store the .db file relative to the bundle?
> Inside it? Home directory? Somewhere on the hard drive? What is the
> usual place for it?

If the database is read-only, you can sore it inside the bundle.

If it is application configuration, you should store it in the application's 
directory in the user's ~/Library/Application Support/

If the database is a document, you should store it wherever the use directs via 
a file dialog.

SQLite itself doesn't care where the database is stored as long as the 
directory it is in is read/write.

> 2. When I done on Windows I should be able to just copy the file and
> drop it on the Mac HD, right?

Yes.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 4:51 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 6 Aug 2012, at 7:48pm, Doug Currie <doug.cur...@gmail.com> wrote:
> 
>> ~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
>> 2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
> 
> I think this copy has been installed by something else.

Yes, I installed it.

>  I don't think it comes with Apple's distribution of Mountain Lion.  If this 
> is the file which is being executed by default (in other words, if that's the 
> file reported by the command 'which sqlite3' on your system) then this may be 
> the cause of your problem.

I don't have a problem, Tobias does, and I suspect it is because the ML version 
of sqlite3 in /usr/bin (2012-04-03) predates the 3.7.12 release, and has the 
bug Dan recalls.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 8:26 AM, Simon Slavin  wrote:

> So either Apple has made a change between versions, or we have different 
> paths.

I use fully qualified pathnames here:

~ e$ /usr/bin/sqlite3  :memory: 'SELECT sqlite_source_id()'
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af
~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004

I never had a pre-release OSX ML installed. I did update /usr/local/bin/sqlite3 
from sqlite.org.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 3:32 PM, Tobias Giesen  wrote:

> Apparently Apple prevents starting other versions of it and redirects 
> everything to
> their current version in /usr/bin.

On ML here I can launch my version in /user/local/bin just fine.

e$ which sqlite3
/usr/local/bin/sqlite3
e$ sqlite3
SQLite version 3.7.12 2012-05-14 01:41:23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sqlite_source_id();
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
sqlite> .exit
e$ uname -mprsv
Darwin 12.0.0 Darwin Kernel Version 12.0.0: Sun Jun 24 23:00:16 PDT 2012; 
root:xnu-2050.7.9~1/RELEASE_X86_64 x86_64 i386

e


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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 2:33 PM, Dan Kennedy  wrote:

> There was a problem similar to your description at one point, but
> it should have been fixed before the 3.7.12 release. What do you
> get from the shell command "SELECT sqlite_source_id();" on
> Mountain Lion?

e$ /usr/bin/sqlite3
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT sqlite_source_id();
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af


e


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

[sqlite] leap seconds

[Doug Currie]

The SQLite3 date & time functions are designed assuming

> […] that every day is exactly 86400 seconds in duration.

Before I start implementing TAI (or GPS time) to/from UTC translator plugin, 
has anyone already done this?

Why? In a device that logs data with sub-second resolution, in my case a 
medical device, timestamps need to account for leap seconds, and support 
translation to/from UTC (and local time) for human readability.

-- e

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

Re: [sqlite] how to build sqlite4 (four)?

[Doug Currie]

On Jun 28, 2012, at 4:05 PM, Nico Williams wrote:

> It's also possibly a good idea to just not have autoincrement.  Let
> the application implement it, no?  After all, it can, including via
> triggers.

Or with PostgreSQL-style sequences

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

(and maybe SERIAL 
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL 
)

e

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

Re: [sqlite] VERY weird rounding error

[Doug Currie]

On Jun 17, 2012, at 12:23 PM, Keith Medcalf wrote:

> SQLITE_SIGNIFICANT_DIGITS defaults to 14, but you can override it.  No matter 
> what is requested, the maximum number of significant digits is limited to the 
> specification, and rounding is applied to the remaining bits of the 
> significand, to round to the specified number of significant digits. 

FYI, the venerable approach:

http://kurtstephens.com/files/p372-steele.pdf

ftp://ftp.ccs.neu.edu/pub/people/will/retrospective.pdf

http://www.cs.washington.edu/education/courses/cse590p/590k_02au/print-fp.pdf

http://www.cesura17.net/~will/Professional/Research/Papers/howtoread.pdf

e

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

Re: [sqlite] Using SQL or C for data analysis?

[Doug Currie]

On Mar 27, 2012, at 3:46 PM, Larry Brasfield wrote:

> A DBMS is a good way to keep your raw data.  But I highly doubt that a 
> majority of your analysis algorithms are going to be expressible in SQL 
> without going way beyond the intended purpose of the language.  You will 
> either find yourself limiting the analyses to what is convenient to express 
> in SQL, or you will spend much more time writing queries than you would spend 
> describing your data processing in a form more suited to functions.  

Yes

> […]  I expect you would find a signal processing library, such as can be 
> found in Matlab, Octave, or Scilab, to be a much better start than what you 
> might write in SQL in reasonable time.

Or use a Statistical Computing language and environment such as R with SQLite

http://www.r-project.org/

http://cran.r-project.org/web/packages/RSQLite/index.html


e

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

Re: [sqlite] SQLite

[Doug Currie]

On Nov 9, 2011, at 11:39 PM, Bhautik Kothadia wrote:

> Is there any Operating System Required for that?

See: http://www.sqlite.org/custombuild.html

especially section 5.0 Porting SQLite To A New Operating System

> If not then How much Memory is required?

See: http://www.sqlite.org/malloc.html

e

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

Re: [sqlite] SQLite

[Doug Currie]

The PIC32MX664F064L has
 64 KiB Program Memory Size
 32 KiB RAM

SQLite as it presently stands will not fit within these constraints.

e

On Nov 9, 2011, at 7:47 AM, Parthiv Shah wrote:

> Respected Sir,
> 
> We want to use DB SQLite in our product.
> 
> We are using PIC32MX664F064L microcontroller from microchip.
> 
> Is it possible to embedded SQLite into it?
> 
> Do we need any Operating system for SQLite ?
> 
> Product is data acquisition system. 
> 
> For data storage we are using SD Card.
> 
> We are using FAT32 file system.
> 
> Please guide us how we can test it?
> 
> For more information about us, please visit our website:
> www.promptsoftech.com
> 
> Best Regards
> Parthiv Shah
> 
> Prompt Softech
> Ahmedabad
> India
> 
> 
> ___
> 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] triggers : NEW keyword with multiple tables

[Doug Currie]

On Oct 25, 2011, at 10:59 AM, Sébastien Escudier wrote:

> CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
> BEGIN
> INSERT INTO table1(type) VALUES(NEW.table1.type);
> INSERT INTO table2(type) VALUES(NEW.table2.type);
> END;
> 
> ...
> 
> Why this syntax does not work anymore ?

You haven't given the view explicit column names, and the ones SQLite3 invents 
are arbitrary; try this instead:

CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type as 
table2_type FROM 

…

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1_type);
INSERT INTO table2(type) VALUES(NEW.table2_type);
END;


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

Re: [sqlite] Using modifiers in julianday function

[Doug Currie]

On Oct 24, 2011, at 11:07 AM, Dilip Ranganathan wrote:

> But as you all know, this doesn't work:
> 
> select datetime(time) from table where time >=
> julianday(datetime(max(time)),'-2 hour','localtime') order by time desc

Try replacing datetime(max(time)) with (select datetime(max(time)) from table)

as in 

sqlite> select datetime(time) from table
   ...> where time >=
   ...> julianday((select datetime(max(time)) from t),'-2 hour','localtime') 
order by time desc;
2011-10-24 15:43:45
2011-10-24 15:43:39
sqlite> 


e

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

Re: [sqlite] MC/DC coverage explained wrong in the home page?

[Doug Currie]

On Sep 23, 2011, at 9:17 PM, Richard Hipp wrote:

> paper above completely ignores this issue.  It is as if the authors had
> never heard of short-circuit evaluation.  Or, perhaps they are familiar with
> the problem but could not reach agreement on its solution so simply didn't
> bring it up.

Another way to look at a short-circuit evaluation is that it does not represent 
a Boolean expression at all. It represents a control statement.

A && B => { if A then return B else return true }

A || B => { if A then return true else return B }

e

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

Re: [sqlite] SQLite + unicode

[Doug Currie]

On Aug 10, 2011, at 12:39 PM, NOCaut wrote:

> I work in VS2008 c++ 
> i create data base my.db and wont use U N I C O D E function from this DLL 
> i find class or unit for connect to my base from VS2008
> http://sqlite.org/download.html - this link help me?
> 
> you understand me?

No, but maybe these links will help...

http://www.sqlite.org/faq.html#q18

http://old.nabble.com/enable-ICU-in-SQLite-on-windows-platform-td27371403.html

http://www.urban-eye.com/pagesqliteicu.html

http://site.icu-project.org/

e

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

Re: [sqlite] c-api

[Doug Currie]

On Jul 27, 2011, at 9:22 AM, Baruch Burstein wrote:

> Is there an easier way to get a single value (for instance "select
> last_insert_rowid();" ) then prepare -> step -> column -> finalize?

http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

e


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

Re: [sqlite] OSX path

[Doug Currie]

On Jun 17, 2011, at 2:56 PM, john darnell wrote:

> I am attempting to open an SQLite database on the Mac (OSX Snow Leopard) and 
> am getting an error.  This is the code I am using:
> 
>   char  DBEnginePath[1000];
> 
>   strcpy(DBEnginePath, "Macintosh HD:Applications:Adobe InDesign 
> CS5:Plug-Ins:WPC_ID:IndexData.db");
>   fprintf(stderr, "%s\n", DBEnginePath);  
>   //  Sends correct path to stderr for verification.
>   Result = sqlite3_open_v2(DBEnginePath, _ptr, SQLITE_OPEN_READONLY, 
> NULL);  //  Errors out here.

Your path has colons instead of slashes for separators.

Open a Terminal window, and drag the database file into the window. The 
terminal.app will display the path name on the command line. You can copy and 
paste from there. It's probably something like:

/Applications/Adobe\ InDesign\ CS5/Plug-Ins/WPC_ID/IndexData.db

e

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

Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6

[Doug Currie]

On May 26, 2011, at 2:54 AM, Jan Hudec wrote:
> Gotcha! No, it's not. -1-x is equivalent, but -x-1 is not:
> 
>sqlite> select -1-(1<<63), -(1<<63)-1;
>9223372036854775807|9.22337203685478e+18
> 
> Besides my point was not that it's not possible, but that it would
> be more readable with dedicated operator.

Yes.

The fact that a negative number (-1) minus a large positive number (1 << 63) 
results in a positive number does not seem to be in concert with the goal of 
handling arithmetic overflows sensibly. 

This is especially egregious in the second case where the result of negating a 
large positive number and subtracting one is positive AND REAL (double float). 

Ideally SQLite would guarantee one of (in my order of preference):

1) Integer operations that overflow 64 bits behave as wrapped twos complement, 
i.e., they return the low 64 bits of the infinite precision twos complement 
integer result

2) Integer operations that overflow 64 bits result in floating point values 
that approximate the result with the precision of IEEE double 

3) Integer operations that overflow have no guaranteed result

I think option 2 is what SQLite is supposed to do (per the release notes), but 
is failing in both cases of this example.

e

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

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

[Doug Currie]

On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote:

> I have tried the latest Explorer and it gets a syntax error on STDEV.  
> However, that function works in Eclipse just fine, to take the standard 
> deviation of a column (like min, max, avg).  Is there a workaround or 
> other fix available?  

See http://www.sqlite.org/contrib  extension-functions.c 

e

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

Re: [sqlite] Backup-restore behaviour

[Doug Currie]

On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote:

> It clarified the situation, that is backup-restore seems to be the best 
> choice:) Just one more question. As you put backup-restore is based upon data 
> pages (that could be binary a format I guess) not on plain SQL/data records. 
> After all: Is the data page/backup format platform indenpendent? For instance 
> can I restore a database on Windows from a backup created on a Linux box?

If your Linux is on ARM, you should pay attention to the 
SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows 
(or x86 Linux for that matter).

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 29, 2010, at 9:37 AM, Rick Regan wrote:

> "For IEEE 754 double-precision numbers and 64-bit integers roughly
> 99.4% of all numbers can be processed efficiently. The remaining 0.6% are
> rejected and need to be printed by a slower complete algorithm."
> 
>  Hmmm. What's involved in the "slower complete algorithm" -- bignums?!

That quote refers to Grisu3 which produces the shortest string or else 
indicates failure, in which case Dragon4 or something similar using bignums is 
needed. But for Grisu2, no bignums are needed, and a correct string is always 
returned, though it is not the shortest string in 0.4% of cases. The Grisu2 
strings do "round trip" correctly, which I think is where this thread started.

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 6:19 PM, Rick Regan wrote:

> On Sun, Nov 28, 2010 at 5:52 PM, Doug Currie <doug.cur...@gmail.com> wrote:
> 
>> 
>> There is a new publication on this subject that may be of interest to those
>> looking at providing solutions:
>> 
>> http://portal.acm.org/citation.cfm?id=1806623
>> 
>> It (Grisu2) works without bignums if you are willing to settle for the
>> shortest string in 99.8% of cases, and an accurate but not shortest string
>> in the remaining cases.
>> 
> 
> Thanks for the reference. I wonder how it compares to David Gay's dtoa.c?

The paper compares the performance of sprintf from glibc 2.11 and Grisu. I 
don't know if glibc sprintf is based on Gay's code; at one point I thought it 
was, but I cannot find an authoritative reference.

In any case, the Grisus benchmarked about 5x to 10x faster than sprintf on 
average for random double inputs. The code should be simpler than dtoa.c since 
there are no bignums involved, and fewer special cases.

Anyone still reading this thread might be interested in the directed rounding 
mode subtleties discovered by Rick Regan: 
http://www.exploringbinary.com/incorrect-directed-conversions-in-david-gays-strtod/

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 5:37 PM, Rick Regan wrote:

> On Sun, Nov 28, 2010 at 4:01 PM, Doug Currie <doug.cur...@gmail.com> wrote:
> 
>> On Nov 28, 2010, at 11:18 AM, Rick Regan wrote:
>> 
>>> Michael,
>>> Thanks for the very thorough analysis.
>> 
>> This is a difficult problem; fortunately it was solved 20 years ago...
>> 
>> Well, it's not solved on Windows. :)

There is a new publication on this subject that may be of interest to those 
looking at providing solutions:

http://portal.acm.org/citation.cfm?id=1806623

It (Grisu2) works without bignums if you are willing to settle for the shortest 
string in 99.8% of cases, and an accurate but not shortest string in the 
remaining cases.

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 11:18 AM, Rick Regan wrote:

> Michael,
> Thanks for the very thorough analysis.

This is a difficult problem; fortunately it was solved 20 years ago...

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

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

Re: [sqlite] Why the deadlock?

[Doug Currie]

On Aug 24, 2010, at 10:57 AM, Nikolaus Rath wrote:

> Nikolaus Rath  writes:
>> Still no one able to clarify the issues raised in this thread?
>> 
>> Let me try to summarize what I still don't understand:
>> 
>> - Will SQLite acquire and release an EXCLUSIVE lock while keeping a
>>   SHARED lock if one executes a UPDATE query with one cursor while a
>>   different cursor is in the middle of a SELECT query,
>> 
>>   -or-
>> 
>>   will the EXCLUSIVE lock be held until the SELECT query finishes?

If you want the main thread to hold an exclusive lock until the work is 
completed (which would prevent the deadlock) put the SELECT and DELETE queries 
in a transaction using BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Then the EXCLUSIVE 
lock be held until the SELECT query finishes.

Alternatively, finalize the SELECT before doing the DELETE.

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

Re: [sqlite] playing with triggers

[Doug Currie]

On Aug 19, 2010, at 4:00 PM, David Bicking wrote:

> I haven't tried RAISE(ROLLBACK... as that seems to severe. 
> RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
> RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
> the first three there, which I can't let happen. It is all or nothing for 
> data changes below Table1.
> 
> Which leads me to believe I can't do what I want without application code 
> supervising the changes.

Would SAVEPOINTs help you here? http://www.sqlite.org/lang_savepoint.html

I've never tried using ROLLBACK TO in a trigger.

e

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

Re: [sqlite] What languages can include SQLite statically?

[Doug Currie]

On Jun 1, 2010, at 2:24 PM, Gilles Ganault wrote:

> Actually, it's a Blackfin processor, and since it's an embedded
> environment, RAM and storage (NAND) are an issue.

You may find eLua interesting.  http://www.eluaproject.net/ 
The supported platforms are heavily ARM based, but in the same performance 
class as Blackfin.

e

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

Re: [sqlite] sqlite on mac os x 64 bits

[Doug Currie]

On May 18, 2010, at 4:14 AM, Sylvain Pointeau wrote:
> but is it 64 bits? or do I have to add a special option?

Last time I built a Universal Binary sqlite3 on OS X (March 2010 3.6.22) I had 
to 

CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64' ./configure 
--disable-dependency-tracking

Without the --disable-dependency-tracking configure gets confused; 
alternatively you can
# edit Makefile removing -M options
# basically turning the .c.o and .c.lo rules into 
# $(COMPILE) -c -o $@ $<
# $(LTCOMPILE) -c -o $@ $<


You can use the file command to reveal the library's compatible machine 
architectures, e.g., 

~ e$ file /usr/local/lib/libsqlite3.dylib 
/usr/local/lib/libsqlite3.dylib: Mach-O universal binary with 2 architectures
/usr/local/lib/libsqlite3.dylib (for architecture i386):Mach-O 
dynamically linked shared library i386
/usr/local/lib/libsqlite3.dylib (for architecture x86_64):  Mach-O 64-bit 
dynamically linked shared library x86_64
~ e$ file /usr/local/lib/libsqlite3.a
/usr/local/lib/libsqlite3.a: Mach-O universal binary with 2 architectures
/usr/local/lib/libsqlite3.a (for architecture i386):current ar archive 
random library
/usr/local/lib/libsqlite3.a (for architecture x86_64):  current ar archive 
random library
~ e$ 


e

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

Re: [sqlite] round problem?

[Doug Currie]

On Feb 15, 2010, at 1:43 PM, Roger Binns wrote:

> Shane Harrelson wrote:
>> I'm looking at how this can be improved.
> 
> It seems that everyone else is converging on using David Gay's dtoa.c 

We've been "converging" for a few years!

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e


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

Re: [sqlite] In Memory Usage

[Doug Currie]

On Jan 4, 2010, at 6:35 AM, sasikuma...@tcs.com wrote:

> I'm using SQLite DB version 3.6.12. I recently read about the feature of 
> In-Memory Database and tried to implement it. I was able to create a new 
> DB connection in memory, able to create a table and insert some set of 
> records into the memory DB. 
> 
> How should I now transfer those records into the real table in the real 
> DB, someone kindly assist and guide me.

Adding to suggestions by Igor and Simon... You can use the ATTACH command to 
attach a disk based db to your memory based db. Then, using (CREATE and) INSERT 
statements you can copy records from the memory based db to the disk based db.

http://www.sqlite.org/lang_attach.html
http://www.sqlite.org/lang_insert.html

e



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

Re: [sqlite] Unique index on REAL values and equality check

[Doug Currie]

On Dec 13, 2009, at 3:16 PM, Alexey Pechnikov wrote:

> As we can see, the unique index can check equlity of REAL values 
> but the "=" operator can not. it's fantastic I think :-)

The problem is not the "=" operator...

sqlite> create table test (save_date REAL unique);
sqlite> insert into test values (julianday('now'));
sqlite> select rowid,* from test;
1|2455179.42227787
sqlite> insert into test select * from test;
SQL error: column save_date is not unique
sqlite> select count(*) from test where save_date=2455179.42227787;
0
sqlite> select count(*) from test where save_date in (select save_date from 
test where rowid=1);
1
sqlite> 

The problem is that floating point values (in SQLite shell) do not have 
write-read idempotency. I have moaned about that on this mailing list for 
years! ;-)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

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

Re: [sqlite] SQLite on PocketBook

[Doug Currie]

On Nov 5, 2009, at 5:15 PM, Beau Wilkinson wrote:

> I really think this warrants further discussion. Perhaps the correct  
> answer (that ARMs implement a non-standard FP type which is  
> incompatible with Sqlite) is already out there, but I think the  
> issues I raised with that answer should at least be addressed.

I don't know if this is the problem on PocketBook, but...

We have successfully used the SQLite compile option  
SQLITE_MIXED_ENDIAN_64BIT_FLOAT when building SQLite for ARM to get  
interoperability of databases between ARM Linux and other platforms  
such as x86 Linux, MacOSX, and Windows. Some compilers and runtimes  
for ARM use a format wherein the two 32-bit halves of a double are  
swapped relative to other platforms (the two 32-bit words are in big- 
endian order, whereas the bytes in the words are in little endian  
order, hence the rationale for the MIXED nomenclature in the option  
name).

e

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Doug Currie]

On Oct 30, 2009, at 10:14 AM, P Kishor wrote:

> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely.

On Oct 29, 2009, at 5:33 PM, Darren Duncan wrote:

> Support for what I indicated could conceivably just be added like  
> how support
> for foreign keys was just added, and it could be turned on/off with  
> a pragma
> likewise to aid backwards compatibility, for people who wrote the  
> column types
> in their SQL but expected enforcement to be lax.

e

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

Re: [sqlite] Huge numbers of savepoints.

[Doug Currie]

On Aug 23, 2009, at 6:46 AM, Chris Dew wrote:

> Note: this is not for production code, just an experiment in keeping a
> history of application 'state', allowing current state to be
> recalculated if an historic input is received 'late'.  See
> http://www.finalcog.com/haskell-decoupling-time-from-physics for a
> similar idea (implemented in Haskell).

This page might give you some ideas:

http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

e

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

Re: [sqlite] What is a Relation?

[Doug Currie]

On Jul 27, 2009, at 10:33 AM, CityDev wrote:

> It's true that Codd and Date used the term 'relational' (They  
> championed the
> N-ary Relational Model - others were around at the same time) but  
> it's not
> easy to track the origin of the term in mathematics.

http://en.wikipedia.org/wiki/Relation_(mathematics)

e


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

Re: [sqlite] SQLite: Porting to another Operating system.

[Doug Currie]

On Jul 24, 2009, at 8:44 AM, D. Richard Hipp wrote:

> SQLite database files are cross-platform.  All you have to do is copy
> the file to the new machine.  There is no separate "external format".
> The same database file format work on all platforms.

Just make sure that if you are moving to a new platform, that the data  
formats match those expected by SQLite. This is especially important  
for platforms with weird floating point formats. For example, on ARM  
platforms there are a couple floating point formats, and the  
SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile switch helps accommodate them.

SQLite provides support to get this right:

** Developers using SQLite on an ARM7 should compile and run their
** application using -DSQLITE_DEBUG=1 at least once.  With DEBUG
** enabled, some asserts below will ensure that the byte order of
** floating point values is correct.

e

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

Re: [sqlite] sqlite3.OperationalError: unable to open database file

[Doug Currie]

On Jul 7, 2009, at 4:36 PM, nixonron wrote:

> conn = sqlite3.connect('c:\Ujimadata\aid.sqlite')

Perhaps you meant

conn = sqlite3.connect('c:\\Ujimadata\\aid.sqlite')

or

conn = sqlite3.connect('c:/Ujimadata/aid.sqlite')

e

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

Re: [sqlite] Shortest time interval? [Was: Re: repeating events?]

[Doug Currie]

On Jun 12, 2009, at 3:46 PM, Allen Fowler wrote:

> sqlite>
> select *, min((strftime('%s', end) -  strftime('%s', start))) as  
> length
> from
>   ...> events where
>   ...> start < datetime('now', '+1 day','start of day',
> '+9 hours','+30 minutes')
>   ...> and end >  datetime('now', '+1 day','start
> of day', '+9 hours','+30 minutes')
>   ...> group by name;
>
> idname  kind   
> start end   length
>        
>     --
> 3 joe   hour   
> 2009-06-13 09:00:00   2009-06-13 10:00:00   3600
> 4 tom   day
> 2009-06-13 00:00:00   2009-06-14 00:00:00   3600
> sqlite>
>
> However this result returned is very wrong.The length col is  
> correct but the other cols for "tom" are wrong.  (It should be "2 |  
> tom | hour | 2009-06-13 09:00:00 | 2009-06-13 10:00:00 | 3600" )
>
> What am I missing here?  Am I doing the query wrong?

Yes. The "group by" doesn't know which rows to use for columns that  
are not either aggregate functions (such as min) or grouped columns  
(such as name). You know what min() does, but the query processor  
doesn't.

You can do this in two steps by getting the min time length per name,  
and joining that with the original table augmented with length.

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

Re: [sqlite] error in documentation of SELECT?

[Doug Currie]

On May 19, 2009, at 10:05 AM, Jean-Denis Muys wrote:

> On 5/19/09 2:44 PM, "Igor Tandetnik"  wrote:
>>
>> Wikipedia gives a definition different from yours, for what it's  
>> worth:
>>
>> http://en.wikipedia.org/wiki/Remainder#The_case_of_general_integers
>
> Also to support my version, the same article says a bit later:
>
> " Usually, in number theory, we always choose the positive remainder".
>
> While programming languages seems to make rather different choices:
>
> "C99 and Pascal choose the remainder with the same sign as the  
> dividend a.
> (Before C99, the C language allowed either choice.) Perl and Python  
> choose
> the remainder with the same sign as the divisor d."

For those with ACM digital library access, the three options are  
defined and analyzed in some detail in the paper "The Euclidean  
definition of the functions div and mod" by Raymond T. Boute,  
University of Nijmegen, ACM Transactions on Programming Languages and  
Systems (TOPLAS) Volume 14, Issue 2  (April 1992), Pages: 127 - 144,
Year of Publication: 1992, ISSN:0164-0925

http://portal.acm.org/citation.cfm?doid=128861.128862

e

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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Doug Currie]

On May 18, 2009, at 5:32 PM, Allen Fowler wrote:

>>> The simple solution would just create a race condition... i think:
>>>
>>> 1) INSERT INTO status_table FROM SELECT oldest task in queue
>>> 2) DELETE oldest task in queue
>>>
>>> Right?
>>
>> It might work fine if you wrap it in an exclusive
>> transaction.
>>
>
>
> "exclusive transaction"? Great!  How do I do that?  :)

Wrap the above two statements in:

0) BEGIN EXCLUSIVE
...
3) COMMIT

> From reading http://www.sqlite.org/lockingv3.html it sounds like  
> SQLite very rarely will want to gain an exclusive lock.  I think,  
> not even issuing an INSERT will do that until other factors cause it  
> to flush to disk.

The BEGIN EXCLUSIVE above is all you need (and more, a simple BEGIN  
may be enough).

> Can someone with more knowledge of SQLite internals explain the  
> right way to "atomic"-lly "pop"-off an item from table in SQlite?   
> (And, in this case, also add it to a 2nd table.)

The above sequence of 4 statements is atomic.

e

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

Re: [sqlite] CURRENT_TIMESTAMP precision

[Doug Currie]

On May 15, 2009, at 9:07 AM, Sam Carleton wrote:

> I would like CURRENT_TIMESTAMP to be more accurate than just one  
> second, any suggestions on how I might do that once?  My solution is  
> all a C/C++ interface, so all features are open to me.

Option 1 - use: julianday('now') instead of CURRENT_TIMESTAMP

Option 2 - modify ctimestampFunc in date.c

Presently ctimestampFunc uses datetimeFunc; datetimeFunc doesn't  
return fractional seconds.

e


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

Re: [sqlite] Sporadic freezes of sqlite

[Doug Currie]

On Apr 22, 2009, at 4:38 PM, Pavel Ivanov wrote:

> I've tried to set pragma synchronous = on (it's off by default for
> me), but it makes application to work 5 times slower which is not
> acceptable for me. I would be happy if there was some solution in
> between that, i.e. for example just a bit slower operation on every
> pwrite but without 8 seconds-peaks.

Perhaps you can occasionally wrap a transaction with:
PRAGMA synchronous = NORMAL;
< do the transaction >
PRAGMA synchronous = OFF;

which would flush OS allocated database cache buffers; do this one out  
of N transactions. If you have threads performing transactions that  
are not otherwise communicating, you base this decision on a random  
number [rand() % N == 1]. Tune N to achieve the maximum pwrite time  
you need.

Caveat: I have never tried this; I don't use PRAGMA synchronous = OFF.

e

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

Re: [sqlite] Query Doesn't Find Record

[Doug Currie]

On Mar 12, 2009, at 12:08 AM, jonwood wrote:

> Doug Currie-2 wrote:
>>
>> Note the '/'s
>>
>
> What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1')  
> return?
> Does DATE() simply have no effect whatsoever?

Sorry to be cryptic.

sqlite> select date('2009/12/03');

sqlite> select date('2009-12-03');
2009-12-03
sqlite>

The '/'s format is not supported by DATE.

See: http://www.sqlite.org/lang_datefunc.html

e

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

Re: [sqlite] Query Doesn't Find Record

[Doug Currie]

On Mar 12, 2009, at 12:01 AM, jonwood wrote:

> PaymentDate=2009/01/05

Note the '/'s

> And then I ran the following query:
>
> SELECT * FROM Payments WHERE FK_CustomerID=5 AND DATE(PaymentDate) >=
> DATE('2009-01-01') AND DATE(PaymentDate) <= DATE('2009-03-11')

Note the '-'s.

'2009/' > '2009-'

e

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

Re: [sqlite] sqlite for embedded devices

[Doug Currie]

On Nov 25, 2008, at 1:44 PM, Igor Augusto Guzzo wrote:

> I get an ARM based embedded system (AT91SAM9260 - ATMEL), linux based,
> with uclibc library and my code, developed in C with the sqlite3
> library, runs fine only in my host linux (Fedora).
>
> Firstly, I compiled the code on Makefile project (the main line is...
> arm-linux-gcc -o $(PROJECT) $(PROJECT).c -L/usr/local/lib -lsqlite3)
> And get the error...
> /opt/eldk/usr/bin/../lib/gcc/arm-linux/4.0.0/../../../../arm-linux/ 
> bin/ld:
> skipping incompatible /usr/local/lib/libsqlite3.so when searching for
> -lsqlite3

Did you use the same compiler to make /usr/local/lib/libsqlite3.so  
that you are using to build your application? What makefile did you  
use to build /usr/local/lib/libsqlite3.so ?

e

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

Re: [sqlite] Performance improvement In Sqlite 3.6.4 version

[Doug Currie]

On Nov 18, 2008, at 5:10 AM, Roshan Sullad wrote:

> [...]
> I have another Visual studio test application where I am using this
> Sqlite3.dll functionality, I have linked statically to Sqlite3.dll by
> including *sqlite3.c,sqlite3.h,sqlite3ext.h* , files in to my test
> application project. And am linking through *Sqlite3.lib* which I  
> generated
> using *Implib.exe. *Everything is linked and built successfully.

If you include sqlite3.c in the project, then you are not using  
Sqlite3.dll at all.

> So my doubt Is I have enabled above macros in the project settings of
> Sqlite3.dll, do I need to define above macros in the project settings

Yes, you must either use the DLL or the macro definitions.

e

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

Re: [sqlite] sqlite insert and delete statements not succeed

[Doug Currie]

On Nov 12, 2008, at 10:31 PM, henry wrote:

> my app, I opened a database handler, insert some records, delete some
> records, then closed the database handler. The problem is the next  
> time
> when I connect the Sqlite, the actions I did last time has all gone
> away, it did not take any effect to the database. There's no error  
> code
> when using the sqlite api, and if I use that handler to query the
> database, the inserted data is there.

What filename did you give to sqlite3_open?

Do you see that file being created by your app?

Is it modified (does the file modified time or size change) when your  
app runs?

e

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

Re: [sqlite] [C++] Import sqlite3.dll and/or sqlite database as resource in project?

[Doug Currie]

On Aug 10, 2008, at 2:12 PM, CAVALO SCHMIDT wrote:

> salutations, using VC++ in WinXP.
>
> I would like to know if it's possible to import and use the
> sqlite3.dll file and/or the sqlite database file as a resource in a
> C++ project, so that it will be integrated to the final Win32
> executable. how would it be possible to use sqlite3_open with a
> database file stored as a resource in the application?

I have no quibble with the other replies to your message; another  
option is EEE

http://www.erikveen.dds.nl/eee/

-- e

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

Re: [sqlite] compiling C program to use the shared library

[Doug Currie]

On Sunday, February 24, 2008 Nuno Lucas wrote: 

> On Mon, Feb 25, 2008 at 2:37 AM, Sam Carleton
> <[EMAIL PROTECTED]> wrote:
>> [...]
>>  generated the stub lib I needed to link my code against and it seems
>>  to run fine with the officially compiled DLL.

> The reason an import library isn't included is because you need a
> different one for each compiler you use to link.

Right, and with gcc on Windows (mingw/msys or cygwin), you don't need
an import library at all; gcc will link against the DLL itself.

e

-- 
Doug Currie
Londonderry, NH, USA

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

Re: [sqlite] Mailing List Changes

[Doug Currie]

On Monday, February 04, 2008 P Kishor wrote: 

> On 2/4/08, Clark Christensen <[EMAIL PROTECTED]> wrote:
>> So, I sent a reply this morning to a list message, and it seems to have gone 
>> to the OP's email address rather than to the list (sorry bash).
>>
>> I don't remember having that issue with the old software (ezmlm).  To fix, 
>> is it a client configuration, or is there a reply-to header that should be 
>> set in Mailman?  I would really like to NOT have to remember to change the 
>> recipient address when I reply.

> +1

> This mailing-list business is becoming a royal pain in the derriere.
> Every other mailing list behaves differently... some default to the
> list, others to the OP. Why can't we all get along.

> Please set the list so default reply is to the list.

http://www.unicom.com/pw/reply-to-harmful.html

e

-- 
Doug Currie
Londonderry, NH, USA

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

Re: [sqlite] SQLite character comparisons

[Doug Currie]

On Sunday, January 20, 2008 Fowler, Jeff wrote: 

> briefly, ANSI SQL-92 specifies that when comparing two character
> fields, trailing spaces should be ignored.

From SQL-92 (draft July 1992) section 4.6

> When values of unequal length are compared, if the collating
> sequence for the comparison has the NO PAD attribute and the shorter
> value is equal to a prefix of the longer value, then the shorter
> value is considered less than the longer value. If the collating
> sequence for the comparison has the PAD SPACE attribute, for the
> purposes of the comparison, the shorter value is effectively
> extended to the length of the longer by concatenation of s on
> the right.

section 8.2 also says

> 3) The comparison of two character strings is determined as fol-
>lows:
> 
>a) If the length in characters of X is not equal to the length
>  in characters of Y, then the shorter string is effectively
>  replaced, for the purposes of comparison, with a copy of
>  itself that has been extended to the length of the longer
>  string by concatenation on the right of one or more pad char-
>  acters, where the pad character is chosen based on CS. If
>  CS has the NO PAD attribute, then the pad character is an
>  implementation-dependent character different from any char-
>  acter in the character set of X and Y that collates less
>  than any string under CS. Otherwise, the pad character is a
>  .

So, using this terminology, the SQLite default collating sequence has
the NO PAD attribute, and the pad character is NUL.

Jeff, can you solve your problem with a custom collating sequence?

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Wednesday, September 05, 2007 Simon Davies wrote: 

> [...]
> This resolves down to calling a function vxprintf, which has the following 
> line:

>while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }

> When this line is reached, realvalue has value 98926650.5 and exp value 0.
> realvalue then goes the following values during the loop:
> 9892665.050007
> 989266.5050012
> 98926.65050018
> 9892.665050032
> 989.2665050039
> 98.92665050051
> 9.892665050051

> exp finishes at 7

> Note the 'error' added in by the successive multiplications is 0.00051

> [...]

> One wonders why XP/VC++ produces such a large error in its floating
> point manipulations that appears to be avoided by other compilers.

...probably because VC++ uses 64 bits for long double (53 bit
mantissa) whereas gcc uses 96 bits (64 bit mantissa). Note that
realvalue above is declared as LONGDOUBLE_TYPE (long double).
http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Wednesday, September 05, 2007 Arjen Markus wrote: 

> Doug Currie wrote:

>>I suspect the bug is in the functions that convert between string and
>>double; that's why I keep harping on Steele and White's (and
>>Clinger's) PLDI 1990 papers. What I don't know is why this bug appears
>>in the binary from sqlite.org but not in the version I build myself
>>with gcc 3.4.5 from SQLite version 3.4.2 source.
>>
>>  
>>
> The implementation of that algorithm is far from trivial. It actually 
> requires the
> use of an arbitrary-precision library (or so at least is my understanding).

Yes, in some cases big integers are needed. In extreme cases, e.g.,
denormalized numbers, up to 160 bytes may be required.

I have implemented these algorithms, twice, but for employers with
proprietary programs. The algorithms are also implemented in glibc
based on high quality code from David Gay (Bell Labs, Lucent, Sandia,
Netlib), available with a BSD license at
http://netlib.sandia.gov/fp/index.html

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Tuesday, September 04, 2007 John Machin wrote: 

> On 5/09/2007 6:18 AM, John Stanton wrote:
>> These are regular floating point numbers, and behave accordingly.

> Utter nonsense. round(98926650.5, 1) -> 98926650.501 is a BUG.

I agree with you that there is a bug here somewhere. But it is not in
the round function, regardless of the merits of its implementation...

C:\pvm3>sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> select 98926650.5 * 1.0;
98926650.501

I suspect the bug is in the functions that convert between string and
double; that's why I keep harping on Steele and White's (and
Clinger's) PLDI 1990 papers. What I don't know is why this bug appears
in the binary from sqlite.org but not in the version I build myself
with gcc 3.4.5 from SQLite version 3.4.2 source.

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Monday, September 03, 2007 Nuno Lucas wrote: 

> Maybe some OS specific error? Wasn't there some discussion earlier
> about the Microsoft compiler not using the full double precision by
> default?

Microsoft C compilers store long doubles in 64 bits, just like
doubles http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx
whereas gcc stores long doubles in 96 bits and uses 64 bit mantissa
(80x87 "extended" type) versus Microsoft 53 bit mantissa.
http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx

Sqlite3 uses long doubles in round, and other functions, and so
results between Microsoft Visual C++ compiled and gcc compiled
versions of sqlite3 are bound to produce different results.

Why my gcc 3.4.5 compiled sqlite3.exe and the one from the sqlite.org
downloads page produce different results is still a mystery to me.

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Monday, September 03, 2007 Nuno Lucas wrote: 

> This made me to remember there was a bug some time ago about the
> rounding algorithm (but can't remember at what version it was fixed),
> so I just tested it.

> "official" amalgamated sqlite 3.4.0 downloaded from the site some time ago:

> SQLite version 3.4.0
> Enter ".help" for instructions
> sqlite> select round(98926650.5, 1) ;
> 98926650.5

> So it seems SQLite is already doing the right job.
> Maybe some OS specific error? Wasn't there some discussion earlier
> about the Microsoft compiler not using the full double precision by
> default?

On WinXP

SQLite version 3.4.2
Enter ".help" for instructions
sqlite> select round(98926650.5, 1);
98926650.501

My results above are using the sqlite3.exe from http://www.sqlite.org.
I belive this is compiled with gcc, but I don't know what version.

However, if I compile from sources, I get

C:\Dev\sqlite\sqlite-3.4.2\bld>.\sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> select round(98926650.5, 1);
98926650.5

I am using:
$ gcc --version
gcc.exe (GCC) 3.4.5 (mingw special)

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Monday, September 03, 2007 Arjen Markus wrote: 

> Serena Lien wrote:

>>Okay, but even if the rounded result does not have a finite binary
>>representation, shouldn't the displayed (human readable) representation be
>>at least truncated to the number of decimal places that were requested in
>>the round function? Not that I am confusing round with truncate, but surely
>>it is a more acceptable result?
>>  
>>
> Hm, that is a completely different question. It would mean that more 
> information is
> associated with the rounded result than merely the number (in internal
> representation).

No.

See the paper cited in my earlier email:
 http://portal.acm.org/citation.cfm?id=93559

Once the number is rounded (correctly), there is enough information in
the number itself. It can be displayed with exactly the smallest
number of digits necessary to reconstruct the number. This will be the
number of digits that Serena expects.

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: select round(98926650.50001, 1) ?

[Doug Currie]

On Monday, September 03, 2007 Arjen Markus wrote: 

> Serena Lien wrote:

>>round(98926650.5, 1) -> 98926650.501
>>round(85227887.01, 1) -> 85227887.001

> They are in fact rounded, but the internal binary representation can
> not be turned into the appropriate decimal (and human readable)
> representation due to the finite precision.

98926650.5 is represented exactly in IEEE double; something else is
mucking up the round or the display of the result, maybe both.

85227887.0 is represented exactly in IEEE double, though 85227887.01
is not. Nevertheless, there are ways to print floating point numbers
readably. http://portal.acm.org/citation.cfm?id=93559

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Doug Currie]

On Friday, June 01, 2007 Ian Frosst wrote: 

> For Windows, this is not the case with Automatic Reset events.  The system
> guarantees that only one thread waiting on the event is woken up (it keeps a
> queue): the others happily keep sleeping until the next setting of the
> event.

That may work for you, but has the possible drawback of starving a
thread... Windows doesn't guarantee that the woken thread is the one
at the head of the "queue." See, e.g.,
http://blogs.msdn.com/oldnewthing/archive/2006/03/13/550402.aspx
So, I never use PulseEvent or Automatic Reset events.

Of course, if you expect there to be intervals when there are no
waiting threads, and you can afford to wait for that interval for a
synchronization point, and you don't care the order in which the
inserts are performed, the Automatic Reset event might work.

e

> On 6/1/07, Doug Currie <[EMAIL PROTECTED]> wrote:
>>
>> On Friday, June 01, 2007 Ian Frosst wrote:
>>
>> > On the topic of a more efficient busy handler, one approach I considered
>> > was to implement an event which was signalled when a database unlock
>> > occurred.
>> > That way, the busy handler could just wait on the event (which is an
>> > efficient wait state), and be guaranteed of a wake up when the lock is
>> > released (the event would be signalled at this time.)  However, I wasn't
>> > at the time familiar enough with SQLite's innards to implement such a
>> > beast.
>> > Can anyone see any pitfalls to such an approach?
>>
>> The problems occur when multiple threads are waiting on the event;
>> they all wake up and compete for the resource again. For better
>> solutions, see: http://world.std.com/~jmhart/batons.htm

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Doug Currie]

On Friday, June 01, 2007 Ian Frosst wrote: 

> On the topic of a more efficient busy handler, one approach I considered was
> to implement an event which was signalled when a database unlock occurred.
> That way, the busy handler could just wait on the event (which is an
> efficient wait state), and be guaranteed of a wake up when the lock is
> released (the event would be signalled at this time.)  However, I wasn't at
> the time familiar enough with SQLite's innards to implement such a beast.
> Can anyone see any pitfalls to such an approach?

The problems occur when multiple threads are waiting on the event;
they all wake up and compete for the resource again. For better
solutions, see: http://world.std.com/~jmhart/batons.htm

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL and SQLite pronounciation?

[Doug Currie]

On Wednesday, April 04, 2007 Martin Pelletier wrote: 

> I use a twist on the various versions listed so far: "es cue el-ait".

> Hearing "sequel" for SQL always makes me cringe.

Yeah. In some circles, "sequel" is reserved for the original SEQUEL
query language were first published in 1974 by Don Chamberlin and Ray
Boyce at the ACM–SIGFIDET Workshop on Data Description, whereas "ess
cue ell" is used for subsequent standardized versions of the language.

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Does julianday work according to the manual?

[Doug Currie]

On Wednesday, January 31, 2007 Rick van der Lans wrote:

> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that the
> statement:

> Select julianday('-4714-11-24 12:00:00');

> Should return 0.0. But it doesn't, it returns -365.0

> Does this mean, that the manual should say "since noon in Greenwich on
> November 24, 4713 B.C.? Or am I missing something?


sqlite> Select julianday('-0001-11-24 12:00:00');
1721022.0
sqlite> Select julianday('-11-24 12:00:00');
1721388.0
sqlite> Select julianday('0001-11-24 12:00:00');
1721753.0

There is no year 0. The calendar goes from -1 BCE to 1 CE. -11-24
is 1 BCE. So, if you want November 24, 4714 B.C. you need to say

sqlite> Select julianday('-4713-11-24 12:00:00');
0.0

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] MSWindows SQLite Compilation

[Doug Currie]

On Wednesday, January 10, 2007 Leonardo Mateo wrote: 

> I'm trying to get an implib from sqlite3.dll to link with.

The easiest way to do this with MinGW/MSYS is to use the configure and
make tools with the source distribution, e.g., sqlite-3.3.10.tar.gz
The instructions on how to do this are in the first 20 lines of the
README file in that archive.

> The generated implib is suspiciously small (790 b)
> When I try to compile I got an undefined reference to sqlite3_open error

When you make an implib from a DLL, ld makes an implib that assumes
your code will use the DLL. There is hardly ever a reason to do this
since gcc will link against the DLL directly.

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Terminating long query in C ?

[Doug Currie]

Tuesday, October 10, 2006, 9:32:18 PM, Mohd Radzi Ibrahim wrote:

> Thank you guys for the suggestion. That's what I really need. Which one is
> better, the sqlite3_interrupt() or sqlite3_progress_handler()? My gut
> feeling is that sqlite3_interrupt() should be better since it does not
> interfere with the running of the query until it is signaled.  Besides, the
> progress_handler does not actually give me the right proportion of 
> completion... Or is there  a way I can know proportion of completion from
> it?

Suggestion:
Create an (atomically accessible) global variable.
At the start of your query set it to zero and start a timer.
Give sqlite3_progress_handler() a function that simply returns the variable.
When the timer expires (or control-C is pressed) atomically set the variable to 
one.

If you use a large value for nOps in sqlite3_progress_handler() the
overhead should be very small.

e

> - Original Message - 
> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
> To: "SQLite" <sqlite-users@sqlite.org>
> Sent: Wednesday, October 11, 2006 8:43 AM
> Subject: [sqlite] Re: Terminating long query in C ?


>> [EMAIL PROTECTED] wrote:
>>> sqlite3_interrupt() has long been used by the sqlite3 shell program.
>>> When you press control-C a posix signal handler invokes
>>> sqlite3_interrupt() to stop the current query.  (This only works on
>>> posix, of course.)  The original purpose of sqlite3_interrupt() was
>>> to support ^C in the shell, and for that purpose the thread
>>> restriction
>>> was entirely reasonable.
>>>
>>> It is easy to understand how this use of sqlite3_interrupt() might
>>> have escaped the notice of users on non-posix systems.
>>
>> One can register a Ctrl-C handler on Windows with SetConsoleCtrlHandler.
>> But the handler is invoked on a background thread created by the system
>> specifically for this purpose, so sqlite3_interrupt couldn't be used
>> there, either.
>>
>> Igor Tandetnik

-- 
Doug Currie
Londonderry, NH


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Query returns all rows

[Doug Currie]

Monday, September 25, 2006, 1:46:12 PM, David Champagne wrote:

> and then I execute a query

> SELECT * FROM License WHERE FORM = "form";

> I get all rows returned

Try:

SELECT * FROM License WHERE FORM = 'form';

e

-- 
Doug Currie
Londonderry, NH


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Doug Currie]

Friday, June 16, 2006, 5:32:32 PM, Andrew Piskorski wrote:

> Would using a non-overwriting MVCC storage layer a la PostgreSQL (but
> still using client SQLite processes only, no client/server
> arrangement) make any of the above easier or better?

See http://www.sqlite.org/cvstrac/wiki?p=BlueSky the shadow pager.

> Note, I'm not suggesting that you should implement anything like this
> in SQLite...

Me neither. ;-)

e

-- 
Doug Currie
Londonderry, NH

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[Doug Currie]

Thursday, May 4, 2006, 1:27:49 PM, Dennis Cote wrote:

> More mysteries. To investigate this low insert performance under WinXP I
> wrote a simple test program that loops writing one character to a file
> and then flushing the file. The strange thing is that it seems to 
> alternate between two different run times ...

Your program...

$ flushtst
15 seconds, 67 flushes/sec

$ flushtst
26 seconds, 38 flushes/sec

$ flushtst
26 seconds, 38 flushes/sec

$ flushtst
27 seconds, 37 flushes/sec

$ flushtst
27 seconds, 37 flushes/sec

Add the line:

DeleteFile("test.txt");

in front of the CreateFile() call...

$ flushtst
15 seconds, 67 flushes/sec

$ flushtst
14 seconds, 71 flushes/sec

$ flushtst
13 seconds, 77 flushes/sec

$ flushtst
12 seconds, 83 flushes/sec

e

Re: [sqlite] [SOLVED] Re: [sqlite] LIKE and GLOB bug with numbers?

[Doug Currie]

Tito,

I am replying off list because I don't want to contribute to turning
this thread into a flame war...

You have shown wonderful patience and politeness on this list. You
have my gratitude and respect for this.

Regards,

e

Tuesday, March 28, 2006, 5:35:37 PM, Tito Ciuro wrote:

> Hi Dennis,

> On 28/03/2006, at 14:24, Dennis Cote wrote:

>> If you give this a try, you might be surprised at how flexible it is.

> That was a very clear explanation. I will sure give it a try when I
> have a chance.

>> Just because something works doesn't mean that it can't be improved.

> I couldn't agree more with you.

> Thanks for the help!

> Regards,

> -- Tito



-- 
Doug Currie
Londonderry, NH

Re: [sqlite] Join on same table and performance.

[Doug Currie]

Friday, March 24, 2006, 2:33:36 PM, Tobias Rundström wrote:

> [...]
> The schema is this:

> create table Media (id integer, key, value, source integer);
> and the indexes:
> create unique index key_idx on Media (id,key,source);
> create index prop_idx on Media (key,value);
> create index source_idx on Media (key,source);
> create index key_source_val_idx on Media (key,source,value);

I wonder what effect

create table Media (id INTEGER PRIMARY KEY, key, value, source integer);

would have on your query time. This would use the already built-in
BTree index.

e

-- 
Doug Currie
Londonderry, NH

[sqlite] 3.3.4 build with MinGW/MSYS

[Doug Currie]

A brief report on building sqlite-3.3.4 with MinGW/MSYS and tcl84 on
WinXP...

1. After configure, a small edit was necessary to the Makefile; for
some reason libtool doesn't put .lo objects into .libs subdirectory
anymore; there was also an issue reported on the mailing list with
stripping the DLL; so I changed the dll target to:

dll: sqlite3.dll

REAL_LIBOBJ = $(LIBOBJ:%.lo=%.o)

sqlite3.dll: $(LIBOBJ) $(TOP)/sqlite3.def
dllwrap --dllname sqlite3.dll --def $(TOP)/sqlite3.def $(REAL_LIBOBJ)
strip --strip-unneeded sqlite3.dll


2. gcc 3.4.5 blew up trying to compile tclsqlite.c

I was able to work around the problem by adding these lines in tclsqlite.c:

#undef EXTERN
#define EXTERN extern

Without these lines, EXTERN is translated to __declspec((dllimport))
which is not what we want, and was giving gcc fits (ICE) for some reason.

I also wonder about the purpose of

#ifdef BUILD_sqlite
#undef TCL_STORAGE_CLASS
#define TCL_STORAGE_CLASS DLLEXPORT
#endif /* BUILD_sqlite */

since TCL_STORAGE_CLASS doesn't see to be used anywhere in the file.

Regards,

e