[sqlite] catastrophic installation on Ubuntu 11.10

[Jean-Christophe]

Hello,

I have to tell that I didn't knew SQLite at all when I started this!
On an Ubuntu 11.10, I have installed:
command line interface for SQLite
Navigateur de bases de données SQLite
Sqliteman

Afterwards, I have downloaded the last version from your website and 
install it with make install.


When I try to make a new database in command line, I have:

|SQLite header and source version mismatch
2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
jean-christophe@PC01:~/Téléchargements/sqlite-autoconf-3071100$|

I look at google, make some research and I find a post, where they use 
ldconfig. When I try ldconfig on my machine, I have this:


|jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
libsqlite3.so.0 (libc6) =>  /usr/lib/i386-linux-gnu/libsqlite3.so.0
libsqlite.so.0 (libc6) =>  /usr/lib/libsqlite.so.0|

So far, so good.
I want to uninstall everything, and start from scratch.
I use the program manager from ubuntu and uninstall: command line 
interface for SQLite, Navigateur de bases de données SQLite and Sqliteman

I make uninstall the downloaded installed package.

All the programs are removed, but if I make the samme command as 
previously, I have now this:

jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
[sudo] password for jean-christophe:
libsqlite3.so.0 (libc6) => /usr/lib/i386-linux-gnu/libsqlite3.so.0
libsqlite3.so.0 (libc6) => /usr/local/lib/libsqlite3.so.0
libsqlite3.so (libc6) => /usr/local/lib/libsqlite3.so
libsqlite.so.0 (libc6) => /usr/lib/libsqlite.so.0

Can you please tell me, what I have to do for removing all occurencies 
of SQLite without damaging some programs using it?
When it's down, could you maybe suggest me som tutorials. I have plan to 
use it with Lazarus.


Thank you
Best regards
Jean-Christophe



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

Re: [sqlite] catastrophic installation on Ubuntu 11.10

[Richard Hipp]

On Thu, Apr 26, 2012 at 2:37 AM, Jean-Christophe  wrote:

> Hello,
>
> I have to tell that I didn't knew SQLite at all when I started this!
> On an Ubuntu 11.10, I have installed:
> command line interface for SQLite
> Navigateur de bases de données SQLite
> Sqliteman
>
> Afterwards, I have downloaded the last version from your website and
> install it with make install.
>
> When I try to make a new database in command line, I have:
>
> |SQLite header and source version mismatch
> 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c9362**72862f32f2
> 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d00**62dc364669
> jean-christophe@PC01:~/**Téléchargements/sqlite-**autoconf-3071100$|
>

This happened because when you compiled the new version of SQLite, the
Makefile somehow picked up the preexisting "sqlite3.h" header file that was
already installed on Ubuntu rather than the latest header file from the
source tree.

What procedure did you use to build?  Can you send us the output of your
build?



>
> I look at google, make some research and I find a post, where they use
> ldconfig. When I try ldconfig on my machine, I have this:
>
> |jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
>libsqlite3.so.0 (libc6) =>  /usr/lib/i386-linux-gnu/**libsqlite3.so.0
>libsqlite.so.0 (libc6) =>  /usr/lib/libsqlite.so.0|
>
> So far, so good.
> I want to uninstall everything, and start from scratch.
> I use the program manager from ubuntu and uninstall: command line
> interface for SQLite, Navigateur de bases de données SQLite and Sqliteman
> I make uninstall the downloaded installed package.
>
> All the programs are removed, but if I make the samme command as
> previously, I have now this:
> jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
> [sudo] password for jean-christophe:
>libsqlite3.so.0 (libc6) => /usr/lib/i386-linux-gnu/**libsqlite3.so.0
>libsqlite3.so.0 (libc6) => /usr/local/lib/libsqlite3.so.0
>libsqlite3.so (libc6) => /usr/local/lib/libsqlite3.so
>libsqlite.so.0 (libc6) => /usr/lib/libsqlite.so.0
>
> Can you please tell me, what I have to do for removing all occurencies of
> SQLite without damaging some programs using it?
> When it's down, could you maybe suggest me som tutorials. I have plan to
> use it with Lazarus.
>
> Thank you
> Best regards
> Jean-Christophe
>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
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] Logging of sqlite3 commands

[Champ Lee]

Richard,

This is excellent news. Thank you so much for such a wonderful tool and
support.

Best regards,
Champ Lee


On Wed, Apr 25, 2012 at 8:47 PM, Richard Hipp  wrote:

> On Wed, Apr 25, 2012 at 4:59 PM, Champ Lee  wrote:
>
> > Hello List,
> >
> > I am using the sqlite3 client on MS windows from a command prompt. I
> > frequently issue a number of select commands while exploring data, and
> > before I know it, have entered several dozen, fairly complex select
> > commands. I would like to capture all of those commands into a file.  Is
> > there a way that I can turn on some kind of command logging, where
> > everything I typed in could be echoed to a file? From a standard DOS
> > command line I might do something like:
> > doskey /h >>c:\logfile.txt
> >
>
> The ".trace" command was added to the command-line shell on
> 2012-04-04.
> It will be in the next release.  Or you can grab the latest source from the
> VCS  and compile them yourself if you
> are in a hurry.
>
>
> >
> > Thanks in advance for any pointers,
> > champl
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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

[sqlite] Query planner creating a slow plan

[Peter]

Hi,

I have a view 'transfer_history' which aggregates records from 3 tables
using UNION ALL. the aggregate is about 102k records.

I have a query:

SELECT transfer_date from transfer_history
 WHERE regn_no = '039540' and transfer_date <= '2012-05-01'

This returns three records and takes a couple of milliseconds - good.

But if I add an 'order by' clause, or an aggregate (max) on
transfer_date, the time goes up to > 300ms. The reason seems to be that
the query planner uses scans for all three sub-queries instead of using
indexes on the underlying tables.

With the basic query yhe QP says;

SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_dateSeems to me it ought to be able to just sort the result of the first 
plan. ATM it's an order of magnitude quicker at least to do the sort in 
Python in the application.


Pete

--

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

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

[nn6eumtr]

To clarify, the below steps to reproduce include building the 
amalgamation from scratch, and the errors I demonstrate occur after 
building and compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE 
and -DSQLITE_OMIT_FOREIGN_KEY.


In Stepheen Beal's earlier comments he appears to have not realized that 
I was rebuilding the amalgamation and not trying to use the 
-DSQLITE_OMIT_* flags with the distributed amalgamation. I still need 
some assistance.


On 4/25/2012 1:09 AM, nn6eumtr wrote:

Steps to recreate:

1) wget -nd -nH -c -t 0 -w 1
http://www.sqlite.org/src/tarball/SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b

2) mv -v
SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b
SQLite-9fb7da6904e479f4.tar.gz
3) tar -xzf SQLite-9fb7da6904e479f4.tar.gz
4) cd SQLite-9fb7da6904e479f4
5) OPT_FEATURE_FLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS
-DSQLITE_ENABLE_FTS4 \
-DSQLITE_ENABLE_LOCKING_STYLE=0 -DSQLITE_ENABLE_MEMORY_MANAGEMENT \
-DSQLITE_ENABLE_MEMSYS5 -DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \
-DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM
-DSQLITE_OMIT_BUILTIN_TEST \
-DSQLITE_OMIT_COMPLETE -DSQLITE_OMIT_DATETIME_FUNCS \
-DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \
-DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \
-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE -DSQLITE_OMIT_TRACE \
-DSQLITE_OMIT_TRIGGER -DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0" \
./configure --disable-tcl --disable-readline
6) make sqlite3.c
8) Compile - gcc -c -Wall -DSQLITE_ENABLE_FTS3_PARENTHESIS \
-DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_LOCKING_STYLE=0 \
-DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_MEMSYS5 \
-DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \
-DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM \
-DSQLITE_OMIT_BUILTIN_TEST -DSQLITE_OMIT_COMPLETE \
-DSQLITE_OMIT_DATETIME_FUNCS -DSQLITE_OMIT_DEPRECATED \
-DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \
-DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \
-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE \
-DSQLITE_OMIT_TRACE -DSQLITE_OMIT_TRIGGER \
-DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0 \
-DSQLITE_ZERO_MALLOC sqlite3.c

This will produce the following errors:

sqlite3.c:(.text+0x462d2): undefined reference to
`sqlite3AlterBeginAddColumn'
sqlite3.c:(.text+0x462fe): undefined reference to
`sqlite3AlterFinishAddColumn'
sqlite3.c:(.text+0x46331): undefined reference to `sqlite3AlterRenameTable'
sqlite3.c:(.text+0x46482): undefined reference to `sqlite3DropTrigger'
sqlite3.c:(.text+0x4657b): undefined reference to
`sqlite3TriggerSelectStep'
sqlite3.c:(.text+0x465b9): undefined reference to
`sqlite3TriggerDeleteStep'
sqlite3.c:(.text+0x4660a): undefined reference to
`sqlite3TriggerInsertStep'
sqlite3.c:(.text+0x46661): undefined reference to
`sqlite3TriggerInsertStep'
sqlite3.c:(.text+0x466b0): undefined reference to
`sqlite3TriggerUpdateStep'
sqlite3.c:(.text+0x4688d): undefined reference to `sqlite3BeginTrigger'
sqlite3.c:(.text+0x468f8): undefined reference to `sqlite3FinishTrigger'


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

Re: [sqlite] catastrophic installation on Ubuntu 11.10

[Jean-Christophe]

Hello Richard

How I did it:

./configure
make
make install

I join the config.log.
If it is not the file you need, please tell me what to do, so I can make a new 
one.

Best Regards
Jean-Christophe





Le 26-04-2012 13:04, Richard Hipp a écrit :

On Thu, Apr 26, 2012 at 2:37 AM, Jean-Christophe  wrote:


Hello,

I have to tell that I didn't knew SQLite at all when I started this!
On an Ubuntu 11.10, I have installed:
command line interface for SQLite
Navigateur de bases de données SQLite
Sqliteman

Afterwards, I have downloaded the last version from your website and
install it with make install.

When I try to make a new database in command line, I have:

|SQLite header and source version mismatch
2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c9362**72862f32f2
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d00**62dc364669
jean-christophe@PC01:~/**Téléchargements/sqlite-**autoconf-3071100$|


This happened because when you compiled the new version of SQLite, the
Makefile somehow picked up the preexisting "sqlite3.h" header file that was
already installed on Ubuntu rather than the latest header file from the
source tree.

What procedure did you use to build?  Can you send us the output of your
build?




I look at google, make some research and I find a post, where they use
ldconfig. When I try ldconfig on my machine, I have this:

|jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
libsqlite3.so.0 (libc6) =>   /usr/lib/i386-linux-gnu/**libsqlite3.so.0
libsqlite.so.0 (libc6) =>   /usr/lib/libsqlite.so.0|

So far, so good.
I want to uninstall everything, and start from scratch.
I use the program manager from ubuntu and uninstall: command line
interface for SQLite, Navigateur de bases de données SQLite and Sqliteman
I make uninstall the downloaded installed package.

All the programs are removed, but if I make the samme command as
previously, I have now this:
jean-christophe@PC01:~$ sudo ldconfig -p | grep sqlite
[sudo] password for jean-christophe:
libsqlite3.so.0 (libc6) =>  /usr/lib/i386-linux-gnu/**libsqlite3.so.0
libsqlite3.so.0 (libc6) =>  /usr/local/lib/libsqlite3.so.0
libsqlite3.so (libc6) =>  /usr/local/lib/libsqlite3.so
libsqlite.so.0 (libc6) =>  /usr/lib/libsqlite.so.0

Can you please tell me, what I have to do for removing all occurencies of
SQLite without damaging some programs using it?
When it's down, could you maybe suggest me som tutorials. I have plan to
use it with Lazarus.

Thank you
Best regards
Jean-Christophe



__**_
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] Possible bug when rebuilding amalgamation without triggers or altertable

[Stephan Beal]

On Thu, Apr 26, 2012 at 1:11 AM, nn6eumtr  wrote:

> In Stepheen Beal's earlier comments he appears to have not realized that I
> was rebuilding the amalgamation and not trying to use the -DSQLITE_OMIT_*
> flags with the distributed amalgamation. I still need some assistance.


The docs go on to say:

All of the SQLITE_OMIT_* options are unsupported.

*Important Note: The SQLITE_OMIT_* compile-time options are unsupported.*

The SQLITE_OMIT_* compile-time options are usually untested and are almost
certainly untested in combination. Any or all of these options may be
removed from the code in future releases and without warning. For any
particular release, some of these options may cause compile-time or
run-time failures, particularly when used in combination with other options.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

[Pavel Ivanov]

> To clarify, the below steps to reproduce include building the amalgamation
> from scratch, and the errors I demonstrate occur after building and
> compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and
> -DSQLITE_OMIT_FOREIGN_KEY.

It's still not clear, did you define these OMITs while building your
new amalgamation? If no you must do it in order to build correct
amalgamation. If yes then these errors fall under the "unsupported
options" statement Stephan points you to. But probably SQLite team
will fix this in some future release if they have some spare time and
the fix is not too expensive for them.


Pavel


On Wed, Apr 25, 2012 at 7:11 PM, nn6eumtr  wrote:
> To clarify, the below steps to reproduce include building the amalgamation
> from scratch, and the errors I demonstrate occur after building and
> compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and
> -DSQLITE_OMIT_FOREIGN_KEY.
>
> In Stepheen Beal's earlier comments he appears to have not realized that I
> was rebuilding the amalgamation and not trying to use the -DSQLITE_OMIT_*
> flags with the distributed amalgamation. I still need some assistance.
>
>
> On 4/25/2012 1:09 AM, nn6eumtr wrote:
>>
>> Steps to recreate:
>>
>> 1) wget -nd -nH -c -t 0 -w 1
>>
>> http://www.sqlite.org/src/tarball/SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b
>>
>> 2) mv -v
>>
>> SQLite-9fb7da6904e479f4.tar.gz?uuid=9fb7da6904e479f4671eeebf1a4b7e4e4e4f2b7b
>> SQLite-9fb7da6904e479f4.tar.gz
>> 3) tar -xzf SQLite-9fb7da6904e479f4.tar.gz
>> 4) cd SQLite-9fb7da6904e479f4
>> 5) OPT_FEATURE_FLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS
>> -DSQLITE_ENABLE_FTS4 \
>> -DSQLITE_ENABLE_LOCKING_STYLE=0 -DSQLITE_ENABLE_MEMORY_MANAGEMENT \
>> -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \
>> -DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM
>> -DSQLITE_OMIT_BUILTIN_TEST \
>> -DSQLITE_OMIT_COMPLETE -DSQLITE_OMIT_DATETIME_FUNCS \
>> -DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \
>> -DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \
>> -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE -DSQLITE_OMIT_TRACE
>> \
>> -DSQLITE_OMIT_TRIGGER -DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0" \
>> ./configure --disable-tcl --disable-readline
>> 6) make sqlite3.c
>> 8) Compile - gcc -c -Wall -DSQLITE_ENABLE_FTS3_PARENTHESIS \
>> -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_LOCKING_STYLE=0 \
>> -DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_MEMSYS5 \
>> -DSQLITE_ENABLE_STAT3 -DSQLITE_OMIT_ALTERTABLE \
>> -DSQLITE_OMIT_AUTOINIT -DSQLITE_OMIT_AUTOVACUUM \
>> -DSQLITE_OMIT_BUILTIN_TEST -DSQLITE_OMIT_COMPLETE \
>> -DSQLITE_OMIT_DATETIME_FUNCS -DSQLITE_OMIT_DEPRECATED \
>> -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY \
>> -DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_LOAD_EXTENSION \
>> -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_TCL_VARIABLE \
>> -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_TRIGGER \
>> -DSQLITE_OMIT_UTF16 -DSQLITE_THREADSAFE=0 \
>> -DSQLITE_ZERO_MALLOC sqlite3.c
>>
>> This will produce the following errors:
>>
>> sqlite3.c:(.text+0x462d2): undefined reference to
>> `sqlite3AlterBeginAddColumn'
>> sqlite3.c:(.text+0x462fe): undefined reference to
>> `sqlite3AlterFinishAddColumn'
>> sqlite3.c:(.text+0x46331): undefined reference to
>> `sqlite3AlterRenameTable'
>> sqlite3.c:(.text+0x46482): undefined reference to `sqlite3DropTrigger'
>> sqlite3.c:(.text+0x4657b): undefined reference to
>> `sqlite3TriggerSelectStep'
>> sqlite3.c:(.text+0x465b9): undefined reference to
>> `sqlite3TriggerDeleteStep'
>> sqlite3.c:(.text+0x4660a): undefined reference to
>> `sqlite3TriggerInsertStep'
>> sqlite3.c:(.text+0x46661): undefined reference to
>> `sqlite3TriggerInsertStep'
>> sqlite3.c:(.text+0x466b0): undefined reference to
>> `sqlite3TriggerUpdateStep'
>> sqlite3.c:(.text+0x4688d): undefined reference to `sqlite3BeginTrigger'
>> sqlite3.c:(.text+0x468f8): undefined reference to `sqlite3FinishTrigger'
>>
> ___
> 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] Possible bug when rebuilding amalgamation without triggers or altertable

[Stephan Beal]

On Thu, Apr 26, 2012 at 4:05 PM, Pavel Ivanov  wrote:

> amalgamation. If yes then these errors fall under the "unsupported
> options" statement Stephan points you to. But probably SQLite team
> will fix this in some future release if they have some spare time and
> the fix is not too expensive for them.
>

The second "important note" does not appear to be specific to the
amalgamation build. It appears to (in my interpretation) be making a
blanket statement about those options.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

What happens if you use a subselect?



selsect transfer_date from (select transfer_date from transfer_history where 
regn_no='039540' and transfer_date <= '2012-05-01') order by transfer_date;





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 7:00 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Query planner creating a slow plan

Hi,

I have a view 'transfer_history' which aggregates records from 3 tables
using UNION ALL. the aggregate is about 102k records.

I have a query:

SELECT transfer_date from transfer_history
  WHERE regn_no = '039540' and transfer_date <= '2012-05-01'

This returns three records and takes a couple of milliseconds - good.

But if I add an 'order by' clause, or an aggregate (max) on
transfer_date, the time goes up to > 300ms. The reason seems to be that
the query planner uses scans for all three sub-queries instead of using
indexes on the underlying tables.

With the basic query yhe QP says;

SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_datehttp://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] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 15:11:

select transfer_date from (select transfer_date from
transfer_history where regn_no='039540' and transfer_date<=
'2012-05-01') order by transfer_date;



Makes no difference.

Pete

--

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

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

[Pavel Ivanov]

On Thu, Apr 26, 2012 at 10:09 AM, Stephan Beal  wrote:
> On Thu, Apr 26, 2012 at 4:05 PM, Pavel Ivanov  wrote:
>> amalgamation. If yes then these errors fall under the "unsupported
>> options" statement Stephan points you to. But probably SQLite team
>> will fix this in some future release if they have some spare time and
>> the fix is not too expensive for them.
>>
>
> The second "important note" does not appear to be specific to the
> amalgamation build. It appears to (in my interpretation) be making a
> blanket statement about those options.

Yes, you're right. I just noted that to execute building with OMITs
correctly (and to have a chance of successful build) one should define
those OMITs while building amalgamation. If this building procedure
was correctly followed then sorry, no luck, read "important note".
I.e. developers didn't test this combination of OMITs and it doesn't
have to work. Maybe they fix this or you can propose a patch.


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

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

I suppose using a temporary table is out of the question?  But, then again, 
that only solves the specific issue.  I guess the more general question is how 
views with unions interact with aggregates and order by.

What happens if you don't use the view, but perform the query using the actual 
tables?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Thursday, April 26, 2012 10:11 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Re Query planner creating a slow plan
> 
> What happens if you use a subselect?
> 
> 
> 
> selsect transfer_date from (select transfer_date from transfer_history
> where regn_no='039540' and transfer_date <= '2012-05-01') order by
> transfer_date;
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> Advanced Analytics Directorate
> 
> Advanced GEOINT Solutions Operating Unit
> 
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Peter [pe...@somborneshetlands.co.uk]
> Sent: Thursday, April 26, 2012 7:00 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Query planner creating a slow plan
> 
> Hi,
> 
> I have a view 'transfer_history' which aggregates records from 3 tables
> using UNION ALL. the aggregate is about 102k records.
> 
> I have a query:
> 
> SELECT transfer_date from transfer_history
>   WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> 
> This returns three records and takes a couple of milliseconds - good.
> 
> But if I add an 'order by' clause, or an aggregate (max) on
> transfer_date, the time goes up to > 300ms. The reason seems to be that
> the query planner uses scans for all three sub-queries instead of using
> indexes on the underlying tables.
> 
> With the basic query yhe QP says;
> 
> SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
> (regn_no=? AND transfer_date INDEX sqlite_autoindex_sheep_1 (regn_no=?)
> (~1 rows)
> COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
> SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1
> (regn_no=?)
> (~1 rows)
> COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
> 
> With the 'order by' clause the QP says:
> 
> SCAN TABLE transfer AS tr (~49043 rows)
> SCAN TABLE sheep AS s (~51858 rows)
> COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
> SCAN TABLE sheep AS s (~25929 rows)
> COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
> SCAN SUBQUERY 1 AS t2 (~4227 rows)
> USE TEMP B-TREE FOR ORDER BY
> 
> Seems to me it ought to be able to just sort the result of the first
> plan. ATM it's an order of magnitude quicker at least to do the sort in
> Python in the application.
> 
> Pete
> 
> --
> 
> Peter Hardman
> ___
> 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] Re Query planner creating a slow plan

[Jim Morris]

It is possible using an alias would force better behavior:

selsect theDate
 from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date<= '2012-05-01') order by
theDate

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 15:30:

I suppose using a temporary table is out of the question?


It's much simpler (and probably quicker though I didn't test it) to read 
the three rows, sort them by date, pick the first row and use that. No 
need to generate random table names, create and drop the table.


Python time.time() gives 0.001sec for all that (to 3 dec places) which 
is fast enough for me ;) (and faster than PostgreSQL at 0.023 using the 
plain query).


 But, then

again, that only solves the specific issue.  I guess the more general
question is how views with unions interact with aggregates and order
by.

What happens if you don't use the view, but perform the query using
the actual tables?


Makes no difference - the planner is still using the scans instead of 
indexes.


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Jim Morris wrote, On 26/04/12 15:36:

It is possible using an alias would force better behavior:

selsect theDate
from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date<= '2012-05-01') order by
theDate



Once again, it makes no difference - the planner still picks the same 
plan using scans.



--

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

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

What indexes are on the underlying tables?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Peter
> Sent: Thursday, April 26, 2012 10:55 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Re Query planner creating a slow plan
> 
> Jim Morris wrote, On 26/04/12 15:36:
> > It is possible using an alias would force better behavior:
> >
> > selsect theDate
> > from (select transfer_date as theDate from transfer_history where
> > regn_no='039540' and transfer_date<= '2012-05-01') order by theDate
> >
> 
> Once again, it makes no difference - the planner still picks the same
> plan using scans.
> 
> 
> --
> 
> Peter Hardman
> ___
> 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] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 15:57:

What indexes are on the underlying tables?



There are indexes on all the fields used in the tables of the 
transfer_history view.


While tinkering I have discovered something:

If instead of
SELECT transfer_date FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

I write

SELECT * FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

then I get an execution time of a couple of milliseconds instead of 
300ms or so (times from Sqliteman this time). The planner has reverted 
to using indexes instead of scans...


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

Out of curiosity, try...

SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = 
'039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

Is the problem that combining the order by with having transfer_date as the 
only returned item make it use the transfer_date index instead of the 
preferable regn_no index?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Peter
> Sent: Thursday, April 26, 2012 11:24 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Re Query planner creating a slow plan
> 
> Marc L. Allen wrote, On 26/04/12 15:57:
> > What indexes are on the underlying tables?
> >
> 
> There are indexes on all the fields used in the tables of the
> transfer_history view.
> 
> While tinkering I have discovered something:
> 
> If instead of
> SELECT transfer_date FROM transfer_history_new WHERE regn_no = '039540'
> and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> I write
> 
> SELECT * FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> then I get an execution time of a couple of milliseconds instead of
> 300ms or so (times from Sqliteman this time). The planner has reverted
> to using indexes instead of scans...
> 
> --
> 
> Peter Hardman
> ___
> 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] Re Query planner creating a slow plan

[Black, Michael (IS)]

And does this also work?  Sounds like the planner isn't seeing all the columns 
in the view unless in the select.



SELECT transfer_date,regn_no FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

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

Re: [sqlite] Re Query planner creating a slow plan

[Simon Slavin]

On 26 Apr 2012, at 4:24pm, Peter  wrote:

> There are indexes on all the fields used in the tables of the 
> transfer_history view.
> 
> While tinkering I have discovered something:
> 
> If instead of
> SELECT transfer_date FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> I write
> 
> SELECT * FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> then I get an execution time of a couple of milliseconds instead of 300ms or 
> so (times from Sqliteman this time). The planner has reverted to using 
> indexes instead of scans...

Comparing the output of EXPLAIN QUERY PLAN for those two, can you see output 
that supports that ?  If so, it does seem to be a bug of some kind.

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 16:38:

Out of curiosity, try...

SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no
= '039540' and transfer_date<= '2012-05-01' order by transfer_date
asc

Is the problem that combining the order by with having transfer_date
as the only returned item make it use the transfer_date index instead
of the preferable regn_no index?


No, that makes no difference either.

Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 16:38:

And does this also work?  Sounds like the planner isn't seeing all the columns 
in the view unless in the select.



SELECT transfer_date,regn_no FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
order by transfer_date asc



No, that uses scans as well.

I'm tempted to suggest this might be a bug since the pattern seems 
illogical.


Pete


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Simon Slavin wrote, On 26/04/12 16:39:

On 26 Apr 2012, at 4:24pm, Peter  wrote:


>  There are indexes on all the fields used in the tables of the 
transfer_history view.
>
>  While tinkering I have discovered something:
>
>  If instead of
>  SELECT transfer_date FROM transfer_history_new
>  WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
>  order by transfer_date asc
>
>  I write
>
>  SELECT * FROM transfer_history_new
>  WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
>  order by transfer_date asc
>
>  then I get an execution time of a couple of milliseconds instead of 300ms or 
so (times from Sqliteman this time). The planner has reverted to using indexes 
instead of scans...

Comparing the output of EXPLAIN QUERY PLAN for those two, can you see output 
that supports that ?  If so, it does seem to be a bug of some kind.

Simon.


See my original post. Explain does indeed show that the difference is 
the use of indexes vs scans.


Pete
--

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

What version are you using?  Can you extract an example of all your 
tables/indexes/data to demonstrate?



One more idea.  Explicitly ask for the indexes...and a compound index might be 
nice to have around.

I dont' think you can ask for more than one index without doing subselects, can 
you?



SELECT transfer_date FROM transfer_history_new INDEXED BY myindex
WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
order by transfer_date asc





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 16:38:
> And does this also work?  Sounds like the planner isn't seeing all the 
> columns in the view unless in the select.
>
>
>
> SELECT transfer_date,regn_no FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
> order by transfer_date asc
>

No, that uses scans as well.

I'm tempted to suggest this might be a bug since the pattern seems
illogical.

Pete


--

Peter Hardman
___
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] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 17:05:

What version are you using?  Can you extract an example of all your
tables/indexes/data to demonstrate?


I'm on 3.7.11 (Arch-Linux).

You can grab a database at 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip


You will need to use a regn_no of 023674 with that as its a cut down 
data set for testing (the live one is 35MB). The date can remain the same.





One more idea.  Explicitly ask for the indexes...and a compound index
might be nice to have around.

I dont' think you can ask for more than one index without doing
subselects, can you?


AFAIK you can't create indexes on a view, or can you?


Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

There is no transfer_table_new in that database.  Or any view named "transfer" 
anything.

So what query are you running on this one?



Here's all the VIEWs.



CREATE VIEW current_flock_owner AS SELECT latest.flock_no, 
latest.owner_person_id, latest.ow
 FROM flock_owner even_later WHERE  latest.flock_no = even_later.flock_no AND 
latest.owner_c
CREATE VIEW original_flock_owner AS SELECT first.flock_no, 
first.owner_person_id, first.owne
OM flock_owner even_earlier WHERE  first.flock_no = even_earlier.flock_no AND 
first.owner_ch
CREATE VIEW three_gen_ped AS SELECT s.regn_no, s.sire_no, s.dam_no, g1.sire_no 
AS gs1, g1.da
1, gg1.dam_no AS ggd1, gg2.sire_no AS ggs2, gg2.dam_no AS ggd2, gg3.sire_no AS 
ggs3, gg3.dam
IN sheep g1 ON s.sire_no = g1.regn_no JOIN sheep g2 ON s.dam_no = g2.regn_no 
JOIN sheep gg1
o JOIN sheep gg3 ON g2.sire_no = gg3.regn_no JOIN sheep gg4 ON g2.dam_no = 
gg4.regn_no;
CREATE VIEW two_gen_ped AS SELECT s.regn_no, s.sire_no, s.dam_no, g1.sire_no AS 
gs1, g1.dam_
ep g1 ON s.sire_no = g1.regn_no JOIN sheep g2 ON s.dam_no = g2.regn_no;
CREATE VIEW current_ear_tag as
CREATE VIEW original_ear_tag as
CREATE VIEW current_eid as
CREATE VIEW current_inspection as
CREATE VIEW current_prp as
CREATE VIEW sheep_data as
CREATE VIEW sheep_basic_data as
CREATE VIEW sheep_progeny_data as





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 11:54 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 17:05:
> What version are you using?  Can you extract an example of all your
> tables/indexes/data to demonstrate?

I'm on 3.7.11 (Arch-Linux).

You can grab a database at
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip

You will need to use a regn_no of 023674 with that as its a cut down
data set for testing (the live one is 35MB). The date can remain the same.


>
> One more idea.  Explicitly ask for the indexes...and a compound index
> might be nice to have around.
>
> I dont' think you can ask for more than one index without doing
> subselects, can you?

AFAIK you can't create indexes on a view, or can you?


Pete

--

Peter Hardman
___
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] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 18:21:

There is no transfer_table_new in that database.  Or any view named "transfer" 
anything.

So what query are you running on this one?


Sorry, that should be transfer_history. The name seems to have got 
corrupted during our email exchanges.


I've just downloaded and extracted the database and it has all 44 tables 
and 18 views - well Sqliteman thinks it has anyway..


Pete

--

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

Re: [sqlite] sqlite3 column widths

[Pete]

Hi TIm,
sqlite3 already includes the ability to define the width manually with the
.width command.  I was hoping that there might be a way to use the defined
string length as part of the default when .width is not used.
Pete

On Thu, Apr 26, 2012 at 9:00 AM,  wrote:

> Nothing to stop you adding some code to the sqlite3 CLI program to have
> extra commands, allowing you to define column widths for display purposes.
> E.g:
>
> sqlite3> .colwidth x 27
>
> where x is the name of a column in some table.
>
> --
> Cheers  --  Tim
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two errors compiling for Windows CE

[Manuel Martín Sánchez]

Hello,

I have tried to generate the executable "sqlite3.exe" appropiate for Windows CE 
(ARM processor).

I used the CeGcc compiler from http://cegcc.sourceforge.net 
("cegcc_mingw32ce_cygwin1.7_r1399.tar.bz2" or 
"cegcc_mingw32ce_cygwin1.7_r1375.tar.bz2").

I executed the compiler under Cygwin (www.cygwin.com), and got two errors:


$ /opt/mingw32ce/bin/arm-mingw32ce-gcc-4.4.0.exe -o sqlite3.exe shell.c 
sqlite3.c

shell.c: In function 'hasTimer':

shell.c:159: warning: passing argument 2 of 'GetProcAddressW' from incompatible 
pointer type

/opt/mingw32ce/lib/gcc/arm-mingw32ce/4.4.0/../../../../arm-mingw32ce/include/winbase.h:1626:
 note: expected 'LPCWSTR' but argument is of type 'char *'

sqlite3.c: In function 'localtime':

sqlite3.c:33344: warning: integer constant is too large for 'long' type
/tmp/cc4vuRzt.o:shell.c:(.text+0x84c0): undefined reference to `_isatty'

/tmp/ccaHcKQQ.o:sqlite3.c:(.text+0x10c5c): undefined reference to 
`osUnlockFileEx'

collect2: ld returned 1 exit status


In order to solve these two errors I have done the following:


Error 1 (shell.c)

=

The line number 134 is

#include 

I had to move it after line number 68, that is, BEFORE any reference to 
the flag "_WIN32_WCE" is being maded. I guest that flag is defined at 



Error 2 (sqlite3.c)

=

Instead of



#if !SQLITE_OS_WINCE

{ "UnlockFileEx",(SYSCALL)UnlockFileEx,0 },

#define osUnlockFileEx ((BOOL(WINAPI*)(HANDLE,DWORD,DWORD,DWORD, \


LPOVERLAPPED))aSyscall[56].pCurrent)


#else


{ "UnlockFileEx",(SYSCALL)0,   0 },


#endif


I put


#define osUnlockFileEx ((BOOL(WINAPI*)(HANDLE,DWORD,DWORD,DWORD, \


LPOVERLAPPED))aSyscall[56].pCurrent)


#if !SQLITE_OS_WINCE


{ "UnlockFileEx",(SYSCALL)UnlockFileEx,0 },


#else


{ "UnlockFileEx",(SYSCALL)0,   0 },


#endif


The solution to the error 2 is just an emergency solution for me, so that the 
compilation goes right.


Thanks,

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

Sqliteman must be pointing to the wrong database.



sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '039540'.
Error: no such table: transfer_history





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 12:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 18:21:
> There is no transfer_table_new in that database.  Or any view named 
> "transfer" anything.
>
> So what query are you running on this one?
>
>
Sorry, that should be transfer_history. The name seems to have got
corrupted during our email exchanges.

I've just downloaded and extracted the database and it has all 44 tables
and 18 views - well Sqliteman thinks it has anyway..

Pete

--

Peter Hardman
___
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] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

We just noticed a behavioral change in SQLite 3.7.11 that caused a
regression in our software.  This change did not exist in 3.7.10.
Looking back at the changelog, I notice this:

"Pending statements no longer block ROLLBACK. Instead, the pending statement will 
return SQLITE_ABORT upon next access after the ROLLBACK."

But I do not know exactly what that means or why that change was
made.  I've got to assume this is not an intended behavioral change
as it is very significant at least from our standpoint as we've
been using the same logic since SQLite 3.3.

I've got 2 threads with different connections to the same database.

This is approximately what I am seeing:
  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
  INSERT INTO foo VALUES (...);
  **sqlite3_step returns 6 (SQLITE_LOCKED)
  ROLLBACK TRANSACTION
  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK


So why, if Thread 2 rolls back does Thread 1 get aborted?  This is
where the 3.7.11 logic doesn't make sense, Thread 2 rolls back
specifically so Thread 1 can continue since it "got there first".

I can probably write up a test case if necessary, but wanted to
pass it by you all first.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Richard Hipp]

On Thu, Apr 26, 2012 at 3:34 PM, Brad House  wrote:

>
> I've got 2 threads with different connections to the same database.
>
> This is approximately what I am seeing:
>  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
>  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
>  INSERT INTO foo VALUES (...);
>  **sqlite3_step returns 6 (SQLITE_LOCKED)
>  ROLLBACK TRANSACTION
>  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK
>
>
> So why, if Thread 2 rolls back does Thread 1 get aborted?
>

A rollback deletes content out from under other queries.  So if you have a
query pending in thread 1 and thread 2 tries to rollback, there are two
options:  (A) The rollback fails  (B) The pending query is aborted.  It
used to be that we did (A).  (If you had checked the return codes from your
"ROLLBACK TRANSACTION" statement you would have been seeing it fail.)  The
latest code does (B) instead.

Your software depends upon behavior (A).  Other users prefer behavior (B).
I don't know how to make everyone happy



-- 
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] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Black, Michael (IS)]

Would WAL mode prevent this?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, April 26, 2012 2:44 PM
To: General Discussion of SQLite Database
Cc: ian
Subject: EXT :Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with 
concurrency -- bug??


A rollback deletes content out from under other queries.  So if you have a
query pending in thread 1 and thread 2 tries to rollback, there are two
options:  (A) The rollback fails  (B) The pending query is aborted.  It
used to be that we did (A).  (If you had checked the return codes from your
"ROLLBACK TRANSACTION" statement you would have been seeing it fail.)  The
latest code does (B) instead.

Your software depends upon behavior (A).  Other users prefer behavior (B).
I don't know how to make everyone happy



--
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] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 19:00:

Sqliteman must be pointing to the wrong database.



sqlite>  SELECT transfer_date FROM transfer_history WHERE regn_no =
'039540'. Error: no such table: transfer_history



Hmm. I've just done the following - cut & paste from my terminal:

[home@system06 test]$ mkdir sqlite
[home@system06 test]$ cd sqlite
[home@system06 sqlite]$ wget 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
--2012-04-26 21:09:04-- 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip

Resolving www.somborneshetlands.co.uk... 91.197.33.236
Connecting to www.somborneshetlands.co.uk|91.197.33.236|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 184279 (180K) [application/zip]
Saving to: `sss-test-nomem.zip'

100%[==>] 
184,279  792K/s   in 0.2s


2012-04-26 21:09:04 (792 KB/s) - `sss-test-nomem.zip' saved [184279/184279]

[home@system06 sqlite]$ unzip sss-test-nomem.zip
Archive:  sss-test-nomem.zip
  inflating: sss-test-nomem.sqlite
[home@system06 sqlite]$ sqlite3 sss-test-nomem.sqlite
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from transfer_history where regn_no = '023674'
   ...> ;
023674|1610|Wycoller|1999-04-01 12:00:00|April 99|Presumed
023674|SSB900|(Dead)|2002-03-31 12:00:00|31/03/2002|Presumed
023674|1004|Glynwood|1995-04-04 00:00:00|4/4/95|Birth
sqlite>


Seems to work for me. You'll have to use 023674 instead of 039540 as the 
latter doesn't exist in this test database.


Pete

--

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 03:44 PM, Richard Hipp wrote:

On Thu, Apr 26, 2012 at 3:34 PM, Brad House  wrote:



I've got 2 threads with different connections to the same database.

This is approximately what I am seeing:
  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
  INSERT INTO foo VALUES (...);
  **sqlite3_step returns 6 (SQLITE_LOCKED)
  ROLLBACK TRANSACTION
  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK


So why, if Thread 2 rolls back does Thread 1 get aborted?



A rollback deletes content out from under other queries.  So if you have a
query pending in thread 1 and thread 2 tries to rollback, there are two
options:  (A) The rollback fails  (B) The pending query is aborted.  It
used to be that we did (A).  (If you had checked the return codes from your
"ROLLBACK TRANSACTION" statement you would have been seeing it fail.)  The
latest code does (B) instead.

Your software depends upon behavior (A).  Other users prefer behavior (B).
I don't know how to make everyone happy


I just ran another test, ensuring I check return codes specifically on
the ROLLBACK (And I was), and from what I can tell, I'm not getting
a failure on either either 3.7.11 or 3.7.10, it is returning SQLITE_OK.
NOTE: I'm using sqlite3_exec() to send the ROLLBACK... Rollback is actually
the only time we don't use sqlite3_prepare/_step/etc.

So I'm not exactly sure what you mean by I would see it failing.

What logic should we be using if we receive an SQLITE_LOCKED and we
should not ROLLBACK?  Do we simply perform an sqlite3_reset() then
retry the sqlite3_step() ... and keep doing that until it succeeds
after the SELECT has released its locks (I'm assuming if we do that
the select WILL succeed, right?)?

The sqlite3_step() documentation provides no guidance on what to do
if SQLITE_LOCKED is returned ... we had assumed the same guidance
as SQLITE_BUSY applied which says explicitly to perform a ROLLBACK.

That said, I'm still struggling to see the merit of a rollback in
one thread causing an abort in another thread.  We always took
rollback to mean we are conceding execution to the other thread,
not the other way around.  We use MySQL, Oracle, Microsoft SQL Server,
PostgreSQL ... never seen such a behavior.

Sorry if I'm being dense here.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 04:09 PM, Black, Michael (IS) wrote:

Would WAL mode prevent this?



We're using WAL mode :)

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Jos Groot Lipman]

>From the docs: 'The default isolation level for SQLite is SERIALIZABLE'

As far as I understand this means: you will not see changes made by other
connections (committed or uncommited) after your transaction started.
If another connections commits a change, you will not see it.
I would expect: If another connections rollbacks the change, you will not
see it either.

Why whould anyone want an aborted read-transaction in this case?

If the PRAGMA read_uncommitted was set you would see the changes from the
other connection even before they where committed. In case of a rollback I
can understand the aborted read-transaction.

Or am I misunderstanding the ACID isolation levels?

Jos

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: donderdag 26 april 2012 21:45
> To: General Discussion of SQLite Database
> Cc: ian
> Subject: Re: [sqlite] SQLite 3.7.11 behavioral change from 
> 3.7.10 with concurrency -- bug??
> 
> On Thu, Apr 26, 2012 at 3:34 PM, Brad House  wrote:
> 
> >
> > I've got 2 threads with different connections to the same database.
> >
> > This is approximately what I am seeing:
> >  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
> >  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
> >  INSERT INTO foo VALUES (...);
> >  **sqlite3_step returns 6 (SQLITE_LOCKED)
> >  ROLLBACK TRANSACTION
> >  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort 
> due to ROLLBACK
> >
> >
> > So why, if Thread 2 rolls back does Thread 1 get aborted?
> >
> 
> A rollback deletes content out from under other queries.  So 
> if you have a query pending in thread 1 and thread 2 tries to 
> rollback, there are two
> options:  (A) The rollback fails  (B) The pending query is 
> aborted.  It used to be that we did (A).  (If you had checked 
> the return codes from your "ROLLBACK TRANSACTION" statement 
> you would have been seeing it fail.)  The latest code does 
> (B) instead.
> 
> Your software depends upon behavior (A).  Other users prefer 
> behavior (B).
> I don't know how to make everyone happy
> 
> 
> 
> --
> 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

[sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...



Looks like it uses the indexes just fine.  I'm using the Window's EXE from the 
website.  Also got the same result on Unix.

Did you compile your own?  Or did you check to see that the indexes still 
weren't being used on your subset?

I compiled the amalgamation:

cc -o sqlite3 -O2 shell.c sqlite3.c -llpthread -ldl

Running on Redhat 5.



sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and 
transfer_date<= '2012-05-01' order by transfer_date asc;
1995-04-04 00:00:00
1999-04-01 12:00:00
2002-03-31 12:00:00

sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE 
regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date asc;
3|0|1|SCAN TABLE flock AS f (~161 rows)
3|1|0|SEARCH TABLE transfer AS tr USING INDEX tr_flock_no_index (flock_no=?) 
(~5 rows)
4|0|1|SCAN TABLE flock AS f (~161 rows)
4|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_org_flock_index 
(originating_flock=?) (~5 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|1|SCAN TABLE flock AS f (~161 rows)
5|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_reg_flock_index 
(registering_flock=?) (~2 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~67 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 3:14 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 19:00:
> Sqliteman must be pointing to the wrong database.
>
>
>
> sqlite>  SELECT transfer_date FROM transfer_history WHERE regn_no =
> '039540'. Error: no such table: transfer_history
>

Hmm. I've just done the following - cut & paste from my terminal:

[home@system06 test]$ mkdir sqlite
[home@system06 test]$ cd sqlite
[home@system06 sqlite]$ wget
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
--2012-04-26 21:09:04--
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
Resolving www.somborneshetlands.co.uk... 
91.197.33.236
Connecting to 
www.somborneshetlands.co.uk|91.197.33.236|:80...
 connected.
HTTP request sent, awaiting response... 200 OK
Length: 184279 (180K) [application/zip]
Saving to: `sss-test-nomem.zip'

100%[==>]
184,279  792K/s   in 0.2s

2012-04-26 21:09:04 (792 KB/s) - `sss-test-nomem.zip' saved [184279/184279]

[home@system06 sqlite]$ unzip sss-test-nomem.zip
Archive:  sss-test-nomem.zip
   inflating: sss-test-nomem.sqlite
[home@system06 sqlite]$ sqlite3 sss-test-nomem.sqlite
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from transfer_history where regn_no = '023674'
...> ;
023674|1610|Wycoller|1999-04-01 12:00:00|April 99|Presumed
023674|SSB900|(Dead)|2002-03-31 12:00:00|31/03/2002|Presumed
023674|1004|Glynwood|1995-04-04 00:00:00|4/4/95|Birth
sqlite>


Seems to work for me. You'll have to use 023674 instead of 039540 as the
latter doesn't exist in this test database.

Pete

--

Peter Hardman
___
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] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 21:39:

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...




Well we're none of us perfect. Only the female of the species can do more than 
two things at once...



Yes, that query is fine.

But add an 'order by transfer_date' clause and the planner no longer uses 
indexes but uses scans instead.


Then if you do a select * ... it goes back to using indexes.

With 100K+ records in the full database the difference in execution times is not 
insignificant.


Pete
--

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

[sqlite] db->pVtabCtx and xCreate accessing a virtual table

[Steinar Midtskogen]

The sqlite3 struct has a pVtabCtx pointer.  It seems to me that it
will be shared between multiple xCreate in action simultaniously and
in that case cause disaster.  This can happen even if there is no
thread concurrency going on using the same database connection.

My xCreate has to run some queries in order to build its declare
statement.  If a virtual table is queried inside xCreate causing a
another xCreate to be called, the following sqlite3_declare_vtab will
fail because pVtabCtx gets erased.

Is this a design flaw of sqlite or intentionally designed this way for
a good reason?

The only workaround that I can think of is to fork() in xCreate having
the child build the statement for the parent's sqlite3_declare_vtab(),
pass that to the waiting parent and exit.  But in my case the child
would also have to pass a lot of other variable length information
back to the parent, which will involve a lot of IPC mess to implement.

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 21:39:

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...



Looks like it uses the indexes just fine.  I'm using the Window's EXE from the 
website.  Also got the same result on Unix.

Did you compile your own?  Or did you check to see that the indexes still 
weren't being used on your subset?

I compiled the amalgamation:

cc -o sqlite3 -O2 shell.c sqlite3.c -llpthread -ldl



Sorry, forgot to answer that.

The Arch build source is http://www.sqlite.org/sqlite-autoconf-3071100.tar.gz

The build runs ./configure --prefix=/usr --disable-static

Then make with CFLAGS='CFLAGS -DSQLITE_ENABLE_FTS3=1 
-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY 
-DSQLITE_SECURE_DELETE'


Pete


--

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Richard Hipp]

On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman  wrote:

> From the docs: 'The default isolation level for SQLite is SERIALIZABLE'
>
> As far as I understand this means: you will not see changes made by other
> connections (committed or uncommited) after your transaction started.
>

My understanding was that Brad is using a single database connection shared
between both threads.  You are correct that if he had been using separate
database connections in each thread, this problem would not come up.


> If another connections commits a change, you will not see it.
> I would expect: If another connections rollbacks the change, you will not
> see it either.
>
> Why whould anyone want an aborted read-transaction in this case?
>
> If the PRAGMA read_uncommitted was set you would see the changes from the
> other connection even before they where committed. In case of a rollback I
> can understand the aborted read-transaction.
>
> Or am I misunderstanding the ACID isolation levels?
>
> Jos
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: donderdag 26 april 2012 21:45
> > To: General Discussion of SQLite Database
> > Cc: ian
> > Subject: Re: [sqlite] SQLite 3.7.11 behavioral change from
> > 3.7.10 with concurrency -- bug??
> >
> > On Thu, Apr 26, 2012 at 3:34 PM, Brad House  wrote:
> >
> > >
> > > I've got 2 threads with different connections to the same database.
> > >
> > > This is approximately what I am seeing:
> > >  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
> > >  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
> > >  INSERT INTO foo VALUES (...);
> > >  **sqlite3_step returns 6 (SQLITE_LOCKED)
> > >  ROLLBACK TRANSACTION
> > >  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort
> > due to ROLLBACK
> > >
> > >
> > > So why, if Thread 2 rolls back does Thread 1 get aborted?
> > >
> >
> > A rollback deletes content out from under other queries.  So
> > if you have a query pending in thread 1 and thread 2 tries to
> > rollback, there are two
> > options:  (A) The rollback fails  (B) The pending query is
> > aborted.  It used to be that we did (A).  (If you had checked
> > the return codes from your "ROLLBACK TRANSACTION" statement
> > you would have been seeing it fail.)  The latest code does
> > (B) instead.
> >
> > Your software depends upon behavior (A).  Other users prefer
> > behavior (B).
> > I don't know how to make everyone happy
> >
> >
> >
> > --
> > 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
>



-- 
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] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 05:11 PM, Richard Hipp wrote:

On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman  wrote:


 From the docs: 'The default isolation level for SQLite is SERIALIZABLE'

As far as I understand this means: you will not see changes made by other
connections (committed or uncommited) after your transaction started.



My understanding was that Brad is using a single database connection shared
between both threads.  You are correct that if he had been using separate
database connections in each thread, this problem would not come up.


As stated in my original request:

"I've got 2 threads with different connections to the same database."

The 2 threads are explicitly not sharing the same connection.  We are
using a connection "pool" where each thread that needs DB access
will pull an available connection out of the pool.

Also, we have set sqlite3_enable_shared_cache(1) and use WAL mode.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 04:38 PM, Jos Groot Lipman wrote:

As far as I understand this means: you will not see changes made by other
connections (committed or uncommited) after your transaction started.
If another connections commits a change, you will not see it.
I would expect: If another connections rollbacks the change, you will not
see it either.

Why whould anyone want an aborted read-transaction in this case?


I would agree ... I'd like to hear the other side of the story here
so we understand why this change was made if it was indeed intentional.

What purpose does this behavior serve?  Not saying it is wrong at
this point, just lacking information.

Also would need to understand the scope of this behavior.  Does
that mean if any connection rolls back that immediately all other
connections abort?  Or is it only one very specific case that this
occurs?

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Richard Hipp]

On Thu, Apr 26, 2012 at 5:15 PM, Brad House  wrote:

> On 04/26/2012 04:38 PM, Jos Groot Lipman wrote:
>
>> As far as I understand this means: you will not see changes made by other
>> connections (committed or uncommited) after your transaction started.
>> If another connections commits a change, you will not see it.
>> I would expect: If another connections rollbacks the change, you will not
>> see it either.
>>
>> Why whould anyone want an aborted read-transaction in this case?
>>
>
> I would agree ... I'd like to hear the other side of the story here
> so we understand why this change was made if it was indeed intentional.
>
> What purpose does this behavior serve?  Not saying it is wrong at
> this point, just lacking information.
>
> Also would need to understand the scope of this behavior.  Does
> that mean if any connection rolls back that immediately all other
> connections abort?  Or is it only one very specific case that this
> occurs?
>

Only the connection that does the rollback has its queries aborted.

If you are seeing other connections get queries aborted, that is something
new that I have not seen before and will need to investigate.

If you do a ROLLBACK in the middle of a query, why would you ever want to
keep going with that query?  What would you expect to see?



>
> Thanks.
> -Brad
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
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] EXT :Re: Re Query planner creating a slow plan

[Black, Michael (IS)]

I must be blind (something my wife would agree with)...but I did use "order by 
transfer_date"the "asc" making no difference.



I showed you mine...you show me yours (on the same database please).



sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE 
regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date;
3|0|1|SCAN TABLE flock AS f (~161 rows)
3|1|0|SEARCH TABLE transfer AS tr USING INDEX tr_flock_no_index (flock_no=?) 
(~5 rows)
4|0|1|SCAN TABLE flock AS f (~161 rows)
4|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_org_flock_index 
(originating_flock=?) (~5 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|1|SCAN TABLE flock AS f (~161 rows)
5|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_reg_flock_index 
(registering_flock=?) (~2 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~67 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 3:57 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 21:39:
> My fault...I thought I had extracted it under another name...turns out I was 
> using a different db...duh...
>
>

Well we're none of us perfect. Only the female of the species can do more than
two things at once...


Yes, that query is fine.

But add an 'order by transfer_date' clause and the planner no longer uses
indexes but uses scans instead.

Then if you do a select * ... it goes back to using indexes.

With 100K+ records in the full database the difference in execution times is not
insignificant.

Pete
--

Peter Hardman
___
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] the xRead method in sqlite3_io_methods

[BaiYang]

We should add a new output argument for xRead method which is in 
sqlite3_io_methods structure like this:

int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst, int* 
piRealAmt);

The new 'piRealAmt' could be used to report the actual read data, this argument 
will greatly boost the ability of VFS Shims. For example, with this argument, 
we can write a encryption shim easily.

It's very simple. Basically, you only need adding just one line code per xRead 
driver like this:

  }else{
pFile->lastErrno = 0; /* not a system error */
/* Unread parts of the buffer must be zero-filled */
memset(&((char*)pBuf)[got], 0, amt-got);
if (piRealAmt) *piRealAmt = (int)got; // <
return SQLITE_IOERR_SHORT_READ;
  }

