Re: [sqlite] Proposed: drop support for LinuxThreads

[Matthew L. Creech]

On Wed, May 12, 2010 at 11:43 AM, D. Richard Hipp  wrote:
> If we drop support for (the non-standard, non-compliant) LinuxThreads
> threading library and instead support only standard Posix threads
> implemented using NPTL, beginning with SQLite release 3.7.0, what
> disruptions might this cause?
>
> Is anybody still using LinuxThreads?
>

FWIW, our only platform still using LinuxThreads is [/was]
coldfire+MMU (v4e), and a couple of months ago CodeSourcery pushed
complete NPTL support for it upstream.  It's not a very popular or
well-supported system, so I'd take that as a sign that LinuxThreads is
on its last legs.

Obviously there will always be some exceptions, but I'd imagine that
few of them are concerned with keeping those systems up to date with
the very latest SQLite.

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

Re: [sqlite] Proposed: drop support for LinuxThreads

[Bill King]

On 05/13/2010 01:43 AM, ext D. Richard Hipp wrote:
> If we drop support for (the non-standard, non-compliant) LinuxThreads  
> threading library and instead support only standard Posix threads  
> implemented using NPTL, beginning with SQLite release 3.7.0, what  
> disruptions might this cause?
>
> Is anybody still using LinuxThreads?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   
Some old embedded platforms have libc compiled with nptl to "save
space", seriously tho, it's pretty much down to a matter of recompiling
libc with the appropriate thread library support. Consider this a vote
for removal/clarity/standardisation, as it's not a huge deal to turn
pthreads on.

-- 
Bill King, Software Engineer
Qt Development Frameworks, Nokia Pty Ltd
Brisbane Office

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

Re: [sqlite] select intersecting intervals

[Jean-Christophe Deschamps]

>
>I would first create an INTEGER primary key and then place an index on 
>name,
>another on i_from, and another on i_to, and then see if the approach below
>has any benefit.
>
>When I tried this with a geo-queryit was actually slower than the standard
>select, and I'm curious if that's always going to be the case. It will 
>come
>down to how efficient the INTERSECT of the vectors of integers is. Each
>vector will have been the result of an index-scan.  If INTERSECT were
>optimized (perhaps with a minimal perfect hash function
>http://cmph.sourceforge.net/index.html) this approach might be useful.


All three following queries use only simple indexes (PK, name, lo, hi).

Query#1:
select * from tst where lo < 345678
 intersect
select * from tst where hi > 123456
 intersect
select * from tst where name = 'aaelj';

Query#2
select * from tst
 join (
 select rowid from tst where lo < 345678
 intersect
 select rowid from tst where hi > 123456
 ) as interval
 on tst.rowid = interval.rowid and name = 'aaelj';

Query#3
select * from tst
 join (
 select rowid from tst where lo < 345678
 intersect
 select rowid from tst where hi > 123456
 intersect
 select rowid from tst where name = 'aaelj'
 ) as interval
 on tst.rowid = interval.rowid;

On a 200K-row test table with random data, queries #2 and #3 were 
essentially identical while #1 was twice slower (moving too much data 
around, uselessly).


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

Re: [sqlite] Proposed: drop support for LinuxThreads

[Florian Weimer]

* D. Richard Hipp:

> If we drop support for (the non-standard, non-compliant) LinuxThreads  
> threading library and instead support only standard Posix threads  
> implemented using NPTL, beginning with SQLite release 3.7.0, what  
> disruptions might this cause?

There are several variants of NPTL with varying feature sets on
different GNU/Linux architectures.  But I think you are only after the
behavior of fcntl locks, and those are the same across architectures.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/12/2010 03:23 PM, Pavel Ivanov wrote:
> pthreadMutexNotheld() checks that mutex is not held by current thread
> - if mutex is held by another thread it will return true.

Ooops, I got this completely wrong!

> Although I see that it can return false in this case if another thread
> is inside pthreadMutexEnter and optimizer has switched order of
> assignment of the values to owner and nRef inside pthreadMutexEnter
> (it is allowed to do so because variables are not volatile). Is it
> possible to see exact disassembly of the executable in question?

