Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Sat, 13 Jul 2013 00:09:36 -0600
"Keith Medcalf"  wrote:

> UPDATE WHERE CURRENT OF CURSOR has been part of SQL since about, oh,
> 1969.  

(I assume that's dramatic license.) 

> Now then SQLite does not support the FOR UPDATE OF clause when
> defining a cursor (ie, doing a prepare) which is intended to inform
> the optimizer that updates will be issued thus and so during cursor
> processing and to make "such arrangements as it deems fit" to prevent
> programmers from doing stupid things.  

FOR UPDATE OF does more than pass a hint to the optimizer.  It changes
the semantics of the cursor from read-only to read-write.  Other than
the clumsy syntax, I don't see how it has anything to do with stupid.  

> Of course, if you want to add a pragma to prevent concurrent
> statements on a single connection, that would be fine -- provided
> that the default is OFF (ie, do not prevent such use -- or can be
> made thus when the library is compiled).  Those who need extra
> protection to prevent themselves from shooting themselves in the head
> can turn it on.  

It seems we differ on what constitutes a good API, and on the value of
simplicity. 

Complexity is evil.  Every change in behavior is another state to
master, doubling the potential states, adding to complexity, making the
interface harder to understand and use correctly.  The goal of the
library design is to find the narrowest path through which the
information can pass, limiting the number of functions and states and
therefore the number of potential errors (on both sides of the API)
for a given functionality.  

Now, it's a fact most programmers don't consider "a little more
complexity" a problem, and you would seem to be in that group.  It's
also a fact that 90% of programmers consider themselves above average,
so we know there are problems with our self-assement, in aggregate.  

In case you don't think modifying a table using the same connection
that is currently processing a SELECT adds to the complexity of the
SQLIte API, I refer to you this very thread.  I'm an experienced
SQLite programmer, and because I hadn't ever been tempted to try that
particular trick, I hadn't ever imagined it would work (or not work,
depending on your perspective).  It took several exchanges to describe
the behavior, eventually prompting Dr. Richard to draft an additional
731 words of documentation.  Even at that,  questions remain
about exactly how it behaves, and about which behavior is semantic and
intentional, and which is incidental.  

Simplicity is intrinsically valuable for everyone, not merely the
stupid. You yourself, a manifestly intelligent being, learned today
that "order by +rowid" changes the behavior. Yet nothing about ORDER BY
(much less "+") suggests a change in isolation semantics.  It's just a
queer tick that happens to work, like the queer tick of using a cross
join to affect the query optimizer.  They represent what is known as
"accidental complexity". They should be rectified, not venerated.  

That's an observation, by the way, not a demand.  The only software that
can't be improved is already perfect.  I haven't come across that
particular gem yet, so I know SQLite has a lot of company.  

I'm faintly surprised I had to make this argument.  To me it's
self-evident that complexity to be avoided, and that weird behavior is
complex almost by definition. To me it's axiomatic that a library
promising transaction isolation shouldn't break that promise under any
circumstances.  

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

Re: [sqlite] Another 2 questions about SQLite

[Keith Medcalf]

> Richard Hipp said on Saturday, 13 July, 2013 11:59:

> > I don't know.  It works entirely as expected.  If I move a row (via
> > update) that I have already visited into a position where it is "yet
> > to be visited", then I will visit it twice -- both times exactly when
> > and where expected.

> Please try again after adding "ORDER BY +rowid" to the query.  The "+"
> before "rowid" is important in the previous.  If you already have an
> ORDER BY clause on your query, simply add a "+" sign to the first term.  

> Let me know if the row still gets visited twice.

This works.

I see that it forces the entire query to run on the first call to _step() and 
storing the results to a temporary b-tree, then returning results from that 
b-tree instead of direct traversal for each _step().  In my case this does fix 
the multiple visits and I expect that it would work in every use case.  As a 
side effect the query results are stable and would not see any updates 
performed against tables involved in the query on the same connection during 
retrieval of the outer result set.





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

Re: [sqlite] Another 2 questions about SQLite

[Richard Hipp]

On Sat, Jul 13, 2013 at 1:53 PM, Keith Medcalf  wrote:

>
> I don't know.  It works entirely as expected.  If I move a row (via
> update) that I have already visited into a position where it is "yet to be
> visited", then I will visit it twice -- both times exactly when and where
> expected.
>

Please try again after adding "ORDER BY +rowid" to the query.  The "+"
before "rowid" is important in the previous.  If you already have an ORDER
BY clause on your query, simply add a "+" sign to the first term.  Let me
know if the row still gets visited twice.

-- 
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] Another 2 questions about SQLite

[Keith Medcalf]

I don't know.  It works entirely as expected.  If I move a row (via update) 
that I have already visited into a position where it is "yet to be visited", 
then I will visit it twice -- both times exactly when and where expected.  If 
there is a row I have not visited yet and I delete it before I read it, then I 
won't be reading it.

There is very little confusion here and very little reliance on anything other 
than the application of logic.

All technologies appear to be magic when those technologies are beyond the 
understanding.  This does not demonstrate magic, but a deficit in wattage and 
understanding.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Saturday, 13 July, 2013 07:37
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Another 2 questions about SQLite
> 
> On 7/13/2013 1:36 AM, Keith Medcalf wrote:
> > Of course, the behaviour is not actually "undefined" -- it is
> perfectly determinable and entirely predictable and reasonable.
> However, if one does not understand the factors which determine the
> behaviour then, for you, the behaviour is undefined.  In other words,
> if one does not know what results are expected to be obtain and why,
> then the responsibility rests with the one who is "using the force"
> instead of understanding what they are doing.
> 
> The results are undefined in the sense that the authors don't document
> any particular behavior, and perhaps more importantly, don't commit to
> maintaining  any particular behavior in future releases. If your
> program
> does something that falls under undefined behavior, and it appears to
> work to your liking, it does so only by accident. It relies on
> internal
> implementation details, subject to change without notice.
> 
> You may of course continue to live dangerously; just don't be
> surprised
> when you upgrade to a newer SQLite version and you program breaks.
> --
> Igor Tandetnik
> 
> ___
> 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] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/13/2013 1:36 AM, Keith Medcalf wrote:

