Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

2014-01-02 Thread Yuriy Kaminskiy
Richard Hipp wrote:
> On Thu, Jan 2, 2014 at 3:25 PM, Yuriy Kaminskiy  wrote:
> 
>> Richard Hipp wrote:
>>> Please verify that the alternative optimization checked-in at
>>> http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that
>>> you identify below.  Tnx.
>> Maybe I overlooked something, but from first look it cannot handle
>> placeholders and constant functions, and my patch does?
> 
> OK.  How about http://www.sqlite.org/src/info/9d05777fe2 - does it work
> better for you?

Yes, thanks :-)

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


Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

2014-01-02 Thread Richard Hipp
On Thu, Jan 2, 2014 at 3:25 PM, Yuriy Kaminskiy  wrote:

> Richard Hipp wrote:
> > Please verify that the alternative optimization checked-in at
> > http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that
> you
> > identify below.  Tnx.
>
> Maybe I overlooked something, but from first look it cannot handle
> placeholders
> and constant functions, and my patch does?
>

OK.  How about http://www.sqlite.org/src/info/9d05777fe2 - does it work
better for you?

-- 
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] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

2014-01-02 Thread Yuriy Kaminskiy
Richard Hipp wrote:
> Please verify that the alternative optimization checked-in at
> http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that you
> identify below.  Tnx.

Maybe I overlooked something, but from first look it cannot handle placeholders
and constant functions, and my patch does?

(Besides, with b7e39851a7 "Special case" code remains expensive no-op.)

(Probably your patch handles some *other* cases my patch does not. Probably,
they can be applied both :-))

===

EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;

3.8.2 + My patch series:
sqlite> EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;
0|Trace|0|0|0||00|
1|Goto|0|19|0||00|
2|IfNot|1|17|1||00| <<< ?1 check moved out of loop
3|IfNot|2|17|1||00| <<< ?2 check moved out of loop
4|IfNot|3|17|1||00| <<< ?3 check moved out of loop
5|IfNot|4|17|1||00| <<< ?4 check moved out of loop
6|OpenRead|0|2|0|3|00|
7|Rewind|0|17|0||00|
8|Column|0|0|5||00|
9|IfNot|5|16|1||00| <<< only i
10|Column|0|1|6||00|
11|IfNot|6|16|1||00|<<< and j check in the inner loop
12|Column|0|0|7||00|
13|Column|0|1|8||00|
14|Column|0|2|9||00|
15|ResultRow|7|3|0||00|
16|Next|0|8|0||01|
17|Close|0|0|0||00|
18|Halt|0|0|0||00|
19|Transaction|0|0|0||00|
20|VerifyCookie|0|1|0||00|
21|TableLock|0|2|0|t|00|
22|Variable|1|1|0||00|
23|Variable|2|2|0||00|
24|Variable|3|3|0||00|
25|Variable|4|4|0||00|
26|Goto|0|2|0||00|

3.8.2 + b7e39851a7 (but without any other commits from trunk):

sqlite> EXPLAIN SELECT * FROM t WHERE ? AND ? AND i AND ? AND j AND ?;
0|Trace|0|0|0||00|
1|Goto|0|19|0||00|
2|OpenRead|0|2|0|3|00|
3|Rewind|0|17|0||00|
4|IfNot|1|16|1||00| <<< ?1 check in the inner loop
5|IfNot|2|16|1||00| <<< ?2 check in the inner loop
6|Column|0|0|3||00|
7|IfNot|3|16|1||00| <<< ?3 check in the inner loop
8|IfNot|4|16|1||00| <<< ?4 check in the inner loop
9|Column|0|1|5||00|
10|IfNot|5|16|1||00|
11|IfNot|6|16|1||00|
12|Column|0|0|7||00|
13|Column|0|1|8||00|
14|Column|0|2|9||00|
15|ResultRow|7|3|0||00|
16|Next|0|4|0||01|
17|Close|0|0|0||00|
18|Halt|0|0|0||00|
19|Transaction|0|0|0||00|
20|VerifyCookie|0|1|0||00|
21|TableLock|0|2|0|t|00|
22|Variable|1|1|0||00|
23|Variable|2|2|0||00|
24|Variable|3|4|0||00|
25|Variable|4|6|0||00|
26|Goto|0|2|0||00|



EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;

My patch:
sqlite> EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;
0|Trace|0|0|0||00|
1|Goto|0|14|0||00|
2|Ne|2|12|1||6a|  <<< `= '01-01'` moved out of loop
3|OpenRead|0|2|0|3|00|
4|Rewind|0|12|0||00|
5|Column|0|0|3||00|
6|IfNot|3|11|1||00|   <<< only [i] checked in the inner loop
7|Column|0|0|4||00|
8|Column|0|1|5||00|
9|Column|0|2|6||00|
10|ResultRow|4|3|0||00|
11|Next|0|5|0||01|
12|Close|0|0|0||00|
13|Halt|0|0|0||00|
14|Transaction|0|0|0||00|
15|VerifyCookie|0|1|0||00|
16|TableLock|0|2|0|t|00|
17|String8|0|9|0|now|00|
18|Function|1|9|7|date(-1)|01|
19|Integer|6|8|0||00|
20|Function|3|7|1|substr(2)|02|
21|String8|0|2|0|01-01|00|
22|Goto|0|2|0||00|

b7e39851a7:

sqlite> EXPLAIN SELECT * FROM t WHERE SUBSTR(DATE('now'),6) = '01-01' AND i;
0|Trace|0|0|0||00|
1|Goto|0|14|0||00|
2|OpenRead|0|2|0|3|00|
3|Rewind|0|12|0||00|
4|Ne|2|11|1||6a| ` = '01-01'` check in the inner loop
5|Column|0|0|3||00|
6|IfNot|3|11|1||00|
7|Column|0|0|4||00|
8|Column|0|1|5||00|
9|Column|0|2|6||00|
10|ResultRow|4|3|0||00|
11|Next|0|4|0||01|
12|Close|0|0|0||00|
13|Halt|0|0|0||00|
14|Transaction|0|0|0||00|
15|VerifyCookie|0|1|0||00|
16|TableLock|0|2|0|t|00|
17|String8|0|9|0|now|00|
18|Function|1|9|7|date(-1)|01|
19|Integer|6|8|0||00|
20|Function|3|7|1|substr(2)|02|
21|String8|0|2|0|01-01|00|
22|Goto|0|2|0||00|

> On Thu, Jan 2, 2014 at 9:25 AM, Yuriy Kaminskiy  wrote:
> 
>> On 2013/11/04 Yuriy Kaminskiy wrote:
>>> On 2012/04/08 Yuriy Kaminskiy wrote:
 On 2011/12/06 Yuriy Kaminskiy wrote:
> On 2011/11/03 Yuriy Kaminskiy wrote:
>> On 2011/11/23 Yuriy Kaminskiy wrote:
>>> On 2011/10/23 Yuriy Kaminskiy wrote:
 When WHERE condition is constant, there are no need to evaluate and
>> check it for
 each row. It works, but only partially:
