[sqlite] Is this a proper call?

[Igor Korot]

Hi, ALL,

Is this a proper call to execute:

if( sqlite3_exec( m_handle, "PRAGMA foreign_keys = OFF", NULL, NULL, 0
) != SQLITE_OK )
{
// displaying error message
}

The problem is that after successful execution of such code, I am
still getting error:
"foreign key constraint failed" when executing the "DELETE FROM ..." query.

Is this correct? Or am I misunderstand something?

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

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Simon Slavin]

On 1 Dec 2013, at 4:51am, Tristan Van Berkom  wrote:

> Do you have a suggestion which does not make an assumption about what
> is the highest value of a character ?

No.  The trick is common in many computer languages and I've never seen a good 
formulaic way of doing that.  You generally see that the programmer has picked 
some high-placed character.

It provides /huge/ increases in speed in big databases so it can be worth 
doing, but it may not make a big difference in your case.

> Will this work without using the BETWEEN statement you describe above ?

In SQLite

a BETWEEN b AND c

is exactly equivalent to

a >= b AND a <= c

In fact I believe that the substitution is made at the parsing level rather 
than involving low-level changes to planning.  So you can just consider it two 
separate restrictions.

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

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Tristan Van Berkom]

On Sun, 2013-12-01 at 00:40 +, Simon Slavin wrote:
> On 30 Nov 2013, at 5:40pm, Tristan Van Berkom  wrote:
> 
> > So, is there a way that I can tell SQLite forcibly to
> > prioritize the index on email_list.value when making
> > a prefix match ?
> 
> Don't use LIKE or GLOB for prefix matches.  Although you as a human can tell 
> that
> 
> >>> email_list.value LIKE 'eddie%'
> 
> is a prefix match, all the computer sees is pattern-matching.  This makes it 
> try all the available combinations.  Instead use
> 
> email_list.value BETWEEN 'eddie' AND 'eddie}'
> 
> (I chose the close curly bracket because it has a very high code and will 
> sort near the end of the possibiliites.)
> 

I'm not comfortable with this.

Do you have a suggestion which does not make an assumption about what
is the highest value of a character ?

Perhaps there is a definite and clear way to find the highest possible
character ?

Using case sensitive LIKE statements *do* allow SQLite to traverse the
index on email_list.value, perhaps not as optimized as the trick you
propose but at least it does not compromise the user facing API.

This sounds like the kind of trick you can get away with when you
are in control of the data which is added to your DB, and the query
terms which will be issued against it (neither of these apply
in my case).

> Using BETWEEN allows SQLite to use any available index for searching and 
> sorting.  In this case it's equivalent to saying "We don't have to look 
> through all 120 pages of this book, we need only pages 34 to 49.".  In the 
> case of the SELECT you show, modified for my suggestion, a good index would 
> be something like
> 
> CREATE INDEX fiel_uv ON folder_id_email_list (uid, value)
> 
> This allows a specific match on a uid and then partial matching on value, 
> which is what it wants.  This can replace the existing index you mention.
> 

Will this work without using the BETWEEN statement you describe above ?
(I think this is orthogonal to the LIKE statement, but I should still
ask to be sure).

Would this replace:

  o The index created on email_list.uid, for the purpose of 
optimizing the DELETES involved in replacing existing entries ?

  o The index on email_list.value for optimized prefix searches
and exact matches ?

  o Both of the above ?

I suppose with this I could just remove the '+' from:
   " ON +email_list.uid = summary.uid " ?

This fancy index definitely interests me, and looks like it
would do the right thing to optimize phone number or email
matches for addressbooks of over 100,000 contacts.

However, as you can see it's not much of an issue at this point:
https://people.gnome.org/~tvb/eds-benchmarks-30-11-2013/filter-by-short-email-address-prefix.png

Even with 200,000 contacts, the short email prefix match is now
down to a couple milliseconds.

I would like to flatten that red line a bit more, but it would
be purely to satisfy my sense of perfectionism ;-)


Thank you very much for sharing your knowledge with me :)

Best,
-Tristan

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


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

Re: [sqlite] Bug in sqlite.exe? NOT !

[Eric Teutsch]

Thanks for that great detailed answer.  I've been meaning to figure out
manifests, your introduction to that subject convinced me to learn more
about it.
Cheers.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RSmith
Sent: November-30-13 20:21
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe? NOT !

The virtualization and UAC caused many a headache for unsuspecting
programmers not used to the Linux way. It's a brilliant new way they do it
but they had to move from an old way to a the new way in a way that wouldn't
break old Windows programs (too much). 
Virtualization provided just the trick at the cost of messing with a lot of
people's ideas of how things work.

If I may suggest that you don't try to find a way to circumvent the UAC -
though placing the DB files in "my documents" will do fine at that, rather
embrace the UAC and get yourself a manifest file set up and then use the
App-data folders in the User's folder for settings and a specified other
folder that is not in a system-tree for shared data. (Any will do, such as
C:\mydata\ - it doesn't care that you want to write data to the root, as
long as it isn't into a protected area). [Just Google "Creating a Windows
manifest" 
- the results are myriad]

With the manifest in place you are basically telling Windows "I know what Im
doing and will abide by the rules of not messing with the protected folders,
so please stop treating my program like a child and virtualizing my
paths"... and Windows will happily oblige. You can also use the manifest to
specifically require escalation to admin privileges from the current user
should you need it (the famous pop-up that goes "Program xxx wants to write
to your system folders, dangers are x y z" to which the user may then decide
to allow or deny, and once allowed, you have almost carte blanche on where
to write to. (almost...)

You could also just kill the UAC on your own machine if this is not a
commercial product, Windows let's you do it... but this is a silly thing and
it means your program will not be compatible with other user-computers on
which it isn't disabled.

Cheers,
Ryan


On 2013/12/01 02:17, Eric Teutsch wrote:
> Yep, that's the reason.  Thanks to Marcus and Clemens (with an extra 
> high-5 for the link) for figuring it out, and everybody for 
> suggestions.  Now to figure out where to put the database so that 
> non-admins can see the same file as admins...
> Eric
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
> Sent: November-30-13 16:15
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Bug in sqlite.exe?
>
> Marcus Grimm wrote:
>> You have the DB file in ProgramData, maybe you are a victim of the 
>> windows file virtualization ?
> Given the symptoms, this is very likely.
> See .
>
>
> Regards,
> Clemens
> ___
> 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

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

Re: [sqlite] Bug in sqlite.exe? NOT !

[RSmith]

The virtualization and UAC caused many a headache for unsuspecting programmers not used to the Linux way. It's a brilliant new way 
they do it but they had to move from an old way to a the new way in a way that wouldn't break old Windows programs (too much). 
Virtualization provided just the trick at the cost of messing with a lot of people's ideas of how things work.


