Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Thanks, Jens.

I will do that.

- Ken


On 01/04/2017 12:29 AM, Jens Alfke wrote:

On Jan 3, 2017, at 9:17 PM, Ken Wagner  wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/ 
   Get names without [1-9].

You should probably report this to the people who run that website. It’s not 
associated with SQLite itself.

—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] SQLite3 Tutorial error

[Jens Alfke]

> On Jan 3, 2017, at 9:17 PM, Ken Wagner  wrote:
> 
> About 2/3 the way down the page at:
> 
> http://www.sqlitetutorial.net/sqlite-glob/ 
>    Get names without [1-9].

You should probably report this to the people who run that website. It’s not 
associated with SQLite itself.

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

Re: [sqlite] Can the Explain explain what is going on here?

[Bart Smissaert]

Have recompiled and indeed this makes both statements run at the same fast
speed.
I can also see that the both Explain now produce near enough the same.
So it looks this has fixed it.
I can see now that this could affect query speed not only in unrealistic
testing situations,
so this looks a useful fix.

RBS


On Wed, Jan 4, 2017 at 1:09 AM, Richard Hipp  wrote:

> On 1/3/17, Richard Hipp  wrote:
> > On 1/3/17, Richard Hipp  wrote:
> >> On 1/3/17, Bart Smissaert  wrote:
> >>>
> >>> Firstly, is this a bug?
> >>
> >> No.  A "bug" means it gets the wrong answer.  In this case, it gets
> >> the correct answer, just more slowly than you would like.  That means
> >> this is an optimization opportunity.
> >>
> >> Thanks for bringing it to my attention.
> >
> > Please experiment with this version of SQLite:
> > https://www.sqlite.org/src/info/acdb8f6f10953ed4
>
> Wrong version.  Sorry.  Should have been
> https://www.sqlite.org/src/info/62e9270a8057d758
>
> --
> 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] Can the Explain explain what is going on here?

[Bart Smissaert]

Thanks, will try that out.

RBS

On Wed, Jan 4, 2017 at 1:09 AM, Richard Hipp  wrote:

> On 1/3/17, Richard Hipp  wrote:
> > On 1/3/17, Richard Hipp  wrote:
> >> On 1/3/17, Bart Smissaert  wrote:
> >>>
> >>> Firstly, is this a bug?
> >>
> >> No.  A "bug" means it gets the wrong answer.  In this case, it gets
> >> the correct answer, just more slowly than you would like.  That means
> >> this is an optimization opportunity.
> >>
> >> Thanks for bringing it to my attention.
> >
> > Please experiment with this version of SQLite:
> > https://www.sqlite.org/src/info/acdb8f6f10953ed4
>
> Wrong version.  Sorry.  Should have been
> https://www.sqlite.org/src/info/62e9270a8057d758
>
> --
> 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] Can the Explain explain what is going on here?

[Bart Smissaert]

All these UDF's are registered like this:

  RegisterUDF = sqlite3_create_function_v2(ByVal lDBHdl, _
   ByVal
cConn.UTF8BytesPointerFromUTF16String(strFunctionName), _
   ByVal lArgCount, _
   ByVal SQLITE_UTF8 Or
SQLITE_DETERMINISTIC, _
   ByVal lIndex, _
   ByVal lAddress, _
   ByVal 0, _
   ByVal 0, _
   ByVal 0)

RBS

On Wed, Jan 4, 2017 at 12:58 AM, Keith Medcalf  wrote:

>
> On Tuesday, 3 January, 2017 17:18. Simon Slavin 
> wrote:
>
> > On 4 Jan 2017, at 12:09am, Bart Smissaert 
> > wrote:
> >
> > > Secondly, can the Explain statement tell me that indeed the UDF runs
> > 10
> > > times in the first SQL and only once for the second SQL?
> >
> > What makes you think that this is the case ?  Why would SQLite not be
> > running the UDF 10 times in both cases ?
> >
> > Did you build any caching of results into your UDF ?  If not, then SQLite
> > has no idea it can optimize it by checking to see if it’s using the same
> > parameters each time.
>
> You do not need caching in the UDF for that, you just need to set the
> SQLITE_DETERMINISTIC flag when defining the function.
>
>
>
>
> ___
> 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] SQLite3 Tutorial error

