Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Doug]

Thanks, Richard.

Something triggered me when I looked at the generated code: you use the 
contents of register 2 for the constant value each time through the loop. What 
if the select looks like this, with more than one function call in the 
coalesce? Do you handle it properly?

SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1;

Just curious...
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Richard Hipp
> Sent: Thursday, March 12, 2020 7:41 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-
> circuit sometimes
> 
> On 3/12/20, Doug  wrote:
> > Richard, what does the explain look like with your code change,
> please.
> 
> Test case:
> 
> CREATE TABLE t1(a);
> explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1;
> 
> Before the change:
> 
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  
> -
> 0 Init   0 10000  Start at
> 10
> 1 OpenRead   0 2 0 1  00  root=2
> iDb=0; t1
> 2 Explain2 0 0 SCAN TABLE t1  00
> 3 Rewind 0 9 000
> 4   Column 0 0 100
> r[1]=t1.a
> 5   NotNull1 7 000  if
> r[1]!=NULL goto 7
> 6   SCopy  2 1 000
> r[1]=r[2]
> 7   ResultRow  1 1 000
> output=r[1]
> 8 Next   0 4 001
> 9 Halt   0 0 000
> 10Transaction0 0 1 0  01
> usesStmtJournal=0
> 11Int64  0 3 0 -9223372036854775808  00
> r[3]=-9223372036854775808
> 12Function   1 3 2 abs(1) 00
> r[2]=func(r[3])
> 13Goto   0 1 000
> 
> Notice that the abs() function is invoked in the "prologue" code.
> The
> prologue begins on instruction 10 and continues through the Goto
> at
> instruction 13.
> 
> After the change:
> 
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  
> -
> 0 Init   0 13000  Start at
> 13
> 1 OpenRead   0 2 0 1  00  root=2
> iDb=0; t1
> 2 Explain2 0 0 SCAN TABLE t1  00
> 3 Rewind 0 12000
> 4   Column 0 0 100
> r[1]=t1.a
> 5   NotNull1 10000  if
> r[1]!=NULL goto 10
> 6   Once   0 9 000
> 7   Int64  0 3 0 -9223372036854775808  00
> r[3]=-9223372036854775808
> 8   Function   1 3 2 abs(1) 00
> r[2]=func(r[3])
> 9   SCopy  2 1 000
> r[1]=r[2]
> 10  ResultRow  1 1 000
> output=r[1]
> 11Next   0 4 001
> 12Halt   0 0 000
> 13Transaction0 0 1 0  01
> usesStmtJournal=0
> 14Goto   0 1 000
> 
> Now the prologue is just instructions 13 and 14 and omits the
> abs()
> function.  The abs() function is now computed on instructions 7
> and 8,
> but those instructions only run one time due to the "Once" opcode
> on
> instruction 6.
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Doug]

Richard, what does the explain look like with your code change, please.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Richard Hipp
> Sent: Thursday, March 12, 2020 3:09 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-
> circuit sometimes
> 
> On 3/12/20, Hick Gunter  wrote:
> > Exactly what I gained from the EXPLAIN output.
> >
> > The SQL "compiler" is extracting the constant expression
> ABS(...) and
> > evaluating it in the program prolog (where schema is checked and
> locks
> > taken). See instructions 11 and 12
> 
> Exactly.  SQLite tries to factor out constant expressions into the
> prologue so that they are only evaluated once, rather than once
> for
> each row.  This is a performance optimization.
> 
> A traditional compiler like gcc or clang would do an enormous
> amount
> of code movement, similar to this, as well as other
> simplifications,
> to make the code run faster, especially with options like -O2.
> But
> the compiler for SQLite does not have that luxury.  Depending on
> the
> query, the time spent compiling the query into byte code can be a
> significant fraction of the total running time.  Hence, the
> compiler
> needs to be very fast.  This is an ongoing design struggle with
> SQLite:  how many CPU cycles do we burn trying to optimize the
> bytecode with the hopes of making up those lost CPU cycles with a
> shorter run-time?  Optimization is also constrained by the desire
> to
> keep the SQLite code small.  Hence, the optimizations applied by
> the
> SQLite byte-code compiler are relatively simple, so that they can
> be
> implemented with few CPU cycles and with few bytes of machine
> code.
> 
> Returning to the original discussion:  The underlying problem is
> that
> the constant expressions that get moved into the prologue, if they
> involve function calls, might throw an exception.  That is what is
> happening with abs(-9223372036854775808).  And that exception
> prevents
> the main body of the code from running, even if the offending
> expression was never actually going to be used.  The solution is
> to
> not factor out expressions that use functions, but instead use the
> OP_Once opcode (https://www.sqlite.org/opcode.html#Once) to
> prevent
> those expressions from being evaluated more than once.  This seems
> to
> make Coalesce (and CASE...END) short-circuit again.  And it also
> fixes
> ticket https://www.sqlite.org/src/info/3c9eadd2a6ba0aa5
> 
> That change is implemented by check-in
> https://www.sqlite.org/src/info/c5f96a085db9688a
> 
> 
> >
> > asql> explain select coalesce((SELECT 'hello'),ABS(-
> 9223372036854775808));
> > addr  opcode p1p2p3p4 p5
> comment
> >   -        -  --  --
> ---
> > 0 Init   0 11000  Start
> at 11
> > 1 Once   0 6 000
> > 2 Null   0 2 200
> r[2..2]=NULL; Init
> > subquery result
> > 3 Integer1 3 000
> r[3]=1; LIMIT
> > counter
> > 4 String80 2 0 hello  00
> r[2]='hello'
> > 5 DecrJumpZero   3 6 000  if (--
> r[3])==0
> > goto 6
> > 6 SCopy  2 1 000
> r[1]=r[2]
> > 7 NotNull1 9 000  if
> r[1]!=NULL goto
> > 9
> > 8 SCopy  4 1 000
> r[1]=r[4]
> > 9 ResultRow  1 1 000
> output=r[1]
> > 10Halt   0 0 000
> > 11Int64  0 5 0 -9223372036854775808  00
> > r[5]=-9223372036854775808
> > 12Function0  1 5 4 abs(1) 01
> r[4]=func(r[5])
> > 13Goto   0 1 000
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] Im
> > Auftrag von Jay Kreibich
> > Gesendet: Mittwoch, 11. März 2020 20:53
> > An: SQLite mailing list 
> > Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com
> > Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-
> circuit
> > sometimes
> >
> >
> >> On Mar 11, 2020, at 2:16 PM, Justin Ng
> 
> >> wrote:
> >>
> >> They generally do short-circuit but there are edge cases where
> they don't.
> >> It isn't ent

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Doug]

> On Tue, Feb 4, 2020 at 5:38 PM Doug  wrote:
> > > You're twisting my point here. I obviously want the reverse,
> > > I want the database types to "drive" the binding done. 1-to-1.
> > > Because even if binding a different type would work, via
> SQLite's
> > > own implicit conversion, I don't want that, because it's
> hiding a
> > > bug in the code most likely instead. --DD
> 
> > Is the code inadvertently putting quotes (') around in integer
> value [...]?
> 
> I'm talking about "real" binding here:
> https://www.sqlite.org/c3ref/bind_blob.html
> In C/C++, you could mess up your col indexes when binding, or bind
> incorrectly for some other reason, and "strong static typing" is more
> likely to find those, via SQL failures, than SQLite's default
> flexible-typing, that accepts any value in any typed column,
> unless you have these explicit CHECK+typeof constraints. --DD

So you are talking about a bug in your code where you inadvertently called:
  sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
instead of
  sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
and you want SQLite to tell you about it.

I have a hard time seeing how you could make that kind of coding error, given 
the different parameters and types in the calls.

Doug



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

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Doug]

> You're twisting my point here. I obviously want the reverse,
> I want the database types to "drive" the binding done. 1-to-1.
> Because even if binding a different type would work, via SQLite's
> own implicit conversion, I don't want that, because it's hiding a
> bug in the code most likely instead. --DD

WRT the code that the bug is in: I'm assuming that your code is creating text 
SQL statements which it passes to some process, right? The "binding" you 
mention is confusing me. You can't be using query.addBindValue() because the 
type is coerced to match the column type.