This is a report from another user, who does frequent this mailing list as
well so hopefully he can provide that.  I believe the code was compiled on a
standard 32 bit i386 Ubuntu Lucid system.  The compilation line would be
like below (from a 64 bit Lucid system).  From a 64 bit disassembly I see
the setting of owner and nRef happening concurrently (nRef is fetched, owner
updated, nRef updated and saved).

  gcc -pthread -fno-strict-aliasing -g -fwrapv -O2 -Wall -Wstrict-prototypes
- -fPIC -g -DSQLITE_DEBUG=1

This user reported this being hard to repeat and that there is lots of
threading involved.

The owner and nRef fields are only used when SQLITE_DEBUG is true.

  http://www.sqlite.org/src/info/51914f6acd

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvrPN4ACgkQmOOfHg372QScxQCfcbit3kH4c+TeIxTwrZnBp27n
aiQAnRDcwkgce/piMMIDAT28Hrhl/3kc
=CBGk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

[Pavel Ivanov]

> The mutex is not recursive.  However if another thread had acquired the
> mutex during the call to sqlite3Malloc above then the mutex would indeed be
> held by that other thread causing the assertion to fail.

pthreadMutexNotheld() checks that mutex is not held by current thread
- if mutex is held by another thread it will return true.

Although I see that it can return false in this case if another thread
is inside pthreadMutexEnter and optimizer has switched order of
assignment of the values to owner and nRef inside pthreadMutexEnter
(it is allowed to do so because variables are not volatile). Is it
possible to see exact disassembly of the executable in question?


Pavel

On Wed, May 12, 2010 at 6:08 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> - From a user backtrace when SQLite is compiled with assertions on, it looks
> like an assertion is incorrect.  The raw backtrace is later.
>
> pcache1Alloc() contains this code:
>
>    pcache1LeaveMutex();
>    p = sqlite3Malloc(nByte);
>    pcache1EnterMutex();
>
> pcache1EnterMutex() is a macro for sqlite3_mutex_enter(pcache1.mutex) which
> in turn calls pthreadMutexEnter which starts with this assertion:
>
> assert( p->id==SQLITE_MUTEX_RECURSIVE || pthreadMutexNotheld(p) );
>
> The mutex is not recursive.  However if another thread had acquired the
> mutex during the call to sqlite3Malloc above then the mutex would indeed be
> held by that other thread causing the assertion to fail.
>
> Raw backtrace:
>
>> #0  0x00f2c422 in __kernel_vsyscall ()
>> #1  0x001a5651 in raise () from /lib/tls/i686/cmov/libc.so.6
>> #2  0x001a8a82 in abort () from /lib/tls/i686/cmov/libc.so.6
>> #3  0x0019e718 in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
>> #4  0x002f312b in pthreadMutexEnter (p=0x3af1a0)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:15631
>> #5  0x002dc302 in sqlite3_mutex_enter (p=0x0)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:14936
>> #6  0x002e201e in pcache1Alloc (nByte=1160)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31158
>> #7  0x003060ed in pcache1AllocPage (p=0x9095908, iKey=1, createFlag=2)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31191
>> #8  pcache1Fetch (p=0x9095908, iKey=1, createFlag=2)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31560
>> #9  0x002eb682 in sqlite3PcacheFetch (pCache=0x919c998, pgno=> optimized out>, createFlag=1,
>>     ppPage=0xb7646f8c) at 
>> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:30639
>> #10 0x0030ff4b in sqlite3PagerAcquire (pPager=0x919c8f0, pgno=1, 
>> ppPage=0xb7646f8c, noContent=0)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:36014
>> #11 0x00310382 in btreeGetPage (pBt=0x919c478, pgno=, 
>> ppPage=0xb7647018,
>>     noContent=0) at 
>> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:40095
>> #12 0x00313fc0 in lockBtree (p=0x919c438, wrflag=)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:40805
>> #13 sqlite3BtreeBeginTrans (p=0x919c438, wrflag=)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:41055
>> #14 0x00354bbf in sqlite3VdbeExec (p=)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:56074
>> #15 0x0035fbc8 in sqlite3Step (pStmt=0x963fde8)
>>     at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:51732
>> #16 sqlite3_step (pStmt=0x963fde8) at 
>> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:51792
>> #17 0x0037028e in APSWCursor_step (self=0x913d950) at src/cursor.c:730
>> #18 0x00371554 in APSWCursor_execute (self=0x913d950, args=
>>     ('SELECT inode FROM contents WHERE name=? AND parent_inode=?', [> at remote 0x968a0c0>, 1314661905L])) at src/cursor.c:999
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkvrJsoACgkQmOOfHg372QSk6QCfcNWmxnYtdYZY0zovx0xvzvA5
> yM4AnitMX/elyC+i3zBQ9xhD6hLNYs7w
> =pbPi
> -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] Incorrect assertion in mutexes plus pcache1Alloc

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

