Re: [sqlite] Optimization opportunity

[Wolfgang Enzinger]

Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp:

> On 4/14/17, Wolfgang Enzinger  wrote:
>>
>> Thank you Richard. I have to admit that it took me quite a while and also
>> reading the comment for check-in [1838a59c] several times to really
>> understand your explanation. Duh, that's tricky indeed!
>>
> 
> But I've spent Good Friday working around it.  Please try using the
> tip of the left-join-view branch
> (https://www.sqlite.org/src/timeline?c=left-join-view) and let me know
> if that version works better for you.  After some additional testing,
> this optimization will likely be merge to trunk and appear in the next
> release.  Your beta-testing is important - Thanks.

Tested now with sqlite-snapshot-201705020130. Excellent! The query plan no
longer shows any full table scan, and a specific query in my project runs ~
1 ms. now - vs. 11-12 sec. using version 3.18. Great enhancement, thank
you!

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[David Raymond]

On the plus side they come out equal, so apart from neatness of the display, 
all's good.
Interesting how the auto EQP output shows up in the .dump output. But at least 
it starts with -- so won't harm anything.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table xxx (x primary key);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.004 user 0.00 sys 0.00

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx (x primary key);
--EQP-- 0,0,0,SCAN TABLE xxx
INSERT INTO xxx(x) VALUES(1.2299822);
COMMIT;

sqlite> INSERT INTO xxx(x) VALUES(1.2299822);
Run Time: real 0.000 user 0.00 sys 0.00
Error: UNIQUE constraint failed: xxx.x

sqlite> delete from xxx;
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> INSERT INTO xxx(x) VALUES(1.2299822);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.003 user 0.00 sys 0.00

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.00 sys 0.00
Error: UNIQUE constraint failed: xxx.x



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, May 03, 2017 2:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP displays floats differently from SELECT

In a previous message I suggest that behaviour under macOS changed between 
SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who 
uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);
…

SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299822);

It does seem that this is due to a recent change in SQLite source code.

Simon.
___
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] .DUMP displays floats differently from SELECT

[Simon Slavin]

In a previous message I suggest that behaviour under macOS changed between 
SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who 
uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);
…

SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299822);

It does seem that this is due to a recent change in SQLite source code.

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

Re: [sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Olivier Mascia]

> Le 3 mai 2017 à 19:44, Tim Streater  a écrit :
> 
>> Would there be a cheap way for SQLite to log some more user-realm context
>> about these?
>> Maybe simply emitting a second call to the log function right after these
>> messages code 284 where the third parameter (msg) would simply point at the
>> prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?
> 
> Why don’t you write your own wrapper functions for the base SQLite calls. My 
> set of these functions logs where each call occurs and what the SQL was.

That's indeed an obvious path, and our C++ wrapper can easily do so. It indeed 
gives us precise context information including the sqlexpanded query when 
appropriate, but only when SQLite raises an error (which is rather exceptional 
in released code).  Here these are not errors, merely informational messages 
from SQLite which we can lightly capture on the fly.  Logging all detailed 
context would be unnecessary bulk. Hence the proposal which is, apparently, 
lighter and only triggers when SQLite has something useful to say.  I try to 
keep things simple and light. :)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



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

Re: [sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Tim Streater]

On 3 May 2017, at 18:16, Olivier Mascia  wrote:

> Would there be a cheap way for SQLite to log some more user-realm context
> about these?
> Maybe simply emitting a second call to the log function right after these
> messages code 284 where the third parameter (msg) would simply point at the
> prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?

Why don’t you write your own wrapper functions for the base SQLite calls. My 
set of these functions logs where each call occurs and what the SQL was.


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

Re: [sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Olivier Mascia]

> Le 3 mai 2017 à 18:46, Richard Hipp  a écrit :
> 
> On 5/3/17, Olivier Mascia  wrote:
>>  automatic index on sqlite_sq_25FA4563E0(ID) (284)
>>  ...
>> 
>> I guess they mean SQLite decided to build some temporary index for some
>> query execution, just as for the first case. But here the table itself looks
>> like internal and temporary. What can I interpret from these?
> 
> Yes.  Those sqlite3_sq_... tables are going to be materializations of
> views and/or subqueries. The "sq" stands for "subquery".
> 
> -- 
> D. Richard Hipp

Thanks Richard.
What recommended path, if there's one favored, should I take to make it easy to 
identify which queries induce those indexes on subqueries?