>>> ...
 [In fact, you can move out out loop not only *whole* constant
>> WHERE, but also
 all constant AND terms of WHERE, like this:
 SELECT * FROM t WHERE const1 AND notconst AND const2 ->
 SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND
>> const2
 I'll take a shot on that later.]
>>> Here it goes.
>>>
>>> Prerequisite: previous patch.
>>> Passes quick regression test (make test).
>>> Possible problem: short-circuits evaluation. Should not be a
>> problem, IMO, as only
>>> constants references? 

Re: [sqlite] SQLite 2013 retrospective

2014-01-02 Thread big stone
Whish list for 2014 =
. a simplified implementation of "Common Table Expressions",
. amazing SQlite4 performance when in ":memory:".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLITE_OPEN_NOMUTEX

2014-01-02 Thread Richard Hipp
On Thu, Jan 2, 2014 at 1:23 PM, Prakash Reddy Bande wrote:

> Hi,
>
> As per documentation opening a database with SQLITE_OPEN_NOMUTEX flag
> opens it in multi-threaded mode.
> http://www.sqlite.org/threadsafe.html says In this mode, SQLite can be
> safely used by multiple threads provided that no single database connection
> is used simultaneously in two or more threads.
>
> I just wanted to be certain that this is true in case of multi-cpu
> machines and cache-coherence related issues won't surface.
>

Confirmed.  As long as you don't use the same database connection on
multiple threads/cores at the same time, you will be fine.

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

2014-01-02 Thread Prakash Reddy Bande
Hi,

As per documentation opening a database with SQLITE_OPEN_NOMUTEX flag opens it 
in multi-threaded mode.
http://www.sqlite.org/threadsafe.html says In this mode, SQLite can be safely 
used by multiple threads provided that no single database connection is used 
simultaneously in two or more threads.

I just wanted to be certain that this is true in case of multi-cpu machines and 
cache-coherence related issues won't surface.

Regards,

Prakash Bande
Director - Hyperworks Enterprise Software
Altair Eng. Inc.
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292

My Secure Drop Box: https://ftam1.altair.com/dropbox/~Ursfnk

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


Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

2014-01-02 Thread Richard Hipp
Please verify that the alternative optimization checked-in at
http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that you
identify below.  Tnx.


On Thu, Jan 2, 2014 at 9:25 AM, Yuriy Kaminskiy  wrote:

> On 2013/11/04 Yuriy Kaminskiy wrote:
> > On 2012/04/08 Yuriy Kaminskiy wrote:
> >> On 2011/12/06 Yuriy Kaminskiy wrote:
> >>> On 2011/11/03 Yuriy Kaminskiy wrote:
>  On 2011/11/23 Yuriy Kaminskiy wrote:
> > On 2011/10/23 Yuriy Kaminskiy wrote:
> >> When WHERE condition is constant, there are no need to evaluate and
> check it for
> >> each row. It works, but only partially:
> > ...
> >> [In fact, you can move out out loop not only *whole* constant
> WHERE, but also
> >> all constant AND terms of WHERE, like this:
> >> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
> >> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND
> const2
> >> I'll take a shot on that later.]
> > Here it goes.
> >
> > Prerequisite: previous patch.
> > Passes quick regression test (make test).
> > Possible problem: short-circuits evaluation. Should not be a
> problem, IMO, as only
> > constants references? Please verify.
>  Ping.
> >>> Ping.
> >> Ping.
> >> For convenience all 3 patches collected below (needed no change for
> 3.7.11).
> >
> > Ping. Over 2 years passed since this patch series was first posted.
> > Updated patch series for 3.8.1 below.
>
> Ping. Same patch series refreshed for 3.8.2 with minor change.
> --
> The author or authors of this code dedicate any and all copyright interest
> in this code to the public domain. We make this dedication for the benefit
> of the public at large and to the detriment of our heirs and successors.
> We intend this dedication to be an overt act of relinquishment in
> perpetuity
> of all present and future rights to this code under copyright law.
>
> Signed-off-by: Yuriy M. Kaminskiy 
>
> Part 1: Move whereSplit() to unbreak constant condition elimination.
>
> (this is very obvious fix: only effect of "Special case" is pWhere
> assignment,
> but pWhere value is only used in whereSplit call *above* of "Special
> case"; this
> whole "Special case" is expensive no-op now)
>
> Test case:
>CREATE TABLE t (i, j, k);
>EXPLAIN SELECT * FROM t WHERE 11;
>
> Index: sqlite3-3.8.2/src/where.c
> ===
> --- sqlite3-3.8.2.orig/src/where.c  2014-01-02 16:18:24.0 +0400
> +++ sqlite3-3.8.2/src/where.c   2014-01-02 17:10:24.0 +0400
> @@ -5423,7 +5423,6 @@ WhereInfo *sqlite3WhereBegin(
>*/
>initMaskSet(pMaskSet);
>whereClauseInit(>sWC, pWInfo);
> -  whereSplit(>sWC, pWhere, TK_AND);
>sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto
> */
>
>/* Special case: a WHERE clause that is constant.  Evaluate the
> @@ -5434,6 +5433,8 @@ WhereInfo *sqlite3WhereBegin(
>  pWhere = 0;
>}
>
> +  whereSplit(>sWC, pWhere, TK_AND);
> +
>/* Special case: No FROM clause
>*/
>if( nTabList==0 ){
> ===
>
> Part 2: optimize "WHERE const AND notconst" too
>
> (trivial generalization of "Special case")
>
> Test case:
>EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;
>
> Index: sqlite3-3.8.1/src/where.c
> ===
> --- sqlite3-3.8.1.orig/src/where.c  2013-11-03 23:27:05.0 +0400
> +++ sqlite3-3.8.1/src/where.c   2013-11-03 23:27:59.0 +0400
> @@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(
>
>whereSplit(>sWC, pWhere, TK_AND);
>
> +  {
> +/* Move const in "WHERE const AND notconst" out of internal loop */
> +int i, j;
> +WhereClause * const pWC = >sWC;
> +
> +for(j=i=0; inTerm; i++){
> +  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
> +sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
> SQLITE_JUMPIFNULL);
> +continue;
> +  }
> +  if( j!=i )
> +pWC->a[j]=pWC->a[i];
> +  j++;
> +}
> +/* XXX if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0]));
> */
> +pWC->nTerm -= i-j;
> +  }
> +
>/* Special case: No FROM clause
>*/
>if( nTabList==0 ){
> ===
> Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
>
> Index: sqlite3-3.8.2/src/where.c
> ===
> --- sqlite3-3.8.2.orig/src/where.c  2014-01-02 17:10:47.0 +0400
> +++ sqlite3-3.8.2/src/where.c   2014-01-02 17:11:54.0 +0400
> @@ -5423,20 +5423,13 @@ WhereInfo *sqlite3WhereBegin(
>*/
>initMaskSet(pMaskSet);
>whereClauseInit(>sWC, pWInfo);
> -  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto
> */
> -
> -  /* Special case: a 

Re: [sqlite] [sqlite-dev] sqlite3 db is locked

2014-01-02 Thread Yuriy Kaminskiy
Simon Slavin wrote:
> On 2 Jan 2014, at 2:57pm, Yuriy Kaminskiy  wrote:
> 
>> Simon Slavin wrote:
>>> sqlite3_busy_timeout()
>> Waiting for timeout *cannot* fix any errors that can trigger failure in
>> sqlite3_close. Those are *program logic* errors.
> 
> I am not trying to fix your program logic errors.  I am telling you how to 
> replace the code in your program which doesn't work (the way you handle 
> SQLITE_BUSY in the code you wrote) with code which does work (SQLite's own 
> internal way of handling a busy database).

SQLite internal way of handling busy database DOES NOT handle case of "there are
unfinalized statement(s) left at the moment of sqlite3_close call" (resulting in
SQLITE_BUSY error).

SQLite won't even TRY to retry sqlite3_close internally, no matter if
busy_timeout was set or not (and this is not a bug; it would be *stupid* waste
of time if it would).

On other hand, OP's code (attempt to enumerate and finalize all statements and
retry close()) *can* help (although, it hides real error [lack of statement
finalization] and can result in SIGSEGV/heap corruption if those
forcibly-finalized statements will be used anyhow elsewhere).

(Besides, it is not "my program", it is OP's program).

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


Re: [sqlite] "Common Table Expression"

2014-01-02 Thread big stone
(sorry keyboard fall on the floor)

Now :
- I wouldn't let someone use SQL without allowing him to use CTE,
- I need to use ubiquitous SQL motors, which are only TWO on windows
(Access and SQLite) , and they still don't have CTE.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] "Common Table Expression"

2014-01-02 Thread big stone
Hello,

When bigger brains created CTEs in SQL:99, I suppose they discussed a long
moment the technical merit of CTEs.

In my own experience :
- the maintenance burden of my queries dropped significantly because of
them,
- I stopped harrassing (myself or a central database administrator) for
views (creation or deletion).

Now :
- I wouldn't let someone use SQL without allowing him to use CTE,
- I need to use ubiquitous SQL motors, which are only TWO :
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] [sqlite-dev] sqlite3 db is locked

2014-01-02 Thread Simon Slavin

On 2 Jan 2014, at 2:57pm, Yuriy Kaminskiy  wrote:

> Simon Slavin wrote:
>> sqlite3_busy_timeout()
> 
> Waiting for timeout *cannot* fix any errors that can trigger failure in
> sqlite3_close. Those are *program logic* errors.

I am not trying to fix your program logic errors.  I am telling you how to 
replace the code in your program which doesn't work (the way you handle 
SQLITE_BUSY in the code you wrote) with code which does work (SQLite's own 
internal way of handling a busy database).

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


Re: [sqlite] [sqlite-dev] sqlite3 db is locked

2014-01-02 Thread Yuriy Kaminskiy
Simon Slavin wrote:
> On 1 Jan 2014, at 7:43am, Alexander Syvak  wrote:
> 
>> The code in function from the 1st e-mail is used before exiting, so the
>> sqlite3_close is called in fact.
> 
> Please do not cross-post between sqlite-dev@ and sqlite@.  If you need to 
> move from one to the other, start a new thread.
> 
> I can't find the post that started the thread but if you're having trouble 
> using sqlite3_close() then  the database is probably not being closed.  
> Please check the result returned and log an error if it's not SQLITE_OK so 
> you can be sure whether it worked or not.
> 
> If _close() really isn't working for you it's probably because you have an 
> unfinalized statement.  This can happen if sqlite3_finalize() fails (though 
> it really shouldn't) So check and log the result code from sqlite3_finalize() 
> too.
> 
>>>   while ( (rc = sqlite3_close(db)) == SQLITE_BUSY)
> 
> 
> I'm not happy about this.  SQLITE_BUSY should not be a reason to start 
> running other statements.  It should be a reason to back off and announce 
> failure to the user.  To avoid getting _BUSY and then having to write a 
> handler yourself set a good timeout (ten seconds ?) using
> 
> sqlite3_busy_timeout()

Waiting for timeout *cannot* fix any errors that can trigger failure in
sqlite3_close. Those are *program logic* errors.

> after the database is opened.  From then on you can treat SQLITE_BUSY the 
> same as other failure results.

But you *cannot*.
Sure, there are cases when longer timeout can help. But not in all.
Even with larger timeout, SQLITE_BUSY often requires some or other special
handling (ROLLBACK transaction *and retry* in case of step()/exec(), detect
unfinished statements in case of close(), etc).
(Sometimes you can avoid some special cases by using BEGIN IMMEDIATE,
close_v2(), etc appropriately).

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


Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

2014-01-02 Thread Woody Wu
Hi, List

To make it easy to review, I've make the test code as an single c file and
removed some unrelated code from the previous version.  Could you so kind
to point out potential problems in it, or tell me what else things I could
get to check in the code or in my environment.

https://www.dropbox.com/s/y6rtajv2j9burag/sqlitecs.c

Even more directly, could anyone can build from this source an arm eabi
binary and send to me? Also should includes the libsqlite so files. If I
can run it without a problem, then it's a compiler problem.

Thanks in advance.


On Thursday, 2 January 2014, Yuriy Kaminskiy wrote:

> Woody Wu wrote:
> > Hi, Simon
> >
> > I upload the source code onto my dropbox:
> > https://www.dropbox.com/s/9shhshi0wn3e717/downloadfile.c  Please have a
> > look at it.
> >
> > The same test program run without a problem on my pc Linux after complied
> > natively.  But I think I should not dout my cross-compiler, which is
> > CodeBench ARM eabi compiler. With the same complier and the toolchain, I
> > have been buit a whole target ARM system including kernel, 1000 open
> source
> > applications, even including a tiny X window.
>
> Well, compiler bugs are sometimes very rarely triggered, and by completely
> innocent code, so I would not exclude this possibility.
> FWIW, I don't see anything obviously broken/sigsegv-worthy in above test
> program
> [assuming missing headers contained something like
>
> int sql_exec_v2(sqlite3 *conn, const char *sql) {
> // note: sql_exec_v2 expected to return SQLITE_DONE on success
> // sqlite3_exec returns SQLITE_OK on success
> int sqlerr, ret;
> sqlite3_stmt *stmt;
> sqlerr = sqlite3_prepare_v2(conn, sql, -1, , NULL);
> if (sqlerr != SQLITE_OK) return sqlerr;
> ret = sqlite3_step(stmt);
> sqlerr = sqlite3_finalize(stmt);
> if (sqlerr != SQLITE_OK) return sqlerr;
> return ret;
> }
> int timespec_diff_ms(const struct timespec *ts1, const struct timespec
> *ts2) {
> return (ts2->tv_sec - ts1->tv_sec)*1000 +
> (ts2->tv_nsec-ts1->tv_nsec)/100;
> }
> #define inst_signal_handler(SIGNAL,HNDL,FOO) signal((SIGNAL),(HNDL))
>
> ], but as this is "impossible error" it would make sense to add error
> checking
> for *everything*, including "impossible errors".
>
> > On Tuesday, 31 December 2013, Simon Slavin wrote:
> >
> >> On 31 Dec 2013, at 8:41am, Woody Wu 
> >> >
> >> wrote:
> >>
> >>> Attached is the test program writting in C.
> >> Sorry, but attachments don't work here.  If your program is short,
> please
> >> post it as text.  If not, please put it on a web site somewhere.
> >>
> >>> Anyway, all above errors looks so strange. And, these operations I
> >> talking
> >>> about are so basic and my real application (another bigger one) really
> >>> depends on these.
> >> You should not be able to make SQLite corrupt its database that easily.
> >>
> >>> Pleaes be kindly to check my test program.
> >> Can you run your program on the computer you used to send that email
> >> message and tell us whether it had the same problem ?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

2014-01-02 Thread Yuriy Kaminskiy
On 2013/11/04 Yuriy Kaminskiy wrote:
> On 2012/04/08 Yuriy Kaminskiy wrote:
>> On 2011/12/06 Yuriy Kaminskiy wrote:
>>> On 2011/11/03 Yuriy Kaminskiy wrote:
 On 2011/11/23 Yuriy Kaminskiy wrote:
> On 2011/10/23 Yuriy Kaminskiy wrote:
>> When WHERE condition is constant, there are no need to evaluate and 
>> check it for
>> each row. It works, but only partially:
> ...
>> [In fact, you can move out out loop not only *whole* constant WHERE, but 
>> also
>> all constant AND terms of WHERE, like this:
>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>> I'll take a shot on that later.]
> Here it goes.
>
> Prerequisite: previous patch.
> Passes quick regression test (make test).
> Possible problem: short-circuits evaluation. Should not be a problem, 
> IMO, as only
> constants references? Please verify.
 Ping.
>>> Ping.
>> Ping.
>> For convenience all 3 patches collected below (needed no change for 3.7.11).
> 
> Ping. Over 2 years passed since this patch series was first posted.
> Updated patch series for 3.8.1 below.

Ping. Same patch series refreshed for 3.8.2 with minor change.
-- 
The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Part 1: Move whereSplit() to unbreak constant condition elimination.

(this is very obvious fix: only effect of "Special case" is pWhere assignment,
but pWhere value is only used in whereSplit call *above* of "Special case"; this
whole "Special case" is expensive no-op now)

Test case:
   CREATE TABLE t (i, j, k);
   EXPLAIN SELECT * FROM t WHERE 11;

Index: sqlite3-3.8.2/src/where.c
===
--- sqlite3-3.8.2.orig/src/where.c  2014-01-02 16:18:24.0 +0400
+++ sqlite3-3.8.2/src/where.c   2014-01-02 17:10:24.0 +0400
@@ -5423,7 +5423,6 @@ WhereInfo *sqlite3WhereBegin(
   */
   initMaskSet(pMaskSet);
   whereClauseInit(>sWC, pWInfo);
-  whereSplit(>sWC, pWhere, TK_AND);
   sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */

   /* Special case: a WHERE clause that is constant.  Evaluate the
@@ -5434,6 +5433,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(>sWC, pWhere, TK_AND);
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===

Part 2: optimize "WHERE const AND notconst" too

(trivial generalization of "Special case")

Test case:
   EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;

Index: sqlite3-3.8.1/src/where.c
===
--- sqlite3-3.8.1.orig/src/where.c  2013-11-03 23:27:05.0 +0400
+++ sqlite3-3.8.1/src/where.c   2013-11-03 23:27:59.0 +0400
@@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(>sWC, pWhere, TK_AND);

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int i, j;
+WhereClause * const pWC = >sWC;
+
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+/* XXX if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0])); */
+pWC->nTerm -= i-j;
+  }
+
   /* Special case: No FROM clause
   */
   if( nTabList==0 ){
===
Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.

Index: sqlite3-3.8.2/src/where.c
===
--- sqlite3-3.8.2.orig/src/where.c  2014-01-02 17:10:47.0 +0400
+++ sqlite3-3.8.2/src/where.c   2014-01-02 17:11:54.0 +0400
@@ -5423,20 +5423,13 @@ WhereInfo *sqlite3WhereBegin(
   */
   initMaskSet(pMaskSet);
   whereClauseInit(>sWC, pWInfo);
-  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
-
-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(>sWC, pWhere, TK_AND);
+  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */

   {
-/* Move const in "WHERE const AND notconst" out 

[sqlite] SQLite3 and EINTR

2014-01-02 Thread Török Edwin
Hi,

If I send signals to a process that is running an sqlite query on a DB in WAL 
mode I get an SQLITE_BUSY error.
I did set a busy handler, and the timeout hasn't elapsed yet, so it appears 
that EINTR is treated as an error and not retried, although
I haven't tracked down exactly where. I noticed that unixFileLock doesn't 
handle EINTR though.

Testcase for SQLite 3.8.2 on Linux amd64:
$ gcc eintr.c -lsqlite3 -o eintr && ./eintr
Query 'INSERT INTO tbl(x) VALUES(3)' failed: database is locked [after 0.025 
sec]
^^^ expected to see >20s here.

#include 
#include 
#include 
#include 
#include 
#include 

static int run(sqlite3 *db, const char *sql)
{
struct timeval tv0, tv1;
char *errmsg = NULL;
gettimeofday(, NULL);
/* should use prepared statements for speed, but this is just for a 
testcase */
if (sqlite3_exec(db, sql, NULL, NULL, )) {
gettimeofday(, NULL);
double dt = tv1.tv_sec - tv0.tv_sec + (tv1.tv_usec - 
tv0.tv_usec)/100.0;
fprintf(stderr,"Query '%s' failed: %s [after %.3f sec]\n", sql, errmsg, 
dt);
sqlite3_free(errmsg);
return -1;
}
return 0;
}

static void handler(int sig)
{
}

int main(int argc, char *argv[])
{
sqlite3 *db = NULL;
char *errmsg = NULL;
int ret = 1;
pid_t parent, pid;
struct timeval tv0, tv1;

signal(SIGUSR2, handler);

parent = getpid();
pid = fork();
if (pid < 0) {
perror("fork failed");
return 2;
}
if (!pid) {
gettimeofday(, NULL);
/* child */
for(;;) {
 /* if you comment out the kill() line, then the queries fail 
showing >20s wait times */
kill(parent, SIGUSR2);
usleep(50);
gettimeofday(, NULL);
if ((tv1.tv_sec - tv0.tv_sec) * 1000 + (tv1.tv_usec - 
tv0.tv_usec)/1000 > 5000)
break;
}
exit(0);
}
pid = fork();
do {
int i;
if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, NULL)) {
fprintf(stderr,"failed to open DB: %s", sqlite3_errmsg(db));
break;
}
sqlite3_busy_timeout(db, 2);
if (run(db, "CREATE TABLE IF NOT EXISTS tbl(x INTEGER)"))
break;
if (run(db, "PRAGMA journal_mode=WAL"))
break;
if (run(db, "PRAGMA synchronous=normal"))
break;
for (i=0;i<1000;i++) {
if (run(db, "INSERT INTO tbl(x) VALUES(3)"))
break;
if (run(db, "UPDATE tbl set x = x+1"))
break;
}
if (i == 1000)
ret = 0;
} while(0);
if (sqlite3_close(db)) {
fprintf(stderr,"Failed to close DB");
ret = 1;
}
return ret;
}

P.S: thank you for the quick fix for sqlite3_randomness!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

2014-01-02 Thread Yuriy Kaminskiy
Woody Wu wrote:
> Hi, Simon
> 
> I upload the source code onto my dropbox:
> https://www.dropbox.com/s/9shhshi0wn3e717/downloadfile.c  Please have a
> look at it.
> 
> The same test program run without a problem on my pc Linux after complied
> natively.  But I think I should not dout my cross-compiler, which is
> CodeBench ARM eabi compiler. With the same complier and the toolchain, I
> have been buit a whole target ARM system including kernel, 1000 open source
> applications, even including a tiny X window.

Well, compiler bugs are sometimes very rarely triggered, and by completely
innocent code, so I would not exclude this possibility.
FWIW, I don't see anything obviously broken/sigsegv-worthy in above test program
[assuming missing headers contained something like

int sql_exec_v2(sqlite3 *conn, const char *sql) {
// note: sql_exec_v2 expected to return SQLITE_DONE on success
// sqlite3_exec returns SQLITE_OK on success
int sqlerr, ret;
sqlite3_stmt *stmt;
sqlerr = sqlite3_prepare_v2(conn, sql, -1, , NULL);
if (sqlerr != SQLITE_OK) return sqlerr;
ret = sqlite3_step(stmt);
sqlerr = sqlite3_finalize(stmt);
if (sqlerr != SQLITE_OK) return sqlerr;
return ret;
}
int timespec_diff_ms(const struct timespec *ts1, const struct timespec *ts2) {
return (ts2->tv_sec - ts1->tv_sec)*1000 + 
(ts2->tv_nsec-ts1->tv_nsec)/100;
}
#define inst_signal_handler(SIGNAL,HNDL,FOO) signal((SIGNAL),(HNDL))

], but as this is "impossible error" it would make sense to add error checking
for *everything*, including "impossible errors".

> On Tuesday, 31 December 2013, Simon Slavin wrote:
> 
>> On 31 Dec 2013, at 8:41am, Woody Wu >
>> wrote:
>>
>>> Attached is the test program writting in C.
>> Sorry, but attachments don't work here.  If your program is short, please
>> post it as text.  If not, please put it on a web site somewhere.
>>
>>> Anyway, all above errors looks so strange. And, these operations I
>> talking
>>> about are so basic and my real application (another bigger one) really
>>> depends on these.
>> You should not be able to make SQLite corrupt its database that easily.
>>
>>> Pleaes be kindly to check my test program.
>> Can you run your program on the computer you used to send that email
>> message and tell us whether it had the same problem ?

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


Re: [sqlite] Android SQLite 3.8.2 issue with triggers & constraints

2014-01-02 Thread Douglas Orr
Thanks, Dan, that does the trick. I didn't know that sqlite used any
temporary files that weren't based on the database filename, but that makes
perfect sense.
Cheers,
Doug


On Wed, Jan 1, 2014 at 1:52 PM, Dan Kennedy  wrote:

> On 12/31/2013 10:03 PM, Douglas Orr wrote:
>
>> Hi,
>>
>> I have run into an issue when trying to use triggers with column
>> constraints on Android (using our build of SQLite 3.8.2, not Android's
>> built-in version from native code.) Executing the following on a
>> file-backed database fails:
>>CREATE TABLE things (number INTEGER NOT NULL);
>>CREATE TRIGGER on_insert_thing AFTER INSERT ON things BEGIN SELECT
>> NULL;
>> END;
>>BEGIN;
>>  INSERT INTO things (number) VALUES (1);
>>  INSERT INTO things (number) VALUES (2);
>>END;
>>
>> But if I do any of the following, it does not fail:
>>   - add ON CONFLICT FAIL to the NOT NULL constraint
>>   - remove the NOT NULL constraint
>>   - remove the trigger
>>   - remove the transaction BEGIN and END
>>   - use an in-memory database
>>   - run on my development machine (Linux based)
>>
>> This is reproduced in the attached program (includes SQLite 3.8.2), which
>> gives the following output when built for armeabi-v7a and run on either an
>> armeabi-v7a emulator (with SD card) or a Samsung Galaxy Note 2:
>>$ env ANDROID_NDK=/path/to/android-ndk-r9b ./build.sh && ./run.sh
>>Log(14): cannot open file at line 29016 of [27392118af]   --- I think
>> this is from the 3.8.2 amalgamation source file
>>Log(14): os_unix.c:29016: (30) open(./etilqs_mRlOFvBwZiFYwdW) -
>>
>
> It's failing to create the temporary file required for a statement
> journal. It's likely any statement that uses a temporary file will
> fail.
>
>   http://www.sqlite.org/tempfiles.html
>
> One way around the problem would be to configure SQLite not to use
> temp files using "PRAGMA temp_store = memory;".
>
> Or ensure that the environment variable SQLITE_TMPDIR is set to
> the path of a writable directory. See also:
>
>   http://www.sqlite.org/c3ref/temp_directory.html
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users