[Ken Wagner]

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without [1-9].

select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but use 
the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.

This works as expected in sqlite3 (3.15.1 and 3.16.1 :

select trackid, name from tracks where name not GLOB '*[1-9]*'; Gets 
names without [1-9].




On 01/03/2017 07:37 PM, Richard Hipp wrote:

On 1/3/17, Ken Wagner  wrote:

Hi SQLite,

In the SQLite3 Tutorial

What tutorial are you referring to?



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

Re: [sqlite] SQLite3 Tutorial error

[Richard Hipp]

On 1/3/17, Ken Wagner  wrote:
> Hi SQLite,
>
> In the SQLite3 Tutorial
What tutorial are you referring to?

-- 
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] SQLite3 Tutorial error

[Ken Wagner]

Hi SQLite,

In the SQLite3 Tutorial the following query is said to find all names 
WITHOUT numbers in them. But it fails to do so.


select trackid, name from tracks where name GLOB '*[^1-9]*';   per the 
tutorial fails to list names WITHOUT numbers in them.


However, this DOES work:

select trackid, name from tracks where name not GLOB '*[1-9]*';

Is this a tutorial error?  Using SQLiteMan the first query DOES work. 
But not in SQLite 3.15.1 or 3.16.1.


Is this a bug or a tutorial error? Can't tell which version of SQLite 
SQLiteMan is using.


System: Ubuntu 16.04, SQLite3 (3.15.1 and 3.16.1). Also tested using 
Ruby 2.3.3 with ruby-sqlite extension.


Thanks,

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

Re: [sqlite] Can the Explain explain what is going on here?

[Richard Hipp]

On 1/3/17, Richard Hipp  wrote:
> On 1/3/17, Richard Hipp  wrote:
>> On 1/3/17, Bart Smissaert  wrote:
>>>
>>> Firstly, is this a bug?
>>
>> No.  A "bug" means it gets the wrong answer.  In this case, it gets
>> the correct answer, just more slowly than you would like.  That means
>> this is an optimization opportunity.
>>
>> Thanks for bringing it to my attention.
>
> Please experiment with this version of SQLite:
> https://www.sqlite.org/src/info/acdb8f6f10953ed4

Wrong version.  Sorry.  Should have been
https://www.sqlite.org/src/info/62e9270a8057d758

-- 
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] Can the Explain explain what is going on here?

[Richard Hipp]

On 1/3/17, Richard Hipp  wrote:
> On 1/3/17, Bart Smissaert  wrote:
>>
>> Firstly, is this a bug?
>
> No.  A "bug" means it gets the wrong answer.  In this case, it gets
> the correct answer, just more slowly than you would like.  That means
> this is an optimization opportunity.
>
> Thanks for bringing it to my attention.

Please experiment with this version of SQLite:
https://www.sqlite.org/src/info/acdb8f6f10953ed4

Let me know if it works any better for you.

-- 
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] problem with sqlite 4

[Jim Callahan]

David Empson wrote:

SQLite Expert Personal is a third party product which uses the SQLite
> database engine. It is not using “SQLite 4” (which is in early development
> stages and not been released), but will be using some version of SQLite 3.
> This mailing list is not an appropriate place to get support for products
> which use SQLite, but this looks like odd behaviour with SQLite itself,
> which may be worth investigating further.


Moreover if one submits this bug report to the "SQLite Expert" mailing list
using the email address:

supp...@sqliteexpert.com

one may qualify for a $50 credit towards one's next purchase:

"Submit a bug report and get $50 discount when purchasing SQLite Expert
Professional!

If you submit one or more bug reports in either SQLite Expert Personal or
Professional, you will receive a promotional code by email which you can
use when purchasing SQLite Expert Professional for $50 discount (over 50%
of the original price).
Conditions:

   -  You must submit at least one bug report to qualify for this offer.
   -  Feature requests do not count as bug reports.
   -  The reported bug must be reproducible with the latest version of
   SQLite Expert.
   -  Multiple bug reports do not qualify for cumulative discount.
   -  If you already purchased SQLite Expert Professional, you are not
   entitled to a partial refund if you submit a bug report. However, you
   qualify for a discount if you wish to purchase an additional license."

