[sqlite] Send me your corrupted databases
Hello, can you please send me your corrupted database files? (or put somewhere where I can download them). I am finishing a new sqlite utility and I would like to test it with a lot of different databases. Thanks a lot! -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible Bug : SQLITE_RTREE_INT_ONLY compile option not returning correctly with sqlite3_compileoption_used()
Hey Guys, I didn't a ticket for this one anywhere, so forgive me if it's already been reported. Trying to use the compile option SQLITE_RTREE_INT_ONLY with SQLITE_ENABLE_RTREE and sqlite3_compileoption_used() won't acknowledge that it's set. I did a quick check in the source and it seems like the only issue is that it isn't added to azCompileOpt like it is with the others. #ifdef SQLITE_ENABLE_RTREE ENABLE_RTREE, #endif Thanks, Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Windows 8: debugging table is locked errors
Hello list, I'm one of the authors of the SQLite-WinRT component for Windows 8. We recently adapted it to use the official Windows 8 compatible release through NuGet. Thank you for providing a current version of SQLite through this channel! Regarding this release, can you tell me what configuration was used to compile it? I'm asking because we wondered why the binary is about double the size of the one we had previously. Also, there's a strange phenomenon occurring which is probably some kind of race condition in the way that multi-threading is done: sqlite3_step() returns a generic error code 1 (for a valid statement) but when I proceed to call sqlite3_errmsg16(), the error message for error 6 (table locked) is returned. Generally speaking, is a locked table something that should be handled or do you think there may be another problem at play here? I see that SQLite does a lot of work to support seamless multi-threading and since the one application is the only process working on the database, I suspect that the problem may lie elsewhere. All the best Marcus Ilgner -- doo Windows Team doo GmbH | Argelander Strasse 1 | D-53115 Bonn https://doo.net | @ma_il https://twitter.com/ma_il | @doohttps://twitter.com/doo| mar...@doo.net AG Bonn, HRB 18719 | GF Frank Thelen, Marc Sieberger, Alex Koch ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ordering records problem (virtual table)
Hello, During testing of our implementation of SQLite Virtual Table mechanism we've encountered an unexpected behavior. For the following virtual table structure: create table X(ID int, RL real) This query returns all the records in the correct descending order by RL field: Query 1: select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t1.RL desc; Execution plan 1: explain query plan select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t1.RL desc; 0|0|0| SCAN TABLE VTab AS t1 VIRTUAL TABLE INDEX 0:D1; (~0 rows) 0|1|1| SCAN TABLE VTab AS t2 VIRTUAL TABLE INDEX 4:C0=0; (~0 rows) D1 here is a value generated by our xBestIndex method implementation and means descending sorting by the field #1 = RL. C0=0 here means equal operation for the field #0 = ID. This works as expected. However, the next query returns rows (the alias of RL field is different) without any sorting: Query 2: select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t2.RL desc Execution plan 2: explain query plan select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t2.RL desc; 0|0|0| SCAN TABLE VTab AS t1 VIRTUAL TABLE INDEX 0: (~0 rows) 0|1|1| SCAN TABLE VTab AS t2 VIRTUAL TABLE INDEX 4:C0=0; (~0 rows) As you can see there is no index mentioned to sort by. The query executed against a real table (having the very same structure as our virtual table) looks as follows: Query 3: select * from Table1 t1 left outer join Table1 t2 on t1.ID = t2.ID order by t2.RL desc Execution plan 3: explain query plan select * from Table1 t1 left outer join Table1t2 on t1.ID = t2.ID order by t2.RL desc; 0|0|0| SCAN TABLE Table1 AS t1 (~100 rows) 0|1|0| SEARCH TABLE Table1 AS t2 USING AUTOMATIC COVERING INDEX (ID=?) 0|1|0| (~7 rows) 0|0|0| USE TEMP B-TREE FOR ORDER BY As you can see there is sorting using B-tree there. From our side we examined what we received in sqlite3_index_orderby structures (the part of sqlite3_index_info structure) when they had come to xBestIndex and they didn't contain any information about sorting. Also the orderByConsumed out parameter returns False (as our output is not ordered and we presume that SQLite itself will order rows). Is this a bug in SQLite Virtual Table support or we missed something? With best regards, Alexey Daryin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size
2012/9/12 Simon Slavin slav...@bigfraud.org On 12 Sep 2012, at 8:00pm, Dmitry Baryshev ksquirrel...@gmail.com wrote: Actually this is a dynamically loaded plugin, so compiler won't strip unused functions. I cannot change this behaviour. That's why I'm asking about SQLITE_* directives. Ah. In that case you want section 1.6 of http://www.sqlite.org/compile.html Ok, thanks Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Krasu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows 8: debugging table is locked errors
Marcus Ilgner wrote: I'm one of the authors of the SQLite-WinRT component for Windows 8. Cool, the one on CodePlex? Regarding this release, can you tell me what configuration was used to compile it? The default compilation options from the MSVC makefile were used, along with the extra options necessary for WinRT (e.g. SQLITE_OS_WINRT, WINAPI_FAMILY, etc). I'm asking because we wondered why the binary is about double the size of the one we had previously. From where? With what compilation options? Also, there's a strange phenomenon occurring which is probably some kind of race condition in the way that multi-threading is done: sqlite3_step() returns a generic error code 1 (for a valid statement) but when I proceed to call sqlite3_errmsg16(), the error message for error 6 (table locked) is returned. That is not necessarily a race condition. It could be the sqlite3_step is returning SQLITE_ERROR and the last error code stored for the database could be something else, like SQLITE_LOCKED. Also, the SQLite DLLs for WinRT are compiled with SQLITE_THREADSAFE=1. Generally speaking, is a locked table something that should be handled or do you think there may be another problem at play here? I see that SQLite does a lot of work to support seamless multi-threading and since the one application is the only process working on the database, I suspect that the problem may lie elsewhere. How many processes and/or threads are attempting to access the database simultaneously? There can be only one writer at a time. Also, unless WAL is used, a writer may block a reader. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting query results as new records are added
Hi there, I'm wondering if there's a way to get incremental results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Any suggestions are appreciated. -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/13/2012 3:20 PM, Paul Vercellotti wrote: I'm wondering if there's a way to get incremental results to a query after it's started, that includes new records added after the query began? If records are added by the same transaction that runs the SELECT statements, then they may or may not be observed (imagine for example that the statement has an ORDER BY clause, and a new row is inserted that comes before the row that the statement is currently positioned at). If records are added by a different transaction (possible in WAL mode), then they definitely will not be observed - that's the whole point of transaction isolation. That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Put a timestamp on each row, rerun the query with timestamp :lastSeenTimestamp condition. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
If you use rowid correctly (always incrementing rowid by using AUTOINCREMENT) you can always query records lastrowid. That's probably easier. http://www.sqlite.org/autoinc.html So something like: startrowid = 0; lastrowid = select max(rowid) from mytable; select * from mytable where rowid = startrowid and .; startrowid = lastrowid + 1; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Paul Vercellotti [pverce...@yahoo.com] Sent: Thursday, September 13, 2012 2:20 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Getting query results as new records are added Hi there, I'm wondering if there's a way to get incremental results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Any suggestions are appreciated. -Paul ___ 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] Windows 8: debugging table is locked errors
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 13.09.2012 17:50, Joe Mistachkin wrote: Marcus Ilgner wrote: I'm one of the authors of the SQLite-WinRT component for Windows 8. Cool, the one on CodePlex? Oh, sorry, I forgot to include the link [1]. The one at Codeplex is more targeted towards C# development while we developed our component to use it from the new JavaScript/HTML-based framework in Windows 8. Regarding this release, can you tell me what configuration was used to compile it? The default compilation options from the MSVC makefile were used, along with the extra options necessary for WinRT (e.g. SQLITE_OS_WINRT, WINAPI_FAMILY, etc). Ok, great to know, I'll have a look at the Makefile then. I'm asking because we wondered why the binary is about double the size of the one we had previously. From where? With what compilation options? We took the official release and included it in the component. The precompiler defines were SQLITE_OS_WINRT;SQLITE_ENABLE_UNLOCK_NOTIFY;SQLITE_TEMP_STORE=2 Also, there's a strange phenomenon occurring which is probably some kind of race condition in the way that multi-threading is done: sqlite3_step() returns a generic error code 1 (for a valid statement) but when I proceed to call sqlite3_errmsg16(), the error message for error 6 (table locked) is returned. That is not necessarily a race condition. It could be the sqlite3_step is returning SQLITE_ERROR and the last error code stored for the database could be something else, like SQLITE_LOCKED. Also, the SQLite DLLs for WinRT are compiled with SQLITE_THREADSAFE=1. Ok, I was under the impression that sqlite3_step would always return the same error code as in the db. If I understand you correctly, it could be expected behaviour that a locked table returns SQLITE_ERROR instead of SQLITE_LOCKED? Generally speaking, is a locked table something that should be handled or do you think there may be another problem at play here? I see that SQLite does a lot of work to support seamless multi-threading and since the one application is the only process working on the database, I suspect that the problem may lie elsewhere. How many processes and/or threads are attempting to access the database simultaneously? There can be only one writer at a time. Also, unless WAL is used, a writer may block a reader. I had activated WAL in this scenario. There should have only been one writer at that point, but I just had an idea how to debug this further. -- Joe Mistachkin Thank you for the quick reply, it has already been very helpful! All the best Marcus Ilgner [1] https://github.com/doo/SQLite3-WinRT -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with Mozilla - http://www.enigmail.net/ iQEcBAEBAgAGBQJQUjoHAAoJELuOTdgGQnyZVhQH/RzFd+FmDrIdk5zCtOHOHEy8 NANmh4Uf4RqfpS2GZlCI0PI7Hyn4pczaPGBGKO1o8z0GJ0PjmqXcJKuKgxzOitaN KBF+Ig8MkeecuwjIuxDLPYd6h7Urm8Srx8VzNhMPCj1e24uGOItkXQDe5MCgWUYY S3kH1zyWYNfPstHsO2qims4YCnczHs49Eq0S2aEKWwLeSS8aqnZ/eY1+s4Iem1Qn vvJT1o/rjzaIobVP1R022ChGMnLYr8rEvMb+1VKcBM/OQMo+6m+fD+DHQzSieB7w V3dUkx8hx4HJbXh3d59qdAE5NLLQLJN789q+eY33eUzWB0cRV+slLl77TK+6EqQ= =1GAj -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Covering index scan optimization
Eleytherios Stamatogiannakis wrote: create table t (c1,c2, c3, c4); create index idxtc1 on t(c1); explain query plan select c1 from t; SCAN TABLE t (~100 rows) explain query plan select c1 from t order by c1; SCAN TABLE t USING COVERING INDEX idxtc1 (~100 rows) It seems to me that using a covering index scan would always be faster in both cases (fewer disk page reads). Yes, if the index has fewer columns than the table. Is there a reason for SQLite to not use a covering index for scans? The query optimizer does not allow indexes that are not needed for some DISTINCT, WHERE, or ORDER BY clause: select c1 from t indexed by idxtc1; Error: cannot use index: idxtc1 However, it doesn't appear to be too difficult to allow this case: --- src/where.c +++ src/where.c @@ -3037,6 +3037,7 @@ int bSort = !!pOrderBy; /* True if external sort required */ int bDist = !!pDistinct; /* True if index cannot help with DISTINCT */ int bLookup = 0; /* True if not a covering index */ +int bFullCovIdxScan = 0; /* True if full covering index scan */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT3 WhereTerm *pFirstTerm = 0;/* First term matching the index */ @@ -3133,7 +3134,7 @@ ** using the main table (i.e. if the index is a covering ** index for this query). If it is, set the WHERE_IDX_ONLY flag in ** wsFlags. Otherwise, set the bLookup variable to true. */ -if( pIdx wsFlags ){ +if( pIdx ){ Bitmask m = pSrc-colUsed; int j; for(j=0; jpIdx-nColumn; j++){ @@ -3143,9 +3144,16 @@ } } if( m==0 ){ -wsFlags |= WHERE_IDX_ONLY; +if( wsFlags ){ + wsFlags |= WHERE_IDX_ONLY; + }else{ + wsFlags = WHERE_COLUMN_RANGE|WHERE_IDX_ONLY; + bFullCovIdxScan = 1; + } }else{ -bLookup = 1; +if( wsFlags ){ + bLookup = 1; + } } } @@ -3209,6 +3217,8 @@ ** it seems to be working well enough at the moment. */ cost = aiRowEst[0]*4; +}else if(bFullCovIdxScan){ + cost = aiRowEst[0]*2; }else{ log10N = estLog(aiRowEst[0]); cost = nRow; Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Sep 13, 2012, at 9:30 PM, Igor Tandetnik itandet...@mvps.org wrote: If records are added by the same transaction that runs the SELECT statements, then they may or may not be observed (imagine for example that the statement has an ORDER BY clause, and a new row is inserted that comes before the row that the statement is currently positioned at). H… doesn't SQLite provide read consistency by default (i.e. isolation level SERIALIZABLE [1])? In other words, a select should only ever see what existed at the beginning of the query, and that is that. Or perhaps are you talking about 'dirty reads', aka 'read uncommitted' [2]? Or something else altogether? [1] http://en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable [2] http://www.sqlite.org/pragma.html#pragma_read_uncommitted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/13/2012 4:14 PM, Petite Abeille wrote: On Sep 13, 2012, at 9:30 PM, Igor Tandetnik itandet...@mvps.org wrote: If records are added by the same transaction that runs the SELECT statements, then they may or may not be observed (imagine for example that the statement has an ORDER BY clause, and a new row is inserted that comes before the row that the statement is currently positioned at). H… doesn't SQLite provide read consistency by default (i.e. isolation level SERIALIZABLE [1])? Yes it does. Note that the concept of isolation level applies to different transactions, not to statements executed within the same transaction. Within a single transaction, you certainly want to see changes in SELECT that you've just made with INSERT or UPDATE. In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Or perhaps are you talking about 'dirty reads', aka 'read uncommitted' [2]? No I do not. This allows one connection to observe changes made by another, under certain circumstances. I'm talking about a connection observing changes it itself made. Or something else altogether? Yes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Sep 13, 2012, at 10:24 PM, Igor Tandetnik itandet...@mvps.org wrote: In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins. So… SQLite always provide statement-level read consistency, yes? [1] http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows 8: debugging table is locked errors
On 13 Sep 2012, at 8:54pm, Marcus Ilgner mar...@doo.net wrote: Ok, I was under the impression that sqlite3_step would always return the same error code as in the db. There's no good way to guess what sqlite3_step() will return, especially if you called it after ignoring another error result code from another API call. It's normal for a program to deal with the results of sqlite3_step() of one of SQLITE_ROW SQLITE_DONE accordingly, then treat any other result code as an error that must be reported/logged without worrying about exactly which result code is being returned. If I understand you correctly, it could be expected behaviour that a locked table returns SQLITE_ERROR instead of SQLITE_LOCKED? If you're finding a lot of results of SQLITE_LOCKED or SQLITE_BUSY then you probably forgot to set a timeout value, or have a network or hardware problem. That sort of thing happens all the time. In contrast SQLITE_ERROR indicates something completely different, more like bad memory management in C, or having ignored one error result you continued to call other API functions as if everything was working fine it should never happen if your code is well written. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite website documentation query
I'm looking for an in-depth discussion of what each of the error codes mean, like the one on the page http://www.sqlite.org/c_interface.html which appears from its position to be part of the current, up-to-date documentation for SQLite. However, that page has the title The C language interface to SQLite Version 2 Should this say version 3 ? Or is there an up-to-date version for SQLite3 which I can't find, and this one should be moved to a historical section ? I note that the page talks about _exec() a lot whereas I'd expect a documentation page for SQLite3 to be talking about _prepare() or something like that instead. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite website documentation query
On Thu, Sep 13, 2012 at 5:21 PM, Simon Slavin slav...@bigfraud.org wrote: I'm looking for an in-depth discussion of what each of the error codes mean, like the one on the page http://www.sqlite.org/c_interface.html which appears from its position to be part of the current, up-to-date documentation for SQLite. However, that page has the title The C language interface to SQLite Version 2 Should this say version 3 ? Or is there an up-to-date version for SQLite3 which I can't find, and this one should be moved to a historical section ? No. The document you found really does describe SQLite version 2. All the links to the version 2 documentation are carefully hidden. They are buried deep down inside the list of all documents ( http://www.sqlite.org/doclist.html) and in the permuted index ( http://www.sqlite.org/sitemap.html#pindex) and are found nowhere else. Links to the version 3 documentation, on the other hand, are plastered all over the homepage (http://www.sqlite.org), getting high-value placement right there under Common Links on the right-hand side, and featured on the About page (http://www.sqlite.org/about.html) and are relatively near the top on the Documentation page (http://www.sqlite.org/docs.html). How did you manage to fine the version 2 documentation page? I note that the page talks about _exec() a lot whereas I'd expect a documentation page for SQLite3 to be talking about _prepare() or something like that instead. Simon. ___ 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] SQLite website documentation query
On 13 Sep 2012, at 10:39pm, Richard Hipp d...@sqlite.org wrote: Links to the version 3 documentation, on the other hand, are plastered all over the homepage (http://www.sqlite.org), getting high-value placement right there under Common Links on the right-hand side, and featured on the About page (http://www.sqlite.org/about.html) and are relatively near the top on the Documentation page (http://www.sqlite.org/docs.html). This is what I expected would turn up, somewhere, a discussion of what each error code meant for SQLite3. This has always worked fine for other things I looked up and was very surprised to turn up something for SQLite2, which is why I posted. Should I now understand that there is no in-depth discussion of result codes for SQLite3 ? How did you manage to fine the version 2 documentation page? I think I googled 'SQLITE_ERROR', and that was the only page returned which appeared to have more than the 'c_abort' page I'm familiar with. But I just tried it again and I didn't get the Version 2 page in Google's results. Sorry and all that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 9/13/2012 4:39 PM, Petite Abeille wrote: On Sep 13, 2012, at 10:24 PM, Igor Tandetnik itandet...@mvps.org wrote: In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins. So… SQLite always provide statement-level read consistency, yes? In SQLite, a query cannot see changes made by other transactions (whether committed or otherwise) that weren't already committed by the time the current transaction started. But it may observe changes made by the current transaction - the transaction the query is running in. If that's what the definition means by dirty data, then no, SQLite doesn't provide statement-level read consistency. But I can't help but wonder. Consider this scenario: begin; update mytable set myfield=1; commit; begin; update mytable set myfield=2; select myfield from mytable; commit; Are you saying that in Oracle, the SELECT statement would observe myfield=1? That seems to follow from the definition you quote, but I can't imagine how such behavior could be useful. In SQLite, the SELECT statement would certainly see myfield=2. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On 13 Sep 2012, at 11:36pm, Igor Tandetnik itandet...@mvps.org wrote: statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins. But I can't help but wonder. Consider this scenario: begin; update mytable set myfield=1; commit; begin; update mytable set myfield=2; select myfield from mytable; commit; Are you saying that in Oracle, the SELECT statement would observe myfield=1? That seems to follow from the definition you quote, but I can't imagine how such behavior could be useful. In SQLite, the SELECT statement would certainly see myfield=2. Worse still, consider begin; update mytable set myfield=2; select myfield,anotherfield from mytable where myfield=1; commit; It's possible that you might get a row which didn't have myfield=1 at SELECT time ? Oh dear. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Covering index scan optimization
On 13/09/12 23:02, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: It seems to me that using a covering index scan would always be faster in both cases (fewer disk page reads). Yes, if the index has fewer columns than the table. In my experience, the most frequent case is for an index to have less columns than the table it indexes. Is there a reason for SQLite to not use a covering index for scans? The query optimizer does not allow indexes that are not needed for some DISTINCT, WHERE, or ORDER BY clause: Do you know if there is a reason for this? select c1 from t indexed by idxtc1; Error: cannot use index: idxtc1 However, it doesn't appear to be too difficult to allow this case: --- src/where.c +++ src/where.c @@ -3037,6 +3037,7 @@ int bSort = !!pOrderBy; /* True if external sort required */ int bDist = !!pDistinct; /* True if index cannot help with DISTINCT */ int bLookup = 0; /* True if not a covering index */ +int bFullCovIdxScan = 0; /* True if full covering index scan */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT3 WhereTerm *pFirstTerm = 0;/* First term matching the index */ @@ -3133,7 +3134,7 @@ ** using the main table (i.e. if the index is a covering ** index for this query). If it is, set the WHERE_IDX_ONLY flag in ** wsFlags. Otherwise, set the bLookup variable to true. */ -if( pIdx wsFlags ){ +if( pIdx ){ Bitmask m = pSrc-colUsed; int j; for(j=0; jpIdx-nColumn; j++){ @@ -3143,9 +3144,16 @@ } } if( m==0 ){ -wsFlags |= WHERE_IDX_ONLY; +if( wsFlags ){ + wsFlags |= WHERE_IDX_ONLY; + }else{ + wsFlags = WHERE_COLUMN_RANGE|WHERE_IDX_ONLY; + bFullCovIdxScan = 1; + } }else{ -bLookup = 1; +if( wsFlags ){ + bLookup = 1; + } } } @@ -3209,6 +3217,8 @@ ** it seems to be working well enough at the moment. */ cost = aiRowEst[0]*4; +}else if(bFullCovIdxScan){ + cost = aiRowEst[0]*2; }else{ log10N = estLog(aiRowEst[0]); cost = nRow; Thank you for the patch!! With a three line change you replicated the new index-only scan feature of PostgreSQL 9.2! Is there a chance that the change will go into SQLite mainline? For my main workload (OLAP) this can make an enormous difference! Thanks again. lefteris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] c++ - Tell SQLite3, read the subsequent rows
I have been able to write to the databaserepeatedly; several records have been added to it in a table named ‘name’ tolook like this: Id - tile - fname - mname - lname 100 - Mr - Me - Who - Papá 101 - Mrs - She - What - Mamá 102 - Mr - He - Who - Papá I am now trying to read all the informationfrom the table using the below strip-down C++ code, but no matter what I try I only get the first row displayed (row Id = 100), I would like to know how does one tell SQLite3 to read the subsequent rows using the C/C++ API. TIA void mySQLite3::setStmt(const Glib::ustring s) { SQLStatement = s; if (mystmt == NULL) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt,NULL); } if(rc != SQLITE_OK) {...} rc = sqlite3_step(mystmt); } const int mySQLite3::read_int(int pos) { if(rc == SQLITE_ROW ) { apint = sqlite3_column_int(mystmt,pos); } return apint; } const Glib::ustring mySQLite3::read_str(const int pos) { if(rc == SQLITE_ROW ) { apstr = (const char*)sqlite3_column_text(mystmt,pos); } return apstr; } -- Happiness has many doors, and when one of them closes another opens, yet we spent so much time looking at the one that just closed that we don't see the one that just opened.. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c++ - Tell SQLite3, read the subsequent rows
ArbolOne arbol...@gmail.com wrote: I would like to know how does one tell SQLite3 to read the subsequent rows using the C/C++ API. One calls sqlite3_step whenever one wishes to advance to the next row. void mySQLite3::setStmt(const Glib::ustring s) { SQLStatement = s; if (mystmt == NULL) { rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt,NULL); } if(rc != SQLITE_OK) {...} rc = sqlite3_step(mystmt); } You seem to be calling sqlite3_step when sqlite3_prepare_v2 has just failed. This makes no sense. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik itandet...@mvps.org wrote: On 9/13/2012 4:39 PM, Petite Abeille wrote: On Sep 13, 2012, at 10:24 PM, Igor Tandetnik itandet...@mvps.org wrote: In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins. So… SQLite always provide statement-level read consistency, yes? In SQLite, a query cannot see changes made by other transactions (whether committed or otherwise) that weren't already committed by the time the current transaction started. But it may observe changes made by the current transaction - the transaction the query is running in. If that's what the definition means by dirty data, then no, SQLite doesn't provide statement-level read consistency. But I can't help but wonder. Consider this scenario: begin; update mytable set myfield=1; commit; begin; update mytable set myfield=2; select myfield from mytable; commit; Are you saying that in Oracle, the SELECT statement would observe myfield=1? That seems to follow from the definition you quote, but I can't imagine how such behavior could be useful. In SQLite, the SELECT statement would certainly see myfield=2. No, Oralce will return myfield=2 in this case. Oracle's statement isolation level (which is not supported by SQLite) is for cases like this: begin; open cursor c as select myfield from mytable; fetch next row from c; fetch next row from c; update mytable set myfield=2 where ...; fetch next row from c; fetch next row from c; commit; In this case cursor will never observe myfield=2 and this behavior is well defined, documented and guaranteed. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting query results as new records are added
Pavel Ivanov paiva...@gmail.com wrote: On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik itandet...@mvps.org wrote: On 9/13/2012 4:39 PM, Petite Abeille wrote: On Sep 13, 2012, at 10:24 PM, Igor Tandetnik itandet...@mvps.org wrote: In other words, a select should only ever see what existed at the beginning of the query, and that is that. Again, the concept of isolation level applies to transactions, not to individual queries. Let me rephrase that, by paraphrasing some other DB doc [1]: statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins. So… SQLite always provide statement-level read consistency, yes? In SQLite, a query cannot see changes made by other transactions (whether committed or otherwise) that weren't already committed by the time the current transaction started. But it may observe changes made by the current transaction - the transaction the query is running in. If that's what the definition means by dirty data, then no, SQLite doesn't provide statement-level read consistency. But I can't help but wonder. Consider this scenario: begin; update mytable set myfield=1; commit; begin; update mytable set myfield=2; select myfield from mytable; commit; Are you saying that in Oracle, the SELECT statement would observe myfield=1? That seems to follow from the definition you quote, but I can't imagine how such behavior could be useful. In SQLite, the SELECT statement would certainly see myfield=2. No, Oralce will return myfield=2 in this case. So then it doesn't support statement-level read consistency, as defined in its own documentation? The claim was that, among other things, as query execution proceeds, only data committed before the query began is visible to the query. But setting myfield to 2 has not yet been committed by the time the query began. The only way it makes sense is if the word commit means something other than commit a transaction - something like a data manipulation statement successfully runs to completion. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users