Would there be a cheap way for SQLite to log some more user-realm context about 
these?
Maybe simply emitting a second call to the log function right after these 
messages code 284 where the third parameter (msg) would simply point at the 
prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 03, 2017 12:30 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>
> On 3 May 2017, at 3:40pm, Scott Robison  wrote:
>
> > On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:
> >
> >> While trying to search/replace some text from an SQLite3 dump I
> >> noticed that, unfortunately, .DUMP does not produce the exact same
> >> numbers as a plain SELECT on the same values.
> >
> > I know all about expected floating point inaccuracies, but I don’t see
> > why it should matter in this case as we have two different places in
> > the same app (SQLite3) where the same number is ‘displayed’ using
> > whatever default format.  IMO, in both places the same number should
> > display exactly the same (i.e., using the same format), both for
> > consistency and easy verification between dump and actual database.
>
> That’s a matter of preference.  There is one possible bug here.  If the 
> results
> of reading the dump file back in produces a different value to the original
> INSERT, that’s a problem.  So I tried it:
>
> SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage hints.
> sqlite> create table xxx(x);
> sqlite> insert into xxx values(1.23);
> sqlite> select * from xxx;
> 1.23
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE xxx(x);
> INSERT INTO "xxx" VALUES(1.23);
> COMMIT;
>
> Hmm.  Cannot reproduce your problem in my version of the command-line
> tool.  You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  
> Or
> perhaps there’s a difference in a library between my platform (macOS) and
> yours.
>
> Simon.

Nor I with 3.14.1 on Windows 7:

SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;
sqlite>

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Effective immediately my new email address is william.dr...@l3t.com. Please 
update your records.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Richard Hipp]

On 5/3/17, Olivier Mascia  wrote:
>   automatic index on sqlite_sq_25FA4563E0(ID) (284)
>   ...
>
> I guess they mean SQLite decided to build some temporary index for some
> query execution, just as for the first case. But here the table itself looks
> like internal and temporary. What can I interpret from these?

Yes.  Those sqlite3_sq_... tables are going to be materializations of
views and/or subqueries. The "sq" stands for "subquery".

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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Simon Slavin]

On 3 May 2017, at 3:40pm, Scott Robison  wrote:

> On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:
> 
>> While trying to search/replace some text from an SQLite3 dump I noticed
>> that, unfortunately, .DUMP does not produce the exact same numbers as a
>> plain SELECT on the same values.
> 
> I know all about expected floating point inaccuracies, but I don’t see why
> it should matter in this case as we have two different places in the same
> app (SQLite3) where the same number is ‘displayed’ using whatever default
> format.  IMO, in both places the same number should display exactly the
> same (i.e., using the same format), both for consistency and easy
> verification between dump and actual database.

That’s a matter of preference.  There is one possible bug here.  If the results 
of reading the dump file back in produces a different value to the original 
INSERT, that’s a problem.  So I tried it:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;

Hmm.  Cannot reproduce your problem in my version of the command-line tool.  
You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  Or 
perhaps there’s a difference in a library between my platform (macOS) and yours.

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

[sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Olivier Mascia]

Dear,

On such a log line as:

automatic index on REMINDER(USER_LOGON) (284)

I understand what it means and what I might want to do (or not).

Though what should I understand from line like these:

automatic index on sqlite_sq_25FA456860(ID) (284)
automatic index on sqlite_sq_25FA455E40(ID) (284)
automatic index on sqlite_sq_25FA456C50(ID) (284)
automatic index on sqlite_sq_25FA455ED0(ID) (284)
automatic index on sqlite_sq_25FA4563E0(ID) (284)
...

I guess they mean SQLite decided to build some temporary index for some query 
execution, just as for the first case. But here the table itself looks like 
internal and temporary. What can I interpret from these?

(I have no apparent problem / issue. Just pinpointed these in the log.)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Scott Robison]

On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:

While trying to search/replace some text from an SQLite3 dump I noticed
that, unfortunately, .DUMP does not produce the exact same numbers as a
plain SELECT on the same values.

I know all about expected floating point inaccuracies, but I don’t see why
it should matter in this case as we have two different places in the same
app (SQLite3) where the same number is ‘displayed’ using whatever default
format.  IMO, in both places the same number should display exactly the
same (i.e., using the same format), both for consistency and easy
verification between dump and actual database.


The select version is output for human readability. The dump version is
output for precision so that every digit goes back into the recreated
database exactly as it came out.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

While trying to search/replace some text from an SQLite3 dump I noticed that, 
unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT 
on the same values.