http://www.sqliteexpert.com/support.html

I am not affiliated with Coral Creek Software and the only information I
could find out about the company as opposed to the product (in less than 2
minutes of Google searching) is:

https://www.bizapedia.com/fl/coral-creek-software.html

Jim Callahan
Orlando, FL

On Mon, Jan 2, 2017 at 5:15 PM, David Empson  wrote:

>
> > On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net wrote:
> >
> > Hi,
> >
> > The problem described here occurs both with the x32 and x64 versions of
> the expert personal 4 (Windows 10). Hereafter, a little database to show
> the bug.
> >
> > The table "sample" is used to store words occurring in texts. Texts are
> identified by an id number.
> >
> > CREATE TABLE IF NOT EXISTS sample (
> > textid INT,
> > word VARCHAR(100),
> > UNIQUE (textid,word)
> > );
> >
> > CREATE INDEX [word index] ON [sample] ([word]);
> >
> > INSERT INTO sample VALUES
> > (1,"hello"),
> > (1,"world"),
> > (1,"apple"),
> > (1,"fruit"),
> > (2,"fruit"),
> > (2,"banana"),
> > (3,"database")
> > ;
> >
> > Now, one wants to list all the tuples corresponding to the texts
> containing the word "fruit". In the table above, only the texts 1 and 2
> contains the word "fruit". Therefore, the expected result must be :
> >
> > RecNo textid word
> > - -- --
> > 1 1 apple
> > 2 1 fruit
> > 3 1 hello
> > 4 1 world
> > 5 2 banana
> > 6 2 fruit
> >
> > The following SQL request should achieve the goal :
> >
> > SELECT l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > But il does not since it delivers the wrong answer :
> >
> > RecNo textid word
> > - -- -
> > 1 1 fruit
> > 2 1 fruit
> > 3 1 fruit
> > 4 1 fruit
> > 5 2 fruit
> > 6 2 fruit
> >
> > However, by adjoining in the SELECT part of the above request either a
> constant string or the command DISTINCT , then the result becomes correct !
> >
> > SELECT "happy new year", l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > RecNo 'happy new year' textid word
> > -  -- --
> > 1 happy new year 1 apple
> > 2 happy new year 1 fruit
> > 3 happy new year 1 hello
> > 4 happy new year 1 world
> > 5 happy new year 2 banana
> > 6 happy new year 2 fruit
> >
> > SELECT DISTINCT l2.textid, l2.[word]
> > FROM sample AS l1, sample AS l2
> > WHERE (l1.[word] = 'fruit' ) and (l2.[textid] = l1.[textid])
> > ;
> >
> > RecNo textid word
> > - -- --
> > 1 1 apple
> > 2 1 fruit
> > 3 1 hello
> > 4 1 world
> > 5 2 banana
> > 6 2 fruit
> >
> > Thank you for your reading. Please, notice that this "strange" behavior
> does not occur with the version 3 of Sqlite expert personal.
> >
> > Claude Del Vigna
>
>
> SQLite Expert Personal is a third party product which uses the SQLite
> database engine. It is not using “SQLite 4” (which is in early development
> stages and not been released), but will be using some version of SQLite 3.
>
> This mailing list is not an appropriate place to get support for products
> which use SQLite, but this looks like odd behaviour with SQLite itself,
> which may be worth investigating further.
>
> The current version of SQLite Expert Personal is 4.2.0, available here:
>
> http://www.sqliteexpert.com/download.html
>
> They don’t appear to give any clues as to which version of SQLite the
> application is using.
>
> I downloaded the 

Re: [sqlite] Can the Explain explain what is going on here?

[Keith Medcalf]

On Tuesday, 3 January, 2017 17:18. Simon Slavin  wrote:

> On 4 Jan 2017, at 12:09am, Bart Smissaert 
> wrote:
> 
> > Secondly, can the Explain statement tell me that indeed the UDF runs
> 10
> > times in the first SQL and only once for the second SQL?
> 
> What makes you think that this is the case ?  Why would SQLite not be
> running the UDF 10 times in both cases ?
> 
> Did you build any caching of results into your UDF ?  If not, then SQLite
> has no idea it can optimize it by checking to see if it’s using the same
> parameters each time.