By using this simple extension, we can write code like this:

//! shim xRead driver for encryption
BAIY_LOCAL int
ReadMethod(
IN OUT   sqlite3_file* piFile, 
OUT  void* pbBuf, 
IN   int   nAmt, 
IN   sqlite_int64  nnOfst,
OPTIONAL OUT int*  piRealAmt)
{
// =
// = Init, Guard
ENCVFS_FILE* piOriFile = reinterpret_cast(piFile);
if (piOriFile->btUnder.empty() || !piOriFile->thCipher.IsValid())
{
return SQLITE_IOERR_READ;
}

sqlite3_file* const piUnder = reinterpret_cast(
piOriFile->btUnder.ref()
);

// =
// = Read
int nReadLen;
piOriFile->btBuf.reserve(nAmt);

const int r = piUnder->pMethods->xRead(
piUnder, piOriFile->btBuf.ref(), nAmt, nnOfst, 
);

switch (r)
{
case SQLITE_OK:
nReadLen = nAmt;
break;
case SQLITE_IOERR_SHORT_READ:
break;
default:
return r; // <
}

// =
// = Decrypt
try
{
piOriFile->thCipher->SeekDecryptor(nnOfst);
piOriFile->thCipher->IterDecrypt(
bySBn((BYTE*)pbBuf, nReadLen), piOriFile->btBuf
);

if (SQLITE_IOERR_SHORT_READ == r)
{
memset(((BYTE*)pbBuf)+nReadLen, 0, nAmt-nReadLen);
if (piRealAmt)
{
*piRealAmt = nReadLen;
}
return SQLITE_IOERR_SHORT_READ;
}
else
{
return SQLITE_OK;
}
}
catch (...)
{
LogError(byT("ReadMethod"));
return SQLITE_IOERR_READ;
}
}