If I may suggest that you don't try to find a way to circumvent the UAC - though placing the DB files in "my documents" will do fine 
at that, rather embrace the UAC and get yourself a manifest file set up and then use the App-data folders in the User's folder for 
settings and a specified other folder that is not in a system-tree for shared data. (Any will do, such as C:\mydata\ - it doesn't 
care that you want to write data to the root, as long as it isn't into a protected area). [Just Google "Creating a Windows manifest" 
- the results are myriad]


With the manifest in place you are basically telling Windows "I know what Im doing and will abide by the rules of not messing with 
the protected folders, so please stop treating my program like a child and virtualizing my paths"... and Windows will happily 
oblige. You can also use the manifest to specifically require escalation to admin privileges from the current user should you need 
it (the famous pop-up that goes "Program xxx wants to write to your system folders, dangers are x y z" to which the user may then 
decide to allow or deny, and once allowed, you have almost carte blanche on where to write to. (almost...)


You could also just kill the UAC on your own machine if this is not a commercial product, Windows let's you do it... but this is a 
silly thing and it means your program will not be compatible with other user-computers on which it isn't disabled.


Cheers,
Ryan


On 2013/12/01 02:17, Eric Teutsch wrote:

Yep, that's the reason.  Thanks to Marcus and Clemens (with an extra high-5
for the link) for figuring it out, and everybody for suggestions.  Now to
figure out where to put the database so that non-admins can see the same
file as admins...
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: November-30-13 16:15
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

Marcus Grimm wrote:

You have the DB file in ProgramData, maybe you are a victim of the
windows file virtualization ?

Given the symptoms, this is very likely.
See .


Regards,
Clemens
___
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] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Simon Slavin]

On 30 Nov 2013, at 5:40pm, Tristan Van Berkom  wrote:

> So, is there a way that I can tell SQLite forcibly to
> prioritize the index on email_list.value when making
> a prefix match ?

Don't use LIKE or GLOB for prefix matches.  Although you as a human can tell 
that

>>> email_list.value LIKE 'eddie%'

is a prefix match, all the computer sees is pattern-matching.  This makes it 
try all the available combinations.  Instead use

email_list.value BETWEEN 'eddie' AND 'eddie}'

(I chose the close curly bracket because it has a very high code and will sort 
near the end of the possibiliites.)

Using BETWEEN allows SQLite to use any available index for searching and 
sorting.  In this case it's equivalent to saying "We don't have to look through 
all 120 pages of this book, we need only pages 34 to 49.".  In the case of the 
SELECT you show, modified for my suggestion, a good index would be something 
like

CREATE INDEX fiel_uv ON folder_id_email_list (uid, value)

This allows a specific match on a uid and then partial matching on value, which 
is what it wants.  This can replace the existing index you mention.

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

Re: [sqlite] Bug in sqlite.exe? NOT !

[Eric Teutsch]

Yep, that's the reason.  Thanks to Marcus and Clemens (with an extra high-5
for the link) for figuring it out, and everybody for suggestions.  Now to
figure out where to put the database so that non-admins can see the same
file as admins...
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: November-30-13 16:15
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

Marcus Grimm wrote:
> You have the DB file in ProgramData, maybe you are a victim of the 
> windows file virtualization ?

Given the symptoms, this is very likely.
See .


Regards,
Clemens
___
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] Concrete example of corruption

[Richard Hipp]

On Sat, Nov 30, 2013 at 5:15 PM, L. Wood  wrote:

> Thanks, that makes sense.
>
> But I can pretty much rule this case out. In my case, it's highly unlikely
> that the folder would be moved during the brief instant between _open() and
> the actual opening of the "-journal" file.
>
> Are there other possible cases of corruption?
>



(1)  Process A opens database at /first/path/to/database.db
(2)  The directory is renamed to /second/path/to/database.db
(3)  Process B begins writing to the database, creating a rollback journal
at /second/path/to/database.db-journal
(4)  Process B crashes without committing or rolling back.
(5)  Process A tries to read the database.  It checks for a hot journal
file at /first/path/to/database.db-journal, finds none (since the hot
journal is now in a different place) and therefore does not know that it
needs to recover the database file.  It sees the database as being corrupt.
Or if A tries to write to the database, it makes the corruption worse, such
that even a third process that does see the hot journal is no longer able
to recover.


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

Re: [sqlite] Concrete example of corruption

[L. Wood]

Thanks, that makes sense.

But I can pretty much rule this case out. In my case, it's highly unlikely that 
the folder would be moved during the brief instant between _open() and the 
actual opening of the "-journal" file.

Are there other possible cases of corruption?


> Date: Sat, 30 Nov 2013 22:26:21 +0100
> From: clem...@ladisch.de
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concrete example of corruption
>
> L. Wood wrote:
>> /Users/lwood/Desktop/folder/db.sqlite
>>
>> Suppose I can only move/rename the *folder*. Suppose I never mess with the 
>> folder's contents.
>>
>> Can you name me a concrete example of corruption that could occur?
>
> 1. Open "/Users/lwood/Desktop/folder/db.sqlite".
> 2. Rename the folder.
> 3. Try to open "/Users/lwood/Desktop/folder/db.sqlite-journal".
>
>
> Regards,
> Clemens
> ___
> 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] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Donald Griggs]

Tristan,

My apologies to you and the list if you mentioned this earlier, but I
assume you've run the analyze command on your database, right?

http://www.sqlite.org/lang_analyze.html

Also possibly relevant:  http://www.sqlite.org/compile.html#enable_stat3

(Of course, Igor's suggestion of the uninary "+" to sent a hint to the
sqlite query planner gives an immediate solution to your particular
question.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[Clemens Ladisch]

L. Wood wrote:
> /Users/lwood/Desktop/folder/db.sqlite
>
> Suppose I can only move/rename the *folder*. Suppose I never mess with the 
> folder's contents.
>
> Can you name me a concrete example of corruption that could occur?

1. Open "/Users/lwood/Desktop/folder/db.sqlite".
2. Rename the folder.
3. Try to open "/Users/lwood/Desktop/folder/db.sqlite-journal".


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

Re: [sqlite] Bug in sqlite.exe?

[Clemens Ladisch]

Marcus Grimm wrote:
> You have the DB file in ProgramData, maybe you are a victim of the
> windows file virtualization ?

Given the symptoms, this is very likely.
See .


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

Re: [sqlite] Inefficient query plan in queries with multiple joins

[George]

On Sat, Nov 30, 2013 at 1:55 PM, Richard Hipp  wrote:
>
> If you have a LIMIT 25, then the TEMP B-TREE never holds more than 25 rows,
> specifically the top 25 rows seen so far.  But SQLite still has to scan
> through the entire results set looking for other rows that belong in the
> top 25.
>
> If you only order by the first column, then apparently there are indices
> that can be used to cause the results to come out of the query in the
> correct order to begin with, so only the first 25 rows of the result set
> need to be examined.  That's normally much, much faster than scanning the
> entire result setting looking for the 25 best.
>

Okay, let's assume this query:

select a.val, b.val, c.val, d.val
from
a
join b on a.fk = b.pk
join c on b.fk = c.pk
join d on c.fk = d.pk
order by a.val, b.val, c.val, d.val
limit 25

If the index on a.val is UNIQUE then the above query is equivalent to
the following:

select * from (

select a.val aval, b.val bval, c.val cval, d.val dval
from
a
join b on a.fk = b.pk
join c on b.fk = c.pk
join d on c.fk = d.pk
order by a.val
limit 25

) subquery
order by aval, bval, cval, dval

If the index on a.val is not UNIQUE then the database will have to act
slightly smarter and keep selecting rows from the "a" table (even
after the 25th one), as long as the value in their a.val column is
equal to that of the 25th row. Then it has only to sort those 25 or
more rows, something that will generally be almost instantaenous.

I assume that sqlite is not using the strategy I describe above, hence
the inefficient query plans.

For my application the index on a.val is not unique, so I will use a heuristic:

select a.val aval, b.val bval, c.val cval, d.val dval
from
a
join b on a.fk = b.pk
join c on b.fk = c.pk
join d on c.fk = d.pk
order by a.val
limit 25 * 10

) subquery
order by aval, bval, cval, dval
limit 25