You do not need caching in the UDF for that, you just need to set the 
SQLITE_DETERMINISTIC flag when defining the function.




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

Re: [sqlite] Can the Explain explain what is going on here?

[Bart Smissaert]

I have checked and my presumed explanation was indeed correct.
Runs 10 times (+ a few more for Prepare and check SQL etc.) with the
first SQL and only a few with the second SQL.

RBS

On Wed, Jan 4, 2017 at 12:17 AM, Simon Slavin  wrote:

>
> On 4 Jan 2017, at 12:09am, Bart Smissaert 
> wrote:
>
> > Secondly, can the Explain statement tell me that indeed the UDF runs
> 10
> > times in the first SQL and only once for the second SQL?
>
> What makes you think that this is the case ?  Why would SQLite not be
> running the UDF 10 times in both cases ?
>
> Did you build any caching of results into your UDF ?  If not, then SQLite
> has no idea it can optimize it by checking to see if it’s using the same
> parameters each time.
>
> 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] Can the Explain explain what is going on here?

[Bart Smissaert]

Thanks for that.
Yes, indeed not a bug.
Not sure there is a scenario imaginable where this optimization will be
helpful in real practice.

RBS

On Wed, Jan 4, 2017 at 12:25 AM, Richard Hipp  wrote:

> On 1/3/17, Bart Smissaert  wrote:
> >
> > Firstly, is this a bug?
>
> No.  A "bug" means it gets the wrong answer.  In this case, it gets
> the correct answer, just more slowly than you would like.  That means
> this is an optimization opportunity.
>
> Thanks for bringing it to my attention.
> --
> 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] Can the Explain explain what is going on here?

[Richard Hipp]

On 1/3/17, Bart Smissaert  wrote:
>
> Firstly, is this a bug?

No.  A "bug" means it gets the wrong answer.  In this case, it gets
the correct answer, just more slowly than you would like.  That means
this is an optimization opportunity.

Thanks for bringing it to my attention.
-- 
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] Can the Explain explain what is going on here?

[Bart Smissaert]

> What makes you think that this is the case ?

I didn't check, but this was the only explanation I could think of.
Will check in a bit.

RBS

On Wed, Jan 4, 2017 at 12:17 AM, Simon Slavin  wrote:

>
> On 4 Jan 2017, at 12:09am, Bart Smissaert 
> wrote:
>
> > Secondly, can the Explain statement tell me that indeed the UDF runs
> 10
> > times in the first SQL and only once for the second SQL?
>
> What makes you think that this is the case ?  Why would SQLite not be
> running the UDF 10 times in both cases ?
>
> Did you build any caching of results into your UDF ?  If not, then SQLite
> has no idea it can optimize it by checking to see if it’s using the same
> parameters each time.
>
> 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] Can the Explain explain what is going on here?

[Simon Slavin]

On 4 Jan 2017, at 12:09am, Bart Smissaert  wrote:

> Secondly, can the Explain statement tell me that indeed the UDF runs 10
> times in the first SQL and only once for the second SQL?

What makes you think that this is the case ?  Why would SQLite not be running 
the UDF 10 times in both cases ?

Did you build any caching of results into your UDF ?  If not, then SQLite has 
no idea it can optimize it by checking to see if it’s using the same parameters 
each time.

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

[sqlite] Can the Explain explain what is going on here?

[Bart Smissaert]

Have a table (only used for testing) like this:

CREATE TABLE Table1([Field1] INTEGER)

It has 10 rows and all the values in Field1 are 1.
There are no indexes.

Now I run queries that include a UDF. The code of this UDF is in VBScript
and generally
this is slow.

This is the first SQL:

select testargs('abc', 12, 1.123) as xxx from table1

This produces an integer value and obviously it will be same for all the
rows.
This runs in about 3 seconds.

This is the second SQL:

select 0 + testargs('abc', 12, 1.123) as xxx from table1

Again obviously this will give exactly the same results.
Now though it runs in some 10 milliseconds.

I guess the reason for this difference is that SQLite doesn't recognize
that the result of the
first SQL will always be the same, so the UDF runs 10 times and that it
does recognize
this for the second SQL and the UDF only runs once.