So, if you are generating text SQL statements: Is the code inadvertently 
putting quotes (') around in integer value or is the user entering a string and 
your code is taking that input and slapping it into a SQL INSERT statement?

Please explain your possible code "bug".

Doug

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

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

[Doug]

> -Original Message-
> From: sqlite-users 
> On Behalf Of J Decker
> Sent: Saturday, January 04, 2020 4:11 AM
> 
> Could wish there was a way to
> pause execution without giving up execution context...

What about?
for (i=1000; i--; i>0);

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

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

[Doug]

Thanks, Jens. I got it. The benchmark sounds like it isn't a real benchmark, 
but a made-up scenario to exercise the Coz code. I've let go now.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Friday, January 03, 2020 10:58 PM
> To: SQLite mailing list 
> Cc: em...@cs.umass.edu; curtsin...@grinnell.edu
> Subject: Re: [sqlite] FW: Questions about your "Performance
> Matters" talk re SQLite
> 
> 
> > On Jan 2, 2020, at 11:54 AM, Doug  wrote:
> >
> > I know there has been a lot of talk about what can and cannot be
> done with the C calling interface because of compatibility issues
> and the myriad set of wrappers on various forms. I’m having a hard
> time letting go of a possible 25% performance improvement.
> 
> This was a heavily multithreaded benchmark (64 threads accessing
> the same connection) on a very hefty server-class CPU. From Dr
> Hipp’s results, it sounds like the speed up may be only in similar
> situations, not to more normal SQLite usage.
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

[sqlite] FW: Questions about your "Performance Matters" talk re SQLite

[Doug]

I asked for some information from Emery Berger about his video talk on 
performance where he said they got a 25% improvement in SQLite performance. 
Here is the reply I got back.

 

I know there has been a lot of talk about what can and cannot be done with the 
C calling interface because of compatibility issues and the myriad set of 
wrappers on various forms. I’m having a hard time letting go of a possible 25% 
performance improvement.

 

I don’t have the slightest idea on how to run a benchmark (but I could learn). 
I wonder if the current set of benchmarks used by SQLite developers actually 
measure throughput using wall-clock numbers. It might be a good idea to put a 
wrapper around all the benchmarks to capture how long they took to run 
(wall-clock), and include things like number and type of cpu cores, average cpu 
busy time, and other relevant numbers. If the benchmarks are run on lots of 
different machines (all over the world?), it would provide an excellent view of 
what changes in SQLite made a difference in performance.

 

Doug

 

From: Curtsinger, Charlie  
Sent: Thursday, January 02, 2020 10:55 AM
To: dougf@comcast.net
Cc: Emery D Berger 
Subject: Re: Questions about your "Performance Matters" talk re SQLite

 

Hello Doug,

 

I was able to track down the sqlite benchmark I ran for the paper, and I’ve 
checked it into the github repository at 
https://github.com/plasma-umass/coz/tree/master/benchmarks/sqlite. This 
benchmark creates 64 threads that operate on independent tables in the sqlite 
database, performing operations that should be almost entirely independent. 
This benchmark exposes contention inside of sqlite, since running it with a 
larger number of hardware threads will hurt performance. I see a performance 
improvement of nearly 5x when I run this on a two-core linux VM versus a 
64-thread Xeon machine, since there are fewer opportunities for the threads to 
interfere with each other.

 

You can also find the modified version of sqlite with the same benchmark at 
https://github.com/plasma-umass/coz/tree/master/benchmarks/sqlite-modified. 
There are just a few changes from indirect to direct calls in the sqlite3.c 
file.

 

I reran the experiment on the same machine we used for the original Coz paper, 
and saw a performance improvement of around 20% with the modified version of 
sqlite. That’s slightly less than what we originally found, but I didn’t do 
many runs (just five) and there’s quite a bit of variability. The compiler has 
been upgraded on this machine as well, so there could be some effect there as 
well. On a much-newer 64-thread Xeon machine I see a difference of just 5%, 
still in favor of the modified version of sqlite. That’s not terribly 
surprising, since Intel has baked a lot of extra pointer-chasing and branch 
prediction smarts into processors in the years since we set up the 64-core AMD 
machine we originally used for the Coz benchmarks.

 

As far as measuring performance, I’d encourage you *not* to use cpu cycles as a 
proxy for runtime. Dynamic frequency scaling can mess up these measurements, 
especially if the clock frequency is dropped in response to the program’s 
behavior. Putting many threads to sleep might allow the OS to drop the CPU 
frequency, thereby reducing the number of CPU cycles. That doesn’t mean the 
program will actually run in a shorter wall clock time. Some CPUs have a 
hardware event that counts “clock cycles” at a constant rate even with 
frequency scaling, but these are really just high-precision timers and would be 
perfectly fine for measuring runtime. I’m thinking of the “ref-cycles” event 
from perf here.

 

Hope this helps,

- Charlie

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

Re: [sqlite] Causal profiling

[Doug]

OK, I get that the definition of the SQLite API is a (large) set of C function 
calls. And that changing the way they work under the covers would break 
something. (Check out the IDEA below!)

I'm wondering if your use case is - in the same application - that you 
dynamically change from option SQLITE_CONFIG_SINGLETHREAD to 
SQLITE_CONFIG_MULTITHREAD to SQLITE_CONFIG_SERIALIZED while your application is 
running? If that is the case, then your application knows which option to use 
dynamically; Otherwise, your application doesn't know and doesn't care which 
option is in effect.

I am suggesting that if the we added the global calls to the underlying 
functions to the API - that is, the functions that are called by the function 
table indirection - then one could code the application to call the underlying 
functions. If the application knows it's single-thread, then code it that way 
and get a 25% improvement (see the talk). If the application makes the choice 
of thread option dynamically, then the penalty for single-thread is at least 
double (application choice, SQLite indirection), so calling the (new) 
underlying function once you made the choice, performs better for that path. I 
grant you that probably will see little improvement on the threaded path.

If you are going to tell me that you need to maintain two versions of your 
application if you run it in a single-thread environment or a multi-thread 
environment, then let's define the (new) API to use a preprocessor macro to 
generate the right code for the option selected based on the values of 
SQLITE_CONFIG_SINGLETHREAD, el al. So now you have a single source but multiple 
executables corresponding to that source. And the choice of which executable to 
use becomes a configuration problem at application deployment time, or at 
application run time. 

 IDEA! 
Thinking about it, I'm surprised that the C API isn't just a set of macros 
already. I can visualize a  C API composed of a set of macro definitions 
_identical_ to the current C function calls. They just use the extra knowledge 
of SQLITE_THREADSAFE and other SQLite compiler options to decide what 
application code to generate. Then the formal API doesn't change from a coding 
point of view. The generated code calls a set of under-the-cover functions 
which are not part of the API. The change doesn't require a new layer of 
testing; presumably, we already have test cases that test the same code using 
different compiler options. What about that?

Best, Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Friday, December 27, 2019 2:22 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Causal profiling
> 
> 
> 
> > On Dec 26, 2019, at 3:45 PM, Doug  wrote:
> >
> > Can you explain what the API is that you use to "set concurrency
> levels per connection", please? Is it a parameter on open() or its
> own function call? And how would the API break, exactly?
> 
> sqlite3_config(), specifically the SQLITE_CONFIG_SINGLETHREAD,
> SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED options.
> 
> This API would break because configuring those options at runtime
> would have no effect on behavior; the only thing that would change
> threading behavior would be the compile-time flags
> SQLITE_MUTEX_OMIT, etc.
> 
> (This is actually global, not per-connection, but that doesn't
> invalidate what I said.)
> 
> > The talk suggested removing the SQLite virtual table of
> functions (specifically the call to free a mutex). The user calls
> the function directly. How does that break an API?
> 
> If SQLite's implementation directly called the mutex lock/unlock
> functions, instead of indirecting, then there would be no way to
> control whether or not mutexes were used. In other words, it would
> be impossible to change any of the above options at runtime.
> 
> > The talk suggested removing the SQLite virtual table of
> functions (specifically the call to free a mutex). The user calls
> the function directly. How does that break an API?
> 
> No, the user does not call those functions directly. The code
> shown in the video is deep inside SQLite itself and not visible
> through the API. (You say you're using a TCL wrapper … so you may
> not be aware of what is or isn't in the C API. Trust me, I use the
> C API a lot.)
> 
> —Jens
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Causal profiling

[Doug]

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Thursday, December 26, 2019 3:11 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Causal profiling
> 
> 
> 
> > On Dec 25, 2019, at 2:53 PM, Doug  wrote:
> >
> > I wrote an application in Qt which uses SQLite. Therefore, I
> invoke SQLite functions with some wrapper. For a 9% performance
> improvement in SQLite using the direct call versus indirect call
> (as discussed in the talk), cannot the wrapper functions be
> changed so my application doesn't know the difference?
> 
> This change would break the API that lets you set concurrency
> levels per connection; instead, the concurrency would be hardcoded
> at compile time. _You_ may not be using this API, but there are
> definitely developers who do.

Can you explain what the API is that you use to "set concurrency levels per 
connection", please? Is it a parameter on open() or its own function call? And 
how would the API break, exactly?

The talk suggested removing the SQLite virtual table of functions (specifically 
the call to free a mutex). The user calls the function directly. How does that 
break an API?
 
> This would especially be a problem for environments where SQLite
> is provided as a shared library in the operating system (e.g. iOS,
> macOS). The concurrency level would then be up to the platform
> vendor, not the application developer. (Unless they bundle their
> own copy of SQLite in their app.)
> 
> One possible workaround would be a compile option that enables the
> direct calls, but which is off by default. People who wanted
> mutexes but with direct calls could then set that option.
> 
> [deletia]
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Causal profiling

[Doug]

Richard, can you please explain each of these?

1. API break
I wrote an application in Qt which uses SQLite. Therefore, I invoke SQLite 
functions with some wrapper. For a 9% performance improvement in SQLite using 
the direct call versus indirect call (as discussed in the talk), cannot the 
wrapper functions be changed so my application doesn't know the difference?  

2. Render SQLITE untestable
Does that mean that you are doing whitebox testing? Surely, all the thousands 
of queries vs responses are blackbox, not whitebox. Why would changing indirect 
calls to direct calls render SQLite untestable?

3. Unable to replicate performance gains
This says to me you actually made the change suggested. And then you ran a test 
suite against the amalgamation. And you actually measured the result. How can 
you have done that if such a change renders SQLite untestable? And (sneaking a 
peak at the talk again re performance measurements), what did you use to 
measure the results?

Best, Doug
> -Original Message-
> From: sqlite-users 
> On Behalf Of Richard Hipp
> Sent: Wednesday, December 25, 2019 3:18 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Causal profiling
> 
> On 12/25/19, sky5w...@gmail.com  wrote:
> > Thanks for sharing!
> > Did his suggested optimization make it to a commit?
> 
> No.  That would be an API break, and would also render SQLite
> untestable.  Furthermore, we have been unable to replicate the
> performance gains.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Result set column names

[Doug]

For column "xxx" what will the full column name be? "foo.xxx"?
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, December 10, 2019 10:37 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Result set column names
> 
> On 10 Dec 2019, at 5:13pm, Doug  wrote:
> 
> > Just to be clear: If I define a column in a table named "xxx",
> and I "select xxx from table", the report will show a column named
> "xxx" always. That's what the standard says, right? It's not
> implementation dependent, right?
> 
> Unless you have specified another column "AS xxx", or used "PRAGMA
> full_column_names".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Result set column names

[Doug]

Just to be clear: If I define a column in a table named "xxx", and I "select 
xxx from table", the report will show a column named "xxx" always. That's what 
the standard says, right? It's not implementation dependent, right?

CREATE TABLE foo(xxx)
INSERT INTO foo(xxx) VALUES(25)
SELECT xxx FROM foo
---|-
xxx 25

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Adrian Ho
> Sent: Tuesday, December 10, 2019 8:04 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Result set column names
> 
> On 9/12/19 3:45 PM, Graham Holden wrote:
> > Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch
>  wrote:
> >
> >> The SQL-92 standard actually says:
> >>
> >> |Syntax Rules
> >> |
> >> |9) Case:
> >> |
> >> |   b) If the i-th  in the 
> does not
> >> | specify an  and the  of
> that
> >> |  is a single , then
> the
> >> |  of the i-th column of the result is C.
> >> |
> > Presumably the third line of clause (b) of the standard SHOULD
> have
> > read: "is a single  C,", otherwise "is C" has
> > nothing to refer to.
> 
> C is actually defined in a preceding paragraph:
> 
>  5) Let C be some column. Let QS be the  specification>. Let
> DCi, for i ranging from 1 to the number of  column>s
> inclusively, be the i-th  simply
> contained in
> the  of QS. For all i, C is an underlying
> column
> of DCi, and of any  that identifies
> DCi, if
> and only if C is an underlying column of the  expression>
> of DCi, or C is an underlying column of the  expression>
> immediately contained in QS.
> 
> --
> Best Regards,
> Adrian
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