- From a user backtrace when SQLite is compiled with assertions on, it looks
like an assertion is incorrect.  The raw backtrace is later.

pcache1Alloc() contains this code:

pcache1LeaveMutex();
p = sqlite3Malloc(nByte);
pcache1EnterMutex();

pcache1EnterMutex() is a macro for sqlite3_mutex_enter(pcache1.mutex) which
in turn calls pthreadMutexEnter which starts with this assertion:

assert( p->id==SQLITE_MUTEX_RECURSIVE || pthreadMutexNotheld(p) );

The mutex is not recursive.  However if another thread had acquired the
mutex during the call to sqlite3Malloc above then the mutex would indeed be
held by that other thread causing the assertion to fail.

Raw backtrace:

> #0  0x00f2c422 in __kernel_vsyscall ()
> #1  0x001a5651 in raise () from /lib/tls/i686/cmov/libc.so.6
> #2  0x001a8a82 in abort () from /lib/tls/i686/cmov/libc.so.6
> #3  0x0019e718 in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
> #4  0x002f312b in pthreadMutexEnter (p=0x3af1a0)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:15631
> #5  0x002dc302 in sqlite3_mutex_enter (p=0x0)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:14936
> #6  0x002e201e in pcache1Alloc (nByte=1160)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31158
> #7  0x003060ed in pcache1AllocPage (p=0x9095908, iKey=1, createFlag=2)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31191
> #8  pcache1Fetch (p=0x9095908, iKey=1, createFlag=2)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:31560
> #9  0x002eb682 in sqlite3PcacheFetch (pCache=0x919c998, pgno= out>, createFlag=1, 
> ppPage=0xb7646f8c) at 
> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:30639
> #10 0x0030ff4b in sqlite3PagerAcquire (pPager=0x919c8f0, pgno=1, 
> ppPage=0xb7646f8c, noContent=0)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:36014
> #11 0x00310382 in btreeGetPage (pBt=0x919c478, pgno=, 
> ppPage=0xb7647018, 
> noContent=0) at 
> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:40095
> #12 0x00313fc0 in lockBtree (p=0x919c438, wrflag=)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:40805
> #13 sqlite3BtreeBeginTrans (p=0x919c438, wrflag=)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:41055
> #14 0x00354bbf in sqlite3VdbeExec (p=)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:56074
> #15 0x0035fbc8 in sqlite3Step (pStmt=0x963fde8)
> at /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:51732
> #16 sqlite3_step (pStmt=0x963fde8) at 
> /home/nikratio/tmp/python-apsw-3.6.23.1-r1/sqlite3/sqlite3.c:51792
> #17 0x0037028e in APSWCursor_step (self=0x913d950) at src/cursor.c:730
> #18 0x00371554 in APSWCursor_execute (self=0x913d950, args=
> ('SELECT inode FROM contents WHERE name=? AND parent_inode=?', [ at remote 0x968a0c0>, 1314661905L])) at src/cursor.c:999

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvrJsoACgkQmOOfHg372QSk6QCfcNWmxnYtdYZY0zovx0xvzvA5
yM4AnitMX/elyC+i3zBQ9xhD6hLNYs7w
=pbPi
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select intersecting intervals

[Tim Romano]

I would first create an INTEGER primary key and then place an index on name,
another on i_from, and another on i_to, and then see if the approach below
has any benefit.

