Re: [sqlite] Getting query results as new records are added

[Igor Tandetnik]

Pavel Ivanov  wrote:
> On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik  wrote:
>> On 9/13/2012 4:39 PM, Petite Abeille wrote:
>>> 
>>> 
>>> On Sep 13, 2012, at 10:24 PM, Igor Tandetnik  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

Re: [sqlite] Getting query results as new records are added

[Pavel Ivanov]

On Thu, Sep 13, 2012 at 3:36 PM, Igor Tandetnik  wrote:
> On 9/13/2012 4:39 PM, Petite Abeille wrote:
>>
>>
>> On Sep 13, 2012, at 10:24 PM, Igor Tandetnik  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] c++ - Tell SQLite3, read the subsequent rows

[Igor Tandetnik]

ArbolOne  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,
> ,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

[sqlite] c++ - Tell SQLite3, read the subsequent rows

[ArbolOne]

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,
,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] Covering index scan optimization

[Elefterios Stamatogiannakis]

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; jnColumn; 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

Re: [sqlite] Getting query results as new records are added

[Simon Slavin]

On 13 Sep 2012, at 11:36pm, Igor Tandetnik  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] Getting query results as new records are added

[Igor Tandetnik]

On 9/13/2012 4:39 PM, Petite Abeille wrote:


On Sep 13, 2012, at 10:24 PM, Igor Tandetnik  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] SQLite website documentation query

[Simon Slavin]

On 13 Sep 2012, at 10:39pm, Richard Hipp  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] SQLite website documentation query

[Richard Hipp]

On Thu, Sep 13, 2012 at 5:21 PM, Simon Slavin  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

[sqlite] SQLite website documentation query

[Simon Slavin]

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] Windows 8: debugging "table is locked" errors

[Simon Slavin]

On 13 Sep 2012, at 8:54pm, Marcus Ilgner  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

Re: [sqlite] Getting query results as new records are added

[Petite Abeille]

On Sep 13, 2012, at 10:24 PM, Igor Tandetnik  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] Getting query results as new records are added

[Igor Tandetnik]

On 9/13/2012 4:14 PM, Petite Abeille wrote:

On Sep 13, 2012, at 9:30 PM, Igor Tandetnik  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

[Petite Abeille]

On Sep 13, 2012, at 9:30 PM, Igor Tandetnik  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] Covering index scan optimization

[Clemens Ladisch]

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; jnColumn; 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] Windows 8: debugging "table is locked" errors

[Marcus Ilgner]

-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] Getting query results as new records are added

[Black, Michael (IS)]

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] Getting query results as new records are added

[Igor Tandetnik]

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

[sqlite] Getting query results as new records are added

[Paul Vercellotti]

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] Windows 8: debugging "table is locked" errors

[Joe Mistachkin]

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

Re: [sqlite] Reducing size

[Dmitry Baryshev]

2012/9/12 Simon Slavin 

>
> On 12 Sep 2012, at 8:00pm, Dmitry Baryshev  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
>
> 
>



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

[sqlite] Ordering records problem (virtual table)

[Alexey Daryin]

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

[sqlite] Windows 8: debugging "table is locked" errors

[Marcus Ilgner]

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  |
@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] Possible Bug : SQLITE_RTREE_INT_ONLY compile option not returning correctly with sqlite3_compileoption_used()

[Michael Park]

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] Send me your corrupted databases

[Marco Bambini]

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