Jose, at least two things bothers me about part of your query:
IfNull('p006', Max(idate))
The first is that 'p006' is never null so the second part of the ifnull() will 
never be used.
The second thing is that the result of this ifnull() is to set the value of the 
"a" field. However, the domain of "a" is 'p001', 'p002',... It is not a date 
which would be returned by Max(idate).
I know you are trying to use side effects, but I don't understand ???
Doug
> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
> 
> 
> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> Jose Isaias Cabrera
> [clip]
> > > >
> > > > INSERT INTO t (a, b, c, d, e, idate)​
> > > > SELECT IfNull('p006', Max(idate)),​
> > > >IfNull(b, 1),​
> > > >IfNull(c, 2),​
> > > >'y',​
> > > >IfNull(e, 4),​
> > > >'2019-20-12'​
> > > >   FROM t​
> > > >  WHERE a = 'p006';​
> >
> > I think that you will never insert the first record with a query
> like this, since
> > the select returns 0 records of there are none in the database
> yet.
> 
> Well, it does...
> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> idate));
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-20-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> sqlite> select * from t;
> p001|1|2|n|4|2019-20-11
> sqlite>
> 
> And, since I put an uniqueness on a and idate, now these can not
> be repeated, so if I run the same command again,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> Error: UNIQUE constraint failed: t.a, t.idate
> sqlite>
> 
> I do not get a repeated record for 'p001' and 2019-02-11; But if
> they are different,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p002', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p002';
> sqlite> select * from t;
> p001|1|2|n|4|2019-02-11
> p002|1|2|n|4|2019-02-11
> 
> It'll work. Thanks.
> 
> josé
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

I'm really confused now. I don't understand the semantics of:
SELECT IfNull('p006', Max(idate)),
   IfNull(b, 1),
   IfNull(c, 2),
   'y',
   IfNull(e, 4),
   '2019-20-12'
  FROM t
  WHERE a = 'p006';

versus this:
SELECT (a,b,c,d,e,idate) from t where a = "p006"

Doesn't the where clause that cannot be satisfied in both cases guarantee that 
no rows will be selected, when there are no records in the database?
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
> 
> 
> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> Jose Isaias Cabrera
> [clip]
> > > >
> > > > INSERT INTO t (a, b, c, d, e, idate)​
> > > > SELECT IfNull('p006', Max(idate)),​
> > > >IfNull(b, 1),​
> > > >IfNull(c, 2),​
> > > >'y',​
> > > >IfNull(e, 4),​
> > > >'2019-20-12'​
> > > >   FROM t​
> > > >  WHERE a = 'p006';​
> >
> > I think that you will never insert the first record with a query
> like this, since
> > the select returns 0 records of there are none in the database
> yet.
> 
> Well, it does...
> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> idate));
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-20-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> sqlite> select * from t;
> p001|1|2|n|4|2019-20-11
> sqlite>
> 
> And, since I put an uniqueness on a and idate, now these can not
> be repeated, so if I run the same command again,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p001', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p001';
> Error: UNIQUE constraint failed: t.a, t.idate
> sqlite>
> 
> I do not get a repeated record for 'p001' and 2019-02-11; But if
> they are different,
> sqlite> INSERT INTO t
>...> SELECT IfNull('p002', Max(idate)),
>...>IfNull(b, 1),
>...>IfNull(c, 2),
>...>IfNull(d,'n'),
>...>IfNull(e, 4),
>...>'2019-02-11'
>...>  FROM t
>...>  WHERE a = 'p002';
> sqlite> select * from t;
> p001|1|2|n|4|2019-02-11
> p002|1|2|n|4|2019-02-11
> 
> It'll work. Thanks.
> 
> josé
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Saturday, November 16, 2019 10:43 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
> 
> 
> Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...​
> > 
> > One approach might be something like this:​
> > 
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT 'p006',​
> >Coalesce(b, 1),​
> >Coalesce(c, 2),​
> >'y',​
> >Coalesce(e, 4),​
> >'2019-20-12'​
> >   FROM (SELECT 1)​
> >   LEFT JOIN​
> >(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate
> DESC LIMIT 1);​
> > 
> > A slightly more succinct (but not universal) way:​
> > Note: see point 1 of​
> >
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_n
> on_aggregate_result_columns_that_are_not_in_the_group_by_clause​
> > 
> > INSERT INTO t (a, b, c, d, e, idate)​
> > SELECT IfNull('p006', Max(idate)),​
> >IfNull(b, 1),​
> >IfNull(c, 2),​
> >'y',​
> >IfNull(e, 4),​
> >'2019-20-12'​
> >   FROM t​
> >  WHERE a = 'p006';​

I think that you will never insert the first record with a query like this, 
since the select returns 0 records of there are none in the database yet.
Doug


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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Doug]

WRT Jose's original context, and just for my enlightment, what happens with the 
following:

insert into t (a, b, c, d, e, idate)
SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';

where p999 does not define a record? Is a new record inserted with values of 
a,b,c, and e null?

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Friday, November 15, 2019 6:21 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Adding a record to a table with one value
> change
> 
> 
> Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote...
> >
> > On 14 Nov 2019, at 10:27pm, Jake Thaw, on
> >
> > > Why not like this?
> > >
> > > insert into t (a, b, c, d, e, idate)
> > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001'
> ORDER BY
> > > idate desc limit 1;
> >
> > Dammit.  I thought I had tried this, and received a syntax
> error.  Now I see that it was because I missed out a comma.
> Thanks for the correction.
> 
> That just lets you know that you are a human. ;-)
> 
> > Good illustration of why responses should go to the list rather
> than direct to the OP.
> 
> Indeed.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Things you shouldn't assume when you store names

[Doug]

Au Contraire, Jens! In many local contexts you can normalize people's names. I 
was born in Kansas, USA. My parents filled out a birth certificate for me. It 
had a place on the form for first name, middle name, last name, and a suffix 
like II or III.

That birth certificate form determined that everyone born in Kansas (at that 
time), had a first, middle, and last name. There was no discussion of the 
matter. That's the way it was. The form led the way; people never thought about 
whether it was effective or not. Each newly-born child was given a first, 
middle, and last name.

Effective was irrelevant for that system. There was no option, no alternative. 
It simply was.

All systems are like that at each moment in time. They are what they are at any 
moment in time, and they force the users to behave the way the system wants 
them to behave. If you want to change the system and momentum is on your side, 
then immediately you have a new system - at that moment in time. It is composed 
of the old system and the momentum.

Back to names: just like the birth certificate, a system which assigns a name 
to you, actually coerces you to have that name, because within that system, you 
exist as that name. The "names" article is totally wrong when it says that each 
assumption is wrong. Each of those assumptions is correct, and I can find at 
least one system which makes each one correct. Within each system, the 
assumption works, and is valid.

My two cents...
Doug 

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Saturday, November 09, 2019 5:11 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Things you shouldn't assume when you store
> names
> 
> On Nov 9, 2019, at 1:09 PM, sky5w...@gmail.com wrote:
> >
> > In this case, data modelers hoping to save a column. arrggg.
> > It flies in the face of data normalization and pushes the
> problem down the
> > line.
> 
> But you _cannot_ normalize people’s names; that’s the exact point
> of that article. Anything you assume about the structure of a name
> will be wrong in some culture.
> 
> -Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] 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] https://www.sqlite.org/draft/gencol.html Typo

[Doug]

Thanks for the clarification.
I suggest you use your words of explanation in the document to make it clearer 
instead of a simple sentence.

> -Original Message-
> From: sqlite-users 
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 29, 2019 2:29 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> 
> On Tuesday, 29 October, 2019 12:25, Doug 
> wondered:
> 
> >The draft says "Nor may a generated column depend on the ROWID."
> 
> >If my table uses ROWID by default:
> 
> >CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
> 
> >where id is ROWID by default, is the generated column disallowed
> because
> >it has an implied dependency on ROWID?
> 
> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
> sqlite> insert into foo values (1,1),(2,1),(3,1);
> sqlite> select * from foo;
> 1|1|2
> 2|1|3
> 3|1|4
> sqlite> create table bar (a INTEGER, b AS (_rowid_ + a));
> Error: no such column: _rowid_
> sqlite> create table bar (a INTEGER, b AS (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (_rowid_+a));
> Error: no such column: _rowid_
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER,
> b as (_rowid_ + a));
> sqlite> insert into bar (a) values (1),(1),(1);
> sqlite> select * from bar;
> 1|1|2
> 2|1|3
> 3|1|4
> 
> So a generated column may not depend on the IMPLICITLY named rowid
> (by whatever magic you want to use), but it may depend on an
> EXPLICIT rowid (or alias).  In other words, only explicitly named
> columns are permitted.  If you EXPLICITY named the rowid alias to
> be _rowid_ it works without problem.  Same rule applies to FOREIGN
> KEY contraints which may only depend on EXPLICITLY named columns,
> not the IMPLICIT rowid.
> 
> --
> The fact that there's a Highway to Hell but only a Stairway to
> Heaven says a lot about anticipated traffic volume.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Doug]

The draft says "Nor may a generated column depend on the ROWID."

If my table uses ROWID by default:

CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));