When I tried this with a geo-queryit was actually slower than the standard
select, and I'm curious if that's always going to be the case. It will come
down to how efficient the INTERSECT of the vectors of integers is. Each
vector will have been the result of an index-scan.  If INTERSECT were
optimized (perhaps with a minimal perfect hash function
http://cmph.sourceforge.net/index.html) this approach might be useful.


select * from T
JOIN
(
select pk_col from T where i_from > ?
intersect
select pk_col from T where i_to < ?
) as DESIREDINTERVAL
ON T.pk_col = DESIREDINTERVAL.pk_col
and T.name = ?


Regards
Tim Romano
Swarthmore PA

On Wed, May 12, 2010 at 12:00 PM, Jan Asselman  wrote:

> Hi,
>
> Given the following table with large row count 'row_count':
>
> CREATE TABLE table
> (
>i_name  TEXT,
>i_from  INTEGER,
>i_toINTEGER,
>i_data  BLOB
> )
>
> I am wondering what would be the fastest way to get all rows with a
> given name 'myname' that intersect with a given interval [a, b]?
>
>
> CREATE INDEX idx_from ON table (i_name, i_from);
> CREATE INDEX idx_to ON table (i_name, i_to);
>
> SELECT data FROM table WHERE name = 'myname' AND i_from < b AND i_to > a
>
>-> index idx_from will be used
>-> in worst case (a is larger than all i_to) all 'myname' rows
>   will be traversed before concluding result set is empty
>
> SELECT data FROM table WHERE name = 'myname' AND i_to > a AND i_from < b
>
>-> index idx_to will be used
>-> in worst case (b is smaller than all i_from) all 'myname'
> rows
>   will be traversed before concluding result set is empty
>
>
>
> I know this is exactly what a one dimensional R-tree index is used for,
> but my project requires 64 bit integer minimum- and maximum-value
> pairs...
>
> All suggestions or corrections are appreciated.
>
>
> Thanks in advance,
>
> Jan Asselman
> ___
> 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] create virtual table if not exists table_id???

[Scott Hess]

On Wed, May 12, 2010 at 10:40 AM, Roger Binns  wrote:
> On 05/11/2010 11:50 AM, Matt Young wrote:
>> sqlite> create virtual table if not exists words using fts3  (f1 );
>> Error: near "not": syntax error
>
>  http://www.sqlite.org/cvstrac/tktview?tn=2604
>
> To fix it requires code changes to SQLite and the SQLite team haven't deemed
> this necessary (yet).

As the person who floated that trial balloon ... I don't recall any
really negative feedback.  Mostly I just didn't have bandwidth to
champion the change.  Someone should pick it up and run with it - make
sure the patch still works, review the codebase to make sure that it's
handling all of the cases for "NOT EXISTS" and "TEMP" that regular
tables consider, and then address any concerns the core team brings
up.  And/or find out that the core team is actually negative on the
addition :-).

-scott

PS:  I didn't consider the workaround as negative feedback.  Just more
that nobody felt strongly enough to run with it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create virtual table if not exists table_id???

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/11/2010 11:50 AM, Matt Young wrote:
> sqlite> create virtual table if not exists words using fts3  (f1 );
> Error: near "not": syntax error

  http://www.sqlite.org/cvstrac/tktview?tn=2604

To fix it requires code changes to SQLite and the SQLite team haven't deemed
this necessary (yet).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkvq6AIACgkQmOOfHg372QTkqgCgx4KP4Lb6kga0h3CAHSQHETiB
cM0An1AhYEVXL7SnYLGCV8rsmRCfPXjp
=4LLB
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create virtual table if not exists table_id???

[Matt Young]

Thanks.  A novice sometimes posts prior to a complete search of documentation.

On 5/11/10, P Kishor  wrote:
> On Tue, May 11, 2010 at 1:50 PM, Matt Young  wrote:
>> sqlite> create virtual table if not exists words using fts3  (f1 );
>> Error: near "not": syntax error
>> sqlite> create  table if not exists U (w1 );
>> sqlite>
>>
>> Different syntax?
>
> Yes.
>
>> virtual tables don't persist?
>
> How did you reach that conclusion? Did you create a virtual table
> successfully, quit sqlite, then reopen the db and found the virtual
> table missing?
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> 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] select intersecting intervals