Firstly, is this a bug?
I can see though that this scenario is pointless and won't happen other
than in experimenting.

Secondly, can the Explain statement tell me that indeed the UDF runs 10
times in the first SQL and only once for the second SQL?

Explain of the first SQL:

addr opcode p1 p2 p3 p4 p5 comment
---
0 Init 0 8 0   00 Start at 8
1 OpenRead 0 528 0 0 00 root=528 iDb=0; Table1
2 Rewind 0 6 0  00
3 Function0 7 2 1 TestArgs(3) 03 r[1]=func(r[2..4])
4 ResultRow 1 1 0  00 output=r[1]
5 Next 0 3 0  01
6 Close 0 0 0  00
7 Halt 0 0 0  00
8 Transaction 0 0 4911 0 01 usesStmtJournal=0
9 TableLock 0 528 0 Table1 00 iDb=0 root=528 write=0
10 String8 0 2 0 abc 00 r[2]='abc'
11 Integer 12 3 0  00 r[3]=12
12 Real 0 4 0 1.123 00 r[4]=1.123
13 Goto 0 1 0   00


Explain of the second
SQL:


addr opcode p1 p2 p3 p4 p5 comment
---
0 Init 0 8 0   00 Start at 8
1 OpenRead 0 528 0 0 00 root=528 iDb=0; Table1
2 Rewind 0 6 0  00
3 Add 3 2 1  00 r[1]=r[3]+r[2]
4 ResultRow 1 1 0  00 output=r[1]
5 Next 0 3 0  01
6 Close 0 0 0  00
7 Halt 0 0 0  00
8 Transaction 0 0 4911 0 01 usesStmtJournal=0
9 TableLock 0 528 0 Table1 00 iDb=0 root=528 write=0
10 Integer 0 2 0  00 r[2]=0
11 String8 0 4 0 abc 00 r[4]='abc'
12 Integer 12 5 0  00 r[5]=12
13 Real 0 6 0 1.123 00 r[6]=1.123
14 Function0 7 4 3 TestArgs(3) 03 r[3]=func(r[4..6])
15 Goto 0 1 0   00


Thanks for any insight.


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

[sqlite] problem with sqlite 4

[Jim Callahan]

In reply to
> On 3/01/2017, at 4:48 AM, claude.del-vi...@laposte.net

David Empson wrote:

SQLite Expert Personal is a third party product which uses the SQLite
> database engine. It is not using “SQLite 4” (which is in early development
> stages and not been released), but will be using some version of SQLite 3.
> This mailing list is not an appropriate place to get support for products
> which use SQLite, but this looks like odd behaviour with SQLite itself,
> which may be worth investigating further.


Moreover if one submits this bug report to the "SQLite Expert" mailing list
using the email address:

supp...@sqliteexpert.com

one may qualify for a $50 credit towards one's next purchase:

"Submit a bug report and get $50 discount when purchasing SQLite Expert
Professional!

If you submit one or more bug reports in either SQLite Expert Personal or
Professional, you will receive a promotional code by email which you can
use when purchasing SQLite Expert Professional for $50 discount (over 50%
of the original price).
Conditions:

   -  You must submit at least one bug report to qualify for this offer.
   -  Feature requests do not count as bug reports.
   -  The reported bug must be reproducible with the latest version of
   SQLite Expert.
   -  Multiple bug reports do not qualify for cumulative discount.
   -  If you already purchased SQLite Expert Professional, you are not
   entitled to a partial refund if you submit a bug report. However, you
   qualify for a discount if you wish to purchase an additional license."

http://www.sqliteexpert.com/support.html

I am not affiliated with Coral Creek Software and the only information I
could find out about the company as opposed to the product (in less than 2
minutes of Google searching) is:

https://www.bizapedia.com/fl/coral-creek-software.html

Jim Callahan
Orlando, FL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite shell tool

[David Raymond]

Unfortunately I don't think there is a way to get the times into redirected 
output automatically. It looks like the callback that handles the redirected 
output runs completely before the endTimer function runs, which does a straight 
up printf right there in the endTimer function with no knowledge of any main 
output redirection.

