[sqlite] [3.6.2] "make test" doesn't work on OS X 10.5.5
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]
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]
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
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
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
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
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
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
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 ?
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
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
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
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
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
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
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 ?
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
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
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?
"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