Of course, the behaviour is not actually "undefined" -- it is perfectly determinable and 
entirely predictable and reasonable.  However, if one does not understand the factors which 
determine the behaviour then, for you, the behaviour is undefined.  In other words, if one does not 
know what results are expected to be obtain and why, then the responsibility rests with the one who 
is "using the force" instead of understanding what they are doing.


The results are undefined in the sense that the authors don't document 
any particular behavior, and perhaps more importantly, don't commit to 
maintaining  any particular behavior in future releases. If your program 
does something that falls under undefined behavior, and it appears to 
work to your liking, it does so only by accident. It relies on internal 
implementation details, subject to change without notice.


You may of course continue to live dangerously; just don't be surprised 
when you upgrade to a newer SQLite version and you program breaks.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Keith Medcalf]

> It's not SQLite's "problem", I suppose.  If it works as intended, it's
> not a bug.  Where we disagree is over whether that intention best
> serves the application programmer.
 
> You think it's OK: he made his bed and now he can lie in it.  Modify
> the table you haven't finished selecting, and prepare to see your
> application melt.
 
> I think it's not OK: The library is in a position to prevent data
> corruption in the application, yet does not.  An error returned by the
> library would prevent errors stemming from very strange behavior while
> processing the SELECT.  (I think we agree the behavior is strange.)
 
> As you pointed out, it's a kind of compromise:  Because SQLite locks
> the
> file, if the same handle cannot be used for more than one statement at
> a time, the application would have to use the database in a strictly
> serial fashion.  And I can understand the appeal of the simplicity of
> One Big Lock.
 
> I would like to see a way to permit multiplexed use of the handle
> while
> preventing "undefined behavior".   I hope you agree that would be an
> improvement.  It would simplify the API and eliminate 731 words of
> documentation.

UPDATE WHERE CURRENT OF CURSOR has been part of SQL since about, oh, 1969.  It 
has *always* been possible to update the current of cursor or to update any 
table or data, whether that table is being used in a current join operation, at 
any time while "running" the cursor (ie, calling _step()).

Now then SQLite does not support the FOR UPDATE OF clause when defining a 
cursor (ie, doing a prepare) which is intended to inform the optimizer that 
updates will be issued thus and so during cursor processing and to make "such 
arrangements as it deems fit" to prevent programmers from doing stupid things.  
In fact, the FOR UPDATE OF clause was only added as a way to prevent those of 
limited understanding from doing things that they didn't understand yet 
insisted on doing anyway.

Of course, if you want to add a pragma to prevent concurrent statements on a 
single connection, that would be fine -- provided that the default is OFF (ie, 
do not prevent such use -- or can be made thus when the library is compiled).  
Those who need extra protection to prevent themselves from shooting themselves 
in the head can turn it on.  Those who know not to point at loaded gun at their 
own basal ganglia and pull the trigger have no need of the extra protection so 
afforded.  In any case, rather serious consequences will arise by preventing 
multiple statements on a single connection.





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

Re: [sqlite] Another 2 questions about SQLite

[Keith Medcalf]

> The major unexpected thing here is how SQLite deals with a case where
> two different connections (which may be from different apps on
> different computers) both have uncommitted changes.  I think
> explaining things using this as the key point may make explaining the
> other aspects unnecessary: they will all come out in the wash.

Two connections are isolated from each other and cannot both have uncommitted 
writes.  Unless you are using shared cache AND uncommitted read (which implies 
the same process running on a single machine) *or* WAL, you cannot "read" while 
you are writing, and only in the former case (shared cache AND read-uncommitted 
in a single process) can your "reader" see in process writes (originating from 
a connection using "the same" shared cache within a single process on a single 
machine).

You need to re-read more slowly.




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

Re: [sqlite] Another 2 questions about SQLite

[Keith Medcalf]

Of course, the behaviour is not actually "undefined" -- it is perfectly 
determinable and entirely predictable and reasonable.  However, if one does not 
understand the factors which determine the behaviour then, for you, the 
behaviour is undefined.  In other words, if one does not know what results are 
expected to be obtain and why, then the responsibility rests with the one who 
is "using the force" instead of understanding what they are doing.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, 12 July, 2013 14:03
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Another 2 questions about SQLite
> 
> On Fri, Jul 12, 2013 at 3:01 PM, Igor Tandetnik 
> wrote:
> 
> > On 7/12/2013 12:30 PM, James K. Lowden wrote:
> >
> >> The documented behavior is - if you modify the data as you iterate
> >>> over that same data, the results are unpredictable.
> >>>
> >>
> >> Where does it say that?
> >>
> >
> > You got me here. The behavior doesn't appear to be documented,
> 
> 
> Proposed documentation enhancement here:
> http://www.sqlite.org/draft/isolation.html
> 
> 
> 
> > and it probably should. The closest I could find to the official
> word is
> > this post by Dr. Hipp from 2009:
> >
> > http://sqlite.1065341.n5.**nabble.com/Python-sqlite-**
> > binding-commit-inside-select-**loop-
> td51927.html#a51941<http://sqlite.1065341.n5.nabble.com/Python-sqlite-
> binding-commit-inside-select-loop-td51927.html#a51941>
> > "The official policy is that if you modify a table (via INSERT,
> UPDATE, or
> > DELETE) in the middle of a SELECT, then what you get from the
> remainder of
> > the SELECT is undefined."
> >
> > --
> > Igor Tandetnik
> >
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-
> **users<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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Fri, 12 Jul 2013 14:25:36 -0400
Igor Tandetnik  wrote:

> >it is very much SQLite's job to prevent logical
> > programming errors from corrupting the data.
> 
> Define "the data". The database file remains perfectly intact, no 
> corruption there. Your internal state might be corrupted - but how is 
> this SQLite's problem?
...
> > SQLite OTOH is a DBMS.  If used in a way that could cause it to
> > return unreliable results, its only alternative is to return an
> > error.
> 
> Correct. So between the action of returning unreliable results, and
> the only alternative action of returning an error, SQLite authors
> chose the former. For good reasons, too.