For my data this will almost certainly return the correct result set.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

alternate stream?  What do you mean?  Are you referring to the :1 in the
path?  this would be something in QTADO or the ODBC driver.  I have an ODBC
system DSN that contains a straight-forward path to c:\ProgramData\...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf
Sent: November-30-13 15:11
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?


Why are you using an alternate stream for the directory?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- 
>boun...@sqlite.org] On Behalf Of Simon Slavin
>Sent: Saturday, 30 November, 2013 12:15
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>
>On 30 Nov 2013, at 7:03pm, Eric Teutsch  wrote:
>
>> I've taken a picture of the odbc tool (showing the tables and the
>database
>> name) and sqlite.exe with .database and .table showing.  You can see 
>> it
>> here: http://www.powersoft.ca/files/sqlite.png
>> It's the same file...
>
>Nice piece of evidence.  Saves us many questions.  Thanks.
>
>I don't know if the difference between 3.7.16.1 and 3.8.1 would cause 
>your problems.  But I suspect that something in the library your GUI 
>app is using is actually the cause.
>
>In any case, the sqlite3 shell tool you're using was developed by the 
>same people who wrote the SQLite API itself.  It is considered 
>cannonical and bugless -- or as bugless as an app which has had open 
>source code for ten years can be.  So if another app argues with it 
>it's the other app that's assumed to be buggy until a precise bug can be
found.
>
>You have, of course, tested for corruption ?  Try this:
>
>
>
>Ideally do this in the shell tool.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

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

Re: [sqlite] Bug in sqlite.exe?

[Keith Medcalf]

Why are you using an alternate stream for the directory?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Simon Slavin
>Sent: Saturday, 30 November, 2013 12:15
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>
>On 30 Nov 2013, at 7:03pm, Eric Teutsch  wrote:
>
>> I've taken a picture of the odbc tool (showing the tables and the
>database
>> name) and sqlite.exe with .database and .table showing.  You can see it
>> here: http://www.powersoft.ca/files/sqlite.png
>> It's the same file...
>
>Nice piece of evidence.  Saves us many questions.  Thanks.
>
>I don't know if the difference between 3.7.16.1 and 3.8.1 would cause
>your problems.  But I suspect that something in the library your GUI app
>is using is actually the cause.
>
>In any case, the sqlite3 shell tool you're using was developed by the
>same people who wrote the SQLite API itself.  It is considered cannonical
>and bugless -- or as bugless as an app which has had open source code for
>ten years can be.  So if another app argues with it it's the other app
>that's assumed to be buggy until a precise bug can be found.
>
>You have, of course, tested for corruption ?  Try this:
>
>
>
>Ideally do this in the shell tool.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] comiling sqlite.....

[Luuk]

Hi,

i'm new at compiling stuff, and trying out some things... ;)

I downloaded the sources last week (27nov) from 
http://www.sqlite.org/cgi/src/timeline?y=ci, and i got this file: 
SQLite-83c0bb9913838d18.tar.gz


Question1: Is this the correct source for SQLite 3.8.2?
or, How and where can i download the sources for SQLite 3.8.2 ?

After this i started compiling this stuff,
first i did: ./configure
than i did: make
after this i did: make test

Question2: because i have 79 errors, this probably indicates i dont have 
the correct sources?


<== last lines of output of 'make test'.>
Memory used:  now 24  max1035312  max-size   33030144
Allocation count: now  1  max1507730
Page-cache used:  now  0  max 13  max-size  65784
Page-cache overflow:  now  0  max5098848
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max  74752  max-size  66540
79 errors out of 2240712 tests
Failures on these tests: corruptC-2.9 fkey_malloc-4.transient.34 
fkey_malloc-4.transient.35 fkey_malloc-4.persistent.35 ioerr-10.79.3 
memsubsys1-3.1.4 trans2-2.10 trans2-2.30 trans2-3.10 trans2-3.30 
trans2-4.10 trans2-4.30 trans2-5.10 trans2-5.30 trans2-6.10 trans2-6.30 
trans2-7.10 trans2-7.30 trans2-8.10 trans2-8.30 trans2-9.10 trans2-9.30 
trans2-10.10 trans2-10.30 trans2-11.10 trans2-11.30 trans2-12.10 
trans2-12.30 trans2-13.10 trans2-13.30 trans2-14.10 trans2-14.30 
trans2-15.10 trans2-15.30 trans2-16.10 trans2-16.30 trans2-17.10 
trans2-17.30 trans2-18.10 trans2-18.30 trans2-19.10 trans2-19.30 
trans2-20.10 trans2-20.30 trans2-21.10 trans2-21.30 trans2-22.10 
trans2-22.30 trans2-23.10 trans2-23.30 trans2-24.10 trans2-24.30 
trans2-25.10 trans2-25.30 trans2-26.10 trans2-26.30 trans2-27.10 
trans2-27.30 trans2-28.10 trans2-28.30 trans2-29.10 trans2-29.30 
trans2-30.10 trans2-30.30 memsubsys1.ioerr-10.165.3 
memsubsys2.ioerr-10.79.3 utf16.ioerr-10.88.3 exclusive.ioerr-10.73.3 
persistent_journal_error.ioerr-10.82.3 no_journal_error.ioerr-10.82.3 
autovacuum_ioerr.ioerr-10.88.3 no_mutex_try.ioerr-10.79.3 
inmemory_journal.conflict2-6.1 inmemory_journal.conflict2-6.5 
inmemory_journal.conflict2-6.8 inmemory_journal.conflict2-6.10 
inmemory_journal.conflict2-6.13 inmemory_journal.conflict2-6.15 
prepare.fkey5-7.1
WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe 
Tcl build