where id is ROWID by default, is the generated column disallowed because it has 
an implied dependency on ROWID?

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 29, 2019 6:21 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> <https://www.sqlite.org/draft/gencol.html>
> 
> OMG.  Much welcomed feature.
> 
> > • Every table must have at least one non-generated column.
> 
> I greatly admire this restriction.
> 
> Can I suggest an addition to gencol.html ?  An explicit statement
> saying whether VIRTUAL and/or STORED columns can be used in CREATE
> INDEX.  In fact it's such an obvious question it might be useful
> to have the same note in lang_createindex.html .  Not to mention
> expridx.html .
> 
> On 29 Oct 2019, at 8:44am, Keith Medcalf 
> wrote:
> 
> > Only VIRTUAL columns can be added using ALTER TABLE.
> 
> I agree with your criticism but not your suggested alternative,
> because the command can be used to add other types of columns.  I
> suggest something like
> 
> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS
> … STORED columns
> 
> be used on /both/ pages.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Can SQLite do this in a single query?

[Doug]

Please explain one phrase in the select:
  total(1) over (partition by city_txt, mode_txt)
Is "total" a function and does "1" refer to city_txt?
I can't wrap my head around what's going on with this phrase.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 22, 2019 7:11 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Can SQLite do this in a single query?
> 
> CREATE TABLE employees(employee_id, city_id, mode_id);
> CREATE TABLE citys(city_id, city_txt);
> CREATE TABLE modes(mode_id, mode_txt);
> 
>   select city_txt,
>  mode_txt,
>  total(1) over (partition by city_txt, mode_txt) /
> total(1) over (partition by city_txt) as percentage
> from employees, citys, modes
>where employees.city_id == citys.city_id
>  and employees.mode_id == modes.mode_id
> group by city_txt, mode_txt
> group by city_txt, mode_txt;
> 
> You have to paint the output table yourself.
> 
> --
> The fact that there's a Highway to Hell but only a Stairway to
> Heaven says a lot about anticipated traffic volume.
> 
> >-Original Message-
> >From: sqlite-users 
> On
> >Behalf Of Winfried
> >Sent: Tuesday, 22 October, 2019 07:23
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: Re: [sqlite] Can SQLite do this in a single query?
> >
> >Yes, I forgot the column mode_id in the Employee's table.
> >
> >Thanks, I'll read up on the features SQLite's SELECT has to
> offer. At
> >worst,
> >I'll just run a simpler query multiple times.
> >
> >https://www.sqlite.org/lang_select.html
> >
> >
> >
> >--
> >Sent from: http://sqlite.1065341.n5.nabble.com/
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Last record

[Doug]

How about something like this that costs more to run:

Given a table T with columns A, B, C,..
BEGIN TRANSACTION
SELECT Count(*) AS Count [filter spec];
SELECT A,B,C,... [filter spec];
ROLLBACK or COMMIT

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 15, 2019 8:35 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Last record
> 
> On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch>
> wrote:
> 
> > how could I know if I am reading the last record with
> > sqlite  (sqlite3_step)?
> 
> Sorry, there's no way to do that for some arbitrary SELECT.
> Because SQLite itself may not know.
> 
> SQLite does not always process your query and store all the
> results in memory.  If there's an ideal index for your query, each
> call to _step() just one more row.  SQLite itself doesn't know it
> has reached the end until it gets an error because it runs off the
> end of the index.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Date time input

[Doug]

I like SQLoaded!
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jose Isaias Cabrera
> Sent: Tuesday, October 08, 2019 12:15 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Date time input
> 
> 
> James K. Lowden, on Tuesday, October 8, 2019 02:39 PM, wrote...
> >
> > On Tue, 8 Oct 2019 09:06:24 -0700
> > Jens Alfke, on
> >
> > > I think the idea of a semi-official ?SQLite++? has been
> floated here
> > > before
> >
> > OK, but it needs a better name.  What better place than here to
> debate
> > that?  ;-)
> 
> SQLiteNoMore
> 
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Fastest way to SELECT on a set of keys?

[Doug]

I blows me away that you are able to produce such things as this at the drop of 
a hat!
Thanks for your insights and ingenuity and completeness!
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Keith Medcalf
> Sent: Friday, September 13, 2019 1:30 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Fastest way to SELECT on a set of keys?
> 
> 
> That depends greatly on the overhead you have for executing each
> select statement.  So I wrote a little test that uses my
> customized apsw library from Python 3.  It also works using the
> as-distributed sqlite3 wrapper (except for the carray interface,
> which requires my customized apsw to be able to build and pass the
> object).  The overheads associated with each method are included
> in the elapsed time.  The only thing that is clear is that where
> the overhead of executing each select is significant it is clearly
> better to execute fewer of them.
> 
> >st 1
> Method 1: Retrieve Individual Row 00:00:00.103779
> Method 2: Individual Row (Sorted) 00:00:00.109945
> Method 3: using dynamic in00:00:00.137431
> Method 4: using sorted dynamic in 00:00:00.110824
> Method 5: using in carray 00:00:00.171037
> Method 5: using in carray sorted  00:00:00.165992
> 
> >st 10
> Method 1: Retrieve Individual Row 00:00:01.023160
> Method 2: Individual Row (Sorted) 00:00:01.187180
> Method 3: using dynamic in00:00:00.159182
> Method 4: using sorted dynamic in 00:00:00.175053
> Method 5: using in carray 00:00:00.192246
> Method 5: using in carray sorted  00:00:00.154138
> 
> >st 100
> Method 1: Retrieve Individual Row 00:00:10.543783
> Method 2: Individual Row (Sorted) 00:00:10.305251
> Method 3: using dynamic in00:00:00.196502
> Method 4: using sorted dynamic in 00:00:00.176414
> Method 5: using in carray 00:00:00.203340
> Method 5: using in carray sorted  00:00:00.191570
> 
> >st 1000
> Method 1: Retrieve Individual Row 00:01:40.558009
> Method 2: Individual Row (Sorted) 00:01:42.051622
> Method 3: using dynamic in00:00:00.246542
> Method 4: using sorted dynamic in 00:00:00.238268
> Method 5: using in carray 00:00:00.249394
> Method 5: using in carray sorted  00:00:00.243244
> 
> >st 1
> Method 3: using dynamic in00:00:00.277059
> Method 4: using sorted dynamic in 00:00:00.296931
> Method 5: using in carray 00:00:00.297005
> Method 5: using in carray sorted  00:00:00.322317
> 
> >st 10
> Method 3: using dynamic in00:00:00.761905
> Method 4: using sorted dynamic in 00:00:00.765864
> Method 5: using in carray 00:00:00.757057
> Method 5: using in carray sorted  00:00:00.69
> 
> >st 100
> Method 3: using dynamic in00:00:04.129529
> Method 4: using sorted dynamic in 00:00:04.301129
> Method 5: using in carray 00:00:04.114985
> Method 5: using in carray sorted  00:00:04.417498
> 
> 
> And the code:
> 
> #! python3
> 
> import apsw
> import datetime
> import random
> import sqlite3
> import sys
> import time
> 
> datasize = 100
> rows = int(sys.argv[1])
> 
> elapsed = lambda st, et: datetime.datetime.utcfromtimestamp((et -
> st)).time()
> 
> db = apsw.Connection('')
> #db = sqlite3.connect('', isolation_level=None)
> 
> db.executescript('''
> create table x
> (
> id  integer primay key,
> datablob
> );
> insert into x select value, randomblob(500) from generate_series
> where start=1 and stop=%d;
> ''' % (datasize,))
> 
> rowset = [random.randint(1, datasize) for i in range(rows)]
> 
> if rows <= 1000:
> print('Method 1: Retrieve Individual Row', end=' ',
> flush=True)
> st = time.time()
> db.executescript('BEGIN')
> for key in rowset:
> for row in db.execute('select * from x where id=?',
> (key,)):
> pass
> db.commit()
> print(elapsed(st, time.time()))
> 
> print('Method 2: Individual Row (Sorted)', end=' ',
> flush=True)
> st = time.time()
> db.executescript('BEGIN')
> for key in sorted(rowset):
> for row in db.execute('select * from x where id=?',
> (key,)):
> pass
> db.commit()
> print(elapsed(st, time.time()))
> 
> print('Method 3: using dynamic in   ', end=' ', flush=True)
> st = time.time()
> for row in db.execute('select * from x where id in (' +
> ','.join(map(str, rowset)) + ')'):
> pass
> print(elapsed(st, time.time()))
> 
> print('Method 4: using sorted dynamic in', end=' ', flush=True)
> st = time.time()
> for row in db.execute('select * from x where id in (' +
> ','.join(map(s

Re: [sqlite] what's wrong with this trigger

[Doug]

What works, please? I saw no answer.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Rael Bauer
> Sent: Monday, September 09, 2019 7:01 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] what's wrong with this trigger
> 
> Thanks, that works.
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] [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]

It occurs to me that if there are sqlite applications today requiring the 
functionality of -0.0, they have implemented it so that it works for their 
application. Perhaps, they accomplished that by adding a flag bit or by some 
other means.

So if you do nothing about -0.0, you will break no existing applications. 
Granted, you are not adhering to IEEE-745.

However, ...

If you can find anyone who has implemented such an application (using -0.0), 
you could find out how they implemented it. To make sqlite adhere to IEEE-745, 
generate an sqlite application note suggesting possible ways an application 
might implement the functionality.

--Doug

___
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] sqlite-users Digest, Vol 138, Issue 4

[Doug]

I tried a few more functions:

select songfile_id, dancename,hex(dancename), hex("Waltz"), 
length(dancename),length("Waltz"),
nullif(dancename,"Waltz"),instr(dancename,"Waltz"),dancegroupname from 
songfiletable where songfile_id=377;
377|Waltz|57616C747A|57616C747A|5|5|Waltz|1|American Smooth