It's not SQLite's "problem", I suppose.  If it works as intended, it's
not a bug.  Where we disagree is over whether that intention best
serves the application programmer.  

You think it's OK: he made his bed and now he can lie in it.  Modify
the table you haven't finished selecting, and prepare to see your
application melt.  

I think it's not OK: The library is in a position to prevent data
corruption in the application, yet does not.  An error returned by the
library would prevent errors stemming from very strange behavior while
processing the SELECT.  (I think we agree the behavior is strange.)  

As you pointed out, it's a kind of compromise:  Because SQLite locks the
file, if the same handle cannot be used for more than one statement at
a time, the application would have to use the database in a strictly
serial fashion.  And I can understand the appeal of the simplicity of
One Big Lock.  

I would like to see a way to permit multiplexed use of the handle while
preventing "undefined behavior".   I hope you agree that would be an
improvement.  It would simplify the API and eliminate 731 words of
documentation.  

The simplest suggestion I can make is a per-connection trivial table
"lock". The set of tables being selected at any one time is known.
Certainly it's not complex to know whether a table is the target of
INSERT, UPDATE, or DELETE and is in the set of those being SELECTed.
Return an error if any attempt is made to modify those tables. Continue
to rely on filesystem semantics when more than one connection handle is
involved.  

That change would remove uncertainty about the effects of updating the
database while SELECT is in progress.  Because the
combination of shared_cache & read_uncommitted have same (nonisolation)
property, that configuration would become more useful, too.  



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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Fri, 12 Jul 2013 16:02:37 -0400
Richard Hipp  wrote:

> On Fri, Jul 12, 2013 at 3:01 PM, Igor Tandetnik 
> wrote:
> 
> > On 7/12/2013 12:30 PM, James K. Lowden wrote:
> >
> >> The documented behavior is - if you modify the data as you iterate
> >>> over that same data, the results are unpredictable.
> >>>
> >>
> >> Where does it say that?
> >>
> >
> > You got me here. The behavior doesn't appear to be documented,
> 
> 
> Proposed documentation enhancement here:
> http://www.sqlite.org/draft/isolation.html

Thank you, Richard.  That does clarify the issue considerably.  

Under summary, Item #6 refers to the "previous four items" of which
five are listed.  Perhaps it would be better to say 

"Database connections that 
* use the same shared cache, and
* enable PRAGMA read_uncommitted 
are considered to be one database connection, and as such
have no isolation support."  

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 12 Jul 2013, at 9:02pm, Richard Hipp  wrote:

> Proposed documentation enhancement here:
> http://www.sqlite.org/draft/isolation.html

I hope you don't mind that I posted this publicly.  It's a bit strong for a 
public forum, but I suspect that other readers of this forum might want to 
chime in and tell me I'm overreacting, or even suggest even better ways of 
conveying the information concerned.

I'm not a fan of the draft version of that page and feel there's scope for 
improvement before production.  It says various things about SQLite but doesn't 
seem to group them in an order that any particular reader would find useful.  
Each reader would have to read a lot of text then try to pick through it to 
figure out what they want to know.  And the page as a whole is too long and 
complicated: I can't tell whether I've gathered the points I need or not.

The page seems to talk about two things:

A) What is a database connection ?  What is the connection between a connection 
and a database lock.  What is the connection between a connection and 
uncommitted changes ?  How do various PRAGMAs (including shared connections) 
change this ?

The major unexpected thing here is how SQLite deals with a case where two 
different connections (which may be from different apps on different computers) 
both have uncommitted changes.  I think explaining things using this as the key 
point may make explaining the other aspects unnecessary: they will all come out 
in the wash.

-

B) The SQL SELECT operation theoretically happens in an instant, but the SQLite 
API allows the programmer to execute it bit by bit.  What happens if (a) 
another connection or (b) the same connection makes changes to the database 
between the start and the end of the operation ?

While this page goes into some detail about (B) I think much of this is 
unnecessary detail and can be replaced by two simple statements: 

(1) Other connections cannot commit changes until the SELECT is finished. (is 
this true ?)
(2) If changes are made using the same connection as is used for the SELECT, 
the results are unpredictable, even to the extent that rows may be omitted or 
returned twice.

Once the reader has accepted (2), everything else necessary follows.  They 
already know what they should or shouldn't do.

-

I'm not even a fan of the summary, which is needed only because the whole page 
is so long.  If one is really needed, then perhaps some of these can be done:

Summary item 1 could point to (or perhaps replace) this page

 [1]

with its useful pointer to the WP article on serializability.

Item 2 is sometimes false because of the combination of pragmas mentioned in 
the body of the article.

Item 3 needs a statement about when a query starts (is it the _prepare() or the 
first _step() or sometimes one sometimes another ?).

Items 3 and 5 should be rephrased or merged to explain the timeline:

_prepare() done here
set 1 of changes
first _step() done here
set 2 of changes
more _step()s here
set 3 of changes
step() returns SQLITE_DONE here

Describe for each set whether SELECT will be affected by the changes at all, 
and whether the changes may cause SELECT to return an inconsistent set of rows.

Items 4 and 5 should be merged.  Or perhaps item 4 doesn't need to be there at 
all, since item 5 is so much stronger than it: the results may not only be 
unpredictable but they may not even make sense.

Items 2 and 6 should be rephrased or merged.  6 weakens and modifies (or 
arguably contradicts) 2.

Simon.

[1] which is missing a 't' on its last line
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[Richard Hipp]

On Fri, Jul 12, 2013 at 3:01 PM, Igor Tandetnik  wrote:

> On 7/12/2013 12:30 PM, James K. Lowden wrote:
>
>> The documented behavior is - if you modify the data as you iterate
>>> over that same data, the results are unpredictable.
>>>
>>
>> Where does it say that?
>>
>
> You got me here. The behavior doesn't appear to be documented,


Proposed documentation enhancement here:
http://www.sqlite.org/draft/isolation.html