[Jan Asselman]

Hi,

Given the following table with large row count 'row_count':

CREATE TABLE table
(
i_name  TEXT,   
i_from  INTEGER,
i_toINTEGER,
i_data  BLOB
)

I am wondering what would be the fastest way to get all rows with a
given name 'myname' that intersect with a given interval [a, b]?


CREATE INDEX idx_from ON table (i_name, i_from);
CREATE INDEX idx_to ON table (i_name, i_to);

SELECT data FROM table WHERE name = 'myname' AND i_from < b AND i_to > a

-> index idx_from will be used
-> in worst case (a is larger than all i_to) all 'myname' rows
   will be traversed before concluding result set is empty

SELECT data FROM table WHERE name = 'myname' AND i_to > a AND i_from < b

-> index idx_to will be used
-> in worst case (b is smaller than all i_from) all 'myname'
rows
   will be traversed before concluding result set is empty



I know this is exactly what a one dimensional R-tree index is used for,
but my project requires 64 bit integer minimum- and maximum-value
pairs...

All suggestions or corrections are appreciated.


Thanks in advance,

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

[sqlite] Proposed: drop support for LinuxThreads

[D. Richard Hipp]

If we drop support for (the non-standard, non-compliant) LinuxThreads  
threading library and instead support only standard Posix threads  
implemented using NPTL, beginning with SQLite release 3.7.0, what  
disruptions might this cause?

Is anybody still using LinuxThreads?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Bug with coalesce since 3.6.21

[Richard Hipp]

On Wed, May 12, 2010 at 4:18 AM, Gerhard Häring  wrote:

> A pysqlite user reported a problem with COALESCE. I could find out
> that his test case worked ok with SQLite versions 3.6.12 through
> 3.6.20, but failed with 3.6.21 to 3.6.23.
>

The problem was actually introduced in 3.6.12.  Here is a simpler test case:

  SELECT CASE WHEN 1 THEN 99 ELSE ? END + ?

Your work-around is to use "?1" and "?2" instead of "?".



>
> I could narrow down his test case to this very simple one below. The
> combination of addition, named parameters and COALESCE causes
> problems. The first SQL produces the expected result, but if you swap
> the operands, NULL is produced.
> SQLite developers,
>
> ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$ cat
> t.py
> from pysqlite2 import dbapi2 as sqlite3
> print "SQLite", sqlite3.sqlite_version
>
> con = sqlite3.connect(":memory:")
> print con.execute('SELECT ? + coalesce(max((select 1)), ?)', (1,
> 0)).fetchone()
> print con.execute('SELECT coalesce(max((select 1)), ?) + ?', (0,
> 1)).fetchone()
>
> ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$
> python t.py
> SQLite 3.6.23.1
> (2,)
> (None,)
> ___
> 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] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[Jay A. Kreibich]

On Wed, May 12, 2010 at 06:23:46AM -0500, Black, Michael (IS) scratched on the 
wall:
 
> Somebody please correct me if I'm wrong on this one...
> I think sqlite can work with multiple processes just doing read-onliy
> operations (like SELECT).

  Yes.

> It's just the write operations
> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.

  "Cause", yes, but both readers and writers can get an SQLITE_BUSY
  return code.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert large data question ??

[Black, Michael (IS)]

You haven't said what kind of machine you're running on.
 
I did this test using 9,000,000 records and got 40,000 inserts per second.
 