All memory allocations freed - no leaks
Memory used:  now  0  max1035312  max-size   33030144
Allocation count: now  0  max1507730
Page-cache used:  now  0  max 13  max-size  65784
Page-cache overflow:  now  0  max5098848
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max  74752  max-size  66540
Maximum memory usage: 1035312 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls
make: *** [fulltest] Error 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite.exe?

[Marcus Grimm]

Am 2013-11-30 20:04, schrieb Eric Teutsch:

Sorry, should have pointed out that the "missing" tables are:
CollectionDevices and CollectionChannels


You have the DB file in ProgramData, maybe you are a victim of the
windows file virtualization ?
Try to use a different folder and see if that changes...

Marcus



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Teutsch
Sent: November-30-13 14:04
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Bug in sqlite.exe?

I've taken a picture of the odbc tool (showing the tables and the 
database
name) and sqlite.exe with .database and .table showing.  You can see 
it

here: http://www.powersoft.ca/files/sqlite.png
It's the same file...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: November-30-13 13:13
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

On 11/30/2013 11:55 AM, Eric Teutsch wrote:

.Tables doesn't show the 2 new tables.  A select statement on one of
those tables says "No such table".  And "select * from 
sqlite_master"

shows the 8 tables and 1 trigger.  And ends there.  But when using
sqliteodbc, I can run the select statement on a new table.


My educated guess is, you are not looking at the file you think you 
are
looking at. Somehow, in the shell you are opening a different 
database file
than the one you are opening in other tools, and that file does 
indeed have

8 tables and 1 trigger.
--
Igor Tandetnik

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

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

___
sqlite-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] Does SQLIte run under Windows CE?

[J Trahair]

Thank you everyone.

Jonathan

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

The integrity_check returned "OK" in sqlite.exe.  Tonight I will rebuild the
database and upgrade everything to run 3.8.1.  I understand your thinking
about bugs and SQLite vs other things (even though it's a bit NIMBY).  It's
just strange that something works in 3rd party tool, but doesn't work in
sqlite.exe.  See: http://www.powersoft.ca/files/sqlite2.png
Thanks for everybody's suggestions.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: November-30-13 14:15
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?


On 30 Nov 2013, at 7:03pm, Eric Teutsch  wrote:

> I've taken a picture of the odbc tool (showing the tables and the database
> name) and sqlite.exe with .database and .table showing.  You can see it
> here: http://www.powersoft.ca/files/sqlite.png  
> It's the same file...

Nice piece of evidence.  Saves us many questions.  Thanks.

I don't know if the difference between 3.7.16.1 and 3.8.1 would cause your
problems.  But I suspect that something in the library your GUI app is using
is actually the cause.

In any case, the sqlite3 shell tool you're using was developed by the same
people who wrote the SQLite API itself.  It is considered cannonical and
bugless -- or as bugless as an app which has had open source code for ten
years can be.  So if another app argues with it it's the other app that's
assumed to be buggy until a precise bug can be found.

You have, of course, tested for corruption ?  Try this:



Ideally do this in the shell tool.

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

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

Re: [sqlite] Bug in sqlite.exe?

[Simon Slavin]

On 30 Nov 2013, at 7:03pm, Eric Teutsch  wrote:

> I've taken a picture of the odbc tool (showing the tables and the database
> name) and sqlite.exe with .database and .table showing.  You can see it
> here: http://www.powersoft.ca/files/sqlite.png  
> It's the same file...

Nice piece of evidence.  Saves us many questions.  Thanks.

I don't know if the difference between 3.7.16.1 and 3.8.1 would cause your 
problems.  But I suspect that something in the library your GUI app is using is 
actually the cause.

In any case, the sqlite3 shell tool you're using was developed by the same 
people who wrote the SQLite API itself.  It is considered cannonical and 
bugless -- or as bugless as an app which has had open source code for ten years 
can be.  So if another app argues with it it's the other app that's assumed to 
be buggy until a precise bug can be found.

You have, of course, tested for corruption ?  Try this:



Ideally do this in the shell tool.

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

Sorry, should have pointed out that the "missing" tables are:
CollectionDevices and CollectionChannels

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Teutsch
Sent: November-30-13 14:04
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Bug in sqlite.exe?

I've taken a picture of the odbc tool (showing the tables and the database
name) and sqlite.exe with .database and .table showing.  You can see it
here: http://www.powersoft.ca/files/sqlite.png
It's the same file...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: November-30-13 13:13
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

On 11/30/2013 11:55 AM, Eric Teutsch wrote:
> .Tables doesn't show the 2 new tables.  A select statement on one of 
> those tables says "No such table".  And "select * from sqlite_master"
> shows the 8 tables and 1 trigger.  And ends there.  But when using 
> sqliteodbc, I can run the select statement on a new table.

My educated guess is, you are not looking at the file you think you are
looking at. Somehow, in the shell you are opening a different database file
than the one you are opening in other tools, and that file does indeed have
8 tables and 1 trigger.
--
Igor Tandetnik

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

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

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

I've taken a picture of the odbc tool (showing the tables and the database
name) and sqlite.exe with .database and .table showing.  You can see it
here: http://www.powersoft.ca/files/sqlite.png  
It's the same file...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: November-30-13 13:13
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

On 11/30/2013 11:55 AM, Eric Teutsch wrote:
> .Tables doesn't show the 2 new tables.  A select statement on one of 
> those tables says "No such table".  And "select * from sqlite_master" 
> shows the 8 tables and 1 trigger.  And ends there.  But when using 
> sqliteodbc, I can run the select statement on a new table.

My educated guess is, you are not looking at the file you think you are
looking at. Somehow, in the shell you are opening a different database file
than the one you are opening in other tools, and that file does indeed have
8 tables and 1 trigger.
--
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] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Igor Tandetnik]

On 11/30/2013 12:40 PM, Tristan Van Berkom wrote:

However, in a statement formed like the one you proposed
above, it screws with the query optimizer in SQLite I suspect,
i.e. when searching for a prefix on an email address,
SQLite (I suspect) decides to prioritize the UID index instead
of the perfectly good (much better even) index on email_list.value.


You can suppress the use of this index by writing " ON +email_list.uid = 
summary.uid ". Note the unary plus. It doesn't change the meaning of the 
expression, but makes it ineligible for participating in an index.

--
Igor Tandetnik

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

Re: [sqlite] Bug in sqlite.exe?

[Igor Tandetnik]

On 11/30/2013 11:55 AM, Eric Teutsch wrote:

.Tables doesn't show the 2 new tables.  A select statement on one of those
tables says "No such table".  And "select * from sqlite_master" shows the 8
tables and 1 trigger.  And ends there.  But when using sqliteodbc, I can run
the select statement on a new table.


My educated guess is, you are not looking at the file you think you are 
looking at. Somehow, in the shell you are opening a different database 
file than the one you are opening in other tools, and that file does 
indeed have 8 tables and 1 trigger.

--
Igor Tandetnik

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