> and it probably should. The closest I could find to the official word is
> this post by Dr. Hipp from 2009:
>
> http://sqlite.1065341.n5.**nabble.com/Python-sqlite-**
> binding-commit-inside-select-**loop-td51927.html#a51941
> "The official policy is that if you modify a table (via INSERT, UPDATE, or
> DELETE) in the middle of a SELECT, then what you get from the remainder of
> the SELECT is undefined."
>
> --
> Igor Tandetnik
>
>
> __**_
> 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] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/12/2013 12:30 PM, James K. Lowden wrote:

The documented behavior is - if you modify the data as you iterate
over that same data, the results are unpredictable.


Where does it say that?


You got me here. The behavior doesn't appear to be documented, and it 
probably should. The closest I could find to the official word is this 
post by Dr. Hipp from 2009:


http://sqlite.1065341.n5.nabble.com/Python-sqlite-binding-commit-inside-select-loop-td51927.html#a51941
"The official policy is that if you modify a table (via INSERT, UPDATE, 
or DELETE) in the middle of a SELECT, then what you get from the 
remainder of the SELECT is undefined."


--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/12/2013 12:30 PM, James K. Lowden wrote:

On Mon, 08 Jul 2013 00:37:55 -0400
Igor Tandetnik  wrote:


I don't believe it's SQLite's job to ensure the programmer doesn't
shoot herself in the foot. After all, you don't expect, say, the C++
compiler to prevent you from destroying an object while another part
of the program holds a pointer to it. This SQLite's behavior is little
different.


Au contraire, mon ami: it is very much SQLite's job to prevent logical
programming errors from corrupting the data.


Define "the data". The database file remains perfectly intact, no 
corruption there. Your internal state might be corrupted - but how is 
this SQLite's problem?



The analogy to C++ is inapt. C++ makes very few guarantees about an
object's lifetime.  That is its design and definition.


I'm not sure I understand this claim. C++ provides certain guarantees 
about object lifetime. SQLite provides certain guarantees about its 
behavior. Is there a particular number of guarantees that must be 
provided, below which the set of guarantees would be reasonably 
described as "very few"? Is C++ below this threshold, and SQLite above it?



SQLite OTOH is
a DBMS.  If used in a way that could cause it to return unreliable
results, its only alternative is to return an error.


Correct. So between the action of returning unreliable results, and the 
only alternative action of returning an error, SQLite authors chose the 
former. For good reasons, too.



More apt would be if open(2), called with O_EXLOCK twice on the
same file, were to permit the second process to modify the file
as the first one reads it.


No, the case here is that the same process, in the middle of reading 
from a file, writes to that same file through the same handle, 
overwriting the data it is just about to read. Again - we are not 
talking about two transactions interfering with each other - that works 
the way you expect. We are talking about a single transaction modifying 
the data it itself is reading.



 In fact, I'd be interested if you could
point to a single standard C library function that, when called
out-of-sequence, doesn't return an error but permits the process to
proceed destructively.


You got to be kidding me. C++ language and library are chock full of 
conditions that trigger undefined behavior. E.g. calling free() twice on 
the same pointer (as Drake Wilson notes), or fclose() twice on the same 
FILE* handle.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Drake Wilson]

Quoth "James K. Lowden" , on 2013-07-12 12:30:13 
-0400:
> as the first one reads it.  In fact, I'd be interested if you could
> point to a single standard C library function that, when called
> out-of-sequence, doesn't return an error but permits the process to
> proceed destructively.  

free().

Now, can we stop the repeated philosophical arguments about these
sort of things on the SQLite list?  They are getting old and drifting
off topic, I think.

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 12 Jul 2013, at 5:30pm, James K. Lowden  wrote:

> There is no "SQLITE_OK_BUT_YOU_ARE_ON_YOUR_OWN" afaik. 

This is the best idea ever.  I vote it gets included in SQLite4.

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Mon, 08 Jul 2013 00:37:55 -0400
Igor Tandetnik  wrote:

> I don't believe it's SQLite's job to ensure the programmer doesn't
> shoot herself in the foot. After all, you don't expect, say, the C++
> compiler to prevent you from destroying an object while another part
> of the program holds a pointer to it. This SQLite's behavior is little
> different.

Au contraire, mon ami: it is very much SQLite's job to prevent logical
programming errors from corrupting the data.  

The analogy to C++ is inapt. C++ makes very few guarantees about an
object's lifetime.  That is its design and definition.  SQLite OTOH is
a DBMS.  If used in a way that could cause it to return unreliable
results, its only alternative is to return an error.  

More apt would be if open(2), called with O_EXLOCK twice on the
same file, were to permit the second process to modify the file
as the first one reads it.  In fact, I'd be interested if you could
point to a single standard C library function that, when called
out-of-sequence, doesn't return an error but permits the process to
proceed destructively.  

> > An interface that "requires a detailed understanding of the
> > [internals]" is not simple; in effect the entire SQLite codebase
> > becomes "the interface".  And an undocumented one at that.
> 
> The documented behavior is - if you modify the data as you iterate
> over that same data, the results are unpredictable. 

Where does it say that?  I haven't been able to find any reference to
this behavior.  The documentation I have found contradicts what I
understand you to be saying:

http://www.sqlite.org/c3ref/step.html

"SQLITE_MISUSE means that the this routine was called
inappropriately. Perhaps it was called on a prepared statement that has
already been finalized or on one that had previously returned
SQLITE_ERROR or SQLITE_DONE. Or it could be the case that the same
database connection is being used by two or more threads at the same
moment in time."

There is no "SQLITE_OK_BUT_YOU_ARE_ON_YOUR_OWN" afaik.  

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/8/2013 12:09 AM, James K. Lowden wrote:

On Thu, 04 Jul 2013 16:08:38 -0400
Igor Tandetnik  wrote:


On 7/4/2013 3:15 PM, James K. Lowden wrote:

This weird case is one of (I would say) misusing the connection.
IMO SQLite should return an error if prepare is issued on a
connection for which a previous prepare was not finalized or
reset.  That would forestall discussions like, this and prevent
confusion and error.


SQLite worked this way, years ago. At some point, the restriction was
removed by popular demand. It is hugely convenient to be able to
manipulate one table as you iterate over another.


