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

[Keith Medcalf]

Actually it makes perfect sense.  Yranslate fetch onto step and the update 
where cirrwnt of cirsor to updare the roww just read using the rowid.  your 
subsequent fwtxjes will nwver again return a row you alreadu reteiebwd.

in pther qprds tjeu arw tje same



Sent from Samsung MobileIgor Tandetnik  wrote:
___
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 Fri, Sep 14, 2012 at 8:57 AM, Igor Tandetnik  wrote:
> On 9/14/2012 10:36 AM, Pavel Ivanov wrote:
>>
>> On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik 
>> wrote:
>>>
>>> Wait a minute. If "statement-level read consistency" only applies to two
>>> queries in different transactions, what bearing, if any, does it have on
>>> your example of "open, fetch, update, fetch"? There, everything happens in
>>> the same transaction. I'm thoroughly confused.
>>
>>
>> I'm sorry, I made that example before I carefully re-read Petite's
>> email and understood what you are concerned about.
>
>
> I'm not actually concerned about anything. It is difficult for me to
> possibly care less about the intricacies of Oracle's transaction management.
> It's just that Petite asked whether SQLite supports something called
> "statement-level read consistency", and I've never heard of such a beast,
> and was confused by the formal definition thereof that Petite cited.
>
> Let me see if I understand it. Oracle (optionally?) provides transaction
> isolation levels weaker than "serializable", whereby transaction A may see
> changes made by another transaction B, committed after A started.

This weaker isolation level is (or at least was when I worked with
Oracle databases) set by default. "Serializable" was optional as it
imposes more problems for db administrators and requires more careful
implementations on programmers side.

> However,
> it guarantees that any individual statement within A, during its lifetime,
> will see data either as it existed before B committed, or as it exists
> afterward, but never a mix of the two. Whereas two identical statements
> executed one after another may very well observe different data. This is
> what's called "statement-level read consistency".
>
> Since SQLite only provides serializable transaction isolation level, no
> statement in transaction A will ever see any changes made by transaction B.
> This is a strictly stronger guarantee than "statement-level read
> consistency", rendering moot the question of whether SQLite supports the
> same.
>
> In addition to, and independently of, all of the above, Oracle provides a
> similar guarantee (whose name was not mentioned in this thread) that a
> statement A within a transaction will see all changes by another statement B
> within the same transaction if B completes before A starts; and will not see
> any changes made by yet third statement C, still within the same
> transaction, if C completes after A starts (even if C starts while A was in
> progress). SQLite only provides half of that guarantee: statement A will see
> the changes made by B; it may see none, some, or all changes made by C, in
> an unpredictable way.
>
> Thus, in SQLite it is best to avoid modifying the same data that an
> outstanding SELECT statement is iterating over, as the outcome of such a
> modification is unpredictable.
>
>
> Does this sound right to you?

Yes, that's right.

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

Re: [sqlite] Covering index scan optimization

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

>> Is there a chance that the change will go into SQLite mainline?
> 
> Not without a copyright release.

And it may require more especially if you are an employee.  See the bottom
section of http://www.sqlite.org/copyright.html

And of course it is more than the few lines of changes - all the test
suites have to be updated to ensure no breakage and 100% coverage.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBToHQACgkQmOOfHg372QR1XgCfZvoXr2uKaVFFDo46sEQZiML6
X3UAn0qWun5ldvSJdGj4SEN/n7dVBd7V
=GQzU
-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

[Clemens Ladisch]

Elefterios Stamatogiannakis wrote:
> On 13/09/12 23:02, Clemens Ladisch wrote:
>> Eleytherios Stamatogiannakis wrote:
>>> 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?

Maybe because it's a special case that nobody has yet bothered to
implement ...

> 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?

Not without a copyright release.

  I dedicate any and all copyright interest in this code and any future
  SQLite contributions to the public domain.  I make this dedication for
  the benefit of the public at large and to the detriment of my heirs
  and successors.  I intend this dedication to be an overt act of
  relinquishment in perpetuity of all present and future rights to this
  code under copyright law.

And now v2 without tabstop damage and with much clarified logic:

--- 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 bIndexOnlyScan = 0;   /* True for full scan over covering index */
 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++){
@@ -3142,10 +3143,17 @@
   m &= ~(((Bitmask)1)< For my main workload (OLAP) this can make an enormous difference!

OLAP isn't quite the typical SQLite use case.  But do you have any
numbers (which would help deciding whether to accept this patch)?


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

[Igor Tandetnik]

On 9/14/2012 10:36 AM, Pavel Ivanov wrote:

On Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik  wrote:

Wait a minute. If "statement-level read consistency" only applies to two queries in 
different transactions, what bearing, if any, does it have on your example of "open, fetch, 
update, fetch"? There, everything happens in the same transaction. I'm thoroughly confused.


I'm sorry, I made that example before I carefully re-read Petite's
email and understood what you are concerned about.


I'm not actually concerned about anything. It is difficult for me to 
possibly care less about the intricacies of Oracle's transaction 
management. It's just that Petite asked whether SQLite supports 
something called "statement-level read consistency", and I've never 
heard of such a beast, and was confused by the formal definition thereof 
that Petite cited.


Let me see if I understand it. Oracle (optionally?) provides transaction 
isolation levels weaker than "serializable", whereby transaction A may 
see changes made by another transaction B, committed after A started. 
However, it guarantees that any individual statement within A, during 
its lifetime, will see data either as it existed before B committed, or 
as it exists afterward, but never a mix of the two. Whereas two 
identical statements executed one after another may very well observe 
different data. This is what's called "statement-level read consistency".


Since SQLite only provides serializable transaction isolation level, no 
statement in transaction A will ever see any changes made by transaction 
B. This is a strictly stronger guarantee than "statement-level read 
consistency", rendering moot the question of whether SQLite supports the 
same.


In addition to, and independently of, all of the above, Oracle provides 
a similar guarantee (whose name was not mentioned in this thread) that a 
statement A within a transaction will see all changes by another 
statement B within the same transaction if B completes before A starts; 
and will not see any changes made by yet third statement C, still within 
the same transaction, if C completes after A starts (even if C starts 
while A was in progress). SQLite only provides half of that guarantee: 
statement A will see the changes made by B; it may see none, some, or 
all changes made by C, in an unpredictable way.


Thus, in SQLite it is best to avoid modifying the same data that an 
outstanding SELECT statement is iterating over, as the outcome of such a 
modification is unpredictable.



Does this sound right to you?
--
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 Fri, Sep 14, 2012 at 5:58 AM, Igor Tandetnik  wrote:
> Pavel Ivanov  wrote:
>> There's another way how it could make sense - if you read that excerpt
>> in context. ;-) The "statement-level read consistency" definition
>> cited by Petite is the transaction isolation level. I.e. it defines
>> what each statement executed in one transaction can see related to the
>> changes done in another transaction.
>
> Well, in this case, if you only ever execute one statement per transaction in 
> SQLite, then it does support "statement-level read consistency." Though I 
> fail to see how it materially differs from "serializable". Am I missing 
> something obvious?

For one statement per transaction "statement-level consistency" and
"serializable" do not differ, you are right. But IIRC Oracle doesn't
have an auto-commit mode of operation and multi-statement transactions
are more common, especially when we talk about stored procedures,
triggers and things like that. And if you ever call "begin
transaction" or execute multi-statement transactions then serializable
will make sure that your transaction sees only changes committed
before "begin", in "statement-level" you can see changes committed
after "begin". The worst problem of this: with "statement-level
consistency" two identical select queries executed inside the same
transaction can return different results.

>> This isolation level doesn't
>> define visibility of changes done in the same transaction. These
>> visibility rules are defined in different place in the documentation
>> and don't depend on transaction isolation level.
>>
>> So returning to my example "statement-level read consistency" means
>> that cursor can see myfield=2 if update is done in another transaction
>> before cursor is opened but after begin is executed.
>
> Wait a minute. If "statement-level read consistency" only applies to two 
> queries in different transactions, what bearing, if any, does it have on your 
> example of "open, fetch, update, fetch"? There, everything happens in the 
> same transaction. I'm thoroughly confused.

I'm sorry, I made that example before I carefully re-read Petite's
email and understood what you are concerned about. Plus I was taking
your example as a base for mine. :)
But for statements inside one transaction Oracle uses the same rules
as in statement-level consistency too. Although it's probably named a
little bit differently and its definition doesn't include "committed
data" (it would be "finished statements" or something like that).

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

Re: [sqlite] SQLite Compile Bug for Windows 8

[Teg]

Hello Igor,

Friday, September 14, 2012, 8:59:54 AM, you wrote:

IT> Chang Li  wrote:
>> At line 14573 require add (char *) for convert from (void *)
>> 
>> z = (char *)sqlite3DbMallocRaw(db, (int)n);

IT> Are you, by any chance, trying to compile in C++ mode? SQLite is
IT> written in C, where such a cast is not required.

Yeah, I'm building under Win8 with no issue. 

-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Javascript API for SQLite?

[Etienne]

