Re: [sqlite] Version 3.5.5 Released

[Mohd Radzi Ibrahim]

Hi,

The Precompiled Binary for Windows : the command-line sqlite-3_5_5.zip still 
contain the 3.5.4 version


regards,
Radzi.
- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Friday, February 01, 2008 1:33 AM
Subject: [sqlite] Version 3.5.5 Released



SQLite version 3.5.5 is now available for download from the
SQLite website:

  http://www.sqlite.org/

The big change from version 3.5.4 is that the internal virtual
machine was reworked to use operands in registers rather than
pulling operands from a stack.  The virtual machine stack has
now been removed.  The removal of the VM stack will help prevent
future stack overflow bugs and will also facilitate new optimizations
in future releases.

There should be no user-visible changes to the operation of SQLite
in this release, except that the output of EXPLAIN looks different.

In order to make this change, about 8.5% of the core SQLite code
had to be reworked.  We thought this might introduce instability.
But we have done two weeks of intensive testing, during which time
we have increased the statement test coverage to 99% and during
which we have found and fixed lots of minor bugs (mostly things
like leaking memory following a malloc failure).  But for all of
that testing, we have not detected a single bug in the new
register-based VM.  And for that reason, we believe the new
VM, and hence version 3.5.5, is stable and ready for production
use.

As usual, please report any problems to this mailing list, or
directly to me.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] unfinalized statements upon close question.

[Ken]

In my code I'm getting an SQLITE_BUSY returned from sqlite3_close.

Is there a way to get sqite to tell me  which statements it thinks are not 
finalized?

Thanks,
Ken

Re: [sqlite] Solaris bus error

[Dan]

On Feb 1, 2008, at 6:37 AM, Ken wrote:


Recompiled without the -O2 flags:

#0  0x00049750 in findLockInfo (fd=4, ppLock=0x1345ad,  
ppOpen=0x1345a9)

at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:670
670   *ppLock = pLock;

*ppLock is Null


Hi Ken,

This doesn't look quite right to me. In the debugger you can
do "print *ppLock" and it doesn't complain?

The test case you were running (async.test) uses more than one
thread, so maybe the one we're looking at is not the one that
caused the problem.

Can you try running [info threads] in the debugger, and then
see if you can get a stack trace for another thread? Thanks.

Dan.






Ken <[EMAIL PROTECTED]> wrote: Core/backtrace info:


(gdb) core-file core
Core was generated by `/home/ixion/LIB/sqlite3test/.libs/ 
testfixture ../sqliteSrc/sqlite-3.5.5/test/qu'.

Program terminated with signal 10, Bus error.
Reading symbols from /home/ixion/LIB/sqlite3test/.libs/ 
libsqlite3.so.0...done.

Loaded symbols for /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0
Reading symbols from /usr/lib/libpthread.so.1...done.
Loaded symbols for /usr/lib/libpthread.so.1
Reading symbols from /usr/local/lib/libtcl8.5.so...done.
Loaded symbols for /usr/local/lib/libtcl8.5.so
Reading symbols from /usr/lib/libdl.so.1...done.
Loaded symbols for /usr/lib/libdl.so.1
Reading symbols from /usr/lib/libsocket.so.1...done.
Loaded symbols for /usr/lib/libsocket.so.1
Reading symbols from /usr/lib/libnsl.so.1...done.
Loaded symbols for /usr/lib/libnsl.so.1
Reading symbols from /usr/lib/libm.so.1...done.
Loaded symbols for /usr/lib/libm.so.1
Reading symbols from /usr/lib/librt.so.1...done.
Loaded symbols for /usr/lib/librt.so.1
Reading symbols from /usr/lib/libc.so.1...done.
Loaded symbols for /usr/lib/libc.so.1
Reading symbols from /usr/local/lib/libgcc_s.so.1...done.
Loaded symbols for /usr/local/lib/libgcc_s.so.1
Reading symbols from /usr/lib/libmp.so.2...done.
Loaded symbols for /usr/lib/libmp.so.2
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so. 
1...done.

Loaded symbols for /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1
Reading symbols from /usr/lib/libthread.so.1...done.
Loaded symbols for /usr/lib/libthread.so.1
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995,  
ppOpen=0xd1991)

at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
671   if( ppOpen!=0 ){
(gdb) backtrace
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995,  
ppOpen=0xd1991)

at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
#1  0x000355b4 in unixOpen (pVfs=0x8a96c,
zPath=0xc0380 "/home/ixion/LIB/sqlite3test/chocolate/banana/ 
vanilla/file.db", pFile=0xd196d, flags=262, pOutFlags=0x)

at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:2268
#2  0x000340bc in sqlite3OsOpen (pVfs=0x2102,
zPath=0xc0380 "/home/ixion/LIB/sqlite3test/chocolate/banana/ 
vanilla/file.db", pFile=0xd196d, flags=262, pFlagsOut=0x)

at ../sqliteSrc/sqlite-3.5.5/src/os.c:112

ppOpen  os_unxi.c/671 is not !=0 but *ppOpen is 0...


James Dennett  wrote: > -Original Message-

From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error


After addressing the -lrt sched yield on solaris.

make test resulted in:

async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)

Any ideas?


Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett  wrote: > -Original Message-

From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error


After addressing the -lrt sched yield on solaris.

make test resulted in:

async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)

Any ideas?


Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett  wrote: > -Original Message-

From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error


After addressing the -lrt sched yield on solaris.

make test resulted in:

async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)

Any ideas?


Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using LIKE to check the first digits?

[P Kishor]

On 1/31/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Gilles <[EMAIL PROTECTED]> wrote:
> > At 15:40 31/01/2008 -0800, James Dennett wrote:
> > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123'
> > >
> > >The optimizer has a decent chance of using an index for LIKE '123%' but
> > >I'd be surprised (and impressed) if it looks inside function calls suchas
> > >substr for opportunities to use indexes.
> >
> > Thanks guys. I'll remember to index the column, for higher performance.
> >
>
> Indices won't help with LIKE unless the column as a NOCASE
> collation.  Use GLOB instead:
>
>... WHERE number GLOB '1234*';
>
> Note that "*" is the wildcard character with GLOB, not "%"
> as in LIKE.  The above will use an index on the number column
> if it is available.


if GLOB is so good, I am curious what are the instances in which one
would prefer

  col LIKE '123%'

over

  col GLOB '123*'

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using LIKE to check the first digits?

[Gilles]

At 02:01 01/02/2008 +, [EMAIL PROTECTED] wrote:
Indices won't help with LIKE unless the column as a NOCASE collation.  Use 
GLOB instead:

  ... WHERE number GLOB '1234*';


Thank you for the tip.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using LIKE to check the first digits?

[drh]

Gilles <[EMAIL PROTECTED]> wrote:
> At 15:40 31/01/2008 -0800, James Dennett wrote:
> > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123'
> >
> >The optimizer has a decent chance of using an index for LIKE '123%' but 
> >I'd be surprised (and impressed) if it looks inside function calls suchas 
> >substr for opportunities to use indexes.
> 
> Thanks guys. I'll remember to index the column, for higher performance.
> 

Indices won't help with LIKE unless the column as a NOCASE
collation.  Use GLOB instead:

   ... WHERE number GLOB '1234*';

Note that "*" is the wildcard character with GLOB, not "%"
as in LIKE.  The above will use an index on the number column
if it is available.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Solaris bus error

[Nicolas Williams]

On Thu, Jan 31, 2008 at 04:38:27PM -0800, Ken wrote:
> Solaris Nevada is an x86 system if I'm reading the Hardware
> Compatibility List correctly.

No, it's an unreleased version of Solaris (the basis for the next
release after Solaris 10).  It runs on x86 and SPARC, but I was trying
this on my laptop, which is x86.

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Solaris bus error

[Ken]

Solaris Nevada is an x86 system if I'm reading the Hardware Compatibility List 
correctly.

The bus erro was produced on a Solaris 8 (ultra Sparc) system... Different 
hardware and different OS.

My system had other failures in addition to the ones you've listed, in addition 
to the bus error.

Regards,
Ken


Nicolas Williams <[EMAIL PROTECTED]> wrote: I didn't get any core dumps in 
Solaris Nevada, but seven tests failed:

Failures on these tests: vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6
vtab6-7.1 vtab6-9.1.1 vtab6-9.2

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] solaris test suite errors, 3.5.5

[Ken]

Removed asyn3.test...Since it was core dumping.

tcl version is 8.5.0 
gcc is 3.4.2
sqlite 3.5.5
Solairs 8 (5.8)

Test suite errors:

13 errors out of 38318 tests
Failures on these tests: bind-4.4 bind-4.5 printf-1.7.6 printf-1.8.6 
printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 
vtab6-9.1.1 vtab6-9.2

Details:

bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
bind-4.6... Ok

printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]

printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]

printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]


tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.3... Ok
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]

vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-8.1... Ok
vtab6-8.2... Ok
vtab6-8.3... Ok
vtab6-9.1... Ok
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
vtab6-10.1... Ok

Re: [sqlite] Solaris bus error

[Nicolas Williams]

I didn't get any core dumps in Solaris Nevada, but seven tests failed:

Failures on these tests: vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6
vtab6-7.1 vtab6-9.1.1 vtab6-9.2

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Using LIKE to check the first digits?

[Gilles]

At 15:40 31/01/2008 -0800, James Dennett wrote:

> WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123'

The optimizer has a decent chance of using an index for LIKE '123%' but 
I'd be surprised (and impressed) if it looks inside function calls suchas 
substr for opportunities to use indexes.


Thanks guys. I'll remember to index the column, for higher performance.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using LIKE to check the first digits?

[Scott Baker]

Gilles wrote:

Hello

I'm no SQL guru, and need to look up phone numbers in a SQLite database 
that start with certain digits. Some customers use 
http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the 
first part is common to all the numbers assigned to this customer, so I 
don't need to enter every single one of them, and just assign the 
prefix, eg. "123" matches "1230001", "1230002", etc.


Should I use the LIKE command for this? Does someone have an example to 
do this?


SELECT * FROM Table WHERE Field LIKE '123%';

Use % as your wildcard, and you're good to go.

--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Using LIKE to check the first digits?

[James Dennett]

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 3:35 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Using LIKE to check the first digits?
> 
> On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote:
> > Hello
> >
> > I'm no SQL guru, and need to look up phone numbers in a SQLite
database
> > that start with certain digits. Some customers use
> > http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that
the
> > first part is common to all the numbers assigned to this customer,
so I
> > don't need to enter every single one of them, and just assign the
> prefix,
> > eg. "123" matches "1230001", "1230002", etc.
> >
> > Should I use the LIKE command for this? Does someone have an example
to
> do
> > this?
> >
> 
> WHERE col LIKE '123%'
> 
> or WHERE substr(col,1, 3) = '123'

One note:

The optimizer has a decent chance of using an index for LIKE '123%' but
I'd be surprised (and impressed) if it looks inside function calls such
as substr for opportunities to use indexes.

-- James


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Solaris bus error

[Ken]

Recompiled without the -O2 flags:

#0  0x00049750 in findLockInfo (fd=4, ppLock=0x1345ad, ppOpen=0x1345a9)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:670
670   *ppLock = pLock;

*ppLock is Null 

Ken <[EMAIL PROTECTED]> wrote: Core/backtrace info:


(gdb) core-file core
Core was generated by `/home/ixion/LIB/sqlite3test/.libs/testfixture 
../sqliteSrc/sqlite-3.5.5/test/qu'.
Program terminated with signal 10, Bus error.
Reading symbols from /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0...done.
Loaded symbols for /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0
Reading symbols from /usr/lib/libpthread.so.1...done.
Loaded symbols for /usr/lib/libpthread.so.1
Reading symbols from /usr/local/lib/libtcl8.5.so...done.
Loaded symbols for /usr/local/lib/libtcl8.5.so
Reading symbols from /usr/lib/libdl.so.1...done.
Loaded symbols for /usr/lib/libdl.so.1
Reading symbols from /usr/lib/libsocket.so.1...done.
Loaded symbols for /usr/lib/libsocket.so.1
Reading symbols from /usr/lib/libnsl.so.1...done.
Loaded symbols for /usr/lib/libnsl.so.1
Reading symbols from /usr/lib/libm.so.1...done.
Loaded symbols for /usr/lib/libm.so.1
Reading symbols from /usr/lib/librt.so.1...done.
Loaded symbols for /usr/lib/librt.so.1
Reading symbols from /usr/lib/libc.so.1...done.
Loaded symbols for /usr/lib/libc.so.1
Reading symbols from /usr/local/lib/libgcc_s.so.1...done.
Loaded symbols for /usr/local/lib/libgcc_s.so.1
Reading symbols from /usr/lib/libmp.so.2...done.
Loaded symbols for /usr/lib/libmp.so.2
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1...done.
Loaded symbols for /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1
Reading symbols from /usr/lib/libthread.so.1...done.
Loaded symbols for /usr/lib/libthread.so.1
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995, ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
671   if( ppOpen!=0 ){
(gdb) backtrace
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995, ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
#1  0x000355b4 in unixOpen (pVfs=0x8a96c,
zPath=0xc0380 
"/home/ixion/LIB/sqlite3test/chocolate/banana/vanilla/file.db", pFile=0xd196d, 
flags=262, pOutFlags=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:2268
#2  0x000340bc in sqlite3OsOpen (pVfs=0x2102,
zPath=0xc0380 
"/home/ixion/LIB/sqlite3test/chocolate/banana/vanilla/file.db", pFile=0xd196d, 
flags=262, pFlagsOut=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os.c:112

ppOpen  os_unxi.c/671 is not !=0 but *ppOpen is 0...


James Dennett  wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett  wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett  wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James

Re: [sqlite] Using LIKE to check the first digits?

[P Kishor]

On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote:
> Hello
>
> I'm no SQL guru, and need to look up phone numbers in a SQLite database
> that start with certain digits. Some customers use
> http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the
> first part is common to all the numbers assigned to this customer, so I
> don't need to enter every single one of them, and just assign the prefix,
> eg. "123" matches "1230001", "1230002", etc.
>
> Should I use the LIKE command for this? Does someone have an example to do
> this?
>

WHERE col LIKE '123%'

or WHERE substr(col,1, 3) = '123'
> Thank you.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Using LIKE to check the first digits?

[Gilles]

Hello

I'm no SQL guru, and need to look up phone numbers in a SQLite database 
that start with certain digits. Some customers use 
http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the 
first part is common to all the numbers assigned to this customer, so I 
don't need to enter every single one of them, and just assign the prefix, 
eg. "123" matches "1230001", "1230002", etc.


Should I use the LIKE command for this? Does someone have an example to do 
this?


Thank you. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Solaris bus error

[Ken]

Core/backtrace info:


(gdb) core-file core
Core was generated by `/home/ixion/LIB/sqlite3test/.libs/testfixture 
../sqliteSrc/sqlite-3.5.5/test/qu'.
Program terminated with signal 10, Bus error.
Reading symbols from /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0...done.
Loaded symbols for /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0
Reading symbols from /usr/lib/libpthread.so.1...done.
Loaded symbols for /usr/lib/libpthread.so.1
Reading symbols from /usr/local/lib/libtcl8.5.so...done.
Loaded symbols for /usr/local/lib/libtcl8.5.so
Reading symbols from /usr/lib/libdl.so.1...done.
Loaded symbols for /usr/lib/libdl.so.1
Reading symbols from /usr/lib/libsocket.so.1...done.
Loaded symbols for /usr/lib/libsocket.so.1
Reading symbols from /usr/lib/libnsl.so.1...done.
Loaded symbols for /usr/lib/libnsl.so.1
Reading symbols from /usr/lib/libm.so.1...done.
Loaded symbols for /usr/lib/libm.so.1
Reading symbols from /usr/lib/librt.so.1...done.
Loaded symbols for /usr/lib/librt.so.1
Reading symbols from /usr/lib/libc.so.1...done.
Loaded symbols for /usr/lib/libc.so.1
Reading symbols from /usr/local/lib/libgcc_s.so.1...done.
Loaded symbols for /usr/local/lib/libgcc_s.so.1
Reading symbols from /usr/lib/libmp.so.2...done.
Loaded symbols for /usr/lib/libmp.so.2
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1...done.
Loaded symbols for /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1
Reading symbols from /usr/lib/libthread.so.1...done.
Loaded symbols for /usr/lib/libthread.so.1
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995, ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
671   if( ppOpen!=0 ){
(gdb) backtrace
#0  0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995, ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
#1  0x000355b4 in unixOpen (pVfs=0x8a96c,
zPath=0xc0380 
"/home/ixion/LIB/sqlite3test/chocolate/banana/vanilla/file.db", pFile=0xd196d, 
flags=262, pOutFlags=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:2268
#2  0x000340bc in sqlite3OsOpen (pVfs=0x2102,
zPath=0xc0380 
"/home/ixion/LIB/sqlite3test/chocolate/banana/vanilla/file.db", pFile=0xd196d, 
flags=262, pFlagsOut=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os.c:112

ppOpen  os_unxi.c/671 is not !=0 but *ppOpen is 0...


James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James




James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James

[sqlite] Precompiled binaries for Windows Not Version 3.5.5

[Robert Wishlaw]

Under the Precompiled binaries for Windows section of the downloads
page, the sqlite-3.5.5.zip, sqlitedll-3.5.5.zip and
tclsqlite-3.5.5.zip files contain 3.5.4 binaries from December 12,
2007.

Robert Wishlaw

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Solaris bus error

[James Dennett]

> -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:55 PM
> To: sqlite
> Subject: [sqlite] Solaris bus error
> 
> 
> After addressing the -lrt sched yield on solaris.
> 
> make test resulted in:
> 
> async3-1.0... Ok
> async3-1.1...make: *** [test] Bus Error (core dumped)
> 
> Any ideas?

Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.

-- James


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Solaris bus error

[Ken]

After addressing the -lrt sched yield on solaris.

make test resulted in:

async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)

Any ideas?

Regards,
Ken

Re: [sqlite] Solaris make test compilation error

[Nicolas Williams]

On Thu, Jan 31, 2008 at 02:32:45PM -0800, James Dennett wrote:
> > > Undefined   first referenced
> > >  symbol in file
> > > sched_yield /var/tmp//cckDMcyL.o
> > > ld: fatal: Symbol referencing errors. No output written to
> > .libs/testfixture
> > 
> > So how does a thread yield its timeslice on solaris?
> 
> sched_yield is there, it just needs -lrt as it is considered part of the
> "Realtime Library Functions" according to its manpage.

Correct.

Note too that you can also use thr_yield(3C) (#include ) or
even yield(2) (#include ).  All three functions yield only the
caller's thread's timeslice; sched_yield() and thr_yield() both end up
calling yield(2).

thr_yield(3C) and yield(2) live in libc, while sched_yield(3RT) lives in
librt.

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: how to do this case when?

[RB Smissaert]

There isn't much in it, but it looks the one with IFNULL is the fastest.
Will stick to that one.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 21:17
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: how to do this case when?

Thanks; I came up with number 3, but I like your number 1.
Any idea what could be the fastest or will it all be the same?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:59
To: SQLite
Subject: [sqlite] Re: how to do this case when?

RB Smissaert <[EMAIL PROTECTED]>
wrote: 
> How do I alter this SQL, so that the original field remains the same
> when 
> there is no match? A case when else end should do it, but I can't get
> it 
> right.
> 
> UPDATE Table1 SET Field1 =
> (SELECT Field2 FROM Table2
> WHERE Table1.Field1 = Table2.Field1)

UPDATE Table1 SET Field1 =
IFNULL(
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1),
Field1);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1
  union all select Table1.Field1;
);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)
where exists
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Solaris make test compilation error

[James Dennett]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 31, 2008 2:23 PM
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] Solaris make test compilation error
> 
> Ken <[EMAIL PROTECTED]> wrote:
> > Solaris 5.8 (solars 8)
> > make test
> >
> > Undefined   first referenced
> >  symbol in file
> > sched_yield /var/tmp//cckDMcyL.o
> > ld: fatal: Symbol referencing errors. No output written to
> .libs/testfixture
> > collect2: ld returned 1 exit status
> > make: *** [testfixture] Error 1
> >
> 
> So how does a thread yield its timeslice on solaris?

sched_yield is there, it just needs -lrt as it is considered part of the
"Realtime Library Functions" according to its manpage.

-- James

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Solaris make test compilation error

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> Solaris 5.8 (solars 8)
> make test
> 
> Undefined   first referenced
>  symbol in file
> sched_yield /var/tmp//cckDMcyL.o
> ld: fatal: Symbol referencing errors. No output written to .libs/testfixture
> collect2: ld returned 1 exit status
> make: *** [testfixture] Error 1
> 

So how does a thread yield its timeslice on solaris?

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Solaris make test compilation error

[Ken]

Solaris 5.8 (solars 8)
make test

Undefined   first referenced
 symbol in file
sched_yield /var/tmp//cckDMcyL.o
ld: fatal: Symbol referencing errors. No output written to .libs/testfixture
collect2: ld returned 1 exit status
make: *** [testfixture] Error 1

to fix I added $(TLIBS) to the testfixture in the make file:

testfixture$(TEXE): $(TOP)/src/tclsqlite.c libsqlite3.la $(TESTSRC)
$(LTLINK) -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 \
-DSQLITE_NO_SYNC=1 $(TEMP_STORE) \
-o testfixture $(TESTSRC) $(TOP)/src/tclsqlite.c \
libsqlite3.la $(LIBTCL) $(TLIBS)

[sqlite] Can not use Attached Tables in the SELECT part of a View

[scott_mcdonald]

I came across an issue where I can not JOIN databases with some tables being in 
an attached tables in the SELECT part when creating a View. I keep 
getting errors about not being able to reference objects in the attached table. 
I understand this error but believe it is too pessimistic. I would 
expect SQLite to create the View and when it is executed (opened) if the proper 
tables were not attached at execution time then it would "throw an 
exception" at that moment.

Attached tables are useful to create "daily transactional or activity" types of 
files, we don't want a system to have a single monolithic database 
file that keeps growing so you have "daily" database files to capture the daily 
activity of a system - I use the timestamp of the day as the filename. 
At "system reset time" you go onto the next day, etc.

Here is a condensed example:

In "System.db3" I have the following Table:

SessionStatusTable
- SessionStatusKey (Primary Key)
- SessionStatus (Text Field)

UserTable
- UserNumber (PrimaryKey)
- UserName (Text Field)

MachineTable
- MachineNumber (PrimaryKey)
- MachineName (Text Field)

SessionEndReasonTable
- SessionEndReasonKey (PrimaryKey)
- SessionEndReason (Text Field)

There are configuration types of tables. This is the first table connected to, 
thus its database name is "Main" as part of the SQLite standard.

In a daily transactional or activity file "-MM-DD.db3" I have the following 
Table:
SessionTable
- SessionNumber (Primary Key)
- SessionStatusKey (Foreign Key)
- UserNumber (Foreign Key)
- MachineNumber (Foreign Key)
- ApplicationName
- BeginTime
- LastRequestTime
- EndTime
- SessionEndReasonKey (Foreign Key)

It would be nice to create a View in this Table where the SELECT part of the 
View can JOIN with the configuration tables in Main so you can view the 
SessionView with the SessionStatus, UserName, MachineName, and SessionEndReason 
text fields instead of their primary keys. This would mean you can use 
any of the SQLite editors, execute the SessionView and it gives a much clearer 
"view" (pardon the pun) of session information.

NOTE: I can create a SELECT query programmatically at runtime that has JOINS 
with attached database tables and that works great, but it would be nice 
to allow a View to use the exact same SELECT query but with the advantage of 
having the view stored with the database file for easy execution by end 
users or support people.

I am right, or am I wrong? Opinions?
- Scott McDonald


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Implementation of TPCC benchmark

[Steve VanDeBogart]

On Thu, 31 Jan 2008, Eduardo Morras wrote:


On Wed, 30 Jan 2008, Steve VanDeBogart wrote:


OSDL-DBT is a set of TPC like benchmarks implemented by OSDL
(http://osdldbt.sourceforge.net/).  I've added SQLite support to the DBT2 
benchmark (TPCC like).  The patch is attached in case anyone is

interested in running a TPCC like benchmark on SQLite.


And where can i compare the results? The DBT2 Benchmark patch permits to do a 
TPCC benchmark? Will the TPCC benchmark include sqlite results?


Sorry but i know nothing about this type of benchs.


DBT2 is a TPC-C like benchmark.  A Full TPC-C benchmark must include
information about the cost and configuration of the hardware it is run
on as well as providing an audited report of the actual benchmarking.

You could use DBT2 to compare different hardware configurations or to
benchmark a change to sqlite (of course with sqlite, performance is not
the only factor to consider -- size and complexity are just as
important).

--
Steve

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Number of columns in table

[Dennis Cote]

Andreas Volz wrote:


What is if the cell format is
Number or BLOB? Does sqlite convert it always to char?



Yes, the callback interface is a throwback to a time when all sqlite 
data was stored as strings. This interface only supports strings, so all 
fields are converted to strings to send to the callback routine.



And how do I find out the cell type?



You can't with the callback interface.

You should really look into using the prepare/step/finalize interface 
along with the column_* access functions. They allow much better control 
of what you get (and avoid the overhead of unnecessary format 
conversions), and allow you to determine the actual stored data types.


For a good example of the new interface you can look at the source for 
sqlite3_exec(). It is implemented using the new interface functions. It 
prepares your query, runs the prepared query, collects the column names 
and field data for each result row, and then calls the callback function.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Number of columns in table

[Igor Tandetnik]

Andreas Volz <[EMAIL PROTECTED]> wrote:

Oh, you're right. I didn't notice that. What is if the cell format is
Number or BLOB? Does sqlite convert it always to char?

And how do I find out the cell type?


When you are using sqlite3_exec, values passed to callback are always 
converted to string and there's no way to get the type. Switch to using 
sqlite3_prepare[_v2], sqlite3_step, sqlite_column_* et al.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: how to do this case when?

[RB Smissaert]

Thanks; I came up with number 3, but I like your number 1.
Any idea what could be the fastest or will it all be the same?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:59
To: SQLite
Subject: [sqlite] Re: how to do this case when?

RB Smissaert <[EMAIL PROTECTED]>
wrote: 
> How do I alter this SQL, so that the original field remains the same
> when 
> there is no match? A case when else end should do it, but I can't get
> it 
> right.
> 
> UPDATE Table1 SET Field1 =
> (SELECT Field2 FROM Table2
> WHERE Table1.Field1 = Table2.Field1)

UPDATE Table1 SET Field1 =
IFNULL(
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1),
Field1);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1
  union all select Table1.Field1;
);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)
where exists
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Broken links to "sqlite3" on website

[David Baird]

On Jan 31, 2008 1:45 PM,  <[EMAIL PROTECTED]> wrote:
> Tnx.  Please try it again now.

Great, seems to be working now, even for other pages that I didn't list.

Thanks!

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Number of columns in table

[Andreas Volz]

Am Wed, 30 Jan 2008 17:04:17 -0700 schrieb Dennis Cote:

> > What if I like e.g. to write each table row into a file in one
> > line? I need to know when a row is finished and the next begins. I
> > could compare azColName, but I hope there is a better way.
> > 
> 
> Andreas,
> 
> Your readCallback function will be called once for each row in the 
> result of your query.
> 
> The argc value tells you how many fields there are in the row.
> 
> The argv array contains pointers to the string representation of each 
> filed in the row, there is one string for each of the argc fields. If
> a field is null then its string pointer (in argv) will be NULL).

Oh, you're right. I didn't notice that. What is if the cell format is
Number or BLOB? Does sqlite convert it always to char?

And how do I find out the cell type?

regards
Andreas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: how to do this case when?

[Igor Tandetnik]

RB Smissaert <[EMAIL PROTECTED]>
wrote: 

How do I alter this SQL, so that the original field remains the same
when 
there is no match? A case when else end should do it, but I can't get
it 
right.


UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)


UPDATE Table1 SET Field1 =
IFNULL(
   (SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1),
   Field1);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1
 union all select Table1.Field1;
);

-- or

UPDATE Table1 SET Field1 =
   (SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)
where exists
   (SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] how to do this case when?

[RB Smissaert]

Found this now:

UPDATE Table1 SET Field1 =
(case when Field1 IN (SELECT Field1 FROM Table2)
then
(SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1)
else
Table1.Field1 end)

Have a feeling though that this can be shorter.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:15
To: sqlite-users@sqlite.org
Subject: [sqlite] how to do this case when?

How do I alter this SQL, so that the original field remains the same when
there is no match? A case when else end should do it, but I can't get it
right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] how to do this case when?

[Fowler, Jeff]

At the end (outside the subquery), add:

WHERE Field1 IN (SELECT Field1 FROM Table2)

- Jeff

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 31, 2008 3:15 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to do this case when?

How do I alter this SQL, so that the original field remains the same
when there is no match? A case when else end should do it, but I can't
get it right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Broken links to "sqlite3" on website

[drh]

"David Baird" <[EMAIL PROTECTED]> wrote:
> The "sqlite3" link is broken from multiple web pages:
> 
> http://www.sqlite.org/c3ref/objlist.html
> http://www.sqlite.org/c3ref/intro.html
> 
> Also, links to "sqlite3_stmt" are broken.  It seems that any link
> containing a %20 is broken, e.g.
> 
> http://www.sqlite.org/c3ref/database%20connection.html
> http://www.sqlite.org/c3ref/prepared%20statement.html
> 
> and I suspect that they are supposed to be these instead:
> 
> http://www.sqlite.org/c3ref/sqlite3.html
> http://www.sqlite.org/c3ref/stmt.html
> 

Tnx.  Please try it again now.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Make test on Ubuntu 7.10 SQLite 3.5.5

[Rob Sciuk]

$ uname -a
Linux linux 2.6.22-14-generic #1 SMP Sun Oct 14 23:05:12 GMT 2007 i686 GNU/Linux

$ make test | grep -v Ok | tee test.output.ubuntu

./testfixture ../test/quick.test
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 8119900 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=81A2C3C nRef=2
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 3305471 microseconds per iteration
time without cache: 14317410 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..   241673 uS   4137.82259 row/s
speed3-1.normal...  229172 uS   4363.53481 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..68500 uS  14598.54015 row/s
speed3-2.normal...  115952 uS   8624.25831 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
13 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 printf-1.7.6 printf-1.8.6 
printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 
vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14162050 bytes
Current memory usage: 0 bytes

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how to do this case when?

[RB Smissaert]

How do I alter this SQL, so that the original field remains the same when
there is no match? A case when else end should do it, but I can't get it
right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test problems on Fedora 7

[Scott Baker]

Alexander Batyrshin wrote:

Looks like you built it without TCL support


Heh... duh..

Still have problems though... I have tcl and tcl-devel install and I 
get this:



checking whether to use an in-ram database for temporary tables... no
checking if executables have the .exe suffix... unknown
checking for Tcl configuration... configure: error: yes directory 
doesn't contain tclConfig.sh


--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Broken links to "sqlite3" on website

[David Baird]

Hi,

I'm not complaining (I love SQLite), but I just wanted to point out
that the "sqlite3" link is broken from multiple web pages:

http://www.sqlite.org/c3ref/objlist.html
http://www.sqlite.org/c3ref/intro.html

Also, links to "sqlite3_stmt" are broken.  It seems that any link
containing a %20 is broken, e.g.

http://www.sqlite.org/c3ref/database%20connection.html
http://www.sqlite.org/c3ref/prepared%20statement.html

and I suspect that they are supposed to be these instead:

http://www.sqlite.org/c3ref/sqlite3.html
http://www.sqlite.org/c3ref/stmt.html

-David

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Implementation of TPCC benchmark

[Eduardo Morras]

At 23:53 30/01/2008, you wrote:


On Wed, 30 Jan 2008, Steve VanDeBogart wrote:


SQLite-ers,

OSDL-DBT is a set of TPC like benchmarks implemented by OSDL
(http://osdldbt.sourceforge.net/).  I've added SQLite support to 
the DBT2 benchmark (TPCC like).  The patch is attached in case anyone is

interested in running a TPCC like benchmark on SQLite.


Sorry, the patch can be obtained from here:
http://cs.ucla.edu/~vandebo/dbt2-sqlite.patch


And where can i compare the results? The DBT2 Benchmark patch permits 
to do a TPCC benchmark? Will the TPCC benchmark include sqlite results?


Sorry but i know nothing about this type of benchs.

TIA 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Version 3.5.5 Released

[Rich Shepard]

  For any who care, 3.5.5 built flawlessly on Slackware-11.0 and -12.0.
Works, too.

  Thank you, development team.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test problems on Fedora 7

[Alexander Batyrshin]

Oh... you know about it. Sorry

>Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need
>some other tcl package? Any idea what I need?

On Jan 31, 2008 8:27 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>> Looks like you built it without TCL support

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test problems on Fedora 7

[Alexander Batyrshin]

Looks like you built it without TCL support

On Jan 31, 2008 7:54 PM, Scott Baker <[EMAIL PROTECTED]> wrote:
> I downloaded sqlite 3.5.5 and ran make test. I get the following errors:
>
> /tmp/cc9i7nKr.o: In function `Sqlitetest1_Init':
> /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to
> `Tcl_CreateCommand'
> /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to
> `Tcl_CreateObjCommand'
> /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to
> `Tcl_LinkVar'
> /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined
> references to `Tcl_LinkVar' follow
> /tmp/cc9i7nKr.o: In function `test_io_trace':
> /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to
> `Tcl_AppendResult'
> /tmp/cc9i7nKr.o: In function `test_printf':
> /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to
> `Tcl_AppendResult'/tmp/cc9i7nKr.o: In function `Sqlitetest1_Init':
> /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to
> `Tcl_CreateCommand'
> /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to
> `Tcl_CreateObjCommand'
> /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to
> `Tcl_LinkVar'
> /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to
> `Tcl_LinkVar'
> /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined
> references to `Tcl_LinkVar' follow
> /tmp/cc9i7nKr.o: In function `test_io_trace':
> /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to
> `Tcl_AppendResult'
> /tmp/cc9i7nKr.o: In function `test_printf':
> /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to
> `Tcl_AppendResult'
> /tmp/cc9i7nKr.o: In function `getDbPointer':
> /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to
> `Tcl_GetCommandInfo'
> /tmp/cc9i7nKr.o: In function `test_busy_timeout':
> /tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to
> `Tcl_AppendResult'
> /tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to
> `Tcl_GetInt'
> /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to
> `Tcl_AppendResult'
>
> /tmp/cc9i7nKr.o: In function `getDbPointer':
> /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to
> `Tcl_GetCommandInfo'
> /tmp/cc9i7nKr.o: In function `test_busy_timeout':
> /tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to
> `Tcl_AppendResult'
> /tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to
> `Tcl_GetInt'
> /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to
> `Tcl_AppendResult'
>
> Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need
> some other tcl package? Any idea what I need?
>
> --
> Scott Baker - Canby Telcom
> RHCE - System Administrator - 503.266.8253
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Version 3.5.5 Released

[Noah Hart]

Got it!  Thanks for the clarification.

Noah


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 31, 2008 10:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Version 3.5.5 Released
Importance: High

"Noah Hart" <[EMAIL PROTECTED]> wrote:
> With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++
> 
> It claims that:
> error LNK2001: unresolved external symbol sqlite3VtabBeginParse

That call *is* surrounded by #ifndef SQLITE_OMIT_VIRTUALTABLE.  But
the #ifndef is located in parse.y.  You are probably trying to build
off of parse.c, which is a generated file.  You have to tell "lemon"
about the SQLITE_OMIT_VIRTUALTABLE macro or it will generate code
to call sqlite3VtabBeginParse().

In general, you cannot use SQLITE_OMIT macros if you are compiling
from the amalgamation or from the preprocessed C code.  You have
to compile from canonical sources, an in particular you must compile
starting with parse.y, not parse.c.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite Compilation Problems on FreeBSD

[L. S.]

I got "sqlite-3.5.4.tar.gz" to compile, cleanly, and installation went 
smooth.


After compiling and installing tcl and tk, the slqite "make" step still 
failed, saying "*** Error code 1", just like it did before tcl/tk; it 
complained about tclsh being installed under multiple names (tclsh, 
tclsh8.4, and tclsh.8.5).


It turned out to be "tclsh", itself, making all the noise. So I did the 
following:

# mv /usr/local/bin/tclsh /usr/local/bin/OFF_tclsh
# ln -s /usr/local/bin/tclsh8.5 /usr/local/bin/tclsh
$ make clean
$ make
# make install

It worked!


Rob Sciuk wrote:


On Sun, 27 Jan 2008, L. S. wrote:


Why doesn't SQLite 3.5.4 compile under FreeBSD 5.3? Can it be done in 
a sane manner?


AFAIK, it just works, but you may require the -lc_r  option to include 
the re-entrant (threadsafe) C library with your linked executeable ...




How can I link against SQLite under FreeBSD 5.3, using gcc?
Is there any way that I could just link the source files into my code?

* Using "./configure" and "make" with "sqlite-3.5.4.tar.gz", I got tcl 
and tcsh errors.
* Using "sqlite-source-3_5_4.zip", with "tclsqlite.c" removed, I got 
pthread errors. They went away with "gcc ... -lpthread", but 
compilation still failed.
* I think "sqlite-amalgamation-3_5_4.zip" produced the same errors as 
sqlite-source-.


I have compiled, installed, linked, and used it on other systems, but 
cannot do the same on my FreeBSD system. (I don't use the FreeBSD 
ports; I like to just download and build.)




Moi aussi, but I have *NEVER* had a problem with SQLite on FreeBSD, 
including 3.5.4 ... Of course I *ALWAYS* install Tcl/Tk first, and am 
happy to use the tclsqlite bindings which result ...


Again, when you link your C code to the libSqlite.so, just add the -lc_r
option, and you can delete the -lpthreads unless you are actually using 
pthreads, in which case you will likely need both -lpthreads *AND* -lc_r

or surprises may result.

Apparently, the -lc_r requirement is new with 3.5.4, and my query on 
this list has been met with silence thus far, so I cannot advise you 
further. Apparently the db engine has been re-worked somewhat 
(extensively) with this release, and I suspect that changes eminate from 
that code, but I haven't had time to investigate ...


HTH,

Rob Sciuk

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] make test problems on Fedora 7

[Scott Baker]

I downloaded sqlite 3.5.5 and ran make test. I get the following errors:

/tmp/cc9i7nKr.o: In function `Sqlitetest1_Init':
/tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to 
`Tcl_CreateCommand'
/tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to 
`Tcl_CreateObjCommand'
/tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to 
`Tcl_LinkVar'
/tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined 
references to `Tcl_LinkVar' follow

/tmp/cc9i7nKr.o: In function `test_io_trace':
/tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to 
`Tcl_AppendResult'

/tmp/cc9i7nKr.o: In function `test_printf':
/tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to 
`Tcl_AppendResult'/tmp/cc9i7nKr.o: In function `Sqlitetest1_Init':
/tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to 
`Tcl_CreateCommand'
/tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to 
`Tcl_CreateObjCommand'
/tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to 
`Tcl_LinkVar'
/tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to 
`Tcl_LinkVar'
/tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined 
references to `Tcl_LinkVar' follow

/tmp/cc9i7nKr.o: In function `test_io_trace':
/tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to 
`Tcl_AppendResult'

/tmp/cc9i7nKr.o: In function `test_printf':
/tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to 
`Tcl_AppendResult'

/tmp/cc9i7nKr.o: In function `getDbPointer':
/tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to 
`Tcl_GetCommandInfo'

/tmp/cc9i7nKr.o: In function `test_busy_timeout':
/tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to 
`Tcl_AppendResult'
/tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to 
`Tcl_GetInt'
/tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to 
`Tcl_AppendResult'


/tmp/cc9i7nKr.o: In function `getDbPointer':
/tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to 
`Tcl_GetCommandInfo'

/tmp/cc9i7nKr.o: In function `test_busy_timeout':
/tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to 
`Tcl_AppendResult'
/tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to 
`Tcl_GetInt'
/tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to 
`Tcl_AppendResult'


Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need 
some other tcl package? Any idea what I need?


--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Alexander Batyrshin]

Here is my errors on mac os 10.5 with 3.5.5 SQLite

lock4-1.3...
Error: database is locked

vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]

vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]

vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]


On Jan 31, 2008 7:27 PM,  <[EMAIL PROTECTED]> wrote:
> "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> > How to get error messages from test? Scroll up and copy-paste? Or
> > there is some other more handy method?
>
> make test | tee testout.txt
>
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote:


Date: Thu, 31 Jan 2008 18:27:35 +
From: [EMAIL PROTECTED]
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:

How to get error messages from test? Scroll up and copy-paste? Or
there is some other more handy method?


make test | tee testout.txt


aha!  Take the simple approach, in keeping I re-ran the 3.5.5 test with:

make test | grep -v Ok | tee test.output

-=-=-=-=-=-=-=-=-= test.output -=-=-=-=-=-=-=-=-=-=
./testfixture ../test/quick.test
bind-4.4...
Error: floating point value is Not a Number
bind-4.5...
Expected: [null real real]
 Got: []
btree-2.1.1...CURSOR 8121088 rooted at1(rw) currently at 1.0 eof
PAGE 1:  flags=0x09  frag=0   parent=0
cell  0: i=913..1023  chld=0nk=2020 nd=1792 payload=*** This is a very 
PAGE 2:  flags=0x08  frag=0   parent=0

PAGE   1 addr=80F7894 nRef=2
cast-3.14...
Expected: [9223372036854774784]
 Got: [9223372036854773760]
cast-3.18...
Expected: [-9223372036854774784]
 Got: [-9223372036854773760]
cast-3.24...
Expected: [9223372036854774784]
 Got: [9223372036854773760]
Skipping crash5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc5 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc6 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc7 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc8 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping malloc9 tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocA tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocB tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocC tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocD tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocE tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocF tests: not compiled with -DSQLITE_MEMDEBUG...
Skipping mallocG tests: not compiled with -DSQLITE_MEMDEBUG...
printf-1.7.6...
Expected: [Three integers: (100) ( f4240) (3641100)]
 Got: [Three integers: ( 100) ( f4240) (3641100)]
printf-1.8.6...
Expected: [Three integers: (9) (3b9ac9ff) (7346544777)]
 Got: [Three integers: ( 9) (3b9ac9ff) (7346544777)]
printf-1.9.7...
Expected: [Three integers: ( 0) (   0x0) ( 0)]
 Got: [Three integers: ( 0) ( 0) ( 0)]
Skipping safety tests since SQLITE_DEBUG is off
time with cache: 2292055 microseconds per iteration
time without cache: 7910271 microseconds per iteration
result=one 10 three 5 two 14
speed3-1.incrvacuum..   201285 uS   4968.08009 row/s
speed3-1.normal...  336842 uS   2968.75093 row/s
Incrvacuum: Read 1098, wrote 94
Normal: Read 10479, wrote 43
speed3-2.incrvacuum..   100244 uS   9975.65939 row/s
speed3-2.normal...  233531 uS   4282.08675 row/s
Incrvacuum: Read 2843, wrote 1
Normal: Read 10011, wrote 0
tcl-1.6...
Expected: [1 {syntax error in expression "x*"}]
 Got: [1 {invalid bareword "x"
in expression "x*";
should be "$x" or "{x}" or "x(...)" or ...}]
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
 Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
 Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
 Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
 Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
 Got: [2 22 {}]
vtab6-9.2...
Expected: []
 Got: [2 22 {}]
16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 
vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/FreeBSD.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[drh]

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Here is my "make test" on Mac OS 10.5.1:
> 
> 8 errors out of 38117 tests
> Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5
> vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> All memory allocations freed - no leaks
> Maximum memory usage: 14161966 bytes
> Current memory usage: 0 bytes
> make: *** [test] Error 1
> 

We tested on Mac OS 10.4.11.  No errors there.

Once again, there is not much I can do to work on this problem
without seeing the text of the error messages

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Version 3.5.5 Released

[drh]

"Noah Hart" <[EMAIL PROTECTED]> wrote:
> With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++
> 
> It claims that:
> error LNK2001: unresolved external symbol sqlite3VtabBeginParse

That call *is* surrounded by #ifndef SQLITE_OMIT_VIRTUALTABLE.  But
the #ifndef is located in parse.y.  You are probably trying to build
off of parse.c, which is a generated file.  You have to tell "lemon"
about the SQLITE_OMIT_VIRTUALTABLE macro or it will generate code
to call sqlite3VtabBeginParse().

In general, you cannot use SQLITE_OMIT macros if you are compiling
from the amalgamation or from the preprocessed C code.  You have
to compile from canonical sources, an in particular you must compile
starting with parse.y, not parse.c.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

On Thu, 31 Jan 2008, [EMAIL PROTECTED] wrote:

Rob Sciuk <[EMAIL PROTECTED]> wrote:

Ran the tests on Freebsd, the make test summary follows:
[delenda => ... Ok]

16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4
vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1



We get zero test failures on Linux.  I don't know if these are
significant or not without seeing the test error messages that
accompany each failure.

Probably this is things where your system is printing 3.4e+05
whereas SQLite is looking for 3.4e+005, in which case the errors
are benign.  But without seeing the actual errors, I cannot say
for sure.
--
D. Richard Hipp <[EMAIL PROTECTED]>



Hmmm ... by my reckoning, you have added 261 tests *JUST* for version 
3.5.5.  Pretty big effort I'd say, and much appreciated I'd like to add.


I re-ran the make test on 3.5.4, and there seems to be a significant 
overlap in those reporting failure (see below).  As I have not yet figured 
out how to run them individually (eg:  make test bind-4.4), nor did I find 
an output error file, I'll make the assumption that you are correct, and 
the diffs are indeed benign (since everything seems to work really well), 
at least until I have some time to re-visit.


Have you done any performance regression tests on the 3.5.5 VM yet??  I 
suspect that there will be an improvement ...


-=-=-=-=-=-=-=-=-=
FreeBSD 6.2-R (same hw), SQLite 3.5.4 reported:
[delendum ... Ok]

9 errors out of 38700 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6

All memory allocations freed - no leaks
Maximum memory usage: 14165134 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.4/FreeBSD.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Alexander Batyrshin]

How to get error messages from test? Scroll up and copy-pase? Or there
is some other more handy method?

On Jan 31, 2008 7:03 PM,  <[EMAIL PROTECTED]> wrote:
> Rob Sciuk <[EMAIL PROTECTED]> wrote:
> > Ran the tests on Freebsd, the make test summary follows:
> > [delenda => ... Ok]
> >
> > 16 errors out of 38961 tests
> > Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24
> > printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4
> > vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> > All memory allocations freed - no leaks
> > Maximum memory usage: 14161974 bytes
> > Current memory usage: 0 bytes
> > *** Error code 1
> >
>
> We get zero test failures on Linux.  I don't know if these are
> significant or not without seeing the test error messages that
> accompany each failure.
>
> Probably this is things where your system is printing 3.4e+05
> whereas SQLite is looking for 3.4e+005, in which case the errors
> are benign.  But without seeing the actual errors, I cannot say
> for sure.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Alexander Batyrshin]

Here is my "make test" on Mac OS 10.5.1:

8 errors out of 38117 tests
Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5
vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
All memory allocations freed - no leaks
Maximum memory usage: 14161966 bytes
Current memory usage: 0 bytes
make: *** [test] Error 1

-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[drh]

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> How to get error messages from test? Scroll up and copy-paste? Or
> there is some other more handy method?

make test | tee testout.txt

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Version 3.5.5 Released

[Noah Hart]

With SQLITE_OMIT_VIRTUALTABLE defined, I get a linker fault under VC++

It claims that:
error LNK2001: unresolved external symbol sqlite3VtabBeginParse

Looking through the source code, I see the following

  Y:\SQLite3\Projects\3_5_5\src\sqliteInt.h(1986)
#ifdef SQLITE_OMIT_VIRTUALTABLE
#  define sqlite3VtabClear(X)
#  define sqlite3VtabSync(X,Y) (Y)
#  define sqlite3VtabRollback(X)
#  define sqlite3VtabCommit(X)
#else
   void sqlite3VtabClear(Table*);
   int sqlite3VtabSync(sqlite3 *db, int rc);
   int sqlite3VtabRollback(sqlite3 *db);
   int sqlite3VtabCommit(sqlite3 *db);
#endif
void sqlite3VtabLock(sqlite3_vtab*);
void sqlite3VtabUnlock(sqlite3*, sqlite3_vtab*);
void sqlite3VtabBeginParse(Parse*, Token*, Token*, Token*);

-- So it is supposed to exist, even with SQLITE_OMIT_VIRTUALTABLE
defined.

Also, Parse uses it here 
Y:\SQLite3\Projects\3_5_5\src\parse.c(3225):   
case 302: /* create_vtab ::= CREATE VIRTUAL TABLE nm dbnm USING nm */
#line 1102 "parse.y"
{
sqlite3VtabBeginParse(pParse, [-3].minor.yy410,
[-2].minor.yy410, [0].minor.yy410);
}

But it is not compiled in Y:\SQLite3\Projects\3_5_5\src\vtab.c, since
this entire routine is excluded via line 16
#ifndef SQLITE_OMIT_VIRTUALTABLE 


Looks like a dummy MACRO or function needs to be created, 
or the PARSE code needs to be bracketed with #ifndef
SQLITE_OMIT_VIRTUALTABLE


Can someone with source access please make this change.

Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Alexander Batyrshin]

How to get error messages from test? Scroll up and copy-paste? Or
there is some other more handy method?

On Jan 31, 2008 7:03 PM,  <[EMAIL PROTECTED]> wrote:
> Rob Sciuk <[EMAIL PROTECTED]> wrote:
> > Ran the tests on Freebsd, the make test summary follows:
> > [delenda => ... Ok]
> >
> > 16 errors out of 38961 tests
> > Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24
> > printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4
> > vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> > All memory allocations freed - no leaks
> > Maximum memory usage: 14161974 bytes
> > Current memory usage: 0 bytes
> > *** Error code 1
> >
>
> We get zero test failures on Linux.  I don't know if these are
> significant or not without seeing the test error messages that
> accompany each failure.
>
> Probably this is things where your system is printing 3.4e+05
> whereas SQLite is looking for 3.4e+005, in which case the errors
> are benign.  But without seeing the actual errors, I cannot say
> for sure.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[drh]

Rob Sciuk <[EMAIL PROTECTED]> wrote:
> Ran the tests on Freebsd, the make test summary follows:
> [delenda => ... Ok]
> 
> 16 errors out of 38961 tests
> Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
> printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 
> vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2
> All memory allocations freed - no leaks
> Maximum memory usage: 14161974 bytes
> Current memory usage: 0 bytes
> *** Error code 1
> 

We get zero test failures on Linux.  I don't know if these are
significant or not without seeing the test error messages that
accompany each failure.

Probably this is things where your system is printing 3.4e+05
whereas SQLite is looking for 3.4e+005, in which case the errors
are benign.  But without seeing the actual errors, I cannot say
for sure.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5

[Rob Sciuk]

Ran the tests on Freebsd, the make test summary follows:
[delenda => ... Ok]

16 errors out of 38961 tests
Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 
printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 
vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2

All memory allocations freed - no leaks
Maximum memory usage: 14161974 bytes
Current memory usage: 0 bytes
*** Error code 1

Stop in /u0/sw/SQLite/sqlite-3.5.5/FreeBSD.


Any comments on the Failures??
Are these normal, or should I be worried??

Cheers,
Rob.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Version 3.5.5 Released

[Marco Bambini]

What about speed?
Can we expect the same performance of version 3.5.4?

Thanks a lot for your continue improvements.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Jan 31, 2008, at 6:33 PM, [EMAIL PROTECTED] wrote:


SQLite version 3.5.5 is now available for download from the
SQLite website:

   http://www.sqlite.org/

The big change from version 3.5.4 is that the internal virtual
machine was reworked to use operands in registers rather than
pulling operands from a stack.  The virtual machine stack has
now been removed.  The removal of the VM stack will help prevent
future stack overflow bugs and will also facilitate new optimizations
in future releases.

There should be no user-visible changes to the operation of SQLite
in this release, except that the output of EXPLAIN looks different.

In order to make this change, about 8.5% of the core SQLite code
had to be reworked.  We thought this might introduce instability.
But we have done two weeks of intensive testing, during which time
we have increased the statement test coverage to 99% and during
which we have found and fixed lots of minor bugs (mostly things
like leaking memory following a malloc failure).  But for all of
that testing, we have not detected a single bug in the new
register-based VM.  And for that reason, we believe the new
VM, and hence version 3.5.5, is stable and ready for production
use.

As usual, please report any problems to this mailing list, or
directly to me.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Version 3.5.5 Released

[drh]

SQLite version 3.5.5 is now available for download from the
SQLite website:

   http://www.sqlite.org/

The big change from version 3.5.4 is that the internal virtual
machine was reworked to use operands in registers rather than
pulling operands from a stack.  The virtual machine stack has
now been removed.  The removal of the VM stack will help prevent
future stack overflow bugs and will also facilitate new optimizations
in future releases.

There should be no user-visible changes to the operation of SQLite
in this release, except that the output of EXPLAIN looks different.

In order to make this change, about 8.5% of the core SQLite code
had to be reworked.  We thought this might introduce instability.
But we have done two weeks of intensive testing, during which time
we have increased the statement test coverage to 99% and during
which we have found and fixed lots of minor bugs (mostly things
like leaking memory following a malloc failure).  But for all of
that testing, we have not detected a single bug in the new 
register-based VM.  And for that reason, we believe the new
VM, and hence version 3.5.5, is stable and ready for production
use.

As usual, please report any problems to this mailing list, or
directly to me.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Proper SQL Syntax for a SELECT with LEFT JOIN Syntax for a 1 Primary Table, N Secondary Tables

[Dennis Cote]

Scott McDonald wrote:


Questions:
- How would you do what I am trying to do?
- Are these SELECT queries equivalent? Probably not as version 1 has a
sub-select query so this is a "2 pass" query, but is version 1 done in "1
pass" or "2 passes"?
- Is version 2 more efficient?
- For some reason, in version 2 I could not use the "USING" shortcut when
the joined fields had the same name so had to explicitly use the "ON" as
shown above, anybody know why?



Scott,

I prefer the second version myself. I think the intent is more obvious.

The following sqlite trace shows that the same query plan is used for 
queries using either view. I believe they are equivalent.


I believe the error produced by the third view below is a bug in sqlite.

SQLite should still have access to the UserStatusKey field from both the 
result of the first join and the USerStatusTable. The using clause in 
the first join causes standard SQL engines to generate a single new 
column that UserTypeKey, that is not considered to be a member of either 
of the tables, and that replaces the UserTypeKey from both of the joined 
tables.


SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table UserTable (
   ...> UserNumber  integer primary key,
   ...> UserTypeKey integer referneces UserTypeTable,
   ...> UserNametext,
   ...> UserStatusKey integer references UserStatusTable,
   ...> Passwordtext,
   ...> PasswordHint text
   ...> );
sqlite>
sqlite> create table UserTypeTable (
   ...> UserTypeKey integer primary key,
   ...> UserTypetext
   ...> );
sqlite>
sqlite> create table UserStatusTable (
   ...> UserStatusKey   integer primary key,
   ...> UserStatus  text
   ...> );
sqlite>
sqlite>
sqlite> CREATE VIEW UserView1 AS
   ...> SELECT UserNumber, UserType, UserName, UserStatus, Password, 
PasswordHint
   ...> FROM (SELECT UserNumber, UserType, UserName, UserStatusKey, 
Password,
   ...> PasswordHint FROM UserTable LEFT JOIN UserTypeTable 
USING(UserTypeKey))

LEFT
   ...> JOIN UserStatusTable USING(UserStatusKey);
sqlite>
sqlite> CREATE VIEW UserView2 AS
   ...> SELECT UserNumber, UserType, UserName, UserStatus, Password, 
PasswordHint

   ...> FROM UserTable
   ...> LEFT JOIN UserTypeTable ON 
UserTable.UserTypeKey=UserTypeTable.UserTypeKey

   ...> LEFT JOIN UserStatusTable ON
   ...> UserTable.UserStatusKey=UserStatusTable.UserStatusKey;
sqlite>
sqlite> explain query plan select * from UserView1;
0|0|TABLE UserTable
1|1|TABLE UserTypeTable USING PRIMARY KEY
2|2|TABLE UserStatusTable USING PRIMARY KEY
sqlite>
sqlite> explain query plan select * from UserView2;
0|0|TABLE UserTable
1|1|TABLE UserTypeTable USING PRIMARY KEY
2|2|TABLE UserStatusTable USING PRIMARY KEY
sqlite>
sqlite> CREATE VIEW UserView3 AS
   ...> SELECT UserNumber, UserType, UserName, UserStatus, Password, 
PasswordHint

   ...> FROM UserTable
   ...> LEFT JOIN UserTypeTable using(UserTypeKey)
   ...> LEFT JOIN UserStatusTable using(UserStatusKey);
SQL error: cannot join using column UserStatusKey - column not present 
in both tables


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_free_table and sqlite3_close

[drh]

Torsten Blix <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> is it safe to do the following in a C program while working with SQLite? I
> tried searching the docs but couldn't find any answer.
> 
> sqlite3_open();
> sqlite3_get_table(...result);
> sqlite3_close();
> sqlite3_free_table(result);
> 
> i.e. freeing the table AFTER the closing of the db_handle?
> 

yes.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_free_table and sqlite3_close

[Torsten Blix]

Hi,

is it safe to do the following in a C program while working with SQLite? I
tried searching the docs but couldn't find any answer.

sqlite3_open();
sqlite3_get_table(...result);
sqlite3_close();
sqlite3_free_table(result);

i.e. freeing the table AFTER the closing of the db_handle?

Cheers,
Torsten Blix


-- 
View this message in context: 
http://www.nabble.com/sqlite3_free_table-and-sqlite3_close-tp15204430p15204430.html
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Getting the no of rows using count(*)

[kirrthana M]

It works fine..Thanks..

Kirrthana

-Original Message-
From: Bharath Booshan L [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 12:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Getting the no of rows using count(*)


Hello Kirrthana,

> should i use prepare and step in this case and where the result of the
query
> will be stored on executing my c code.

  retValue = sqlite3_prepare( dataBaseConnection ,sqlQuery,-1, ,0);

if( retValue != SQLITE_BUSY && (retValue = sqlite3_step( ppStmt ) ==
SQLITE_ROW )
{
   numOfRows  =sqlite3_column_int64(ppStmt ,0);

}

sqlite3_finalize( ppStmt );


This should do it

--
Bharath

On 1/31/08 11:00 AM, "kirrthana M" <[EMAIL PROTECTED]> wrote:

> Hi all,
>
> Im using the query Select count(*) from table to get the no of rows in the
> table in my c code.
> In the command line it will print the no of rows ,but in my c code how can
i
> get the result,
> should i use prepare and step in this case and where the result of the
query
> will be stored on executing my c code.
>
> Regards
> Kirrthana
>
> The information contained in this electronic message and any attachments
to
> this message are intended for the exclusive use of the addressee(s) and
may
> contain proprietary, confidential or privileged information. If you are
not
> the intended recipient, you should not disseminate, distribute or copy
this
> e-mail. Please notify the sender immediately and destroy all copies of
this
> message and any attachments contained in it.
>
> Contact your Administrator for further information.
>



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails
to and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be
safe. However, no warranty is given that this email is free of malicious
content or virus.




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.

Contact your Administrator for further information.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Proper SQL Syntax for a SELECT with LEFT JOIN Syntax for a 1 Primary Table, N Secondary Tables

[Scott McDonald]

This is mainly a SQL syntax question but I am using the SQL for creating
views in SQLite database files.

Basic question: What is the proper SQL syntax for "Left Outer Joins" for 1
primary with N secondary (support) tables; I have come up with 2
possibilities that work but not sure which one is better or more efficient.
It is best to explain with an actual example from the system under
development:

Primary Table:

UserTable
- UserNumber (Primary Key)
- UserTypeKey (Foreign Key)
- UserName
- UserStatusKey (Foreign Key)
- Password
- PasswordHint

Secondary Tables:

UserTypeTable
- UserTypeKey (Primary Key)
- UserType

UserStatusTable
- UserStatusKey (Primary Key)
- UserStatus

You can probably guess where I am going next, but I am creating a "UserView"
where the "UserType" and "UserStatus" text fields are "joined" from the
respective tables, the foreign keys in the UserTable could be NULL hence the
need for "Left Outer Joins".

Version #1:
CREATE VIEW UserView AS
SELECT UserNumber, UserType, UserName, UserStatus, Password, PasswordHint
FROM (SELECT UserNumber, UserType, UserName, UserStatusKey, Password,
PasswordHint FROM UserTable LEFT JOIN UserTypeTable USING(UserTypeKey)) LEFT
JOIN UserStatusTable USING(UserStatusKey)

Version #2:
CREATE VIEW UserView AS SELECT UserNumber, UserType, UserName, UserStatus,
Password, PasswordHint
FROM UserTable
LEFT JOIN UserTypeTable ON UserTable.UserTypeKey=UserTypeTable.UserTypeKey
LEFT JOIN UserStatusTable ON
UserTable.UserStatusKey=UserStatusTable.UserStatusKey

Questions:
- How would you do what I am trying to do?
- Are these SELECT queries equivalent? Probably not as version 1 has a
sub-select query so this is a "2 pass" query, but is version 1 done in "1
pass" or "2 passes"?
- Is version 2 more efficient?
- For some reason, in version 2 I could not use the "USING" shortcut when
the joined fields had the same name so had to explicitly use the "ON" as
shown above, anybody know why?

Any feedback/help is much appreciated - thanks,
Scott McDonald









-
To unsubscribe, send email to [EMAIL PROTECTED]
-