Why not simply open a separate connection, and allow the library to do
its job?


The library would do its job by preventing the second connection from 
writing to the database while the first connection reads from it. Which, 
of course, would defeat the purpose of the exercise. You seem to assume 
per-table locking; SQLite doesn't do that - a transaction locks the 
whole database file.



No other DBMS I know of willingly
allows a single process to corrupt the results of a SELECT statement
by calling other functions.


How many other embedded DBMS have you worked with? Your indignation 
would be perfectly reasonable when directed towards a traditional 
client-server database - but not really an embedded one.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/8/2013 12:09 AM, James K. Lowden wrote:

In real life, programs are complex, and libraries are misused
(intentionally or not).  SQLite's job is to be a DBMS: to provide
predictable, safe access to the database, defending against errors
foreign and domestic (i.e. hardware errors, OS errors, *and* user
errors).  To permit corrupted data to arrive in the application while a
SELECT statement is being evaluated is to abrogate its most
basic promise.


For SQLite, the "user" is the programmer integrating SQLite library into 
her application, not the end user of that application. I don't believe 
it's SQLite's job to ensure the programmer doesn't shoot herself in the 
foot. After all, you don't expect, say, the C++ compiler to prevent you 
from destroying an object while another part of the program holds a 
pointer to it. This SQLite's behavior is little different.



CS theory tells us that simple interfaces help control complexity.


... but simplicity has to be balanced against functionality. A library 
that does nothing at all is very simple, but also very useless. CS 
practice tells us that there are trade-offs to be made.



An
interface that "requires a detailed understanding of the [internals]"
is not simple; in effect the entire SQLite codebase becomes "the
interface".  And an undocumented one at that.


The documented behavior is - if you modify the data as you iterate over 
that same data, the results are unpredictable. So don't do that, and 
then you won't need detailed understanding of SQLite internals.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Thu, 04 Jul 2013 16:08:38 -0400
Igor Tandetnik  wrote:

> On 7/4/2013 3:15 PM, James K. Lowden wrote:
> > This weird case is one of (I would say) misusing the connection.
> > IMO SQLite should return an error if prepare is issued on a
> > connection for which a previous prepare was not finalized or
> > reset.  That would forestall discussions like, this and prevent
> > confusion and error.
> 
> SQLite worked this way, years ago. At some point, the restriction was 
> removed by popular demand. It is hugely convenient to be able to 
> manipulate one table as you iterate over another. 

Why not simply open a separate connection, and allow the library to do
its job?  

Many, many applications choose to do what you describe, even though
it's usually a terrible choice from the point of view of
transactionality or performance.  No other DBMS I know of willingly
allows a single process to corrupt the results of a SELECT statement
by calling other functions.  

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Thu, 4 Jul 2013 20:36:10 +0100
Simon Slavin  wrote:

> On 4 Jul 2013, at 8:15pm, James K. Lowden 
> wrote:
> 
> > It doesn't usually matter, right?  The fact that the atomic SELECT
> > is spread out across N function calls is irrelevant if they are
> > executed in uninterrupted sequence, because other connections are
> > blocked from modifying the affected tables until the SELECT is
> > finalized.  
> 
> Right.  The supported, cannonical, predictable, deterministic way to
> use _prepare() is to get all the way from _prepare() to _finalize()
> without doing anything but use that statement.  And if you do that it
> doesn?t matter precisely which call does the lock and which one does
> the unlock.

Thank you for the clarification, Simon.  

> In real life SQLite allows many other things to happen.  But
> predicting what happens then requires a detailed understanding of
> SQLite.  

In real life, programs are complex, and libraries are misused
(intentionally or not).  SQLite's job is to be a DBMS: to provide
predictable, safe access to the database, defending against errors
foreign and domestic (i.e. hardware errors, OS errors, *and* user
errors).  To permit corrupted data to arrive in the application while a
SELECT statement is being evaluated is to abrogate its most
basic promise.  

CS theory tells us that simple interfaces help control complexity.  An
interface that "requires a detailed understanding of the [internals]"
is not simple; in effect the entire SQLite codebase becomes "the
interface".  And an undocumented one at that.  

One practical effect of complexity is confusion and error.  Which is
exactly what happened in real life: the OP was surprised that an
application logic error resulted in nondeterministic *library*
behavior.  As well he and thousands like him should be and will be as
long as the situation stands.  

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

Re: [sqlite] Another 2 questions about SQLite

[Kees Nuyt]

On Thu, 4 Jul 2013 15:15:14 -0400, "James K. Lowden"
 wrote:

> This weird case is one of (I would say) misusing the connection.  IMO
> SQLite should return an error if prepare is issued on a connection for
> which a previous prepare was not finalized or reset.  That would
> forestall discussions like, this and prevent confusion and error.  

Not the _prepare() is critical, but the first call of_step() after
_prepare() or _reset().
In fact it is a nice feature to prepare (a whole bunch of) statements in
advance (which runs the optimizer and generates the code for the virtual
machine) and reuse them (with different bindings). 

Every use (AKA statement execution) is: 
loop
_bind()
_step()
... other stuff
endloop
_reset()

At program init: _prepare()
At program exit: _finalize()

Re-prepare() is only necessary when the schema changes, and can be
automatic in some versions of _prepare().
In most applications the schema is quite static.

my EUR 0.02

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/4/2013 3:15 PM, James K. Lowden wrote:

This weird case is one of (I would say) misusing the connection.  IMO
SQLite should return an error if prepare is issued on a connection for
which a previous prepare was not finalized or reset.  That would
forestall discussions like, this and prevent confusion and error.


SQLite worked this way, years ago. At some point, the restriction was 
removed by popular demand. It is hugely convenient to be able to 
manipulate one table as you iterate over another. You do have to be 
careful not to modify the same data you are iterating over, but it's 
still way better than being unable to make any changes at all. We (the 
SQLite users) have seen this movie before, and we didn't like it.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/4/2013 3:15 PM, James K. Lowden wrote:

If two processes sharing a connection...


This is a physical impossibility. There ain't no such thing as two 
processes sharing a connection.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 4 Jul 2013, at 8:15pm, James K. Lowden  wrote:

> It doesn't usually matter, right?  The fact that the atomic SELECT is
> spread out across N function calls is irrelevant if they are executed
> in uninterrupted sequence, because other connections are blocked from
> modifying the affected tables until the SELECT is finalized.  

Right.  The supported, cannonical, predictable, deterministic way to use 
_prepare() is to get all the way from _prepare() to _finalize() without doing 
anything but use that statement.  And if you do that it doesn’t matter 
precisely which call does the lock and which one does the unlock.

In real life SQLite allows many other things to happen.  But predicting what 
happens then requires a detailed understanding of SQLite.  Which means you’re 
starting to mess with things that can change with different versions.  Which is 
to be avoided unless it’s really useful to you.

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

Re: [sqlite] Another 2 questions about SQLite

[Stephan Beal]

On Thu, Jul 4, 2013 at 9:15 PM, James K. Lowden wrote:

> On Mon, 01 Jul 2013 23:59:15 -0400
> Igor Tandetnik  wrote:
>
> > > 2.  Trying to re-use a single connection to issue a second query
> > > before finalizing the first one should return an error
> >
> > No it should not, and does not. Try it.
> >
> > > because the library is being improperly used.
> >
> > That may be, but it doesn't result in an error.
>

FWIW, just a bit of trivia: for MySQL you can't do two concurrent selects
on the same connection. Its over-the-wire protocol does not support
traversing two selects at the same time. Wrappers which allow that hide
that by loading/caching all data from the first select in advance.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Tue, 2 Jul 2013 11:57:43 +0100
Simon Slavin  wrote:

> The SELECT statement is fine and consistent.  But the SELECT
> statement is all of _prepare(), _step(), and _finalize().  Igor is
> pointing out that that if you stop before _step() has returned
> SQLITE_DONE then you haven?t /done/ a SELECT statement, you?ve just
> started one and it is still, in his word, "live".

That's very interesting.  I've spent many hours reading the SQLite
documentation, and just reviewed sqlite3_step().  The only hint I find
to that effect is that sqlite3_step "must be called one or more times
to *evaluate* the statement" (my emphasis).  There is no metion of when
locks are established or how atomicity is enforced.  

It doesn't usually matter, right?  The fact that the atomic SELECT is
spread out across N function calls is irrelevant if they are executed
in uninterrupted sequence, because other connections are blocked from
modifying the affected tables until the SELECT is finalized.  

This weird case is one of (I would say) misusing the connection.  IMO
SQLite should return an error if prepare is issued on a connection for
which a previous prepare was not finalized or reset.  That would
forestall discussions like, this and prevent confusion and error.  

> > So I still don't see how the SELECT could be anything
> > undeterministic. 
> 
> It's not predictable in any simple way because its behaviour changes
> depending on lots of things like whether there?s a covering index,
> and what other threads do things with the database and when. So you
> can't write a simple set of rules that it?ll always follow: it does
> different things under different circumstances.

I take that to mean that the information returned is deterministic, but
how it is executed and the order in which the rows are returned
varies.  Acknowledged.  I just think it's a bad idea, under any
circumstances, to allow the number of columns returned by a SELECT
statement to vary during its "evaluation" between steps.  

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Mon, 01 Jul 2013 23:59:15 -0400
Igor Tandetnik  wrote:

> > 2.  Trying to re-use a single connection to issue a second query
> > before finalizing the first one should return an error
> 
> No it should not, and does not. Try it.
> 
> > because the library is being improperly used.
> 
> That may be, but it doesn't result in an error.

Hmm, is this considered a bug or a feature?  

To me, the SQLite library should enforce deterministic outcomes.  If
two processes sharing a connection can undermine the atomicity of the
exectution of an SQL statement, the library fails that test.  

--jkl

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 2 Jul 2013, at 3:33am, James K. Lowden  wrote:

> Igor Tandetnik  wrote:
> 
>> On 6/30/2013 11:13 PM, Igor Korot wrote:
>>> Well I will use another statement variable as in the sample code.
>>> My questions was: if I call delete on the record that was just
>>> retrieved in another query will this delete affects it?
>> 
>> If you change data that a live SELECT statement is iterating over,
>> the outcome is unpredictable. It may appear to work, it may skip
>> rows, it may return some rows more than once
> 
> Really?  That's not what SERIALIZABLE means.  
> 
> If I have 10-row table T and issue 
> 
>   select * from T;
> 
> and from a different process issue
> 
>   delete * from T;
> 
> you're saying the SELECT statement may fetch any number of rows
> between 0 and 10?  

No.  The SELECT statement is fine and consistent.  But the SELECT statement is 
all of _prepare(), _step(), and _finalize().  Igor is pointing out that that if 
you stop before _step() has returned SQLITE_DONE then you haven’t /done/ a 
SELECT statement, you’ve just started one and it is still, in his word, "live".


On 2 Jul 2013, at 4:30am, James K. Lowden  wrote:

> So I still don't see how the SELECT could be anything undeterministic. 

SELECT is deterministic.  You want to know what it does, read the source code 
and understand the file system protocols it uses.  But it’s not predictable in 
any simple way because its behaviour changes depending on lots of things like 
whether there’s a covering index, and what other threads do things with the 
database and when.  So you can't write a simple set of rules that it’ll always 
follow: it does different things under different circumstances.

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/1/2013 11:30 PM, James K. Lowden wrote:

Restricting ourselves to one process, I can think of two ways that
might go:

1.  With two connections, one connection or the other will wait.
SELECT will return 0 or 10 rows.


Yes, between two connection, normal transaction isolation rules apply, 
whether these connections are established by the same or separate processes.



2.  Trying to re-use a single connection to issue a second query
before finalizing the first one should return an error


No it should not, and does not. Try it.


because the library is being improperly used.


That may be, but it doesn't result in an error.


 SELECT will return 0 or 10 rows, or an error.


Not necessarily. Again, try it, see for yourself.


So I still don't see how the SELECT could be anything undeterministic.