sqlite3 test.db :
> On Tue, May 11, 2010 at 12:47 AM, ??  wrote:
>> Dear
>>
>> I have about 9 million data insert string need to insert into an table ,each
>> row data is unique
>>
>> this is a sample:
>> insert into mydata
>> VALUES(38824801,56888,'AABBCC',4.999,157,'2009/9/10
>> 19:55:50');
>>
>> this is my schema:
>> table|mydata|mydata|2|CREATE TABLE mydata
>> (
>>itno VARCHAR(20),
>>lcno VARCHAR(20),
>>srno VARCHAR(10),
>>ran  VARCHAR(20),
>>pp INTEGER,
>>cdate VARCHAR(20),
>>PRIMARY KEY (itno DESC, lcno ASC)
>> )
> ..
>> but i have question about performance,everytime doing the job takes about
>> 63000 seconds
>
> Use transactions. But, do you also realize that most of your columns
> are defined as VARCHAR, but you are inserting stuff that doesn't look
> like TEXT. You will be/should be surprised by the results. From the
> example above,
>
>>itno VARCHAR(20),
>>lcno VARCHAR(20),
>>srno VARCHAR(10),
>>ran  VARCHAR(20),
>>pp INTEGER,
>>cdate VARCHAR(20),
>
> ltno VARCHAR(20): 38824801 <-- if it had leading zeroes, they would vanish
> lcno VARCHAR(10): 56888 <-- will become 56888
> srno VARCHAR(10): 'AABBCC' <-- inserted correctly
> ran  VARCHAR(20): 4.999 <-- are you expecting this to remain a REAL?
> pp INTEGER: 157 <-- inserted correctly
> cdate VARCHAR: '2009/9/10 19:55:50' <-- inserted correctly
>
> enclose your VARCHARs in single quotes.
>
> --
> Puneet Kishor http://www.punkish.org  
> Carbon Model http://carbonmodel.org  
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org 
>  
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu 
>  
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> 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] Attach

[Jay A. Kreibich]

On Tue, May 11, 2010 at 11:28:14PM -0700, Matt Young scratched on the wall:
> C:\R\SQLite>sql ap.data.3.Food.db
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .tables
> Col2U   seriesdata  seriesid
> sqlite> .q
> 
> C:\R\SQLite>sql
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach  'ap.data.3.Food.db' as d;
> sqlite> .tables
> sqliite>
> sqlite> select * from seriesid limit 2;
> APU70|2|364
> APU701311|365|388
> sqlite>
> Case one, I attach the database on invocation, tables can be seen
> Case two, I open with attach command, no tables.
> 
> Is it the quotes.  In the second case I can still read data

  Unless a database name is specifically given (i.e. .backup/.restore),
  in most cases the sqlite3 dot-commands only work on the "main" and
  "temp" database.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach

[Black, Michael (IS)]

H...I duplicated this behavior on 3.6.23.1...I can only assume this is by 
intent as the interpreter isn't designed for mulitiple attached databases.
 
.dump doesn't work on an attached table either
 
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Matt Young
Sent: Wed 5/12/2010 1:28 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Attach



C:\R\SQLite>sql ap.data.3.Food.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Col2U   seriesdata  seriesid
sqlite> .q

C:\R\SQLite>sql
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> attach  'ap.data.3.Food.db' as d;
sqlite> .tables
sqliite>
sqlite> select * from seriesid limit 2;
APU70|2|364
APU701311|365|388
sqlite>
Case one, I attach the database on invocation, tables can be seen
Case two, I open with attach command, no tables.

Is it the quotes.  In the second case I can still read data
___
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] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[Black, Michael (IS)]

SQLITE_BUSY is not an error...just a fact.
 
All your processes cannot work on the database at the same time...at least not 
when one of them is doing an insert.  You could be changing the table while 
you're scanning it.  EXXCLUSIVE doesn't change that idea.
 
Somebody please correct me if I'm wrong on this one...
I think sqlite can work with multiple processes just doing read-onliy 
operations (like SELECT).It's just the write operations (INSERT/UPDATE) 
which will cause SQLITE_BUSY to occur.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
Sent: Tue 5/11/2010 9:20 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and 
SQLITE_MISUSE




Thank you very much!

It may be because my system's resource is limited. It's a embedded system
containing 32M RAM, ARM9 CPU.

My "reiterating 20 times" is already using usleep().

After I add the loop in the prepare statements, the system performance is
still very bad... And there are still many errores of SQLITE_BUSY.

The only improvement is the disappear of the error of SQLITE_MISUSE.

And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are same
with them without using it.