--
  Best Regards
  BaiYang
  baiy...@263.net.cn
  http://baiy.cn
 < END OF EMAIL > 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the xRead method in sqlite3_io_methods

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/04/12 14:54, BaiYang wrote:
> The new 'piRealAmt' could be used to report the actual read data,

Why?  The requirements are that you read the amount of data requested or
return an error.  If your internal code results in less data being read
then you'll need to keep repeating until you have the amount requested.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+ZyCYACgkQmOOfHg372QRDIQCgyo8nARHPfB3KUYjv/vpS5xWz
ZTkAni/JaeSFlNVJjO0xRHTqqtxCyVib
=cSZL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the xRead method in sqlite3_io_methods

[BaiYang]

> If your internal code results in less data being read then you'll need to 
> keep repeating until you have the amount requested.
I'm confused, does you mean a xRead should NEVER return a 
"SQLITE_IOERR_SHORT_READ" ?

--
  Best Regards
  BaiYang
  baiy...@263.net.cn
  http://baiy.cn
 < END OF EMAIL > 

From: Roger Binns
Date: 2012-04-27 06:11
To: General Discussion of SQLite Database
Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/04/12 14:54, BaiYang wrote:
> The new 'piRealAmt' could be used to report the actual read data,

Why?  The requirements are that you read the amount of data requested or
return an error.  If your internal code results in less data being read
then you'll need to keep repeating until you have the amount requested.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+ZyCYACgkQmOOfHg372QRDIQCgyo8nARHPfB3KUYjv/vpS5xWz
ZTkAni/JaeSFlNVJjO0xRHTqqtxCyVib
=cSZL
-END PGP SIGNATURE-
___
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] db->pVtabCtx and xCreate accessing a virtual table