Notice that nullif() failed to find the field and the literal equal. That is 
consistent with the failure.

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: sqlite-users Digest, Vol 138, Issue 4
> 
> Send sqlite-users mailing list submissions to
>   sqlite-users@mailinglists.sqlite.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
>   http://mailinglists.sqlite.org/cgi-
> bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
>   sqlite-users-requ...@mailinglists.sqlite.org
> 
> You can reach the person managing the list at
>   sqlite-users-ow...@mailinglists.sqlite.org
> 
> When replying, please edit your Subject line so it is more
> specific
> than "Re: Contents of sqlite-users digest..."
> 
> 
> Today's Topics:
> 
>1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
>2. select * where abc like "xxx" works,...where abc='xxx'
> fails
>   (Doug)
>3. Re: select * where abc like "xxx" works, ...where abc='xxx'
>   fails (Simon Slavin)
>4. Re: select * where abc like "xxx" works,...where abc='xxx'
>   fails (Keith Medcalf)
>5. Re: Bug in sqlite3.c (bhandari_nikhil)
> 
> 
> --
> 
> 
> Message: 1
> Date: Sun, 2 Jun 2019 11:15:35 -0300
> From: Carlos Eduardo de Paula 
> To: dcla...@blastwave.org,  "sqlite-users@mailinglists.sqlite.org"
>   
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
>il.com>
> Content-Type: text/plain; charset="UTF-8"
> 
> I'm on a SiFive Unleashed board running Debian Linux on Kernel
> 4.19.
> --
> Sent from IPhone
> 
> 
> --
> 
> Message: 2
> Date: Mon, 3 Jun 2019 16:46:47 -0700
> From: "Doug" 
> To: 
> Subject: [sqlite] select * where abc like "xxx" works,
>   ...where
>   abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> Content-Type: text/plain; charset="us-ascii"
> 
> I am using Sqlite under QT 5.12.0.
> 
> 
> 
> Why does the "=" query fail and the "like" query work? There are
> no
> wildcards involved.
> 
> 
> 
> I create the table this way:
> 
> 
> 
> QString sqlcreate = QLatin1String(
> 
>   "CREATE TABLE songfiletable ("
> 
> "songfile_id INTEGER PRIMARY KEY,"
> 
> "dancename TEXT"
> 
>   ");"  );
> 
> 
> 
> I populated the table this way with
> dancename=QLatin1String("Waltz"):
> 
> 
> 
> QString sqlinsert = QLatin1String(
> 
> "INSERT INTO songfiletable ("
> 
> "dancename"
> 
> ") VALUES(?);");
> 
>   queryinsert.prepare(sqlinsert);
> 
>   queryinsert.addBindValue(dancename.toUtf8());
> 
> 
> 
> The query that works for dancename="Waltz" is this (with 'like'):
> 
>     QByteArray dn = d->dance_name.toUtf8();
> 
> QByteArray filter = "(dancename like '" + dn + "')";
> 
> 
> 
> The query that fails for dancename="Waltz" is this:
> 
> QByteArray dn = d->dance_name.toUtf8();
> 
> QByteArray filter = "(dancename='" + dn + "')";
> 
> 
> 
> The behavior is the same in Sqlite command line. There are no
> wildcards involved.
> 
> 
> 
> I have simplified the table and the queries to a single column for
> this message. The actual table has 22 columns.
> 
> 
> 
> Why does the "=" query fail and the "like" query work?
> 
> 
> 
> Regards, Doug
> 
> 
> 
> 
> 
> 
> 
> --
> 
> Message: 3
> Date: Tue, 4 Jun 2019 00:55:45 +0100
> From: Simon Slavin 
> To: SQLite mailing list 
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
>   abc='xxx' fails
> Message-ID: <73ebbe3c-dbc8-4ea2-a055-e4c160899...@bigfraud.org>
> Content-Type: text/plain; 

Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4

[Doug]

Sqlite version is 3.27.1.

I have no indexes defined.

Here is a query I just ran from bash:

select songfile_id,dancename,dancegroupname from songfiletable where 
songfile_id=377;
377|Waltz|American Smooth

... and another:

select songfile_id,dancename,dancegroupname from songfiletable where dancename 
like "Waltz";
377|Waltz|American Smooth
388|Waltz|American Smooth
459|Waltz|American Smooth
647|Waltz|American Smooth
827|Waltz|American Smooth
873|Waltz|American Smooth
896|Waltz|American Smooth
1156|Waltz|American Smooth
1157|Waltz|American Smooth
1158|Waltz|American Smooth
1159|Waltz|American Smooth
1160|Waltz|American Smooth
1161|Waltz|American Smooth
1162|Waltz|American Smooth
1164|Waltz|American Smooth
1167|Waltz|American Smooth

... and yet another:

select songfile_id,dancename,dancegroupname from songfiletable where dancename 
= "Waltz";
sqlite>

Nothing selected using "=".

Also a hex dump:

select songfile_id,dancename,hex(dancename),dancegroupname from songfiletable 
where songfile_id=377;
377|Waltz|57616C747A|American Smooth

Clearly what I think is in the database is there. Is there supposed to be a 
trailing null in the database for text fields that is/is not in play here?

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: sqlite-users Digest, Vol 138, Issue 4
> 
> Send sqlite-users mailing list submissions to
>   sqlite-users@mailinglists.sqlite.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
>   http://mailinglists.sqlite.org/cgi-
> bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
>   sqlite-users-requ...@mailinglists.sqlite.org
> 
> You can reach the person managing the list at
>   sqlite-users-ow...@mailinglists.sqlite.org
> 
> When replying, please edit your Subject line so it is more
> specific
> than "Re: Contents of sqlite-users digest..."
> 
> 
> Today's Topics:
> 
>1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
>2. select * where abc like "xxx" works,...where abc='xxx'
> fails
>   (Doug)
>3. Re: select * where abc like "xxx" works, ...where abc='xxx'
>   fails (Simon Slavin)
>4. Re: select * where abc like "xxx" works,...where abc='xxx'
>   fails (Keith Medcalf)
>5. Re: Bug in sqlite3.c (bhandari_nikhil)
> 
> 
> --
> 
> 
> Message: 1
> Date: Sun, 2 Jun 2019 11:15:35 -0300
> From: Carlos Eduardo de Paula 
> To: dcla...@blastwave.org,  "sqlite-users@mailinglists.sqlite.org"
>   
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
>il.com>
> Content-Type: text/plain; charset="UTF-8"
> 
> I'm on a SiFive Unleashed board running Debian Linux on Kernel
> 4.19.
> --
> Sent from IPhone
> 
> 
> --
> 
> Message: 2
> Date: Mon, 3 Jun 2019 16:46:47 -0700
> From: "Doug" 
> To: 
> Subject: [sqlite] select * where abc like "xxx" works,
>   ...where
>   abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> Content-Type: text/plain; charset="us-ascii"
> 
> I am using Sqlite under QT 5.12.0.
> 
> 
> 
> Why does the "=" query fail and the "like" query work? There are
> no
> wildcards involved.
> 
> 
> 
> I create the table this way:
> 
> 
> 
> QString sqlcreate = QLatin1String(
> 
>   "CREATE TABLE songfiletable ("
> 
> "songfile_id INTEGER PRIMARY KEY,"
> 
> "dancename TEXT"
> 
>   ");"  );
> 
> 
> 
> I populated the table this way with
> dancename=QLatin1String("Waltz"):
> 
> 
> 
> QString sqlinsert = QLatin1String(
> 
> "INSERT INTO songfiletable ("
> 
> "dancename"
> 
> ") VALUES(?);");
> 
>   queryinsert.prepare(sqlinsert);
> 
>   queryinsert.addBindValue(dancename.toUtf8());
> 
> 
> 
> The query that works for dancename="Waltz" is this (with 'like'):
> 
> QByteArray dn = d->dance_name.toUtf8();
> 
> QByteArray filter = "(dancename like '" + dn + "')";
> 
> 
> 
> The query that fails for dancename="Waltz" is this:
> 
> QByteArray dn = d->dance_name.toUtf8();
> 
> QByteArray filter = "(dancename='" + dn + "')";
> 
> 
> 
> T

[sqlite] select * where abc like "xxx" works, ...where abc='xxx' fails

[Doug]

I am using Sqlite under QT 5.12.0. 

 

Why does the "=" query fail and the "like" query work? There are no
wildcards involved.

 

I create the table this way:

 

QString sqlcreate = QLatin1String(

  "CREATE TABLE songfiletable ("

"songfile_id INTEGER PRIMARY KEY,"

"dancename TEXT"

  ");"  );

 

I populated the table this way with dancename=QLatin1String("Waltz"):

 

QString sqlinsert = QLatin1String(

"INSERT INTO songfiletable ("

"dancename"

") VALUES(?);");

  queryinsert.prepare(sqlinsert);

  queryinsert.addBindValue(dancename.toUtf8());

 

The query that works for dancename="Waltz" is this (with 'like'):

QByteArray dn = d->dance_name.toUtf8();

QByteArray filter = "(dancename like '" + dn + "')";

 

The query that fails for dancename="Waltz" is this:

QByteArray dn = d->dance_name.toUtf8();

QByteArray filter = "(dancename='" + dn + "')";

 

The behavior is the same in Sqlite command line. There are no
wildcards involved.

 

I have simplified the table and the queries to a single column for
this message. The actual table has 22 columns.

 

Why does the "=" query fail and the "like" query work? 

 

Regards, Doug

 

 

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

Re: [sqlite] JDBC driver experience

[Doug White]

This writing is 4/22/2018.
I have been playing with the latest connect/j for android java downloaded
from Oracle's web site and have had no luck getting it to connect to the
latest Community version of MySQL on Windows 10 using Oracles example java
code... then I found an example that someone posted that does all of the
java calls from within a java thread and it works perfectly on several
types of SQL calls that I added to the code. As soon as I try to
incorporate the same code outside of a thread it won't connect. Has anyone
else had similar problems, solutions or ideas about what may be causing the
problem.  A working android studio code example would be wonderful to
analyze.
Doug White
dglsn...@gmail.com

On Apr 22, 2018 12:42 PM, "Seiji Amalai" <seijiama...@gmail.com> wrote:

no


On Wed, Apr 18, 2018 at 8:34 PM, Richard Hipp <d...@sqlite.org> wrote:

> Are you using SQLite with JDBC?  If so, can you please answer a few
> questions below?
>
> You can post on this mailing list or send your reply directly to me.
>
> 1. Which JDBC are you using?
> 2. For how long have you been using it?
> 3. What issues you had with this driver?
> 4. What advise do you have for avoiding problems in this driver?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
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] Ordering a GROUP BY, or other concatenating tricks?

[Doug Nebeker]

Is it just a matter of using sqlite3_create_function to register a function 
that guarantees it will concatenate in the order rows are received?  Would that 
guarantee that your example works, or is order no longer guaranteed once they 
leave the inner select?

SELECT group_concat(LineText, '\n') FROM
(SELECT LineText FROM DocLines
WHERE DocID = 10
ORDER BY LineIndex)

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