Black, Michael (IS) wrote:
>
> Your "reiterating 20 times" is not using a usleep so you'll blow by this
> most every time it's busy.
> 
> Do this instead in all your proc's
> 
> ret = sqlite3_step (p_stmt);
> if (SQLITE_BUSY == ret)
> {
> int n=0;
> usleep(10); // try one more time before error
> while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
> printf("proc1 ret==BUSY %d\n",++n);
> usleep(10);
> }
> }
>
> And you'll also need to handle "database is locked" coming from your
> prepare statements.  I saw that error too.
> You'll need to loop there too.
>
> The more you drop the usleep time the more times it will show as busy.
> 1/10th or 1/100th of second is about all you want I would think.
> 
> And get rid of the usleep at the bottom of each proc -- it's pretty
> useless at 100 microseconds.  You don't need to sleep unless you're busy.
> 
> I tested your code with this and got no errors at all -- just a bunch of
> BUSY messages.
> 
> 
> Not sure what your purpose is in sqlrun.c with looping and killing.  Looks
> pretty squirrely to me.  You're not waiting for the forks to finish so
> what is your logic here?
> 
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
> 
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Tue 5/11/2010 4:57 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and
> SQLITE_MISUSE
>
> ...
>
> --
> View this message in context:
> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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
>
>

--
View this message in context: 
http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28531394.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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

[sqlite] Bug with coalesce since 3.6.21

[Gerhard Häring]

A pysqlite user reported a problem with COALESCE. I could find out
that his test case worked ok with SQLite versions 3.6.12 through
3.6.20, but failed with 3.6.21 to 3.6.23.

I could narrow down his test case to this very simple one below. The
combination of addition, named parameters and COALESCE causes
problems. The first SQL produces the expected result, but if you swap
the operands, NULL is produced.
SQLite developers,

ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$ cat t.py
from pysqlite2 import dbapi2 as sqlite3
print "SQLite", sqlite3.sqlite_version

con = sqlite3.connect(":memory:")
print con.execute('SELECT ? + coalesce(max((select 1)), ?)', (1, 0)).fetchone()
print con.execute('SELECT coalesce(max((select 1)), ?) + ?', (0, 1)).fetchone()

ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$ python t.py
SQLite 3.6.23.1
(2,)
(None,)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CORRUPT: database disk image is malformed

[Kees Nuyt]

On Tue, 11 May 2010 13:05:10 + (GMT), kundan bharti
 wrote:

>Hello Sir/Maam,
>
> I am getting the error "SQLITE_CORRUPT: database disk image
> is malformed" while opening the attached database in
> SQlite Expert. 

Attachments usually don't make it in mailing lists, perhaps
you can put it on a website give us a link?
Do you get the same error with the sqlite3 command line
tool?

> Can you please tell me the reason as well as

To analyse the reason we would like to know in which
environment and with which sqlite3 version the database was
created.
One of the reasons could be deletion of a journal after a
failed transaction.

> a solution to rectify this issue.

First make a backup copy of the database file.
Try the  .dump  command in the command line tool.
If that doesn't return all your data you can try
 .dump tablename  for every single table separately.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Select via Wi-fi very slow

[Kevin Youren]

Emany,

this has possibly already been done, but have you got an index on the table
for the column called "description"?

I would suggest a timed test on both the Symbol device and the computer
before and after the index is added.

Also, the Symbol may only have 64Mb of RAM, which probably would slow it
down.

There is a method, unfortunately not available is Sqlite, called a Stored
Procedure. A Stored Procedure is especially useful in your case because you
move the database access from the Symbol to a more powerful computer, and
reduce the back and forth WiFi traffic. The SQL would be sent from the
Symbol to the computer hosting the database, the query would executed on the
host computer and the result sent back to the Symbol.

If possible, a workaround or emulation of a Stored Procedure could be done.
You could send the SQL to a program on the host, and that program would
interrogate the database and return the result. It would need a reasonable
level of skill to implement.

regards,

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

[sqlite] Attach

[Matt Young]

C:\R\SQLite>sql ap.data.3.Food.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Col2U   seriesdata  seriesid
sqlite> .q

C:\R\SQLite>sql
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> attach  'ap.data.3.Food.db' as d;
sqlite> .tables
sqliite>
sqlite> select * from seriesid limit 2;
APU70|2|364
APU701311|365|388
sqlite>
Case one, I attach the database on invocation, tables can be seen
Case two, I open with attach command, no tables.

Is it the quotes.  In the second case I can still read data
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users