[Richard Hipp]

On Thu, Apr 26, 2012 at 5:07 PM, Steinar Midtskogen
wrote:

> The sqlite3 struct has a pVtabCtx pointer.  It seems to me that it
> will be shared between multiple xCreate in action simultaniously and
> in that case cause disaster.  This can happen even if there is no
> thread concurrency going on using the same database connection.
>
> My xCreate has to run some queries in order to build its declare
> statement.  If a virtual table is queried inside xCreate causing a
> another xCreate to be called, the following sqlite3_declare_vtab will
> fail because pVtabCtx gets erased.
>

Fixed here:  http://www.sqlite.org/src/info/696a5a40bb


>
> Is this a design flaw of sqlite or intentionally designed this way for
> a good reason?
>
> The only workaround that I can think of is to fork() in xCreate having
> the child build the statement for the parent's sqlite3_declare_vtab(),
> pass that to the waiting parent and exit.  But in my case the child
> would also have to pass a lot of other variable length information
> back to the parent, which will involve a lot of IPC mess to implement.
>
> --
> Steinar
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Query Planner Bug With Simple Reproduction on 3.7.11

[Kyle McKay]

Credit to Pete Hardman who posted the original thread:

  http://thread.gmane.org/gmane.comp.db.sqlite.general/73931

