[sqlite] [3.6.2] "make test" doesn't work on OS X 10.5.5

[Alexander Batyrshin]

Looks like "make test" can't links with TCL library:

Undefined symbols:
  "_Tcl_GetIndexFromObjStruct", referenced from:
  _processDevSymArgs in ccpuPiSI.o
  _test_config in ccy8gMQR.o
  "_Tcl_GetInt", referenced from:
  _test_get_table_printf in ccux7BhL.o
  _sqlite3_mprintf_int in ccux7BhL.o
  _sqlite3_mprintf_str in ccux7BhL.o
[...]
  "_Tcl_ResetResult", referenced from:
  _sqlite3TestErrCode in ccux7BhL.o
  _overloadedGlobFunction in cc8Hgdly.o
  _overloadedGlobFunction in cc8Hgdly.o
  _c_collation_test in cc7AxOBb.o
  _c_realloc_test in cc7AxOBb.o
  _c_misuse_test in cc7AxOBb.o
  _DbTraceHandler in ccUU2Qo6.o
  _DbProfileHandler in ccUU2Qo6.o
  _DbObjCmd in ccUU2Qo6.o
ld: symbol(s) not found
collect2: ld returned 1 exit status
make: *** [testfixture] Error 1


--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN works very strange [3.6.2]

[Alexander Batyrshin]

I just want to add that this SQL query works great at 3.5.4.
And this is explain:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT
town.id, town_log.new_player_id, player.name
FROM
town_log
LEFT JOIN town
LEFT JOIN player
ON
town.id = town_log.town_id AND town_log.new_player_id = player.id
WHERE
town_log.id = 5195;  00
1 Integer5195  1 000
2 Goto   0 46000
3 SetNumColumns  0 5 000
4 OpenRead   0 14000
5 SetNumColumns  0 1 000
6 OpenRead   1 6 000
7 SetNumColumns  0 2 000
8 OpenRead   2 2 000
9 SetNumColumns  0 2 000
10OpenRead   3 3 0 keyinfo(1,BINARY)  00
11MustBeInt  1 41000
12NotExists  0 41100
13Integer0 3 000
14Rewind 1 38000
15Integer1 3 000
16Integer0 4 000
17Column 0 4 600
18IsNull 6 33000
19Affinity   6 1 0 db 00
20MoveGe 3 336 1  00
21IdxGE  3 336 1  01
22IdxRowid   3 10000
23MoveGe 2 0 10   00
24Column 1 0 10   00
25Column 0 1 11   00
26Ne 113210collseq(BINARY)  6b
27Integer1 4 000
28Column 1 0 12   00
29Column 0 4 13   00
30Column 2 1 14   00
31ResultRow  123 000
32Next   3 21000
33IfPos  4 37000
34NullRow2 0 000
35NullRow3 0 000
36Goto   0 27000
37Next   1 15000
38IfPos  3 41000
39NullRow1 0 000
40Goto   0 15000
41Close  0 0 000
42Close  1 0 000
43Close  2 0 000
44Close  3 0 000
45Halt   0 0 000
46Transaction0 0 000
47VerifyCookie   0 92000
48TableLock  0 140 town_log   00
49TableLock  0 6 0 town   00
50TableLock  0 2 0 player 00
51Goto   0 3 000


PS:
And the last one. I want to say that generally SQLite-3.6.3 is slowly
than 3.5.4 on my OS X 10.5.5.
My result is that 3.6.3 is slowly near 5-7%.

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



On Sat, Sep 20, 2008 at 8:45 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello everyone,
>
> I gets strange result from this query on SQLite-3.6.2
>
> SELECT
>town.id, town_log.new_player_id, player.name
> FROM
>town_log
>LEFT JOIN town
>LEFT JOIN player
> ON
>town.id = town_log.town_id AND town_log.new_player_id = player.id
> WHERE
>town_log.id = 5195
>
> As you can see this query should return only 1 row, because
> town_log.id is unique key.
> But I gets rows with different town.id (1 col), with constant
> new_player.id (2 col) and JOIN do not work for player.name, couse 3
> col is empty.
>
> result:
> []
> 45512   9266
> 44544   9266
> 45229   9266
> 46376   9266
> 45927   9266
> 46645   9266
> []
>
>
> Any ideas what's going on?
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JOIN works very strange [3.6.2]

[Alexander Batyrshin]

 Hello everyone,

I gets strange result from this query on SQLite-3.6.2