I know all about expected floating point inaccuracies, but I don’t see why it 
should matter in this case as we have two different places in the same app 
(SQLite3) where the same number is ‘displayed’ using whatever default format.  
IMO, in both places the same number should display exactly the same (i.e., 
using the same format), both for consistency and easy verification between dump 
and actual database.

Below is a sample script and its output that demonstrate the issue (using 
SQLite 3.18.0 2017-03-28 18:48:43):

SCRIPT:

create table xxx(x);
insert into xxx values(1.23);
select * from xxx;
.dump

OUTPUT:

1.23  
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO xxx VALUES(1.2299822);
COMMIT;

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

Re: [sqlite] bug for user auth

[Dominique Devienne]

2017-05-03 10:56 GMT+02:00 XIAO DAI :

> I have compiled SQLite v3.15.2 with the functions
> "sqlite3_user_authenticate, it runs well, for all the versions > 3.15.2, I
> can add the logins into the database, but sqlite(shell.c) does NOT ask for
> the authentication.
>

From https://www.sqlite.org/src/artifact/e6641021a9210364

Probably because the shell you're using is not compiled with the
-DSQLITE_USER_AUTHENTICATION compile-time option.

The User-Auth extension is based on the presence of the sqlite_user table,
which is somewhat "protected" simply by using the reversed sqlite_ prefix,
unless using "PRAGMA writable_schema=ON", as the doc above mentions.

So basically any normal SQLite app can bypass the user-auth, unless the DB
is also encrypted. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bug for user auth

[XIAO DAI]

Hello,


I have compiled SQLite v3.15.2 with the functions "sqlite3_user_authenticate, 
it runs well, for all the versions > 3.15.2, I can add the logins into the 
database, but sqlite(shell.c) does NOT ask for the authentication.


Sorry for my poor english


sincerely



Xiao DAI

Ingénieur BE

01 49 62 27 83

x@noralsy.com

 16, rue Lavoisier - ZI - 94430 Chennevières/Marne - France


[http://www.noralsy.com/wp-content/uploads/2015/08/sign.jpg]

Venez assister aux séminaires de formation Noralsy : inscription sur 
http://www.noralsy.com/formations/


Ce courriel et les documents qui lui sont joints peuvent contenir des 
informations confidentielles ou ayant un caractère privé. S'ils ne vous sont 
pas destinés, nous vous signalons qu'il est strictement interdit de les 
divulguer, de les reproduire ou d'en utiliser de quelque manière que ce soit le 
contenu. Si ce message vous a été transmis par erreur, merci d'en informer 
l'expéditeur et de supprimer immédiatement de votre système informatique ce 
courriel ainsi que tous les documents qui y sont attachés.

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

Re: [sqlite] Thread notification for new record in a table.

[petern]

FYI. I proposed a portable solution for a responsive interprocess work
queue within SQLite without using native IPC less than 2 weeks ago on this
very board.  [See Dori the forgetful fish.]

https://www.mail-archive.com/sqlite-users@mailinglists.
sqlite.org/msg102741.html

DB reader(s) block/poll up to a specified busy_timeout but are directly
synchronized to completion of the DB writer's transaction payload.

See: http://sqlite.org/c3ref/busy_timeout.html

Basically, Instead of sleep(), the reader(s) set busy_timeout().  Thus, the
polling interval can be very long without reducing responsiveness.




On Tue, May 2, 2017 at 12:48 AM, John Found  wrote:

> On Tue, 2 May 2017 09:38:30 +0200
> Olivier Mascia  wrote:
>
> > > Le 2 mai 2017 à 09:00, John Found  a écrit :
> > >
> > > What is the best way (less CPU consuming) to put a thread in sleep and
> wake it up when new record has been written to a given table of SQLite
> database?
> > >
> > > Now I am implementing this by polling and time based sleep, but such
> solution is very dirty compromise, trading response time for CPU load. I
> want both. :)
> > >
> > > --
> > > http://fresh.flatassembler.net
> > > http://asm32.info
> > > John Found 
> >
> > Reading your question I assume a single, multi-threaded, application.
> You could write a SQL function (see sqlite3_create_function_v2 and
> associates) which signal an event.  And add a SQL trigger calling this
> function when appropriate.
>
> In my case I have several running applications that write in the database.
> But isn't this solution applicable nevertheless? Maybe with some kind of
> IPC.
>
> >
> >
> > --
> > Best Regards, Meilleures salutations, Met vriendelijke groeten,
> > Olivier Mascia, http://integral.software
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> 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