Here's a much simpler reproduction of the query planner bug:

$ /var/tmp/sqlite3 testview.sq3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .schema
CREATE TABLE t1(regn_no, transfer_date);
CREATE TABLE t2(regn_no, transfer_date);
CREATE TABLE t3(regn_no, transfer_date);
CREATE VIEW v1 as select * from t1 union all select * from t2 union  
all select * from t3;

CREATE INDEX t1_index on t1(regn_no);
CREATE INDEX t2_index on t2(regn_no);
CREATE INDEX t3_index on t3(regn_no);
sqlite> explain query plan select * from v1 where regn_no = '039540'  
order by transfer_date;

2|0|0|SEARCH TABLE t1 USING INDEX t1_index (regn_no=?) (~10 rows)
2|0|0|USE TEMP B-TREE FOR ORDER BY
3|0|0|SEARCH TABLE t2 USING INDEX t2_index (regn_no=?) (~10 rows)
3|0|0|USE TEMP B-TREE FOR ORDER BY
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
4|0|0|SEARCH TABLE t3 USING INDEX t3_index (regn_no=?) (~10 rows)
4|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
sqlite> explain query plan select regn_no, transfer_date from v1 where  
regn_no = '039540' order by transfer_date;

3|0|0|SCAN TABLE t1 (~100 rows)
4|0|0|SCAN TABLE t2 (~100 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|0|SCAN TABLE t3 (~100 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~30 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> .quit
$

The two queries would seem to be identical except one specifies * for  
the columns and the other lists them out yet the first query results  
in use of the indices whereas the second does not.


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

Re: [sqlite] the xRead method in sqlite3_io_methods

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/04/12 15:27, BaiYang wrote:
> I'm confused, does you mean a xRead should NEVER return a
> "SQLITE_IOERR_SHORT_READ" ?

It is a fatal error.  The only time it is okay is after the file is first
created (zero length) and SQLite tries to read the header page which
doesn't exist at that point in time.

At all other times a short error is fatal.  The extended error code is so
that the caller has more details than just I/O error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+Z1O8ACgkQmOOfHg372QTyoACglrfe4j5Grsl2YzJ387bbquxE
U4YAoOCoFPF425QNlEB3/tYntK+vTbHk
=5wii
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the xRead method in sqlite3_io_methods

[BaiYang]

> It is a fatal error.  The only time it is okay is after the file is first
> created (zero length) ...
Really? But I have seen many location seems not like you sad. For example:

In pager_playback:
}else if( rc==SQLITE_IOERR_SHORT_READ ){
  /* If the journal has been truncated, simply stop reading and
  ** processing the journal. This might happen if the journal was
  ** not completely written and synced prior to a crash.  In that
  ** case, the database should have never been written in the
  ** first place so it is OK to simply abandon the rollback. */
  rc = SQLITE_OK;
  goto end_playback;

In readDbPage:
  if( rc==SQLITE_OK && !isInWal ){
i64 iOffset = (pgno-1)*(i64)pPager->pageSize;
rc = sqlite3OsRead(pPager->fd, pPg->pData, pgsz, iOffset);
if( rc==SQLITE_IOERR_SHORT_READ ){
  rc = SQLITE_OK;
}
  }

And so on.

All these codes are dealing with the header?

--
  Best Regards
  BaiYang
  baiy...@263.net.cn
  http://baiy.cn
 < END OF EMAIL > 

From: Roger Binns
Date: 2012-04-27 07:06
To: General Discussion of SQLite Database
Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/04/12 15:27, BaiYang wrote:
> I'm confused, does you mean a xRead should NEVER return a
> "SQLITE_IOERR_SHORT_READ" ?

It is a fatal error.  The only time it is okay is after the file is first
created (zero length) and SQLite tries to read the header page which
doesn't exist at that point in time.

At all other times a short error is fatal.  The extended error code is so
that the caller has more details than just I/O error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+Z1O8ACgkQmOOfHg372QTyoACglrfe4j5Grsl2YzJ387bbquxE
U4YAoOCoFPF425QNlEB3/tYntK+vTbHk
=5wii
-END PGP SIGNATURE-
___
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] the xRead method in sqlite3_io_methods

[BaiYang]

And according to the official document:

If xRead() returns SQLITE_IOERR_SHORT_READ it must also fill in the unread 
portions of the buffer with zeros. A VFS that fails to zero-fill short reads 
might seem to work. However, failure to zero-fill short reads will eventually 
lead to database corruption. 

http://www.sqlite.org/c3ref/io_methods.html 

It's seems not as simple as you sad :-)

--
  Best Regards
  BaiYang
  baiy...@263.net.cn
  http://baiy.cn
 < END OF EMAIL > 

From: Roger Binns
Date: 2012-04-27 07:06
To: General Discussion of SQLite Database
Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/04/12 15:27, BaiYang wrote:
> I'm confused, does you mean a xRead should NEVER return a
> "SQLITE_IOERR_SHORT_READ" ?

It is a fatal error.  The only time it is okay is after the file is first
created (zero length) and SQLite tries to read the header page which
doesn't exist at that point in time.

At all other times a short error is fatal.  The extended error code is so
that the caller has more details than just I/O error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk+Z1O8ACgkQmOOfHg372QTyoACglrfe4j5Grsl2YzJ387bbquxE
U4YAoOCoFPF425QNlEB3/tYntK+vTbHk
=5wii
-END PGP SIGNATURE-
___
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] the xRead method in sqlite3_io_methods