SELECT
town.id, town_log.new_player_id, player.name
FROM
town_log
LEFT JOIN town
LEFT JOIN player
ON
town.id = town_log.town_id AND town_log.new_player_id = player.id
WHERE
town_log.id = 5195

As you can see this query should return only 1 row, because
town_log.id is unique key.
But I gets rows with different town.id (1 col), with constant
new_player.id (2 col) and JOIN do not work for player.name, couse 3
col is empty.

result:
[]
45512   9266
44544   9266
45229   9266
46376   9266
45927   9266
46645   9266
[]


Any ideas what's going on?

--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[D. Richard Hipp]

On Sep 19, 2008, at 5:47 PM, Russ Leighton wrote:

>
> Observation 1:  Group by (in my case) is faster w/out using the index
> than with using the index by 10X
>
> In my app I have a table-
>
>  create table foo(k1integer ,k2 integer,k3 integer,...);
>  create index foo_idx on foo(k1,k2,k3);
>
> when I do-
>
>  select k1,k2,sum() as s,count(1) as c from foo group by k1,k2;
>
> The query is 10X slower with the above index created as when I drop
> the index and don't use it. Bug or
> just happens to be the case for some data distribution this is just
> the way it is? Yes, I know I  have
> a 3 column index and only using 2 for this query.

I do not see how it is possible for what you say to be true - unless  
you have omitted important details of your query, such as a WHERE  
clause.

What is the argument to sum(), btw?  You should get an error, I think.

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Russ Leighton]

Observation 1:  Group by (in my case) is faster w/out using the index  
than with using the index by 10X

In my app I have a table-

  create table foo(k1integer ,k2 integer,k3 integer,...);
  create index foo_idx on foo(k1,k2,k3);

when I do-

  select k1,k2,sum() as s,count(1) as c from foo group by k1,k2;

The query is 10X slower with the above index created as when I drop  
the index and don't use it. Bug or
just happens to be the case for some data distribution this is just  
the way it is? Yes, I know I  have
a 3 column index and only using 2 for this query.


Observation 2: I can force the index to not be used with '+' but the  
result has nulls for key values!

If I do -

 select k1,k2,sum() as s,count(1) as c from foo group by +k1,+k2;

then the index is not used, it is fast BUT the output has null values  
for k1,k2! Seems like a bug

Thoughts?

-Russ


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

[sqlite] mac file locking

[Dave Dyer]

I'm experimenting with using networked files as sqlite databases
(yes, I'm aware this is a questionable practice) 

If the client is running on a mac, and the database file is not a local 
disk, "database locked" error is returned immediately.  I assume this
means that the file lock mechanism failed, since there are no existing
users sharing the database.

Does anyone have information about this? 

On a related topic, XCODE seems to not be up to debugging the single 
file release - it gets very confused about where breakpoints are
located. 

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

Re: [sqlite] Core dumps on AIX with optimization

[Ken]

I use sqlite on AIX, but compile using gcc.

Can you try the gcc compiler instead?


--- On Fri, 9/19/08, Ribeiro, Glauber <[EMAIL PROTECTED]> wrote:
From: Ribeiro, Glauber <[EMAIL PROTECTED]>
Subject: [sqlite] Core dumps on AIX with optimization
To: "General Discussion of SQLite Database" 
Date: Friday, September 19, 2008, 11:55 AM

Just wondering, are there other AIX Sqlite users out there, and what
have you done in order to get a successful compile?

I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
command line utility sqlite2 dumps core when running SQL that contains
an aggregation function, if it was compiled with any level of
optimization. It seems to work fine if C optimization is turned off.

I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
(ticket 3344), and at least one more person reported seeing the same
problem.

I'm not worried about the lack of optimization, just thinking that this
may be caused by an underlying problem in the C code.

Thanks,

glauber
___
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] [?? Probable Spam] Re: Vista frustrations

[Roger Binns]

Mohit Sindhwani wrote:
> Bogusław Brandys wrote:
>> Silly thought,but could it be related to database file extension ? I 
>> mean , is it possible that OS is caching some files depending on 
>> extension  like it was with Windows XP system restore "feature".
>> I know it is almost impossible in this case but who knows...\
>>   
> That's interesting! I think Windows uses .db for the thumbnail cache 
> also, doesn't it?

The list of monitored extensions is at
http://msdn.microsoft.com/en-us/library/aa378870.aspx

This page contains Windows info:

  http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows

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