> I work on an application that uses an SQLite database as it's binary document 
> format (with a custom extension). Now we want to develop a Web App variation 
> of that application, that should be able to read and write those 
> documents/databases. Since the Web App is written in Javascript, I am now 
> looking for a Javascript implementation of the SQLite library.

> I have used the C SQLite library in the past, so I know about using SQLite 
> from C. However, I am just starting with Javascript and Web Apps and I am 
> quite a newbie on that side (Other people on the team are experienced, but I 
> have been asked to work on the SQLite integration).

> What would be my options?


JSDB (SQLite library embedded )  is worth a look:

"JSDB is JavaScript for databases, a scripting language for data-driven, 
network-centric programming on Windows, Mac, Linux, and SunOS. JSDB works with 
databases, XML, the web, and email. It is free and open-source. Use it as a 
JavaScript shell, to run CGI programs, or as a web server". (home page)


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

Re: [sqlite] Javascript API for SQLite?

[Eleytherios Stamatogiannakis]

There is a version of SQLite compiled in Javascript:

http://www.syntensity.com/static/sqlite_wip.html

But as others said, it is not possible to do block access on files from 
inside a Web browser's contained Javascript VM.


Nevertheless, theoretically you could load the full DB file in memory 
[*], do your operations on it in memory and then write it as a whole 
back to the disk, all on the client side.


lefteris.

[*] https://github.com/eligrey/FileSaver.js

On 14/09/12 16:05, Simon Slavin wrote:


On 13 Sep 2012, at 3:13pm, Jean-Denis Muys  wrote:


I work on an application that uses an SQLite database as it's binary document 
format (with a custom extension). Now we want to develop a Web App variation of 
that application, that should be able to read and write those 
documents/databases. Since the Web App is written in Javascript, I am now 
looking for a Javascript implementation of the SQLite library.


This can't be done entirely in JavaScript, since JavaScript running in a web 
browser has no way of getting at files on your hard disk.  This is to prevent 
the programmers of a web site spying on your computer's files.


I have used the C SQLite library in the past, so I know about using SQLite from 
C. However, I am just starting with Javascript and Web Apps and I am quite a 
newbie on that side (Other people on the team are experienced, but I have been 
asked to work on the SQLite integration).


There are ways you can allow JavaScript to access data inside a file on a web 
server.  The standard way is to write a shim in PHP or some similar language.  
The PHP code runs on the web server and uses PHP's SQLite3 library to access 
databases.  You ask it to execute a SQLite command, and it returns the results 
in JSON (or some other) format.  So for instance

https://myserver.com/databases/doSQLCommand.php?file=money.sqlite&command=SELECT
 * FROM transactions WHERE id=123

might return a page of application/json type containing

{id: 123, trandate: "20030205", amount: 274.53}

Of course in real life you're more likely to pass the parameters using POST 
than GET.

Your JavaScript code asks the shim for the data using an XMLHttpRequest and 
uses JSON.parse() to turn the results into an array or an object.

There are, of course, many security concerns with such a setup, so most shim 
programs check to see that they're being called only from their own server, by 
a program they recognise, running on a computer they recognise.  I sometimes 
use a setup like this, though my shim returns the requested results as only a 
small part of the stuff it returns, the rest being things like error messages 
and last-insert-id and stuff like that.

Simon.
___
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] Javascript API for SQLite?

[Clemens Ladisch]

Jean-Denis Muys wrote:
> I am now looking for a Javascript implementation of the SQLite library.

 says:
| sql.js is a port of SQLite to JavaScript, by compiling the SQLite
| C code with Emscripten.

It's completely in-memory, but:

| Database objects ... have the following methods:
| * .exportData() serializes the data to a typed array of 8-bit values,
|   which you can save using any method you like (localStorage,
|   indexedDB, send to a remote server, etc.), and later re-use by
|   calling SQL.open with that data.

It's one of the engines used on sqlfiddle.com, and seems to work just
fine.


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

Re: [sqlite] Javascript API for SQLite?

[Simon Slavin]

On 13 Sep 2012, at 3:13pm, Jean-Denis Muys  wrote:

> I work on an application that uses an SQLite database as it's binary document 
> format (with a custom extension). Now we want to develop a Web App variation 
> of that application, that should be able to read and write those 
> documents/databases. Since the Web App is written in Javascript, I am now 
> looking for a Javascript implementation of the SQLite library.

This can't be done entirely in JavaScript, since JavaScript running in a web 
browser has no way of getting at files on your hard disk.  This is to prevent 
the programmers of a web site spying on your computer's files.

> I have used the C SQLite library in the past, so I know about using SQLite 
> from C. However, I am just starting with Javascript and Web Apps and I am 
> quite a newbie on that side (Other people on the team are experienced, but I 
> have been asked to work on the SQLite integration).