So it looks like recording times is done via copying off the console screen 
only. Or I suppose I should say that it would have to be done outside of the 
CLI to capture its output, be that copy/paste or something more fancy.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MONSTRUO Hugo González
Sent: Tuesday, January 03, 2017 3:35 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQlite shell tool

What are the orders to get the following text ?

I tried with:
.once prueba.txt
.output prueba.txt
.log prueba.txt
.timer ON
.echo ON
PRAGMA integrity_check;
...

but I did not get the lines "Run Time..." into de file

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> PRAGMA integrity_check;
ok
Run Time: real 11.962 user 9.205414 sys 2.752183
sqlite> SELECT COUNT (*) FROM bm_ppal;
724816
Run Time: real 0.016 user 0.005380 sys 0.009552
sqlite> SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.016 user 0.005368 sys 0.009806
sqlite> SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.058 user 0.046052 sys 0.010368
sqlite>
___
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] SQlite shell tool

[MONSTRUO Hugo González]

What are the orders to get the following text ?

I tried with:
.once prueba.txt
.output prueba.txt
.log prueba.txt
.timer ON
.echo ON
PRAGMA integrity_check;
...

but I did not get the lines "Run Time..." into de file

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> .timer ON
sqlite> PRAGMA integrity_check;
ok
Run Time: real 11.962 user 9.205414 sys 2.752183
sqlite> SELECT COUNT (*) FROM bm_ppal;
724816
Run Time: real 0.016 user 0.005380 sys 0.009552
sqlite> SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.016 user 0.005368 sys 0.009806
sqlite> SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId;
724816
Run Time: real 0.058 user 0.046052 sys 0.010368
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with sqlite 4

[Drago, William @ CSG - NARDA-MITEQ]

> The current version of SQLite Expert Personal is 4.2.0, available here:
>
> http://www.sqliteexpert.com/download.html
>
> They don’t appear to give any clues as to which version of SQLite the
> application is using.

The SQLite version is displayed just below the ribbon on the right hand side of 
the screen. It is 3.15.2

--
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] What is the best way to select "evenly" ?

[R Smith]

On 2017/01/02 5:28 PM, Ron Aaron wrote:

Hello, all -

I'm trying to select from a table which contains pairs of "id", "name",
where the "name" may appear in multiple "ids".  For example, the table
might have

"10", "abc"
"20", "abc"
"10", "def"
"10", "ghi"
"20", "ghi"
etc.That is, not every id will have every name in it.

What I would like to accomplish, is to select distinct names, along with
an id, such that the id is as uniformly represented as possible.

My current solution is to create a temporary table with a unique key for
the name, and select into it like:

insert or ignore into tbl
select id,name
from originaltbl
where name in (...)
order by random() ;

This sort of works, in that I get each name just once, but the
distribution of ids is not as uniform as I would like, and I'm
scratching my head as to how to accomplish this.

The purpose here is to distribute jobs over processing units,
distributing the load as evenly as possible.


Might I suggest:
CREATE TABLE tbl (UKey TEXT COLLATE NOCASE PRIMARY KEY);

INSERT OR IGNORE INTO tbl
SELECT DISTINCT id||name FROM originaltbl WHERE...

Perhaps use only as a temporary table, or keep it up to date with a trigger.

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

Re: [sqlite] Testing Sqlite3* DB_handle?

[Jens Alfke]

> On Jan 2, 2017, at 6:28 PM, Domonic Tom  wrote:
> 
> Is there a way to test whether the DB_handle used when opening a database is 
> good?
> I need to find some way of testing it to make sure it represents the live 
> connection to a database?

“DB_handle” isn’t a term used in the SQLite C API. If you’re using a different 
(wrapper) API, you need to state which one you’re using, because the answer may 
depend on it.

In the C API, after you’ve closed a connection, the `sqlite3*` pointer used as 
the database handle is garbage — it points to deallocated/freed heap memory. 
There’s no way to tell whether a heap block has been freed or not*. So the 
answer to your question is probably “no”.

—Jens

* not without groping into data structures private to the implementation of 
malloc/free...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite announce

[Richard Hipp]

The current thinking is that version 3.16.1 release will be available
within 24 hours and that I will simply do an announcement then, and
completely skip the 3.16.0 announcement.