The database has been closed and opened many times, by both the application,
as well as different adhoc query interfaces (sqlite.exe, sqlite expert,
qtado -- I've made sure that all access is terminated).  The database has
been deleted (no complaints like file is in use...) and regenerated with
same effect.  The only thing I haven't done in a while is reboot of
computer, I'll try that overnight.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: November-30-13 12:56
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?


On 30 Nov 2013, at 5:43pm, Eric Teutsch  wrote:

> That is an interesting concept and could explain another weirdness - 
> my sqliteodbc connection always tells me that the database is 
> readonly.  Is there a way to find out if a database is in a 
> transaction and waiting for a commit or a rollback?

The correct answer to this is to try closing the database or doing another
BEGIN.  Using the proper SQLite API you should get an error that they can't
be done because you're in the middle of a transaction.

However a lot of pretend APIs try to help you by tidying up loose ends for
you and won't admit anything is wrong.

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

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

Re: [sqlite] Bug in sqlite.exe?

[Simon Slavin]

On 30 Nov 2013, at 5:43pm, Eric Teutsch  wrote:

> That is an interesting concept and could explain another weirdness - my
> sqliteodbc connection always tells me that the database is readonly.  Is
> there a way to find out if a database is in a transaction and waiting for a
> commit or a rollback?

The correct answer to this is to try closing the database or doing another 
BEGIN.  Using the proper SQLite API you should get an error that they can't be 
done because you're in the middle of a transaction.

However a lot of pretend APIs try to help you by tidying up loose ends for you 
and won't admit anything is wrong.

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

Re: [sqlite] Bug in sqlite.exe?

[Simon Slavin]

On 30 Nov 2013, at 3:01pm, Eric Teutsch  wrote:

> Hi, I've got a database (3.7.16.1) which has a bunch of tables and 2
> triggers.  The order of creation is 8 tables, 2 triggers, 2 tables.  When
> looking at the database with the ODBC client (sqliteodb 0.992), I can see
> 100% of the tables (I use QTADO as my interactive tool, and it doesn't show
> triggers).  But when I look at the database with sqlite.exe (3.8.0.1), I see
> the 8 of tables and 1 trigger.  I cannot query the remaining two tables.

My guess is that you're opening two different database files, with the same 
name but in different folders.  Each program is assuming a different default 
folder.

For debugging purposes, try specifying a full path including folder name in 
both of your programs.  Or try

ALTER TABLE x RENAME TO y

in one of the applications and see if you can see the change when using the 
other application.

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

That is an interesting concept and could explain another weirdness - my
sqliteodbc connection always tells me that the database is readonly.  Is
there a way to find out if a database is in a transaction and waiting for a
commit or a rollback?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf
Sent: November-30-13 12:11
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?


COMMIT ?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- 
>boun...@sqlite.org] On Behalf Of Eric Teutsch
>Sent: Saturday, 30 November, 2013 09:56
>To: 'General Discussion of SQLite Database'
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>.Tables doesn't show the 2 new tables.  A select statement on one of 
>those tables says "No such table".  And "select * from sqlite_master" 
>shows the
>8
>tables and 1 trigger.  And ends there.  But when using sqliteodbc, I 
>can run the select statement on a new table.
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org
>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
>Sent: November-30-13 11:36
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>On 11/30/2013 10:01 AM, Eric Teutsch wrote:
>> But when I look at the database with sqlite.exe (3.8.0.1), I see the 
>> 8 of tables and 1 trigger.  I cannot query the remaining two tables.
>
>Define "cannot". Exactly what happens when you try? What do you mean by 
>"see" - see where and how?
>
>Can you show the results of this query:
>
>select * from sqlite_master
>
>
>___
>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

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

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Tristan Van Berkom]

On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote:
> On 11/27/2013 11:52 PM, Tristan Van Berkom wrote:
> > 
> > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary
> > LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list
> >  ON phone_list.uid = summary.uid
> > LEFT OUTER JOIN 'folder_id_email_list' AS email_list
> >  ON email_list.uid = summary.uid
> > WHERE (phone_list.value IS NOT NULL AND phone_list.value LIKE '%0505')
> >AND (email_list.value IS NOT NULL AND email_list.value LIKE 'eddie%')
> 
> Why are you using outer joins when your WHERE clause discards unmatched 
> records anyway? If you replace LEFT OUTER with INNER, the end result 
> would be exactly the same.
> 
> I have a strong feeling that, once you replace outer joins with regular 
> joins, this statement would run just as fast as your convoluted one with 
> nested selects. By using outer joins, you prevent SQLite from reordering 
> the conditions and using the most efficient search strategy - it is 
> forced to perform those joins left to right, which results in bad 
> performance because, apparently, you don't have indexes on 
> phone_list.uid or email_list.uid.
> 
> Try this straightforward query:
> 
> SELECT DISTINCT summary.uid, summary.vcard FROM folder_id AS summary
> JOIN folder_id_phone_list AS phone_list
>  ON phone_list.uid = summary.uid
> JOIN folder_id_email_list AS email_list
>  ON email_list.uid = summary.uid
> WHERE phone_list.value LIKE '%0505'  AND email_list.value LIKE 'eddie%';

This has been a great help, I've run benchmarks and this does perform
well with large data sets (large for addressbooks at least).

But I have now one more question.

I have a tradeoff that depends on whether I create an index
on email_list.uid or not.

The reason it was important to create an index on the uid
column (related to the primary key in the main summary table),
is because it speeds up inserts.

Maybe my insert statements are wrong, what I do at insert time
is first:
   DELETE FROM 'email_list' WHERE uid = 'the uid to remove'

And then I run the normal INSERT OR (REPLACE/FAIL) INTO
the summary table, and then populate the email_list table
again (it's important to replace all the rows in the email_list
table with the new ones from the new vCard).

When we have huge books, the index on email_list.uid helps
a lot.

However, in a statement formed like the one you proposed 
above, it screws with the query optimizer in SQLite I suspect,
i.e. when searching for a prefix on an email address,
SQLite (I suspect) decides to prioritize the UID index instead
of the perfectly good (much better even) index on email_list.value.

For exact matches, it would seem email_list.value is correctly
chosen for index traversal, for prefix matches, SQLite seems to
want to just prioritize the UID.

Here are my results:

Prefix matches on the email_list.value column:
  https://people.gnome.org/~tvb/filter-by-short-email-address-prefix.png

Exact matches on the email_list.value column:
  https://people.gnome.org/~tvb/filter-by-email-address.png

Inserting contacts:
  https://people.gnome.org/~tvb/contact-saving.png

The green line you can ignore, we're interested in "Master" and
"Experimental".

Master adds no index to the email_list.uid column

Experimental adds an index to the email_list.uid column

So, is there a way that I can tell SQLite forcibly to
prioritize the index on email_list.value when making
a prefix match ?

Would an explicit "COLLATE email_list.value" placed 
somewhere in the above query help ?