There are ways you can allow JavaScript to access data inside a file on a web 
server.  The standard way is to write a shim in PHP or some similar language.  
The PHP code runs on the web server and uses PHP's SQLite3 library to access 
databases.  You ask it to execute a SQLite command, and it returns the results 
in JSON (or some other) format.  So for instance

https://myserver.com/databases/doSQLCommand.php?file=money.sqlite&command=SELECT
 * FROM transactions WHERE id=123

might return a page of application/json type containing

{id: 123, trandate: "20030205", amount: 274.53}

Of course in real life you're more likely to pass the parameters using POST 
than GET.

Your JavaScript code asks the shim for the data using an XMLHttpRequest and 
uses JSON.parse() to turn the results into an array or an object.

There are, of course, many security concerns with such a setup, so most shim 
programs check to see that they're being called only from their own server, by 
a program they recognise, running on a computer they recognise.  I sometimes 
use a setup like this, though my shim returns the requested results as only a 
small part of the stuff it returns, the rest being things like error messages 
and last-insert-id and stuff like that.

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

Re: [sqlite] SQLite Compile Bug for Windows 8

[Igor Tandetnik]

Chang Li  wrote:
> At line 14573 require add (char *) for convert from (void *)
> 
> z = (char *)sqlite3DbMallocRaw(db, (int)n);

Are you, by any chance, trying to compile in C++ mode? SQLite is written in C, 
where such a cast is not required.
-- 
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

[Igor Tandetnik]

Pavel Ivanov  wrote:
> There's another way how it could make sense - if you read that excerpt
> in context. ;-) The "statement-level read consistency" definition
> cited by Petite is the transaction isolation level. I.e. it defines
> what each statement executed in one transaction can see related to the
> changes done in another transaction.

Well, in this case, if you only ever execute one statement per transaction in 
SQLite, then it does support "statement-level read consistency." Though I fail 
to see how it materially differs from "serializable". Am I missing something 
obvious?

> This isolation level doesn't
> define visibility of changes done in the same transaction. These
> visibility rules are defined in different place in the documentation
> and don't depend on transaction isolation level.
> 
> So returning to my example "statement-level read consistency" means
> that cursor can see myfield=2 if update is done in another transaction
> before cursor is opened but after begin is executed.

Wait a minute. If "statement-level read consistency" only applies to two 
queries in different transactions, what bearing, if any, does it have on your 
example of "open, fetch, update, fetch"? There, everything happens in the same 
transaction. I'm thoroughly confused.
-- 
Igor Tandetnik

___
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

[Rob Richardson]

Igor, you didn't read ArbolOne's incomprehensible code correctly.  Here it is 
with reasonable formatting:



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;

}





You don't show where these functions are being called, but you are doing one of 
two things.  You are calling setStmt inside a loop, in which case you are 
rebuilding the statement every time and then reading the first row each time 
(completely ignoring Igor's message about opening a book, reading the first 
page, closing the book, repeating that entire sequence, and wondering why you 
never finish the book), or you are calling setStmt once, in which case you open 
the book, read the first page, close the book, and wonder why you never finish 
the book.



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

[sqlite] SQLite Compile Bug for Windows 8

[Chang Li]

At line 14573 require add (char *) for convert from (void *)

z = (char *)sqlite3DbMallocRaw(db, (int)n);


Sent from my Windows 8 PC
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Javascript API for SQLite?

[Jean-Denis Muys]

Hello,

I work on an application that uses an SQLite database as it's binary document 
format (with a custom extension). Now we want to develop a Web App variation of 
that application, that should be able to read and write those 
documents/databases. Since the Web App is written in Javascript, I am now 
looking for a Javascript implementation of the SQLite library.

I have used the C SQLite library in the past, so I know about using SQLite from 
C. However, I am just starting with Javascript and Web Apps and I am quite a 
newbie on that side (Other people on the team are experienced, but I have been 
asked to work on the SQLite integration).

What would be my options?

Thanks for any help.

Jean-Denis

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

Re: [sqlite] System.Data.SQLite: Add [app|web].config file to NuGetpackage

[Joe Mistachkin]

Felix Obermaier wrote:
>
> Doesn't it make sense to add such a config file to the NuGet package?
>

I'm somewhat hesitant to add more complexity to the NuGet packages,
especially since not every project requires this database provider
block in their configuration file.  The additional complexity would
be (this may not be a complete list):

1. Merging the new database provider configuration block into the
existing one for the project upon install, but only if it's not
there already.

2. Removing the database provider configuration block from the
configuration file upon uninstall, but only if we were the ones
that added it.

--
Joe Mistachkin

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