[sqlite] Ordering a GROUP BY, or other concatenating tricks?

[Doug Nebeker]

Imagine a table that holds individual lines of text documents:

CREATE TABLE DocLines
(
DocID INTEGER,
LineIndex INTEGER,
LineText TEXT
);

INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (1, 1, 'Mary had a 
little lamb');
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (1, 2, 'It had white 
fleece');

//inserted in reverse order so insertion order doesn't happen to make the 
SELECT work

INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (2, 2, 'Humpty dumpty 
had a great fall');
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (2, 1, 'Humpty dumpty 
sat on a wall');

How can I select a document and get the complete sorted text back in a single 
row (so I can do a JOIN on a different table with additional information)?  

The below happens to work fine for DocID 1 (because of insertion order), but 
fails for DocID 2.

SELECT group_concat(LineText, '\n') 
FROM DocLines 
WHERE DocID = 1
GROUP BY DocID;

Is there any way to order a GROUP BY, or some other way to concatenate text?


___
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] SQLite - how to get number of active connection?

[Doug Nebeker]

This isn't exactly the same, but you can find out what other applications have 
a file open (SQLite database file in this case) using the following Windows 
APIs:

RmRegisterResources
RmGetList

That's what I use to help diagnose locking issues.

Doug

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bhavesh Patel
Sent: Friday, September 9, 2016 7:47 AM
To: SQLite mailing list
Subject: [sqlite] SQLite - how to get number of active connection?

I want to run certain clean-up tasks on the SQLite database only and only when 
there is only 1 connection(self) to SQLite.

I do not want to lock the database during the cleanup process.

The main application(s) using the SQLite DB is given 1st preference. the 
cleanup application is executed at low priority and will stop doing the clean 
up of there is any database connection other than the cleanup application.

The cleanup tool will connect SQLite check number of active connection, if 
found one will do some clean up tasks like deleted few tables, cleaning up 
tables etc.

I don't want to manage a flag system and looking for coming out of the box from 
sqlite or may be from windows API.


can use Process 
Explorer<http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx>   but 
how to get it programmatically . 
http://stackoverflow.com/questions/12138260/how-can-i-count-the-number-of-open-connections-in-an-sqlite-database


Is there any way to know a number of active connection SQLite has ?

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

Re: [sqlite] Failed to install Microsoft Visual C++ Runtime

[Doug Nebeker]

Our experience might not apply completely, but we just went through moving to 
VS2015, but because of all of the vcredist issues with the new version 
(involving the UCRT), we had to go back to VS2012.  Note that installing 
vcredist:

“To install this update, you must have April 2014 update rollup for Windows RT 
8.1, Windows 8.1, and Windows Server 2012 R2 (2919355) installed in Windows 8.1 
or Windows Server 2012 R2. Or, install Service Pack 1 for Windows 7 or Windows 
Server 2008 R2. Or, install Service Pack 2 for Windows Vista and for Windows 
Server 2008.”

I believe update KB3118401 is also needed.  Look at KB2919355.  It's a mess :(

Once you can get it and all its dependencies installed, everything works great. 
 But you often need to do more than just install the vcredist as had always 
been the case in the past.  We even did a technical support case with MS.


Doug



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Joe Mistachkin
Sent: Sunday, July 31, 2016 6:42 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Failed to install Microsoft Visual C++ Runtime


Zehev Spitz wrote:
> 
> > Failed to install Microsoft Visual C++ Runteim:
vcredist_x86_2015_VSU2.exe,
> Another version of this product is already installed. Installation of 
> this version cannot conitnue. To configure or remove the existing 
> version of
this
> product, use Add/Remove Programs on the Control Panel.
> 

Do you have the setup logs handy for this error?  They could help us to resolve 
the issue in the future setup packages.  The setup itself already has a check 
for the error codes that are documented to be used for this particular 
situation; however, there must (?) be (at least) one that is new (to Visual 
Studio 2015).

--
Joe Mistachkin

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

[sqlite] Database corruption question

[Doug Nebeker]

Been using SQLite for a long time and a huge fan.  We occasionally see database 
corruption on a local NTFS Windows drive and I've been trying to figure it out.

I finally have some logs from the SQLITE_CONFIG_LOG callback that may be of 
help:

(11) database corruption at line 78267 of [3d862f207e]
statement aborts at 29: [DELETE FROM StatData WHERE StatID IN (SELECT StatID 
FROM Statistic WHERE OwnerType IN (4) AND 
OwningComputer='f7ab745b-1aa7-4159-bbf7-b0b4d1262804') AND Date < 1350715577;]

This is for version 3.11, and the line is for a OP_NotExists case.
There was no power outage or other OS problem, this was not a restored database 
but had been in use for at least 12 hours.  It was not using WAL, and there is 
only one process using the database file (though multiple threads, but they 
each have their own database handle).

I've been through https://www.sqlite.org/howtocorrupt.html a number of times 
over the years :)

Would it be possible for a file scanning process (anti-virus, backup, etc) to 
grab hold of a database file at just the right moment, momentarily blocking a 
write or delete, and causing corruption?

There is a greater chance that this is my bug or environmental than being in 
SQLite, so I'm looking for anything I can do to decrease these occurrences.

Thanks for any input.

Doug
___
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] Article about pointer abuse in SQLite

[Doug Nebeker]

> For obvious security reasons all allocations from the Operating System are 
> pre-initialized to 0x00.  

Time to bash Windows, but according to the docs for HeapAlloc, memory is not 
automatically initialized to 0

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366597(v=vs.85).aspx

This fits with my experience as well.

Doug



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
Sent: Tuesday, March 22, 2016 8:41 PM
To: SQLite mailing list
Subject: Re: [sqlite] Article about pointer abuse in SQLite


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

The initialization of memory to non-0x00 is a compiler function.

For obvious security reasons all allocations from the Operating System are 
pre-initialized to 0x00.  This is so that your program cannot request a big 
hunk of virtual memory which is full of a predecessor process data and then 
proceed to search it for nifty things like previously used private keys, 
userids, passwords, and so forth.  Such behaviour is required for any Operating 
Systems to obtain any security certification level whatsoever. 




___
sqlite-users mailing list
sqlite-users at 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] Rare database corruption - does this help?

[Doug Nebeker]

I'm hoping this might be of help to contribute to SQLite's robustness.

We've got thousands of SQLite installations and they almost always work 
flawlessly.  Every once in a while we get a corruption error and I finally have 
a log that catches it.

SQLite has been in use in the project since version 3.0.3, so 10 years?  Over 
the years we've poured over:
https://www.sqlite.org/howtocorrupt.html

The environment is Windows Server 2012 R2, with the database on a local NTFS 
drive.

The process had been running, and the database in constant use, for at least 90 
minutes when the problem occurred.  The error callback from 
sqlite3_config(SQLITE_CONFIG_LOG) reports:

(11) database corruption at line 67560 of [2ef4f3a5b1]

There were no previous database errors that my logging captured.

I have the database and can provide it if it helps. I'm pretty sure this is 
with SQLite 3.8.10.2.