[Pavel Ivanov]

> All these codes are dealing with the header?

They are not about header but they are essentially dealing with fatal
errors. First example is (as comment says) executed when database
tries to recover after a crashed process. And short read here means
that journal is corrupted and should be ignored. In the second example
if you follow the code further you'll see that SQLite assumes that
short read means 0 bytes was read and the whole page is filled with
zeros. That will mean that the page should be created.


Pavel


On Thu, Apr 26, 2012 at 7:13 PM, BaiYang  wrote:
>> It is a fatal error.  The only time it is okay is after the file is first
>> created (zero length) ...
> Really? But I have seen many location seems not like you sad. For example:
>
> In pager_playback:
>        }else if( rc==SQLITE_IOERR_SHORT_READ ){
>          /* If the journal has been truncated, simply stop reading and
>          ** processing the journal. This might happen if the journal was
>          ** not completely written and synced prior to a crash.  In that
>          ** case, the database should have never been written in the
>          ** first place so it is OK to simply abandon the rollback. */
>          rc = SQLITE_OK;
>          goto end_playback;
>
> In readDbPage:
>  if( rc==SQLITE_OK && !isInWal ){
>    i64 iOffset = (pgno-1)*(i64)pPager->pageSize;
>    rc = sqlite3OsRead(pPager->fd, pPg->pData, pgsz, iOffset);
>    if( rc==SQLITE_IOERR_SHORT_READ ){
>      rc = SQLITE_OK;
>    }
>  }
>
> And so on.
>
> All these codes are dealing with the header?
>
> --
>  Best Regards
>  BaiYang
>  baiy...@263.net.cn
>  http://baiy.cn
>  < END OF EMAIL > 
>
> From: Roger Binns
> Date: 2012-04-27 07:06
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 26/04/12 15:27, BaiYang wrote:
>> I'm confused, does you mean a xRead should NEVER return a
>> "SQLITE_IOERR_SHORT_READ" ?
>
> It is a fatal error.  The only time it is okay is after the file is first
> created (zero length) and SQLite tries to read the header page which
> doesn't exist at that point in time.
>
> At all other times a short error is fatal.  The extended error code is so
> that the caller has more details than just I/O error.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk+Z1O8ACgkQmOOfHg372QTyoACglrfe4j5Grsl2YzJ387bbquxE
> U4YAoOCoFPF425QNlEB3/tYntK+vTbHk
> =5wii
> -END PGP SIGNATURE-
> ___
> 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] the xRead method in sqlite3_io_methods

[Pavel Ivanov]

> 1. The sqlite engine will discard ALL of the partially read data returned by 
> xRead?
> For example, if the sqlite engine request 2096 bytes and xRead only got 1234 
> bytes, does the engine will ALWAYS discard the 1234 bytes of data ?
> i.e: SQLITE_IOERR_SHORT_READ constantly means "no data" for sqlite engine ?

No, it doesn't mean "no data" for SQLite engine, but with
non-corrupted database it can happen only when there's no data. So if
SQLite requests 2096 bytes and xRead returns 1234 bytes SQLite will
detect database corruption either right away or a little bit later
(depending on when it will actually try to use the data that should
have been above 1234 bytes).

> 2. Are following warnings: "If xRead() returns SQLITE_IOERR_SHORT_READ it 
> must also fill in the unread portions of the buffer with zeros. A VFS that 
> fails to zero-fill short reads might seem to work. However, failure to 
> zero-fill short reads will eventually lead to database corruption. ", Which 
> listed in the official document: http://www.sqlite.org/c3ref/io_methods.html 
> a joke ?

No, it's not a joke. It's a requirement that will help SQLite engine
to detect database corruption.


Pavel