Re: [sqlite] Core dumps on AIX with optimization

[John Stanton]

We use Sqlite on AIX and discovered that when compiling with gcc we have 
to remove the debug -g from the makefiles  No optimization issues.  We 
always link statically.

Ribeiro, Glauber wrote:
> Just wondering, are there other AIX Sqlite users out there, and what
> have you done in order to get a successful compile?
> 
> I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
> command line utility sqlite2 dumps core when running SQL that contains
> an aggregation function, if it was compiled with any level of
> optimization. It seems to work fine if C optimization is turned off.
> 
> I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
> (ticket 3344), and at least one more person reported seeing the same
> problem.
> 
> I'm not worried about the lack of optimization, just thinking that this
> may be caused by an underlying problem in the C code.
> 
> Thanks,
> 
> glauber
> ___
> 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] How to convert any dataType to sqlite3_value ?

[Igor Tandetnik]

suku249 <[EMAIL PROTECTED]>
wrote:
> I need to convert dataTypes like int or string into sqlite3_value.

Why? What are you trying to achieve?

Igor Tandetnik 



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

Re: [sqlite] Between And statement too much slow

[Igor Tandetnik]

Giuseppe Costanzi <[EMAIL PROTECTED]> wrote:
> the query look on 16750 record on tblTansactions and return 85 rows
>
>
> sSQL ="""SELECT
> tblProducts.ProductName,tblProducts.WarehouseCode,tblProducts.UnitsInStock,SUM(tblTransactions.Quantity)
> FROM tblProducts
> INNER JOIN tblTransactions ON
> tblProducts.ProductID=tblTransactions.ProductID
> WHERE tblTransactions.TransactionType = 0
> AND tblProducts.WarehouseID = ?
> AND tblTransactions.TransactionDate BETWEEN ? AND
> ? GROUP BY tblProducts.ProductID
> ORDER BY tblProducts.ProductName"""
>

Try creating an index on tblTransactions(ProductID, TransactionDate)

Igor Tandetnik



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

Re: [sqlite] Between And statement too much slow

[Stephen Oberholtzer]

On Fri, Sep 19, 2008 at 12:36 PM, Giuseppe Costanzi <[EMAIL PROTECTED]>wrote:

>
> How could improve my code?


Go into the sqlite3 command line and issue

EXPLAIN QUERY PLAN 

You'll have to fill in some values for the ?s, but that'll give you some
hints.


 Also: How long is "too slow"? Several seconds?


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Core dumps on AIX with optimization

[Ribeiro, Glauber]

Just wondering, are there other AIX Sqlite users out there, and what
have you done in order to get a successful compile?

I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
command line utility sqlite2 dumps core when running SQL that contains
an aggregation function, if it was compiled with any level of
optimization. It seems to work fine if C optimization is turned off.

I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
(ticket 3344), and at least one more person reported seeing the same
problem.

I'm not worried about the lack of optimization, just thinking that this
may be caused by an underlying problem in the C code.

Thanks,

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

[sqlite] Between And statement too much slow

[Giuseppe Costanzi]

Hi,
I'm using this query on my aplication to retrive a recordset that shows
transactions between two date.
The problem is that it's too much slow.
I'm using python and the field TransactionDate is Datetime (e.g. 
2008-01-01) and i passed some such parameters

('1', datetime.date(2008, 7, 1), datetime.date(2008, 7, 31))

the query look on 16750 record on tblTansactions and return 85 rows


sSQL ="""SELECT 
tblProducts.ProductName,tblProducts.WarehouseCode,tblProducts.UnitsInStock,SUM(tblTransactions.Quantity)
 FROM tblProducts
 INNER JOIN tblTransactions ON 
tblProducts.ProductID=tblTransactions.ProductID
 WHERE tblTransactions.TransactionType = 0
 AND tblProducts.WarehouseID = ?
 AND tblTransactions.TransactionDate BETWEEN ? AND ?
 GROUP BY tblProducts.ProductID
 ORDER BY tblProducts.ProductName"""

How could improve my code?

regards

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

Re: [sqlite] [?? Probable Spam] Re: Vista frustrations

[Mohit Sindhwani]

Bogusław Brandys wrote:
> Virgilio Alexandre Fornazin pisze:
>   
>> Sure. I just tell to do this test to check if the bug is related to this
>> component, since it debuted on Vista.
>>  
>> 
>
> Silly thought,but could it be related to database file extension ? I 
> mean , is it possible that OS is caching some files depending on 
> extension  like it was with Windows XP system restore "feature".
> I know it is almost impossible in this case but who knows...\
>   
That's interesting! I think Windows uses .db for the thumbnail cache 
also, doesn't it?