On 1/3/17, David Raymond  wrote:
> Makes sense, thank you.
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Richard Hipp
> Sent: Tuesday, January 03, 2017 10:50 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] SQLite announce
>
> On 1/3/17, David Raymond  wrote:
>> Quick question, did something go out to the sqlite-announce mailing list
>> for
>> the 3.16.0 release? Just noticed that it got released yesterday and I'm
>> not
>> seeing a notification in my inbox or in any of my junk or spam folders. I
>> confirmed I'm on the list and a password reminder request email got
>> through
>> to my inbox just fine, so I just want to make sure it's something else on
>> my
>> end here.
>
> I should have announced it.  And I probably will.  But there are
> complications
>
> After we tagged the release and started publishing it, E.Pasma
> reported a bug against version 3.15.0, on this mailing list - a bug
> that was previously unknown to us and hence was not fixed by the new
> release.  And as we investigated his bug, we found another related
> problem.  (Both issues have to do with the row-value feature added by
> 3.15.0 and both are now fixed on trunk, we believe, though we are
> still testing.)
>
> We don't know yet if there will be a quick follow-on 3.16.1 release
> that fixes these problems.  I was holding off on the announcement of
> 3.16.0 until I figured that out.
> --
> 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
>


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

Re: [sqlite] SQLite announce

[David Raymond]

Makes sense, thank you.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, January 03, 2017 10:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQLite announce

On 1/3/17, David Raymond  wrote:
> Quick question, did something go out to the sqlite-announce mailing list for
> the 3.16.0 release? Just noticed that it got released yesterday and I'm not
> seeing a notification in my inbox or in any of my junk or spam folders. I
> confirmed I'm on the list and a password reminder request email got through
> to my inbox just fine, so I just want to make sure it's something else on my
> end here.

I should have announced it.  And I probably will.  But there are
complications

After we tagged the release and started publishing it, E.Pasma
reported a bug against version 3.15.0, on this mailing list - a bug
that was previously unknown to us and hence was not fixed by the new
release.  And as we investigated his bug, we found another related
problem.  (Both issues have to do with the row-value feature added by
3.15.0 and both are now fixed on trunk, we believe, though we are
still testing.)

We don't know yet if there will be a quick follow-on 3.16.1 release
that fixes these problems.  I was holding off on the announcement of
3.16.0 until I figured that out.
-- 
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] SQLite announce

[David Raymond]

Quick question, did something go out to the sqlite-announce mailing list for 
the 3.16.0 release? Just noticed that it got released yesterday and I'm not 
seeing a notification in my inbox or in any of my junk or spam folders. I 
confirmed I'm on the list and a password reminder request email got through to 
my inbox just fine, so I just want to make sure it's something else on my end 
here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite announce

[Richard Hipp]

On 1/3/17, David Raymond  wrote:
> Quick question, did something go out to the sqlite-announce mailing list for
> the 3.16.0 release? Just noticed that it got released yesterday and I'm not
> seeing a notification in my inbox or in any of my junk or spam folders. I
> confirmed I'm on the list and a password reminder request email got through
> to my inbox just fine, so I just want to make sure it's something else on my
> end here.

I should have announced it.  And I probably will.  But there are
complications

After we tagged the release and started publishing it, E.Pasma
reported a bug against version 3.15.0, on this mailing list - a bug
that was previously unknown to us and hence was not fixed by the new
release.  And as we investigated his bug, we found another related
problem.  (Both issues have to do with the row-value feature added by
3.15.0 and both are now fixed on trunk, we believe, though we are
still testing.)

We don't know yet if there will be a quick follow-on 3.16.1 release
that fixes these problems.  I was holding off on the announcement of
3.16.0 until I figured that out.
-- 
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] 3.16.0 Release notes bug

[jose isaias cabrera]

Happy new 2017 to everyone on this list.  Once again, I want to thank 
Dr. Hipp for his wonderful provision to the world called SQLite.


Anyway, in the release notes,

https://www.sqlite.org/releaselog/3_16_0.html

on the first bullet, between the parenthesis, it states:

(See the CPU performance measurement report for details on how the this 
performance increase was computed.)


I think the "the" or the "this" after the "how" could be deleted.  
Thanks.


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