Database usage:
1. Only one thread at a time is ever using a connection handle, though there 
are multiple threads and multiple database handles (each thread has it's own).  
Only one process accesses the database file.
2. Not using shared cache
3. Not using WAL
4. PRAGMA synchronous=1
5. PRAGMA temp_store=1
6. The database files might possibly be read by an external backup/anti-virus 
app, but nothing was written to, and journals shouldn't be getting deleted
7. Server was not rebooted/crash/lost power

sqlite> pragma integrity_check;
*** in database main ***
Page 145387: Rowid 30174458 out of order (max larger than parent max of 0)
Page 137945: Rowid 29568549 out of order (max larger than parent max of 0)
row 14154506 missing from index Ind_StatData_Date
row 14154507 missing from index Ind_StatData_Date
row 14154508 missing from index Ind_StatData_Date
row 14154509 missing from index Ind_StatData_Date
row 14154510 missing from index Ind_StatData_Date
row 14154511 missing from index Ind_StatData_Date
row 14154512 missing from index Ind_StatData_Date
row 14154513 missing from index Ind_StatData_Date
row 14154514 missing from index Ind_StatData_Date
row 14154515 missing from index Ind_StatData_Date
row 14154516 missing from index Ind_StatData_Date
row 14154517 missing from index Ind_StatData_Date
row 14154518 missing from index Ind_StatData_Date
row 14154519 missing from index Ind_StatData_Date
row 14154520 missing from index Ind_StatData_Date
row 14154521 missing from index Ind_StatData_Date
row 14154522 missing from index Ind_StatData_Date
row 14154523 missing from index Ind_StatData_Date
row 14154524 missing from index Ind_StatData_Date
row 14154525 missing from index Ind_StatData_Date
row 14154526 missing from index Ind_StatData_Date
row 14154527 missing from index Ind_StatData_Date
row 14154528 missing from index Ind_StatData_Date
row 14154529 missing from index Ind_StatData_Date
row 14154530 missing from index Ind_StatData_Date
row 14154531 missing from index Ind_StatData_Date
row 14154532 missing from index Ind_StatData_Date
row 14154533 missing from index Ind_StatData_Date
row 14154534 missing from index Ind_StatData_Date
row 14154535 missing from index Ind_StatData_Date
row 14154536 missing from index Ind_StatData_Date
row 14154537 missing from index Ind_StatData_Date
row 14154538 missing from index Ind_StatData_Date
row 14154539 missing from index Ind_StatData_Date
row 14154540 missing from index Ind_StatData_Date
row 14154541 missing from index Ind_StatData_Date
row 14154542 missing from index Ind_StatData_Date
row 14154543 missing from index Ind_StatData_Date
row 14154544 missing from index Ind_StatData_Date
row 14154545 missing from index Ind_StatData_Date
row 14154546 missing from index Ind_StatData_Date
row 14154547 missing from index Ind_StatData_Date
row 14154548 missing from index Ind_StatData_Date
row 14154549 missing from index Ind_StatData_Date
row 14154550 missing from index Ind_StatData_Date
row 14154551 missing from index Ind_StatData_Date
row 14154552 missing from index Ind_StatData_Date
row 14154553 missing from index Ind_StatData_Date
row 14154554 missing from index Ind_StatData_Date
row 14154555 missing from index Ind_StatData_Date
row 14154556 missing from index Ind_StatData_Date
row 14154557 missing from index Ind_StatData_Date
row 14154558 missing from index Ind_StatData_Date
row 14154559 missing from index Ind_StatData_Date
row 14154560 missing from index Ind_StatData_Date
row 14154561 missing from index Ind_StatData_Date
row 14154562 missing from index Ind_StatData_Date
row 14154563 missing from index Ind_StatData_Date
row 14154564 missing from index Ind_StatData_Date
row 14154565 missing from index Ind_StatData_Date
row 14154566 missing from index Ind_StatData_Date
row 14154567 missing from index Ind_StatData_Date
row 14154568 missing from index Ind_StatData_Date
row 14154569 missing from index Ind_StatData_Date
row 14154570 missing from index Ind_StatData_Date
row 14154571 missing from index Ind_StatData_Date
row 14154572 missing from index Ind_StatData_Date
row 14154573 missing from index 

[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
>

[sqlite] PRAGMA Synchronous safety

[Doug Nebeker]

Thank you for your responses Simon and Richard.  To your questions:

> Are you using any PRAGMAs apart from "PRAGMA synchronous" ?

PRAGMA temp_store=1 (file)
PRAGMA cache_size=2000
PRAGMA page_size=4096
sqlite3_soft_heap_limit( 1GB )

Those are the only non-default settings.

> Are you testing the result codes of /all/ your sqlite3_ calls

Yes.  But I usually don't see the full log to see if something has happened 
earlier.  I'll have to track down a log and see if the corruption error is the 
first one that happens.

> are you replacing it with a known good one before continuing

Yes.  Once we get the report, the user has to delete the file and start fresh 
with an empty database.

> Depends on whether you are using WAL mode or not.

I am not using WAL.

Doug


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, March 2, 2015 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA Synchronous safety


On 2 Mar 2015, at 4:45pm, Doug Nebeker  wrote:

> 1. Is setting synchronous to FULL necessary to prevent these corruption 
> problems?

You should not be getting this corruption problem, so I don't know whether FULL 
would cure it.

> 2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?

Sorry but it's too hard to predict 'much' since the amount of time taken varies 
with different versions of the OS, width of the data bus, disk format, and 
other such things.  All you can do is try it on your own specific setup.

In the meantime ...

Are you using any PRAGMAs apart from "PRAGMA synchronous" ?

Are you testing the result codes of /all/ your sqlite3_ calls to check that 
they're SQLITE_OK ?  Sometimes it's not the expected call which causes the 
corruption, or which first returns an error because the database is corrupt.

Once the database is reported as corrupt, are you replacing it with a known 
good one before continuing ?  Corruption is not automatically fixed and once a 
database is corrupt it will remain corrupt until something is done about it.

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

[sqlite] PRAGMA Synchronous safety

[Doug Nebeker]

In the documents, using PRAGMA synchronous 1 (NORMAL) is supposed to be safe 
unless there is an OS crash or power failure.

I've had a few customers (about one a month, out of thousands) that is getting 
the "database disk image is malformed" ExtErr=11 error with the NORMAL pragma, 
and they claim there was no crash, power failure or anything else unusual.  The 
database _does_ get hammered under normal operation.

There are two separate sqlite3* handles to the database.  Each one is protected 
by a critical section, so there is never more than one thread accessing a 
handle at a time.  The database is on a local NTFS drive.

Two questions:

1. Is setting synchronous to FULL necessary to prevent these corruption 
problems?

2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?

Thanks
Doug

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] Best Practice: Storing Dates

[Doug Nebeker]

Whatever format you choose to store it in, I highly recommend storing the UTC 
time.  It might be a little more work, but:

1. your program can display the correct local time, even if the 
database/app/user is in/changes to another timezone
2. you won't have to deal with seeing two 1:30am on the day that day light 
savings kicks in


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Chris Keilitz
Sent: Wednesday, January 14, 2015 7:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Best Practice: Storing Dates

I'm a new sqlite user and new to this mailing list. I hope this question is 
appropriate.

I am writing an application that needs to track a timestamp - date + time down 
to the seconds. I'd like to store the date/time in a standard, efficient, 
usable, portable format.  I have looked over the sqlite date/time functions / 
data types and the ISO 8601 standard and have landed on these two options:

1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as an 
INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

Since sqlite and most RDMS implementations have functions to convert to and 
from both options and using a LONG should allow the date/time to function way 
past 2038, it seems it comes down to how many bytes it takes to store the 
timestamp and how fast are the conversion routines. The application I'm writing 
won't push any performance boundaries and likely won't need to overly worry 
about storage.  I just want to make the right call on data type and format and 
learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I 
apologize.

Thanks!

Chris
___
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] 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] Primary Key without DataAnnotation for Windows Runtime

[Doug McDonald]

We achieved something similar by doing the following:

Creating a seperate assembly for our entities
Define the primary key attribute in that assembly, but inside the SQLite
namespace.
Comment out the primary key attribute in the SQLite/net project to avoid
re-defining it.
We were then able to share this assembly between projects, without being
tied to SQLite explicitely, but when we include SQLite in a project, the
primary key attribute is included.

Maybe that's of some use as to an approach.


On Mon, Mar 31, 2014 at 3:52 PM, Benedikt Neuhold <bened...@neuhold.pro>wrote:

> Hi,
>
>
>
> we are building an App for Windows 8 and want to use sqlite as local DB. We
> want to separate our Models from our DB Engine. So we need a way to set the
> Primary Key without DataAnnotation. Is there a way?
>
>
>
> Thanks a lot!
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Doug McDonald

BSc(Hons) | MCTS | MBCS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation error: sqlite3_mutex_try

[Doug Nebeker]

The documentation says that sqlite3_mutex_try will always return SQLITE_BUSY  
for "some systems (for example, Windows 95)".

That's not quite accurate from what I see in the latest implementation of 
winMutexTry.  It will ALWAYS return SQLITE_BUSY for any Windows usage, making 
the existence of the function somewhat misleading in this case.




___
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] To BEGIN or not to BEGIN. That is the question...

[Doug Nebeker]

Igor is naturally correct.  One additional thing to keep in mind - the commit 
phase of a transaction is where a lot of work gets done (meaning slow disk 
access).  So if you have a lot of INSERTs or DELETEs to do, doing many within a 
transaction will give you better performance.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 28, 2013 5:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] To BEGIN or not to BEGIN. That is the question...

On 8/28/2013 6:28 PM, jose isaias cabrera wrote:
> I know that if I am doing INSERTs and such, I need to,
>
> BEGIN;
>  INSERT...
> END;

No, you don't need to. You can, if you want to, but there's no reason to have 
to.

> But, do I need to begin if I am going to create a table? ie.
>
> BEGIN;
>  CREATE TABLE tableName
>  (
>  JobID integer primary key, SubProjID integer, ProjID integer
>  );
> END;

Same here.

> Also, what other commands should I wrap with BEGINs and ENDs?

BEGIN starts an explicit transaction; END commits the same. You need an 
explicit transaction if you want to execute two or more statements atomically, 
so that either they all succeed, or one fails and then the database is rolled 
back to the original state.

If you don't start a transaction explicitly, then each statement is implicitly 
wrapped in its own transaction.
--
Igor Tandetnik

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

Re: [sqlite] CREATE INDEX and column order

[Doug Nebeker]

Thanks Simon, that makes a lot of sense.  Does the order of columns in a WHERE 
clause matter, or will the query optimizer look at them as a set and find the 
best index?  (ignoring all the special cases)



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, August 27, 2013 7:29 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CREATE INDEX and column order


On 27 Aug 2013, at 1:07pm, Doug Nebeker <ad...@poweradmin.com> wrote:

> I was reading about the new query planner and came across a few references to 
> that idea that the left most columns in the index definition should be the 
> most unique (as far as values in the column are concerned).
> 
> Is that correct?  In my case, many tables have a timestamp column, and I've 
> been using that as my right-most column, but it seems it would be a great 
> candidate to be switched.

When using an index, SQL has to work from the most significant end -- the left 
-- to the least significant end -- the right.  For instance, suppose you have a 
phone book

CREATE TABLE phonebook (firstname TEXT, surname TEXT, phonenumber TEXT) CREATE 
INDEX psf ON phonebook (surname, firstname)

This index is useless for looking someone up by their firstname, because it has 
everyone listed in surname order:

Abelson, David
Abelson, Joan
Smith, David
Smith, Martine
Smith, Tom

If you wanted to look up all the 'Martines' you'd just have to look through the 
whole index anyway.  You might as well scan the original table. [1]

Simon.

[1] Yes, many picky details about this but I'm simplifying for the purpose of 
explanation.
___
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] CREATE INDEX and column order

[Doug Nebeker]

I was reading about the new query planner and came across a few references to 
that idea that the left most columns in the index definition should be the most 
unique (as far as values in the column are concerned).

Is that correct?  In my case, many tables have a timestamp column, and I've 
been using that as my right-most column, but it seems it would be a great 
candidate to be switched.

Thanks
Doug
___
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] Beginning database question

[Doug Nebeker]

This is a tough one to answer because there is so much context to consider.  
SQLite, or any database, could easily solve the problem you mention (storing 
values that can change without needing to recompile your program).  

Whether it's more efficient is another question.  If you think you'll have many 
more than 10 entries (likes thousands or millions), or the 10 entries change 
very often (once a minute??) and you could automate entering them, a simple app 
using a database like SQLite would be a decent way to go.   Does this need to 
get put on a website?  Is the app going to be mailed around? (in which case a 
spreadsheet might be just as easy).

If you'd like to use this as an excuse to learn more about databases, this 
project would be a perfect opportunity.  It's surprising how often databases, 
especially a small and simple one like SQLite come in handy.

Compared to using Excel:
> coding efficiency - no coding needed to use Excel, so Excel wins
> processing speed - your app would almost certainly start up and run quicker 
> than Excel
> memory usage - your app would definitely use less memory than Excel, but does 
> this really matter with only 10 entries?