On Thu, Apr 26, 2012 at 8:02 PM, BaiYang  wrote:
>> They are not about header but they are essentially dealing with fatal
>> errors. First example is (as comment says) executed when database
>> tries to recover after a crashed process. And short read here means
>> that journal is corrupted and should be ignored. In the second example
>> if you follow the code further you'll see that SQLite assumes that
>> short read means 0 bytes was read and the whole page is filled with
>> zeros. That will mean that the page should be created.
>
> I don't know what you really want to explained, did you means:
>
> 1. The sqlite engine will discard ALL of the partially read data returned by 
> xRead?
> For example, if the sqlite engine request 2096 bytes and xRead only got 1234 
> bytes, does the engine will ALWAYS discard the 1234 bytes of data ?
> i.e: SQLITE_IOERR_SHORT_READ constantly means "no data" for sqlite engine ?
>
> 2. Are following warnings: "If xRead() returns SQLITE_IOERR_SHORT_READ it 
> must also fill in the unread portions of the buffer with zeros. A VFS that 
> fails to zero-fill short reads might seem to work. However, failure to 
> zero-fill short reads will eventually lead to database corruption. ", Which 
> listed in the official document: http://www.sqlite.org/c3ref/io_methods.html 
> a joke ?
>
> I think the new argument is useful if any one is "no".
>
> --
>  Best Regards
>  BaiYang
>  baiy...@263.net.cn
>  http://baiy.cn
>  < END OF EMAIL > 
>
> From: Pavel Ivanov
> Date: 2012-04-27 07:34
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
>> All these codes are dealing with the header?
>
> They are not about header but they are essentially dealing with fatal
> errors. First example is (as comment says) executed when database
> tries to recover after a crashed process. And short read here means
> that journal is corrupted and should be ignored. In the second example
> if you follow the code further you'll see that SQLite assumes that
> short read means 0 bytes was read and the whole page is filled with
> zeros. That will mean that the page should be created.
>
>
> Pavel
>
>
> On Thu, Apr 26, 2012 at 7:13 PM, BaiYang  wrote:
>>> It is a fatal error.  The only time it is okay is after the file is first
>>> created (zero length) ...
>> Really? But I have seen many location seems not like you sad. For example:
>>
>> In pager_playback:
>>        }else if( rc==SQLITE_IOERR_SHORT_READ ){
>>          /* If the journal has been truncated, simply stop reading and
>>          ** processing the journal. This might happen if the journal was
>>          ** not completely written and synced prior to a crash.  In that
>>          ** case, the database should have never been written in the
>>          ** first place so it is OK to simply abandon the rollback. */
>>          rc = SQLITE_OK;
>>          goto end_playback;
>>
>> In readDbPage:
>>  if( rc==SQLITE_OK && !isInWal ){
>>    i64 iOffset = (pgno-1)*(i64)pPager->pageSize;
>>    rc = sqlite3OsRead(pPager->fd, pPg->pData, pgsz, iOffset);
>>    if( rc==SQLITE_IOERR_SHORT_READ ){
>>      rc = SQLITE_OK;
>>    }
>>  }
>>
>> And so on.
>>
>> All these codes are dealing with the header?
>>
>> --
>>  Best Regards
>>  BaiYang
>>  baiy...@263.net.cn
>>  http://baiy.cn
>>  < END OF EMAIL > 
>>
>> From: Roger Binns
>> Date: 2012-04-27 07:06
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 26/04/12 15:27, BaiYang wrote:
>>> I'm confused, does you mean a xRead should NEVER return a
>>> "SQLITE_IOERR_SHORT_READ" ?
>>
>> It is a fatal error.  The only time it is okay is after 

Re: [sqlite] Query Planner Bug With Simple Reproduction on 3.7.11

[Richard Hipp]

On Thu, Apr 26, 2012 at 7:05 PM, Kyle McKay  wrote:

> Credit to Pete Hardman who posted the original thread:
>
>  
> http://thread.gmane.org/gmane.**comp.db.sqlite.general/73931
>
> Here's a much simpler reproduction of the query planner bug:
>

Fixed here:  http://www.sqlite.org/src/info/a49e909c87

A minor point of nomenclature:  I really prefer to reserve the use of the
word "bug" for cases where the software gets the wrong answer (or crashes,
which is always the wrong answer).  By that definition, this issue is not a
bug but merely an inefficiency or an "opportunity for performance
improvement".  By reserving the use of "bug" for cases where the wrong
answer appears, we make the word "bug" stronger, so that when you see a
phrase like "a bug in SQLite" you know you ought to pay attention.  Calling
obscure inefficiencies "bugs" tends to weaken the term and make it less
helpful for describing serious problems.


>
> $ /var/tmp/sqlite3 testview.sq3
> SQLite version 3.7.11 2012-03-20 11:35:50
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT SQLITE_SOURCE_ID();
> 2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d00**62dc364669
> sqlite> .schema
> CREATE TABLE t1(regn_no, transfer_date);
> CREATE TABLE t2(regn_no, transfer_date);
> CREATE TABLE t3(regn_no, transfer_date);
> CREATE VIEW v1 as select * from t1 union all select * from t2 union all
> select * from t3;
> CREATE INDEX t1_index on t1(regn_no);
> CREATE INDEX t2_index on t2(regn_no);
> CREATE INDEX t3_index on t3(regn_no);
> sqlite> explain query plan select * from v1 where regn_no = '039540' order
> by transfer_date;
> 2|0|0|SEARCH TABLE t1 USING INDEX t1_index (regn_no=?) (~10 rows)
> 2|0|0|USE TEMP B-TREE FOR ORDER BY
> 3|0|0|SEARCH TABLE t2 USING INDEX t2_index (regn_no=?) (~10 rows)
> 3|0|0|USE TEMP B-TREE FOR ORDER BY
> 1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
> 4|0|0|SEARCH TABLE t3 USING INDEX t3_index (regn_no=?) (~10 rows)
> 4|0|0|USE TEMP B-TREE FOR ORDER BY
> 0|0|0|COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
> sqlite> explain query plan select regn_no, transfer_date from v1 where
> regn_no = '039540' order by transfer_date;
> 3|0|0|SCAN TABLE t1 (~100 rows)
> 4|0|0|SCAN TABLE t2 (~100 rows)
> 2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
> 5|0|0|SCAN TABLE t3 (~100 rows)
> 1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
> 0|0|0|SCAN SUBQUERY 1 (~30 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> sqlite> .quit
> $
>
> The two queries would seem to be identical except one specifies * for the
> columns and the other lists them out yet the first query results in use of
> the indices whereas the second does not.
>
> -- Kyle
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
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] the xRead method in sqlite3_io_methods

[BaiYang]

Ok, thanks for the reply :-)

--
  Best Regards
  BaiYang
  baiy...@263.net.cn
  http://baiy.cn
 < END OF EMAIL > 

From: Pavel Ivanov
Date: 2012-04-27 09:03
To: General Discussion of SQLite Database
Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
> 1. The sqlite engine will discard ALL of the partially read data returned by 
> xRead?
> For example, if the sqlite engine request 2096 bytes and xRead only got 1234 
> bytes, does the engine will ALWAYS discard the 1234 bytes of data ?
> i.e: SQLITE_IOERR_SHORT_READ constantly means "no data" for sqlite engine ?

No, it doesn't mean "no data" for SQLite engine, but with
non-corrupted database it can happen only when there's no data. So if
SQLite requests 2096 bytes and xRead returns 1234 bytes SQLite will
detect database corruption either right away or a little bit later
(depending on when it will actually try to use the data that should
have been above 1234 bytes).

> 2. Are following warnings: "If xRead() returns SQLITE_IOERR_SHORT_READ it 
> must also fill in the unread portions of the buffer with zeros. A VFS that 
> fails to zero-fill short reads might seem to work. However, failure to 
> zero-fill short reads will eventually lead to database corruption. ", Which 
> listed in the official document: http://www.sqlite.org/c3ref/io_methods.html 
> a joke ?

No, it's not a joke. It's a requirement that will help SQLite engine
to detect database corruption.


Pavel


On Thu, Apr 26, 2012 at 8:02 PM, BaiYang  wrote:
>> They are not about header but they are essentially dealing with fatal
>> errors. First example is (as comment says) executed when database
>> tries to recover after a crashed process. And short read here means
>> that journal is corrupted and should be ignored. In the second example
>> if you follow the code further you'll see that SQLite assumes that
>> short read means 0 bytes was read and the whole page is filled with
>> zeros. That will mean that the page should be created.
>
> I don't know what you really want to explained, did you means:
>
> 1. The sqlite engine will discard ALL of the partially read data returned by 
> xRead?
> For example, if the sqlite engine request 2096 bytes and xRead only got 1234 
> bytes, does the engine will ALWAYS discard the 1234 bytes of data ?
> i.e: SQLITE_IOERR_SHORT_READ constantly means "no data" for sqlite engine ?
>
> 2. Are following warnings: "If xRead() returns SQLITE_IOERR_SHORT_READ it 
> must also fill in the unread portions of the buffer with zeros. A VFS that 
> fails to zero-fill short reads might seem to work. However, failure to 
> zero-fill short reads will eventually lead to database corruption. ", Which 
> listed in the official document: http://www.sqlite.org/c3ref/io_methods.html 
> a joke ?
>
> I think the new argument is useful if any one is "no".
>
> --
>  Best Regards
>  BaiYang
>  baiy...@263.net.cn
>  http://baiy.cn
>  < END OF EMAIL > 
>
> From: Pavel Ivanov
> Date: 2012-04-27 07:34
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] the xRead method in sqlite3_io_methods
>> All these codes are dealing with the header?
>
> They are not about header but they are essentially dealing with fatal
> errors. First example is (as comment says) executed when database
> tries to recover after a crashed process. And short read here means
> that journal is corrupted and should be ignored. In the second example
> if you follow the code further you'll see that SQLite assumes that
> short read means 0 bytes was read and the whole page is filled with
> zeros. That will mean that the page should be created.
>
>
> Pavel
>
>
> On Thu, Apr 26, 2012 at 7:13 PM, BaiYang  wrote:
>>> It is a fatal error.  The only time it is okay is after the file is first
>>> created (zero length) ...
>> Really? But I have seen many location seems not like you sad. For example:
>>
>> In pager_playback:
>>}else if( rc==SQLITE_IOERR_SHORT_READ ){
>>  /* If the journal has been truncated, simply stop reading and
>>  ** processing the journal. This might happen if the journal was
>>  ** not completely written and synced prior to a crash.  In that
>>  ** case, the database should have never been written in the
>>  ** first place so it is OK to simply abandon the rollback. */
>>  rc = SQLITE_OK;
>>  goto end_playback;
>>
>> In readDbPage:
>>  if( rc==SQLITE_OK && !isInWal ){
>>i64 iOffset = (pgno-1)*(i64)pPager->pageSize;
>>rc = sqlite3OsRead(pPager->fd, pPg->pData, pgsz, iOffset);
>>if( rc==SQLITE_IOERR_SHORT_READ ){
>>  rc = SQLITE_OK;
>>}
>>  }
>>
>> And so on.
>>
>> All these codes are dealing with the header?
>>
>> --
>>  Best Regards
>>  BaiYang
>>  baiy...@263.net.cn
>>  http://baiy.cn
>>  < END OF EMAIL > 
>>
>> From: Roger Binns
>> Date: 2012-04-27 07:06
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] the xRead method in 

[sqlite] Mail ID

[Bageesh.M.Bose]

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

Re: [sqlite] db->pVtabCtx and xCreate accessing a virtual table

[Steinar Midtskogen]

Richard Hipp  writes:

> On Thu, Apr 26, 2012 at 5:07 PM, Steinar Midtskogen
> wrote:
>
>> My xCreate has to run some queries in order to build its declare
>> statement.  If a virtual table is queried inside xCreate causing a
>> another xCreate to be called, the following sqlite3_declare_vtab will
>> fail because pVtabCtx gets erased.
>>
>
> Fixed here:  http://www.sqlite.org/src/info/696a5a40bb

Thanks.  I can confirm that it fixes everything for me.

One other thing which perhaps is surprising, but it can be argued that
it is a feature rather than a bug, is that a virtual table can be
queried before its xCreate gets finished and its sqlite3_declare_vtab
is called.  So if someone says CREATE VIRTUAL TABLE x ... and its
xCreate has a SELECT ... FROM x, it wont get "no such table".  Rather
it will call itself and eventually crash unless xCreate takes care to
test for the recursion somehow.

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

[sqlite] Problem with insert

[Bageesh.M.Bose]

I have a file named "stock.txt" with datas seperated by "|" symbol.I want
insert these datas into a table named "stock".How can i do this in c(in
linux) with sqlite.

Please help me...

-- 
With Regards,
*Bageesh.M.Bose*
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users