[sqlite] Bug in retrieving last rowid?
I have created Tables A & AS_FTS "create table A (id integer primary key, string Text);" "create virtual table AS_FTS (Name);" and a trigger "insert into A_FTS (rowid,Name) values (New.%@,New.%@);" (and a not shown delete trigger). Now, I enter two strings into tue table: one and two. I delete table entry one and insert afterwrds two times one again. Finally I check the last inserted rowid using "sqlite_last_insert_rowid". Unfortunately, this is wrong. BTW: other non-FTS insert triggers seem to work. I am using 3.6.14. Any ideas? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree performance problems?
Am 18.08.2008 um 21:32 schrieb Dennis Cote: > Thomas Sailer wrote: >> >> Interestingly, the original query is extremely compute-bound, there >> is >> almost no disk activity! >> >> Looking at the output opcodes from the queries, I can't see any >> significant difference. Though I have to admit I'm by far no expert >> in >> vmdb opcodes... >> > > You can use "explain query plan " to get some clues as to > what is happening, but the vdbe code shows it best. > > The original query is doing a full table scan through all 2.6 million > records in the main table, and for each record it uses the rtree to > locate the 20 records inside the rectangle set by your limits, then it > compares the id of each of these records to see if it matches the id > of > the main table record. For each match it dumps all the data in both > records. > > The second query use the rtree index to locate the 20 records in the > limit rectangle, and then uses the btree index to locate those 20 > records quickly, and then dumps the data for that record. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Hello, I had an identical experience a few days ago and posted it under a different topic. Because SQLite is not doing well as mentioned in the example of the documentation I suggest to change the documentation using the proposed solution. I also had to find it out the hard way and I think by modifying the documentation there are at least a few people less who have to go that way. BTW: I suppose the same problem occurs when using fts? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RTree query
I have two tables with about 100 000 rows. One is the main data table and the other an RTree table. I started a query like described at http://www.sqlite.org/rtree.html: SELECT data.location_name FROM data, rtree WHERE data.id=rtree.id AND rtree.x >= 0 AND rtree.x <= 10; This runs for about 4 secs. When using this query: SELECT location_name FROM data WHERE id IN (SELECT id FROM rtree WHERE rtree.x >= 0 AND rtree.x <= 10); the result is available after less than 0.4 s. This time I expected also for the first query but it seems to be that something is going wrong overthere (the index for the ID does not seem to be used or something similar). I had a look at the command using "explain" but I am not an expert for the VDBE instructions. Any ideas? Hartwig FYI: the rtree query by itself returns less than 50 rows. FYI: I am using sqlite3 version 3.6.1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe documentation bug in sqlite3_close leading to crash
Am 26.07.2008 um 17:21 schrieb Stephen Woodbridge:
> Hartwig Wiesmann wrote:
>> The latest sqlite3_close(sqlite3*) documentation states:
>>
>> Applications should finalize all prepared statements and close all
>> BLOB handles associated with the sqlite3 object prior to attempting
>> to
>> close the object. The sqlite3_next_stmt() interface can be used to
>> locate all prepared statements associated with a database connection
>> if desired. Typical code might look like this:
>>
>> sqlite3_stmt *pStmt;
>> while( (pStmt = sqlite3_next_stmt(db, 0))!=0 ){
>> sqlite3_finalize(pStmt);
>> }
>> This is dangerous and does NOT always work!
>> Actually, this causes a terrible bug in the RTree module: the rtree
>> module stores dynamically 9 prepared statement (see rtreeSqlInit). If
>> now all prepared statements are closed before sqlite3_close is called
>> these statements are also finalized.
>> Now, sqlite3_close is called. This call also terminates the rtree
>> module. But this module does not know that the prepared and stored
>> statements (pointers to the previously prepared statements) have
>> already been finalized and do not exist anymore. So, it tries to
>> clean
>> up the not anymore existing part again and crashes!
>>
>> Any workarounds are welcome!!
>
> Seems like the sqlite3_finalize() should check if the pointer is null
> before destroying it, and after destroying it set the freed pointers
> to
> NULL so that they are not destroyed again. Or do the equivalent if
> they
> are not all pointers.
>
> Then again maybe I should not comment without looking at the code :)
> that works pretty darn well most all the time for me.
>
None of the pointers have the value null because the pointers in the
rtree module are not modified by the finalize statement (actually the
finalize statement does not know that there are a couple of pointers
pointing to the non-finalized statement).
Hartwig
> -Steve
> ___
> 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] Severe documentation bug in sqlite3_close leading to crash
The latest sqlite3_close(sqlite3*) documentation states:
Applications should finalize all prepared statements and close all
BLOB handles associated with the sqlite3 object prior to attempting to
close the object. The sqlite3_next_stmt() interface can be used to
locate all prepared statements associated with a database connection
if desired. Typical code might look like this:
sqlite3_stmt *pStmt;
while( (pStmt = sqlite3_next_stmt(db, 0))!=0 ){
sqlite3_finalize(pStmt);
}
This is dangerous and does NOT always work!
Actually, this causes a terrible bug in the RTree module: the rtree
module stores dynamically 9 prepared statement (see rtreeSqlInit). If
now all prepared statements are closed before sqlite3_close is called
these statements are also finalized.
Now, sqlite3_close is called. This call also terminates the rtree
module. But this module does not know that the prepared and stored
statements (pointers to the previously prepared statements) have
already been finalized and do not exist anymore. So, it tries to clean
up the not anymore existing part again and crashes!
Any workarounds are welcome!!
Hartwig
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree module crashes
Hi Dan, sorry, but I do not have any access to the page, I think. Hartwig Am 04.07.2008 um 17:00 schrieb Dan: > > On Jul 4, 2008, at 9:24 PM, Hartwig Wiesmann wrote: > >> Hi, >> >> I posted a while ago the mail attached below but did not receive any >> answer. If there is any better place to discuss it please let me >> know. >> >> When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will >> crash when opening a database (Mac OSX). The reason seems to be that >> in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be >> prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. >> are undefined. >> >> So, my solution: >> >> SQLITE_ENABLE_RTREE set to 1 >> SQLITE_CORE set to 1 >> and define i64, u8 etc. in all cases. >> >> Did I do anything wrong? > > See here: > > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008- > June/004005.html > > Dan. > > ___ > 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] rtree module crashes
Hi, I posted a while ago the mail attached below but did not receive any answer. If there is any better place to discuss it please let me know. When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will crash when opening a database (Mac OSX). The reason seems to be that in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. are undefined. So, my solution: SQLITE_ENABLE_RTREE set to 1 SQLITE_CORE set to 1 and define i64, u8 etc. in all cases. Did I do anything wrong? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree module crashes
When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will crash when opening a database (Mac OSX). The reason seems to be that in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. are undefined. So, my solution: SQLITE_ENABLE_RTREE set to 1 SQLITE_CORE set to 1 and define i64, u8 etc. in all cases. Did I do anything wrong? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparison of SQLite applications for Mac
Hi Tom, SQLite Database Browser (sqlitebrowser.sourceforge.net) seems to be missing. Hartwig Am 07.05.2008 um 06:20 schrieb BareFeet: > Dennis Cote wrote: > >>> 2. Know of another application that should be included. >>> > >> You may want to include the free SQLite Manager add on for Firefox. >> See >> https://addons.mozilla.org/en-US/firefox/addon/5817 for additional >> information. >> >> It provides a general database browser and editor that works on Mac >> OS as well. > > Thanks for the pointer, Dennis. I've added the SQLite Manager for > Firefox to my review matrix of SQLite GUI software at: > http://www.tandb.com.au/sqlite/compare/?mlp > > If anyone else knows of another program worth adding to the mix, > please let me know. > > Please let me know of any corrections to what's there or any stand out > features in your favorite program that you think are worth comparing. > > Thanks, > Tom > BareFeet > > ___ > 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] [OT] Program to document database l ayout (revised version)
Hi, sorry, but I forgot to mention in the original e-mail the operation system: Mac OSX preferably. Hartwig Original: Hello, this is a bit off topic: I am looking for a program that is suitable for documenting the structure of a SQLite database. I am looking for a program that can visualize the relations between different tables, their connections and indices. Furthermore, comments should be attached to tables or their fields or references to other tables. It is not necessary to read the structure itself from the database. Do you have any recommendations? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OT] Program to document database l ayout
Hello, this is a bit off topic: I am looking for a program that is suitable for documenting the structure of a SQLite database. I am looking for a program that can visualize the relations between different tables, their connections and indices. Furthermore, comments should be attached to tables or their fields or references to other tables. It is not necessary to read the structure itself from the database. Do you have any recommendations? Hartwig ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_column_blob and memory allocation
Hi, I have got two questions concerning the function "sqlite3_column_blob". As sqlite3_column_blob returns "void const*": 1) I assume that the returned pointer will be released by sqlite3, right? So, I do not have to call free() on this pointer. 2) How long is this pointer valid? Till the next call of a sqlite3 function, till a next query etc.? Hartwig - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLiteSpy - new topic
Hello, thanks for the recommendations. Hartwig
[sqlite] Is BETWEEN faster than comparison operators?
Hello, I have read somewhere that BETWEEN is faster than the equivalent expression using comparison operators. Example: ... WHERE Column BETWEEN 1 AND 10 compared with ...WHERE Column >= 1 AND Column <= 10 The reason might be that for the first expression the column value has to be retrieved from the database only once while for the second statement it has to be done twice. So, is BETWEEN in general faster when using SQLite? The second question is what happens with the statement BETWEEN 1 AND 1. It seems to be that this statement using SQLite is identical with the equality operator. But on another SQL system I made the experience that this is not the case. Hartwig
[sqlite] SQLiteSpy - new topic
Hello, is there a similar program as SQLiteSpy for other platforms available? I am especially interested in programs running on MacOS X. Hartwig
Re: [sqlite] sqlite3 on MacOSX
Thanks! Using the latest sqlite3 command tool helped! Hartwig
[sqlite] sqlite3 on MacOSX
Hello! I have created a database (let's name it "Test.db") on the Mac by using the sqlite lib (sqlite version 3.3.6) in my program. Everything works fine. I also wanted to check the database contents using the sqlite command line program. Calling "sqlite3 Test.db" from the terminal and afterwards sending the command ".databases" leads to an error message "unsupported file format". What am I doing wrong? Hartwig