Thanks everyone for your help, I've come a long way with
the advice I've gotten from this list.

Best,
-Tristan


> 
> > o Leverage the index which I've created on 'folder_id_email_list'
> >   (I am using case insensitive LIKE statements so the indexes
> >   work in that statement).
> 
> Normally, you need case-sensitive LIKE in order to use the index, unless 
> the index is created with COLLATE NOCASE. You could use EXPLAIN QUERY 
> PLAN to confirm that the index is indeed being utilized.


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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

It works fine with SQLite Expert, using same version of SQLite as sqlite.exe
Thanks for all suggestions

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Chrzanowski
Sent: November-30-13 11:35
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?

Try using SQLite Expert (For your short term use, use the free version) and
see if you can pull up the DB.  You can select which DLL you want to use and
see if its a library issue or if its something to do with the database, or
whatever else.  I have a bunch of DLLs in my VCS that I can extract and put
online for your testing.


On Sat, Nov 30, 2013 at 10:01 AM, Eric Teutsch  wrote:

> Hi, I've got a database (3.7.16.1) which has a bunch of tables and 2 
> triggers.  The order of creation is 8 tables, 2 triggers, 2 tables.  
> When looking at the database with the ODBC client (sqliteodb 0.992), I 
> can see 100% of the tables (I use QTADO as my interactive tool, and it 
> doesn't show triggers).  But when I look at the database with 
> sqlite.exe (3.8.0.1), I see the 8 of tables and 1 trigger.  I cannot 
> query the remaining two tables.
> The database seems to work fine from within my application, and the 
> triggers seem to work properly.
>
>
>
> Any suggestions on what I should do about this?  I'm new with Sqlite, 
> and don't know how to submit a bug.  And maybe I screwed up somewhere 
> and the bug is behind this keyboard.
>
>
>
> Thanks,
>
> Eric
>
> ___
> 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] Bug in sqlite.exe?

[Keith Medcalf]

COMMIT ?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Eric Teutsch
>Sent: Saturday, 30 November, 2013 09:56
>To: 'General Discussion of SQLite Database'
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>.Tables doesn't show the 2 new tables.  A select statement on one of
>those
>tables says "No such table".  And "select * from sqlite_master" shows the
>8
>tables and 1 trigger.  And ends there.  But when using sqliteodbc, I can
>run
>the select statement on a new table.
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org
>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
>Sent: November-30-13 11:36
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Bug in sqlite.exe?
>
>On 11/30/2013 10:01 AM, Eric Teutsch wrote:
>> But when I look at the database with sqlite.exe (3.8.0.1), I see the 8
>> of tables and 1 trigger.  I cannot query the remaining two tables.
>
>Define "cannot". Exactly what happens when you try? What do you mean by
>"see" - see where and how?
>
>Can you show the results of this query:
>
>select * from sqlite_master
>
>
>___
>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] Bug in sqlite.exe?

[Eric Teutsch]

No, all names are without spaces.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mohit Sindhwani
Sent: November-30-13 11:59
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in sqlite.exe?

On 1/12/2013 12:55 AM, Eric Teutsch wrote:
> .Tables doesn't show the 2 new tables.  A select statement on one of 
> those tables says "No such table".  And "select * from sqlite_master" 
> shows the 8 tables and 1 trigger.  And ends there.  But when using 
> sqliteodbc, I can run the select statement on a new table.

Do any table names have spaces?

Best Regards,
Mohit.

___
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] Does SQLIte run under Windows CE?

[Mohit Sindhwani]

On 30/11/2013 1:28 AM, J Trahair wrote:
Does SQLIte run under Windows CE? And if so, which version should I 
download?


Thank you.

J Trahair


Yes, we are using various versions of 3.7.xx in Windows CE 5.0 and 6.0 
without any problems.


Best Regards,
Mohit.
1/12/2013 | 12:59 AM.

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

Re: [sqlite] Bug in sqlite.exe?

[Mohit Sindhwani]

On 1/12/2013 12:55 AM, Eric Teutsch wrote:

.Tables doesn't show the 2 new tables.  A select statement on one of those
tables says "No such table".  And "select * from sqlite_master" shows the 8
tables and 1 trigger.  And ends there.  But when using sqliteodbc, I can run
the select statement on a new table.


Do any table names have spaces?

Best Regards,
Mohit.

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

Re: [sqlite] Bug in sqlite.exe?

[Eric Teutsch]

.Tables doesn't show the 2 new tables.  A select statement on one of those
tables says "No such table".  And "select * from sqlite_master" shows the 8
tables and 1 trigger.  And ends there.  But when using sqliteodbc, I can run
the select statement on a new table.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: November-30-13 11:36
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

On 11/30/2013 10:01 AM, Eric Teutsch wrote:
> But when I look at the database with sqlite.exe (3.8.0.1), I see the 8 
> of tables and 1 trigger.  I cannot query the remaining two tables.

Define "cannot". Exactly what happens when you try? What do you mean by
"see" - see where and how?

Can you show the results of this query:

select * from sqlite_master


___
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] Percentile function in SQLite 3.8.0 (for the .NET version)

[Igor Tandetnik]

On 11/29/2013 6:38 PM, Hayden Livingston wrote:

I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.

I want to find the 25th %ile latency for this rowset.


If you mean "a value X such that 25% of all operations had latency 
greater than X", then something like this:


select min(latency) from (
  select latency from MyTable
  order by latency desc
  limit .25 * (select count(*) from MyTable)
);


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

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Jim Callahan]

Would the rtree be useful in a composite primary key and/or ad-hoc
composite query context?
http://www.sqlite.org/rtree.html

If so, how would SQLite know to create and/or use an rtree without explicit
user intervention?

Jim Callahan

> But this does point out a possible enhancement to SQLite:
> Suppose the query is something like this:
> SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25;
> If SQLite is unable to find a combination of indices that makes things
> naturally come out in a,b,c,d order, then it scans the entire result set
> looking for the 25 entries with the least values for a,b,c,d.  Even if the
> indices guarantee that "a" is coming out in order, because b,c,d are not in
> order, it still scans the entire result set.
> Suppose "a" is in order but b,c,d are not.  Then if the TEMP B-TREE is
> filled up with 25 entries and if the current "a" is greater than the
> largest "a" in the TEMP B-TREE, the scan can stop at that point, no?  But
> SQLite keeps on scanning until the end, even though none of the extra rows
> scanned will ever be in the top 25.
> Version 3.8.2 is in its quiet period right now.  But maybe we can do
> something about this for version 3.8.3.



On Sat, Nov 30, 2013 at 7:26 AM, Richard Hipp  wrote:

> On Sat, Nov 30, 2013 at 7:08 AM, Simon Slavin 
> wrote:
>
> >
> > On 30 Nov 2013, at 10:24am, George  wrote:
> >
> > > I have noticed that when I order using just one column then the query
> is
> > > very fast, because no TEMP B-TREE is used. When I add the other columns
> > > then TEMP B-TREE is used and the query is very slow.
> >
> > Do you have indexes on those four tables ideally suited to the query
> > you're executing ?  Think about how you, as a human, would need to look
> > things up on each table to satisfy your JOIN and ORDER BY clauses.
> >
> >
> For joins, it isn't sufficient just to have indices on the order-by
> columns.  There are lots of other conditions as well.  Generally speaking,
> the indices need to be UNIQUE and the columns indexed need to be NOT NULL.
> The OP's query might not be amenable to that.
>
> But this does point out a possible enhancement to SQLite:
>
> Suppose the query is something like this:
>
> SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25;
>
> If SQLite is unable to find a combination of indices that makes things
> naturally come out in a,b,c,d order, then it scans the entire result set
> looking for the 25 entries with the least values for a,b,c,d.  Even if the
> indices guarantee that "a" is coming out in order, because b,c,d are not in
> order, it still scans the entire result set.
>
> Suppose "a" is in order but b,c,d are not.  Then if the TEMP B-TREE is
> filled up with 25 entries and if the current "a" is greater than the
> largest "a" in the TEMP B-TREE, the scan can stop at that point, no?  But
> SQLite keeps on scanning until the end, even though none of the extra rows
> scanned will ever be in the top 25.
>
> Version 3.8.2 is in its quiet period right now.  But maybe we can do
> something about this for version 3.8.3.
> --
> 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] Bug in sqlite.exe?

[Igor Tandetnik]

On 11/30/2013 10:01 AM, Eric Teutsch wrote:

But when I look at the database with sqlite.exe (3.8.0.1), I see
the 8 of tables and 1 trigger.  I cannot query the remaining two tables.


Define "cannot". Exactly what happens when you try? What do you mean by 
"see" - see where and how?


Can you show the results of this query:

select * from sqlite_master


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

Re: [sqlite] Bug in sqlite.exe?

[Stephen Chrzanowski]

Try using SQLite Expert (For your short term use, use the free version) and
see if you can pull up the DB.  You can select which DLL you want to use
and see if its a library issue or if its something to do with the database,
or whatever else.  I have a bunch of DLLs in my VCS that I can extract and
put online for your testing.


On Sat, Nov 30, 2013 at 10:01 AM, Eric Teutsch  wrote:

> Hi, I've got a database (3.7.16.1) which has a bunch of tables and 2
> triggers.  The order of creation is 8 tables, 2 triggers, 2 tables.  When
> looking at the database with the ODBC client (sqliteodb 0.992), I can see
> 100% of the tables (I use QTADO as my interactive tool, and it doesn't show
> triggers).  But when I look at the database with sqlite.exe (3.8.0.1), I
> see
> the 8 of tables and 1 trigger.  I cannot query the remaining two tables.
> The database seems to work fine from within my application, and the
> triggers
> seem to work properly.
>
>
>
> Any suggestions on what I should do about this?  I'm new with Sqlite, and
> don't know how to submit a bug.  And maybe I screwed up somewhere and the
> bug is behind this keyboard.
>
>
>
> Thanks,
>
> Eric
>
> ___
> 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] Bug in sqlite.exe?

[Eric Teutsch]

Hi, I've got a database (3.7.16.1) which has a bunch of tables and 2
triggers.  The order of creation is 8 tables, 2 triggers, 2 tables.  When
looking at the database with the ODBC client (sqliteodb 0.992), I can see
100% of the tables (I use QTADO as my interactive tool, and it doesn't show
triggers).  But when I look at the database with sqlite.exe (3.8.0.1), I see
the 8 of tables and 1 trigger.  I cannot query the remaining two tables.
The database seems to work fine from within my application, and the triggers
seem to work properly.

 

Any suggestions on what I should do about this?  I'm new with Sqlite, and
don't know how to submit a bug.  And maybe I screwed up somewhere and the
bug is behind this keyboard.

 

Thanks,

Eric

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

Re: [sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)

[RSmith]

Hi Hayden,

The most usual form of percentile function (I believe) is where you have record values in a selection set (or "sample" from a 
"population" for the astute statisticians), and you wish to know if you sorted them all by value, and then grouped them in clusters 
of 1% so that you have a 100 such clusters in a row all numbered from 1 to 100, then in which n-th of those little clusters would 
entry x be?


Of course in real maths the "clusters" are really exact values and non-integer and in 
a set [ c | 0 < c < 100 ].

One of the short-cut methods of getting a percentile is simple indexing, but it won't give us an accurate x.xxx-th percentile, only 
an integer n-th percentile - which might suffice for your application and is a lot quicker to do.


Indexing simply requires ordering the data-values from small to large, then finding "the first value bigger than your value" in the 
list, subtract 1, divide by the total number of values in the list ( Count() ) and round it to find value that will be very very 
close to the actual percentile (but not exact).


Similarly, finding which value represents the n-th percentile where you specify the percentile and need the value for it (as is your 
question's case), you could simply find the n-th percentile index or indices and see which value(s) are in there, the last value of 
which (in case of multiple indices) usually represents it the best, especially if you reverse-engineer it back to a percentile.


This shortcut in SQL terms then:
SELECT `latency` FROM `latencytable` WHERE 1 ORDER BY `latency` ASC LIMIT (SELECT (Count() / ( 100 / ?1 ) ) FROM `latencytable` 
WHERE 1), 1;


The value ?1 in this case needs to be the required percentile, so if you are 
looking for the 25th percentile, then ?1 = 25.
(Note: ?1 = 0 may throw exceptions )

Essentially the query counts the values, finds the x-th percentile index (call it n), then lists the table ordered ascending 
starting from offset n and listing only 1 value, et voila.


Now this will be accurate enough on large samples. On really small samples where values are widely differing it will be more 
accurate to find a set of every index that falls within a percentile and then averaging it - which can easily be added to the above, 
but should not be needed and will take much more processing.


And if you have sets that are very very small (<50 items) then you should not 
have a need to know the percentile.


Hope this helps!
Ryan

(PS. in the query, I use "WHERE 1" and lots of quotes and spaces which all help to see the query structure better, but they are very 
superfluous in SQLite terms and may well be omitted.)




On 2013/11/30 01:38, Hayden Livingston wrote:

Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".

Is it like the EXCEL 2013 function?

I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.

I want to find the 25th %ile latency for this rowset.

(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?

Many thanks!
___
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] Concrete example of corruption

[L. Wood]

Let me put it this way:

Suppose I have my main database file *in a folder*:

/Users/lwood/Desktop/folder/db.sqlite

Suppose I can only move/rename the *folder*. Suppose I never mess with the 
folder's contents.

Can you name me a concrete example of corruption that could occur?

It seems that all the examples out there have to do with the 
"db.sqlite-journal" file getting a mismatching name or a different location 
from "db.sqlite", but that's not possible here. Both files are always together.

I'm on Mac OS X.

I don't see any problem. I can imagine getting an *error* from the SQLite C 
functions, from which I can easily recover by re-opening the database from the 
new path. But I can't imagine an example of *corruption*. Please name one.  
   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLIte run under Windows CE?

[Richard Hipp]

On Fri, Nov 29, 2013 at 12:28 PM, J Trahair  wrote:

> Does SQLIte run under Windows CE? And if so, which version should I
> download?
>

Yes.  All versions should work on WinCE.  See, for example, release test
10j for version 3.8.1: http://www.sqlite.org/checklists/3080100/index#c10

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

Re: [sqlite] Does SQLIte run under Windows CE?

[Stephan Beal]

On Fri, Nov 29, 2013 at 6:28 PM, J Trahair
wrote:

> Does SQLIte run under Windows CE? And if so, which version should I
> download?
>

i know that the version which was current in late 2007 ran on WinCE, but i
haven't tried any newer versions.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: Namespace System.Data.SQLite.Linq not accessible in Visual Studio 2013 Express

[Vladimir Bradaska]

Hello, guys. I want to report bug...




Namespace System.Data.SQLite.Linq not accessible in Visual Studio 2013
Express

Steps to reproduce bug:

Windows7x64SP1 machine,
Visual Studio 2013 Express for Desktop
[Installation date:  27. 11. 2013],

New Console C# project...install SQLite package...
NuGet Package: System.Data.SQLite (x86/x64)
[Installation date:  29. 11. 2013]
[Version: 1.0.89.0, Last Published: 27.10.2013],

System.Data.SQLite.dll and System.Data.SQLite.Linq.dll references exist,
but when...

Try to IntelliSense subitem of the System.Data.SQLite.Linq namespace...not
working!
Try to expand System.Data.SQLite.Linq namespace in Object Browser...failed
(empty)!
Tried with different target frameworks... 3.5, 4.0, 4.5, 4.5.1 ...all
failed!



Best regards!
Vladimir Bradaška
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)