Cheers,
Mohit.
9/20/2008 | 12:04 AM.

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

Re: [sqlite] Database locking issue

[Lothar Behrens]

Am 19.09.2008 um 17:03 schrieb Ken:

> Try it with the latest full build say version 3.6.2 and see what  
> happens instead of a "patched"
>

I'll give that a try. Could the current code be compiled with Open  
Watcom as A DLL ?
(I haven't seen these __declspec(dllexport) and the opposite stuff in  
the actual code)

Lothar

>

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








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

[sqlite] How to convert any dataType to sqlite3_value ?

[suku249]

hi,

I need to convert dataTypes like int or string into sqlite3_value. Any Idea
how to do this? Thanks in advance.


-- 
View this message in context: 
http://www.nabble.com/How-to-convert-any-dataType-to-sqlite3_value---tp19574177p19574177.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

Re: [sqlite] Database locking issue

[Ken]

Try it with the latest full build say version 3.6.2 and see what happens 
instead of a "patched" 



--- On Fri, 9/19/08, Lothar Behrens <[EMAIL PROTECTED]> wrote:
From: Lothar Behrens <[EMAIL PROTECTED]>
Subject: [sqlite] Database locking issue
To: "General Discussion of SQLite Database" 
Date: Friday, September 19, 2008, 9:02 AM

Hi,

I am using  Version 3.5.2 of Sqlite with the changes of the following  
CVS checkin numbers: 4543 and 5243 to get the sqlite3_sql and  
sqlite3_next_stmt functions
into my API.

I have patched my files due to the need to figuring out what  
statements were unfinalized. But now I get 'database is locked' errors 

right after reopening the
database (the closing of the database works as I try to keep my  
statements open as short as possible thus closing now works).

Due to this new error ('database is locked') I added reporting  
statemens in use. The result of this is a report of the insert  
statement itself that causes the error for
this report.

So I am a little confused.

If a database is locked, is there always a statement that causes this  
lock, or is the lock keeping even the statement gets finalized ?

Or did I have to apply other patches related to them above ?

Thanks

Lothar

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








___
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] Database locking issue

[Lothar Behrens]

Hi,

I am using  Version 3.5.2 of Sqlite with the changes of the following  
CVS checkin numbers: 4543 and 5243 to get the sqlite3_sql and  
sqlite3_next_stmt functions
into my API.

I have patched my files due to the need to figuring out what  
statements were unfinalized. But now I get 'database is locked' errors  
right after reopening the
database (the closing of the database works as I try to keep my  
statements open as short as possible thus closing now works).

Due to this new error ('database is locked') I added reporting  
statemens in use. The result of this is a report of the insert  
statement itself that causes the error for
this report.

So I am a little confused.

If a database is locked, is there always a statement that causes this  
lock, or is the lock keeping even the statement gets finalized ?

Or did I have to apply other patches related to them above ?

Thanks

Lothar

-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen








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

Re: [sqlite] Can SQLite take advantage of multiple CPU'sand/ormultiple cores?

[Igor Tandetnik]

"Graeme" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Friday 19 September 2008 09:50:53 Igor Tandetnik wrote:
>> <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>>
>>> Can SQLite take advantage of multiple CPU's and/or multiple
>>> cores?
>>
>> Not automatically: SQLite API functions run on whatever thread you
>> call them on. Of course, you can create multiple threads and open
>> multiple SQLite connections - preferably to different DB files, to
>> maximize concurrency.
>
> Looking at the docs and the wiki it looks to me that there are no
> problems with multiple processes using the same DB, but one needs to
> be a bit more careful with threads (and you have to be sure it is
> compiled with threads enabled).

It doesn't matter whether several DB connections belong to multiple 
processes or to multiple threads within the same process. The locking 
scheme is exactly the same (except when shared cache is enabled, which 
supports some interesting features, e.g. per-table locking).

Modern versions of SQLite are also safe for sharing the same connection 
across threads. However, it's implemented by simply having every API 
call lock the same mutex, so potential for concurrency is somewhat 
limited.

> Also, are there any disadvantages to compiling threadsafe? I imagine
> there is some impact on performance. Anything else?

Not that I know of.

Igor Tandetnik 



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