You are laboring under incorrect assumptions, which would allow you to 
justify any conclusion.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Mon, 01 Jul 2013 23:00:27 -0400
Igor Tandetnik  wrote:
> On 7/1/2013 10:33 PM, James K. Lowden wrote:
> > Igor Tandetnik  wrote:
> >> If you change data that a live SELECT statement is iterating over,
> >> the outcome is unpredictable. It may appear to work, it may skip
> >> rows, it may return some rows more than once
> >
> > Really?  That's not what SERIALIZABLE means.

> > you're saying the SELECT statement may fetch any number of rows
> > between 0 and 10?
> 
> "Different process" is a key phrase here. No, the delete statement
> won't be able to commit while the select statement keeps the reader 
> transaction open.

Thank you for clarifying that.  Order is restored in my universe.  ;-)  

> The situation would be different if a single process, while half-way 
> through iterating over the select statement, would issue the delete 
> statement. 

Restricting ourselves to one process, I can think of two ways that
might go:

1.  With two connections, one connection or the other will wait.
SELECT will return 0 or 10 rows.   

2.  Trying to re-use a single connection to issue a second query
before finalizing the first one should return an error, because the
library is being improperly used.  SELECT will return 0 or 10 rows, or
an error.  

So I still don't see how the SELECT could be anything undeterministic.  

--jkl

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/1/2013 10:33 PM, James K. Lowden wrote:

On Sun, 30 Jun 2013 23:27:23 -0400
Igor Tandetnik  wrote:

If you change data that a live SELECT statement is iterating over,
the outcome is unpredictable. It may appear to work, it may skip
rows, it may return some rows more than once


Really?  That's not what SERIALIZABLE means.


SERIALIZABLE is a transaction isolation level. It applies to interaction 
between two transactions on two separate connections. It is perfectly 
normal and expected for a single transaction to be affected by changes 
it itself makes.



If I have 10-row table T and issue

select * from T;

and from a different process issue

delete * from T;

you're saying the SELECT statement may fetch any number of rows
between 0 and 10?


"Different process" is a key phrase here. No, the delete statement won't 
be able to commit while the select statement keeps the reader 
transaction open.


The situation would be different if a single process, while half-way 
through iterating over the select statement, would issue the delete 
statement. This is roughly similar to what the OP is trying to do, as 
far as I can tell.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Sun, 30 Jun 2013 23:27:23 -0400
Igor Tandetnik  wrote:

> On 6/30/2013 11:13 PM, Igor Korot wrote:
> > Well I will use another statement variable as in the sample code.
> > My questions was: if I call delete on the record that was just
> > retrieved in another query will this delete affects it?
> 
> If you change data that a live SELECT statement is iterating over,
> the outcome is unpredictable. It may appear to work, it may skip
> rows, it may return some rows more than once

Really?  That's not what SERIALIZABLE means.  

If I have 10-row table T and issue 

select * from T;

and from a different process issue

delete * from T;

you're saying the SELECT statement may fetch any number of rows
between 0 and 10?  

--jkl

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

Re: [sqlite] Another 2 questions about SQLite

[RSmith]

I wish.
I need to remove those records from another table as well. That's why I
need to retrieve playerid first.
So once again: will the delete affect the outer looping SQLite statement?


I hate to be the one asking the obvious questions, but why are you not using a trigger or or a foreign key to handle this?  Unless 
your "other table" is also in another database, which I doubt due to your other question, then you can still set up a code call-back 
to your own program to do the linked deletion, but as the other Igor noted, I think you are trying to overcomplicate things.


If you can give us a more detailed view of your database and exactly how and when linked fields need to be 
deleted/updated/whatever,  we'd be able to suggest an uncomplicated clear Foreign key relationship or Trigger method that should 
achieve it with the maximum efficiency.


Have a great day!




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

Re: [sqlite] Another 2 questions about SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/06/13 20:17, Igor Korot wrote:
> Well I'm not familiar with SQLite internals, but one thing for sure: 
> why go thru the process if you can avoid it?

You are trying to do premature optimization.

Note the "Lite" in the SQLite.  It is already designed and implemented to
do the least amount of work - you haven't found a way to do less because
SQLite is already doing the least!

It only calculates the next (or first) result row when you ask for it, and
not before.

Roger

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

iEYEARECAAYFAlHRALsACgkQmOOfHg372QSjJgCgv0zNhFZRdOaR4gzaq4b6Qw9B
6HQAoMZcfyORD9++LT0iTJCT0Crs+pSn
=xURz
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 6/30/2013 11:17 PM, Igor Korot wrote:

On Sun, Jun 30, 2013 at 7:47 PM, Igor Tandetnik  wrote:


On 6/30/2013 10:27 PM, Igor Korot wrote:


1. I'm trying to minimize the number of requests I'm doing to the DB. What
I need is a way to count the number of rows that the query return to me
prior to going thru the "sqlite3_step()".
If this number is 0, I want to skip the processing and just return.



How is this different from just calling sqlite3_step, seeing it return
SQLITE_DONE right away, and getting out of the loop?



Well I'm not familiar with SQLite internals, but one thing for sure:
why go thru the process if you can avoid it?


You can't.


Just run this statement;

DELETE FROM players WHERE players.isnew="1";

You are making it way too complicated.



I wish.
I need to remove those records from another table as well.


delete from AnotherTable where playerId in
(select playerid from players where isnew = '1');

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 1 Jul 2013, at 4:13am, Igor Korot  wrote:

> Well I will use another statement variable as in the sample code.
> My questions was: if I call delete on the record that was just retrieved in
> another query will this delete affects it?

I can’t say a definite 'yes’ because it depends on all sorts of details like 
whether there is a spanning index.  But the canonical answer to your question 
is that you shouldn’t do that.  Generally speaking, unless you know intimate 
details of how SQLite works, treat a _prepare() as a lock on the file which 
isn’t unlocked until you do _reset() or _finalize().

If a query is still open (i.e. you didn’t run _reset() or _finalize() on it) 
and you mess with a table that query consults, the results are not predictable.

For instance, suppose your query is

SELECT hometeam,awayteam FROM matches WHERE homescore = awayscore

and you did

DELETE matches WHERE rowid=163