That's my best shot.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Carl Gross
Sent: Tuesday, April 16, 2013 3:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Beginning database question

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects, but 
I'm having trouble determining exactly how.  I'm hoping someone in this list 
may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10 
discrete weights,' and each height/weight combination corresponds to one of two 
'teams.'  All of this information is hardcoded into my program.  My program 
asks a user to enter his own height and weight, and the program will output the 
user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be 
beneficial in terms of coding efficiency, processing speed, memory usage, or 
any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to tell 
me how to do something.  I'm looking for someone to assure me that SQLite 
(rather than a simple spreadsheet) is right for me, and to hopefully steer me 
towards some documentation that may be beneficial to me.

Thanks,

Carl
___
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] set journal off working witth SQLiteConfig?

[Doug Crites]

Hello,

I am using the sqlite-jdbc-3.7.8-20111025.014814-1.jar , trying to set the 
journaling off on my 'main' only opened db.

SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.OFF);

writeLog("Timestamp properties");
Properties propSqlite = config.toProperties();

When I run,  I see from my properties display that the journaling is off,  but 
I still see a journal file being created when I run it.
{open_mode=6, journal_mode=OFF)

Does anyone have experience with turning this off thru the SqlLiteConfig 
object?  Any tips on usage?
The reason I'm tring this is that we keep running out of memory during the 
processing of a large transaction (about 52,000 updates).  I try changing cache 
size and ournal size limits,  but I'm not sure if it's really taking affect.
If anyone has some tips on settings for large transactions like this, please 
share!

Thanks,
Doug


Doug Crites
Sr Software Engineer
doug.cri...@asg.com<mailto:doug.cri...@asg.com>
The Commons
708 Goodlette Road N
Naples, FL 34102
Tel: 239.435.2293
Fax: 239.213.3501
Toll-Free: 800.932.5536 USA Only
www.asg.com<http://www.asg.com/>
ASG-CloudFactory - The Single Solution to Build, Deploy & Manage your Cloud 
Environments
Click here for more information on the 
ASG-CloudFactory<http://www.asg.com/cloudfactory>

___
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] VC++ and SQLite

[Doug Nebeker]

You might be surprised at the speed increase you see in compile time if
you've got large projects.  The time isn't lost to CPU as much, but disk I/O
time adds up when hitting many hundreds of small (header) files (even with
an SSD).

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Monday, November 12, 2012 1:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VC++ and SQLite

I always turn pre-compiled headers off for every VC++ project.  In my
opinion, they are artifacts from a time when processors were a few hundred
times slower than they are now.  The benefit in time saved now is far less
than the confusion they cause when something goes wrong.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
Sent: Monday, November 12, 2012 10:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VC++ and SQLite

It isn't VS2010 specific. Even going back to VS6 writing your own C++
wrapper and including the.c file you had to tell it to not use precompiled
headers for that file. (Both Debug and Release builds)

You should tell VS that this file will not ever be using precompiled
headers.

On VS2012 Professional Edition one can:
Right click on the file within VS10, select Properties.
Open the C/C++ tree.
Select Precompiled Headers.
Set Precompiled Header to Not Using Precompiled Headers.


Adam

On Mon, Nov 12, 2012 at 10:17 AM, John Drescher <dresche...@gmail.com>
wrote:
>>> I know this question is not a SQLite question, but I am hoping that 
>>> someone here has had a similar experience and/or can point me to the 
>>> right place to ask this question.
>>>
>>> After years or using Code::Blocks and Dev-Cpp, I have recently 
>>> installed Visual Studio 10 Express; it is the first time I am using 
>>> it, in my Windows
>>> 7 machine.
>>>
>>> I have written, with the help of this mailing list a wrapper class 
>>> for the latest SQLite3 library using C::B as my development 
>>> platform, now that I want to switch to VS10, there were a lot of gcc 
>>> specific code that I had to repair and after clearing all the C++ 
>>> discrepancies between MinGW's g++ and MS's VC++ I have been left with
this error message:
>>>
>>> fatal error C1853: 'Debug\sql.pch' precompiled header file is from a 
>>> previous version of the compiler, or the precompiled header is C++ 
>>> and you are using it from C (or vice versa
>>>
>>>
>>>
>>> Does anyone know how to resolve this issue or perhaps a VS10 
>>> specific
>
> You can like the other poster said disable PCH in visual studio or 
> just delete all the PCH files and have VS rebuild them. The second is 
> what I do in Visual Studio retail versions when I get this error.
>
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
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] 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] sqlite time is 2 hours to late

[Doug Nebeker]

>  UTC is "the right time."  If you're doing anything with dates and
>  times I would STRONGLY recommend that all recorded times are in UTC.

Jay is right.  I've been bitten by storing local times before.  Even if your
users
are in the same time zone, that time zone shifts with day light savings.  It
was a 
painful lesson.  Always store times in UTC.

Doug


___
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] Handle leak using IIS on windows 7?

[Doug]

This was exactly the problem; I didn't realize the 'static' variables are
persisted between page views in ASP.Net

Adding an '_instance = null;' fixed the issue.

Thanks muchly.

Cheers,
Doug.

On Tue, Jul 19, 2011 at 11:58 AM, Joe Mistachkin <sql...@mistachkin.com>wrote:

>
> After reading the code, I noticed the following:
>
> 1. From the static Dump method, an instance of the DbLogger class is
> created via the static Get method and stored in the _instance static
> variable.
>
> 2. The connection itself is opened in the constructor for the DbLogger
> class via the InitDb method.
>
> 3. Prior to returning a result, the Dump method closes the connection
> and sets the _connection instance variable to null.
>
> 4. The second time the Dump method is executed, the existing instance
> of the DbLogger class will be used (i.e. the one stored in the static
> _instance variable).
>
> 5. This existing instance of the DbLogger class no longer has a valid
> connection because it was previously closed (and set to null).
>
> 6. Newly created commands will not have a valid connection.
>
> 7. Attempting to execute a command without a valid connection will
> result in the exception you are seeing.
>
> --
> Joe Mistachkin
>
> ___
> 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] Handle leak using IIS on windows 7?

[Doug]

Hm... I'm not doing anything fancy.

I've attached the tiny sqlite logging class below. It's called in an MVC app
simply by invoking:

@Html.Raw(Doug.Utils.Web.DbLogger.Dump())

Yes, it uses transactions; is that a bad thing?

I'm pretty sure the issue is something to do with file handles. For the same
reason after loading the page (from an IIS server) and then closing the
page, waiting 1 minute or two and then attempting to remove the db.sqlite
file, I get an "error, file is in use".

(Obviously, if you use File.Open() without a File.Close() this does not
happen; when the page view ends the file handle is automatically released).

If you want to see it in action, create a new MVC project and add:
DbLogger.Get();

To the home index page. Run it in debug mode and you'll see the issue.

Cheers,
Doug.

code (in case the attachment fails):

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SQLite;
using System.IO;

namespace Doug.Utils.Web
{
public class DbLoggerRecord
{
public String Context { get; set; }
public String Message { get; set; }
public DateTime Created { get; set; }
}

public class DbLogger
{
private static DbLogger _instance = null;

/// 
/// Where to store the logging database.
/// 
private const string relativeDbPath = "~/App_Data/DbLogger.sqlite";

private SQLiteConnection _connection = null;

private SQLiteTransaction _transaction = null;

public DbLogger()
{
var dbPath = Path.GetTempFileName();
if (HttpContext.Current != null)
dbPath = HttpContext.Current.Server.MapPath(relativeDbPath);
_connection = InitDb(dbPath);
}

private SQLiteConnection InitDb(String dbPath)
{
bool init = false;
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
init = true;
}
var rtn = new SQLiteConnection("Data Source="+dbPath);
rtn.Open();

// Pragma or this doesn't work in app_data folder.
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = rtn;
string pragma = "PRAGMA journal_mode = OFF";
cmd.CommandText = pragma;
cmd.ExecuteNonQuery();

// Check if we realy neeed to init?
if (!init) {
try
{
GetRecords(1);
}
catch(Exception)
{
try
{
File.Delete(dbPath);
init = true;
}
catch(Exception)
{
}
}
}

if (init)
CreateTable(rtn);

return rtn;
}

private void CreateTable(SQLiteConnection c)
{
var createTable = new StringBuilder();
createTable.Append("CREATE TABLE Log (");
createTable.Append("  Id PRIMARY KEY,");
createTable.Append("  Context TEXT,");
createTable.Append("  Message TEXT,");
createTable.Append("  Created TEXT)");
var cmd = createTable.ToString();
var sqlCmd = new SQLiteCommand(c);
sqlCmd.CommandText = cmd;
try
{
sqlCmd.ExecuteNonQuery();
}
catch(Exception)
{
}
}

public void Trace(String context, String message)
{
if (_transaction == null)
_transaction = _connection.BeginTransaction();

var insertMsg = new StringBuilder();
insertMsg.Append("INSERT INTO Log (Context, Message, Created)
VALUES (@Context, @Message, @Created)");
var cmd = insertMsg.ToString();
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;

// Params
sqlCmd.Parameters.AddWithValue("@Context", context);
sqlCmd.Parameters.AddWithValue("@Message", message);
sqlCmd.Parameters.AddWithValue("@Created",
DateTime.Now.ToString());

sqlCmd.ExecuteNonQuery();
}

public void Close() {
if (_connection != null)
{
_connection.Close();
_connection = null;
}
}

public IEnumerable GetRecords(int limit)
{
Commit();
var rtn = new List();

string cmd = "SELECT * FROM Log ORDER BY Id ASC LIMIT " + limit;
var sqlCmd = new SQLiteCommand(_conn

[sqlite] Handle leak using IIS on windows 7?

[Doug]

Hi there,

I've searched around and found a few threads like this:
http://sqlite.phxsoftware.com/forums/t/2480.aspx

Basically, I have the same issue.

When access the sqlite database via a  website (MVC3 running on IIS) the
first time, sqlite works fine.

I properly call connections.Close() when I'm done...

And the next time I try to access it I get:
System.InvalidOperationException: No connection associated with this command

Manually stopping the dev web server, or restarting the iis application pool
fixes this for one more page view.

It seems like the IIS config is leaving the process hanging around, and
after calling close there (I guess) must be some handle which is being kept
and keeping a reference to the database, preventing anything else from
accessing it.

Seeing as how this has happened to a few people, I was hoping someone here
had seen this before and had a solution?

I'm using the Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki(32
bit mode enabled on iis), but I've tried the 64-bit version with the
same result.

Cheers,
Doug.
___
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