[Hayden Livingston]

Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".

Is it like the EXCEL 2013 function?

I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.

I want to find the 25th %ile latency for this rowset.

(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?

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

[sqlite] Does SQLIte run under Windows CE?

[J Trahair]

Does SQLIte run under Windows CE? And if so, which version should I 
download?


Thank you.

J Trahair

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

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Richard Hipp]

On Sat, Nov 30, 2013 at 7:08 AM, Simon Slavin  wrote:

>
> On 30 Nov 2013, at 10:24am, George  wrote:
>
> > I have noticed that when I order using just one column then the query is
> > very fast, because no TEMP B-TREE is used. When I add the other columns
> > then TEMP B-TREE is used and the query is very slow.
>
> Do you have indexes on those four tables ideally suited to the query
> you're executing ?  Think about how you, as a human, would need to look
> things up on each table to satisfy your JOIN and ORDER BY clauses.
>
>
For joins, it isn't sufficient just to have indices on the order-by
columns.  There are lots of other conditions as well.  Generally speaking,
the indices need to be UNIQUE and the columns indexed need to be NOT NULL.
The OP's query might not be amenable to that.

But this does point out a possible enhancement to SQLite:

Suppose the query is something like this:

SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25;

If SQLite is unable to find a combination of indices that makes things
naturally come out in a,b,c,d order, then it scans the entire result set
looking for the 25 entries with the least values for a,b,c,d.  Even if the
indices guarantee that "a" is coming out in order, because b,c,d are not in
order, it still scans the entire result set.

Suppose "a" is in order but b,c,d are not.  Then if the TEMP B-TREE is
filled up with 25 entries and if the current "a" is greater than the
largest "a" in the TEMP B-TREE, the scan can stop at that point, no?  But
SQLite keeps on scanning until the end, even though none of the extra rows
scanned will ever be in the top 25.

Version 3.8.2 is in its quiet period right now.  But maybe we can do
something about this for version 3.8.3.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Simon Slavin]

On 30 Nov 2013, at 10:24am, George  wrote:

> I have noticed that when I order using just one column then the query is
> very fast, because no TEMP B-TREE is used. When I add the other columns
> then TEMP B-TREE is used and the query is very slow.

Do you have indexes on those four tables ideally suited to the query you're 
executing ?  Think about how you, as a human, would need to look things up on 
each table to satisfy your JOIN and ORDER BY clauses.

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

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Richard Hipp]

On Sat, Nov 30, 2013 at 5:24 AM, George  wrote:

> I have a query that is used to populate a table in my website. The query
> joins 4 different tables and returns around 10 columns, and I want to order
> on 4 of them, each of those 4 being on a different table. I also use a
> LIMIT clause for pagination.
>
> I have noticed that when I order using just one column then the query is
> very fast, because no TEMP B-TREE is used. When I add the other columns
> then TEMP B-TREE is used and the query is very slow.
>
> Is there some way to avoid this TEMP B-TREE? Since I am using LIMIT 25, the
> database really only needs to order those 25 (or slightly more) rows.
>

If you have a LIMIT 25, then the TEMP B-TREE never holds more than 25 rows,
specifically the top 25 rows seen so far.  But SQLite still has to scan
through the entire results set looking for other rows that belong in the
top 25.

If you only order by the first column, then apparently there are indices
that can be used to cause the results to come out of the query in the
correct order to begin with, so only the first 25 rows of the result set
need to be examined.  That's normally much, much faster than scanning the
entire result setting looking for the 25 best.


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

Re: [sqlite] Inefficient query plan in queries with multiple joins

[Kees Nuyt]

On Sat, 30 Nov 2013 12:24:01 +0200, George 
wrote:

>I have a query that is used to populate a table in my website. The query
>joins 4 different tables and returns around 10 columns, and I want to order
>on 4 of them, each of those 4 being on a different table. I also use a
>LIMIT clause for pagination.
>
>I have noticed that when I order using just one column then the query is
>very fast, because no TEMP B-TREE is used. When I add the other columns
>then TEMP B-TREE is used and the query is very slow.
>
>Is there some way to avoid this TEMP B-TREE? Since I am using LIMIT 25, the
>database really only needs to order those 25 (or slightly more) rows.

No, to be able to order those, it has to order the whole result set, and
offer you 25 rows of the ordered result.

You could try to use more restrictive ON and/or WHERE clauses to reduce
the size of the intermediate result set.

For faster pagination, read :
http://sqlite.org/cvstrac/wiki?p=ScrollingCursor 



-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] Inefficient query plan in queries with multiple joins

[George]

I have a query that is used to populate a table in my website. The query
joins 4 different tables and returns around 10 columns, and I want to order
on 4 of them, each of those 4 being on a different table. I also use a
LIMIT clause for pagination.

I have noticed that when I order using just one column then the query is
very fast, because no TEMP B-TREE is used. When I add the other columns
then TEMP B-TREE is used and the query is very slow.

Is there some way to avoid this TEMP B-TREE? Since I am using LIMIT 25, the
database really only needs to order those 25 (or slightly more) rows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users