there can be all sorts of problems if the SELECT statement is still pointing at 
row 163 when you delete it.  It might not know how to get from a non-existant 
row to the next valid row, for instance.

Basically, no, don’t do that.  It’s safe to mess with /another/ table, one 
which isn’t mentioned in your SELECT.  But to mess with the table you’re 
looking at make a list of rows to delete and delete them when your SELECT is 
finished.  Or write a WHERE clause for your DELETE that figures out which rows 
to delete for you.

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 6/30/2013 11:13 PM, Igor Korot wrote:

Well I will use another statement variable as in the sample code.
My questions was: if I call delete on the record that was just retrieved in
another query will this delete affects it?


If you change data that a live SELECT statement is iterating over, the 
outcome is unpredictable. It may appear to work, it may skip rows, it 
may return some rows more than once, it may report rows out of order 
(for queries with ORDER BY clause). Don't do that.

--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Igor Korot]

On Sun, Jun 30, 2013 at 7:47 PM, Igor Tandetnik  wrote:

> On 6/30/2013 10:27 PM, Igor Korot wrote:
>
>> 1. I'm trying to minimize the number of requests I'm doing to the DB. What
>> I need is a way to count the number of rows that the query return to me
>> prior to going thru the "sqlite3_step()".
>> If this number is 0, I want to skip the processing and just return.
>>
>
> How is this different from just calling sqlite3_step, seeing it return
> SQLITE_DONE right away, and getting out of the loop?


Well I'm not familiar with SQLite internals, but one thing for sure:
why go thru the process if you can avoid it?


>
>
>   check the number of rows returned by the query
>> if( numRows == 0 )
>>   return;
>> else
>> {
>> sqlite3_step( stmt );
>> sqlite3_finalize( stmt );
>> }
>>
>
> This leaks a statement handle. Make it
>
> if (sqlite_step(stmt) == SQLITE_ROW) {
>   // Process the row
>
> }
> sqlite3_finalize( stmt );
>

Yes, it just a sample code. ;-)


>
>  Is there such a function?
>>
>
> No there is not.
>
>
>  2. Considering the same code above, if I want to delete this row, I will
>> need another statement variable. But will it screw up the original select
>> statement? Something like this:
>>
>
> Just run this statement;
>
> DELETE FROM players WHERE players.isnew="1";
>
> You are making it way too complicated.
>

I wish.
I need to remove those records from another table as well. That's why I
need to retrieve playerid first.
So once again: will the delete affect the outer looping SQLite statement?

Thank you.


> --
> Igor Tandetnik
>
>
> __**_
> 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] Another 2 questions about SQLite

[Igor Korot]

Simon,

On Sun, Jun 30, 2013 at 7:41 PM, Simon Slavin  wrote:

>
> On 1 Jul 2013, at 3:27am, Igor Korot  wrote:
>
> > 1. I'm trying to minimize the number of requests I'm doing to the DB.
> What
> > I need is a way to count the number of rows that the query return to me
> > prior to going thru the "sqlite3_step()".
>
> Sorry, not possible.  SQLite does not process the entire SELECT when you
> do _prepare() and /return/ the results row by row.  Each _step() command
> tells SQLite to figure out whether there is another row and, if so, what it
> is. The first time you know whether the SELECT found any rows is when you
> look at the result of the first _step().
>
> A solution is to do a
>
> SELECT count(*) WHERE <...>
>
> first, then use the result of that to figure out whether you need to do
> the proper SELECT or not.  Which is what you were probably doing before.
>

Why life is so complicated? (C) ;-)


>
> > 2. Considering the same code above, if I want to delete this row, I will
> > need another statement variable. But will it screw up the original select
> > statement?
>
> As you suspected, it is possible that it will.  Try not to modify the
> result set while you have a statement prepared.  Either _reset() or
> _finalize() it first.
>

Well I will use another statement variable as in the sample code.
My questions was: if I call delete on the record that was just retrieved in
another query will this delete affects it?

You can also refer to my Igor's reply.

Thank you.


> 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] Another 2 questions about SQLite

[Igor Tandetnik]

On 6/30/2013 10:27 PM, Igor Korot wrote:

1. I'm trying to minimize the number of requests I'm doing to the DB. What
I need is a way to count the number of rows that the query return to me
prior to going thru the "sqlite3_step()".
If this number is 0, I want to skip the processing and just return.


How is this different from just calling sqlite3_step, seeing it return 
SQLITE_DONE right away, and getting out of the loop?



 check the number of rows returned by the query
if( numRows == 0 )
  return;
else
{
sqlite3_step( stmt );
sqlite3_finalize( stmt );
}


This leaks a statement handle. Make it

if (sqlite_step(stmt) == SQLITE_ROW) {
  // Process the row
}
sqlite3_finalize( stmt );


Is there such a function?


No there is not.


2. Considering the same code above, if I want to delete this row, I will
need another statement variable. But will it screw up the original select
statement? Something like this:


Just run this statement;

DELETE FROM players WHERE players.isnew="1";

You are making it way too complicated.
--
Igor Tandetnik

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

Re: [sqlite] Another 2 questions about SQLite

[Simon Slavin]

On 1 Jul 2013, at 3:27am, Igor Korot  wrote:

> 1. I'm trying to minimize the number of requests I'm doing to the DB. What
> I need is a way to count the number of rows that the query return to me
> prior to going thru the "sqlite3_step()".

Sorry, not possible.  SQLite does not process the entire SELECT when you do 
_prepare() and /return/ the results row by row.  Each _step() command tells 
SQLite to figure out whether there is another row and, if so, what it is. The 
first time you know whether the SELECT found any rows is when you look at the 
result of the first _step().

A solution is to do a

SELECT count(*) WHERE <...>

first, then use the result of that to figure out whether you need to do the 
proper SELECT or not.  Which is what you were probably doing before.

> 2. Considering the same code above, if I want to delete this row, I will
> need another statement variable. But will it screw up the original select
> statement?

As you suspected, it is possible that it will.  Try not to modify the result 
set while you have a statement prepared.  Either _reset() or _finalize() it 
first.

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