[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 8:12pm, Bart Smissaert  wrote:

> Will do some timings, but as you guess the slowdown may be negligible.

You know, on rereading the documentation for _reset() and _finalize() I can see 
why you thought you'd be able to check only the results for those calls.  
Unfortunately I don't think that's possible, and I have never seen code from an 
experienced SQLite user which depended on those calls echoing the results from 
_step().  So I thin you had better avoid it for now.

Simon.

[sqlite] Documentation fault for errcode.html

[Simon Slavin]

On 17 Mar 2015, at 8:41pm, Richard Hipp  wrote:

> Modified copy found at https://www.sqlite.org/draft/c3ref/errcode.html

That one is clear.  Thanks.

Simon.

[sqlite] Documentation fault for errcode.html

[Simon Slavin]

The first two sentences on this page



contradict one-another.  Here they are:

"The sqlite3_errcode() interface returns the numeric result code or extended 
result code for the most recent failed sqlite3_* API call associated with a 
database connection. If a prior API call failed but the most recent API call 
succeeded, the return value from sqlite3_errcode() is undefined."

Suppose there is the following pattern of usage:

sqlite3_bind_int()  -- fails with a result of SQLITE_RANGE 
sqlite3_bind_text() -- succeeds without problems.

The first sentence says that the result code of sqlite3_errcode() will 
definitely be SQLITE_RANGE.  The second sentence says that the result code of 
sqlite3_errcode() is undefined.

Simon.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, thanks, I see now.
Will do some timings, but as you guess the slowdown may be negligible.

RBS

On Tue, Mar 17, 2015 at 8:08 PM, Simon Slavin  wrote:

>
> On 17 Mar 2015, at 8:00pm, Bart Smissaert 
> wrote:
>
> > OK, but I can find out from sqlite3_errcode after the loop if there was
> an
> > error, saving all the checks inside the loop.
> > Would there be any harm from that?
>
> From the documentation:
>
> 
>
> "If a prior API call failed but the most recent API call succeeded, the
> return value from sqlite3_errcode() is undefined."
>
> In other words, it's possible for one call within the loop to generate an
> error, then the next one to make it impossible to figure that out.
>
> I know that right now it looks like having an error accumulator function
> might be useful, but calls to sqlite3_errcode() execute extremely quickly
> because they just pick up a value which was previously set.  They won't
> slow your program down.  It takes longer to call sqlite3_errmsg() but you
> only need to do that if there's an error you hadn't predicted.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 8:00pm, Bart Smissaert  wrote:

> OK, but I can find out from sqlite3_errcode after the loop if there was an
> error, saving all the checks inside the loop.
> Would there be any harm from that?

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, but I can find out from sqlite3_errcode after the loop if there was an
error, saving all the checks inside the loop.
Would there be any harm from that?

RBS

On Tue, Mar 17, 2015 at 7:52 PM, Simon Slavin  wrote:

>
> On 17 Mar 2015, at 7:39pm, Bart Smissaert 
> wrote:
>
> > The result is fine and no duplicates are inserted, only thing wrong is
> the
> > result the one from last sqlite3_finalize.
> > Note that I don't check the return values in the loop, maybe I should,
> but
> > it saves some time.
>
> The result you get from _finalize() is only guaranteed to tell you about
> problems with _finalize().  To find out if _step() worked you have to check
> the result from each call to _step().
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Simon Slavin]

On 17 Mar 2015, at 7:39pm, Bart Smissaert  wrote:

> The result is fine and no duplicates are inserted, only thing wrong is the
> result the one from last sqlite3_finalize.
> Note that I don't check the return values in the loop, maybe I should, but
> it saves some time.

The result you get from _finalize() is only guaranteed to tell you about 
problems with _finalize().  To find out if _step() worked you have to check the 
result from each call to _step().

Simon.

[sqlite] Understanding Sqlite IO on Windows CE

[Nicholas Smit]

Hello.

We have an app on CE 5, and CE 7.

We are keen to move from using SQL CE, to Sqlite, for our main data, as
Sqlite is superior in many ways.

The app runs on a mobile device, where power can be removed at any time.
The data is stored on SD cards, with FAT filesystem.

Sometimes the IO sub-system is known to be slow.  As such, corruption and
data loss is our number one fear. Our main aim is to keep the IO to a
minimum, thus reducing the risk of corruption anywhere.  Corruption of the
file itself, but also the filesystem, leading to problems accessing the
file.

Of course this fear has nothing to do with Sqlite per se, but we're trying
to understand how the risk might *change*, as we move from SQL CE to
Sqlite.

With Sqlite, we're using Synchronous=FULL (as we don't want to lose data),
Journal Mode=Persist (to inhibit unnecessary IO on the journal file).

In SQL CE, the minimum interval for flushes to disk is 1 second, which is
what we've been using.

My question is (finally I get to it!) with these settings, it sounds like
if we do (say) 3 transactions in a particular second, we will end up doing:

 - 3 updates to the FAT to obtain the exclusive lock
 - 3 writes to the sqlite journal
 - 3 writes to the sqlite main db file
 - 3 updates to the FAT to release the exclusive lock, update the last
modified file date, etc.


 So a total of 12 writes to the file system. (excluding any reads etc
required in the actual transaction).

 Whereas in SQL CE this would have been about 1 (to just append the new
data for all 3, at the end of the flush interval. It presumably keeps the
file open, so no FAT updates.).

 a) Is my understanding roughly correct here in terms of the IO?
 b) If so, is there any way to reduce the IO effort?
 c) Can anyone comment on whether Windows CE VFS does actually honour the
fsync commands of sqlite, or does the OS perhaps buffer them anyway?

 Much thanks in advance for any pointers.
 Nik

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

OK, let me try to explain:

Simple table with primary integer key, call the field ID

strSQL = "Insert into table1 values(?)"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)

strSQL = "BEGIN TRANSACTION"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)
lReturnValue = sqlite3_step(lStatementHandle)
lReturnValue = sqlite3_finalize(lStatementHandle)

In a loop, looping through rows of a VBA variant array:

sqlite3_bind_int lStatementHandle, 1, vArray(r)
sqlite3_step lStatementHandle
sqlite3_reset lStatementHandle

After this loop:

lReturnValue = sqlite3_finalize(lStatementHandle)   < unexpected
lReturnValue here

strSQL = "COMMIT TRANSACTION"

lReturnValue = sqlite3_prepare16_v2(lDBHandle,
  StrPtr(strSQL),
  Len(strSQL) * 2,
  lReturnStatementHandle,
  0)
lReturnValue = sqlite3_step(lStatementHandle)
lReturnValue = sqlite3_finalize(lStatementHandle)



That is it.
The result is fine and no duplicates are inserted, only thing wrong is the
result the one from last sqlite3_finalize.
Note that I don't check the return values in the loop, maybe I should, but
it saves some time.


RBS


On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp  wrote:

> On 3/17/15, Bart Smissaert  wrote:
> > Have a simple table with a primary integer key.
> > Doing an insert (with no insert or ignore or insert or replace) with
> > duplicate values
> > for this primary integer key field produces zero on sqlite3_finalize, but
> > 19 from sqlite3_errorcode.
>
> I wrote a test program for this and I get 19 in both cases.  Maybe you
> could share your test case with us?
>
> > I thought that the result value of sqlite3_finalize also should produce a
> > non-zero value.
> > This is with the latest version.
> > Anything I am overlooking here or misunderstanding?
> >
> > RBS
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Hick Gunter]

Your calling sequence should be

- sqlite3_prepare() or sqlite3_prepare_v2()
- sqlite3_bind_xxx() if you have any parameters
- sqlite3_step()
- sqlite3_reset() or sqlite3_finalize() depending on if you want to run the 
statement again or not

You need to *check* the return status of *every* call.

Probably you are ignoring SQLITE_ERROR from your sqlite3_step() call.

-Urspr?ngliche Nachricht-
Von: Bart Smissaert [mailto:bart.smissaert at gmail.com]
Gesendet: Dienstag, 17. M?rz 2015 19:19
An: General Discussion of SQLite Database
Betreff: [sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces 
(rightly) SQLITE_CONSTRAINT

Have a simple table with a primary integer key.
Doing an insert (with no insert or ignore or insert or replace) with duplicate 
values for this primary integer key field produces zero on sqlite3_finalize, but
19 from sqlite3_errorcode.
I thought that the result value of sqlite3_finalize also should produce a 
non-zero value.
This is with the latest version.
Anything I am overlooking here or misunderstanding?

RBS
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Bart Smissaert]

Have a simple table with a primary integer key.
Doing an insert (with no insert or ignore or insert or replace) with
duplicate values
for this primary integer key field produces zero on sqlite3_finalize, but
19 from sqlite3_errorcode.
I thought that the result value of sqlite3_finalize also should produce a
non-zero value.
This is with the latest version.
Anything I am overlooking here or misunderstanding?

RBS

[sqlite] Safe use of custom collations that are not available in all tools

[Hick Gunter]

How about coding the collation in C and statically linking it into the SQLite 
library you provide with your application?

-Urspr?ngliche Nachricht-
Von: Gerry Snyder [mailto:mesmerizerfan at gmail.com]
Gesendet: Dienstag, 17. M?rz 2015 18:02
An: 'General Discussion of SQLite Database'
Betreff: [sqlite] Safe use of custom collations that are not available in all 
tools

In one of my tables I need a collation that is an extension of NOCASE (it 
ignores spaces and punctuation, translates a very limited set of accented 
characters into their unaccented forms). I wrote it in Tcl, and it works well 
in my tools.

At first I used the collation only in SELECT statements, but more than once I 
failed to specify it and did not get the desired ordering (of course). So, I 
put the collation in the column definition in the table definition. This works 
perfectly in my s/w, but other tools complain about a missing collation.

So, two questions:

1) Any advice (other than take the collation out of the table definition and 
always code carefully)?

2) How does SQLite handle missing collations? Are there any things that are 
safe to do using tools that do not have the collation? (For instance, is 
read-only access ok? It seems to be, and using DB Browser for SQLite gives a 
warning but then handles the table correctly, including ordering by the column 
with the missing collation. The Command Line Shell refuses to do anything with 
the table except .dump it.)

All suggestions welcome.

Gerry Snyder
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] regarding loops in vdbe code

[Sairam Gaddam]

But in my example there are multiple rows which satisfy the condition.

My example:

sql="create table em(name int primary key,age int);"\
"create table idv(id int primary key,name text);"\
"create table mny(id int primary key,sal int);"\
"create table lo(name int primary key,addr text);";

sql="insert into em values(44,21);"\
"insert into em values(11,20);"\
"insert into em values(5,20);"\
"insert into idv values(11,44);"\
"insert into idv values(5,11);"\
"insert into idv values(44,180);"\
"insert into mny values(5,1);"\
"insert into mny values(11,5000);"\
"insert into mny values(44,5000);"\
"insert into lo values(5,'NY');"\
"insert into lo values(44,'che');"\
"insert into lo values(11,'NY');";

sql="select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id ";

VDBE:

   0 Init 0   410   00
   1 OpenRead 020 2 00
   2 OpenRead 190 2 00
   3 OpenRead 4   100 k(2,nil,nil)  00
   4 OpenRead 270 2 00
   5 OpenRead 350 2 00
   6 OpenRead 560 k(2,nil,nil)  00
   7 Rewind   0   340   00
   8 Column   001   00
   9 IsNull   1   330   00
  10 Affinity 110 D 00
  11 SeekGE   4   331 1 00
  12 IdxGT4   331 1 00
  13 IdxRowid 420   00
  14 Seek 120   00
  15 Rewind   2   330   00
  16 Column   203   00
  17 IsNull   3   320   00
  18 Affinity 310 D 00
  19 SeekGE   5   323 1 00
  20 IdxGT5   323 1 00
  21 IdxRowid 540   00
  22 Seek 340   00
  23 Column   005   00
  24 Column   016   00
  25 Column   407   00
  26 Column   118   00
  27 Column   209   00
  28 Column   21   10   00
  29 Column   50   11   00
  30 Column   31   12   00
  31 ResultRow580   00
  32 Next 2   160   01
  33 Next 080   01
  34 Close000   00
  35 Close100   00
  36 Close400   00
  37 Close200   00
  38 Close300   00
  39 Close500   00
  40 Halt 000   00
  41 Transaction  00 44729 0 01
  42 TableLock020 em00
  43 TableLock090 lo00
  44 TableLock070 mny   00
  45 TableLock050 idv   00
  46 Goto 010   00


How sqlite works in this case ?
because there are 4 tables and for only 2 tables(or indices) it opened
loops ?
The next opcodes belongs to which tables in this case?


On Tue, Mar 17, 2015 at 5:47 PM, Hick Gunter  wrote:

> If there can be not more than one row that satisfies the constraints (i.e.
> the constraints specifiy a unique key) and there is an index (express or
> implied or autocreated) available, then a simple index lookup will suffice.
>
> How many rows do you expect to have a rowid of 1?
> How many rows do you expect to read from table a for each row of table b
> if a_rowid is a foreign key?
>
> create temp table a (f1 integer);
> create temp table b (a_rowid integer, f2 integer);
>
> .explain
> explain select * from a where rowid = 1;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000  NULL
> 1 Integer1 1 000  NULL
> 2 Goto   0 10000  NULL
> 3 OpenRead   0 2 1 1  00  a
> 4 MustBeInt  1 8 000  NULL
> 5 NotExists  0 8 100  pk
> 6 Column 0 0 300  a.f1
> 7 ResultRow  3 1 000  NULL
> 8 Close  0 0 000  NULL
> 9 Halt   0 0 000  NULL
> 10Transaction1 0 000  NULL
> 11VerifyCookie   1 1 000  NULL
> 12TableLock  1 2 0 a  00  NULL
> 13Goto   

[sqlite] regarding loops in vdbe code

[Sairam Gaddam]

When joining a table in sqlite with some condition using where clause,
sqlite sometimes generate less number of loops(Next opcodes) than the
number of tables.
Can anyone explain how sqlite iterates through all the tables when it has
less number of loops.

[sqlite] Documentation fault for errcode.html

[Richard Hipp]

On 3/17/15, Richard Hipp  wrote:
> On 3/17/15, Simon Slavin  wrote:
>> The first two sentences on this page
>>
>> 
>>
>> contradict one-another.  Here they are:
>>
>> "The sqlite3_errcode() interface returns the numeric result code or
>> extended
>> result code for the most recent failed sqlite3_* API call associated with
>> a
>> database connection.


Modified copy found at https://www.sqlite.org/draft/c3ref/errcode.html


>
> "most recent" is intended to modify "sqlite3_* API" not "failed
> sqlite3_* API".  But I see how that can be ambiguous so I will find a
> way to reword it.
>
>  If a prior API call failed but the most recent API call
>> succeeded, the return value from sqlite3_errcode() is undefined."
>>
>> Suppose there is the following pattern of usage:
>>
>> sqlite3_bind_int()   -- fails with a result of SQLITE_RANGE
>> sqlite3_bind_text()  -- succeeds without problems.
>>
>> The first sentence says that the result code of sqlite3_errcode() will
>> definitely be SQLITE_RANGE.  The second sentence says that the result
>> code
>> of sqlite3_errcode() is undefined.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Documentation fault for errcode.html

[Richard Hipp]

On 3/17/15, Simon Slavin  wrote:
> The first two sentences on this page
>
> 
>
> contradict one-another.  Here they are:
>
> "The sqlite3_errcode() interface returns the numeric result code or extended
> result code for the most recent failed sqlite3_* API call associated with a
> database connection.

"most recent" is intended to modify "sqlite3_* API" not "failed
sqlite3_* API".  But I see how that can be ambiguous so I will find a
way to reword it.

 If a prior API call failed but the most recent API call
> succeeded, the return value from sqlite3_errcode() is undefined."
>
> Suppose there is the following pattern of usage:
>
> sqlite3_bind_int()-- fails with a result of SQLITE_RANGE
> sqlite3_bind_text()   -- succeeds without problems.
>
> The first sentence says that the result code of sqlite3_errcode() will
> definitely be SQLITE_RANGE.  The second sentence says that the result code
> of sqlite3_errcode() is undefined.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] regarding loops in vdbe code

[Hick Gunter]

You define table em(ployee) to have the field "name" (which is defined as 
numeric affinity) as the primary key. This forces SQLite to create a unique 
index on this table.

You also define table lo(cation) to have the field "name" (which is defined as 
numeric affinity) as the primary key.

You specify lo.name=em.name in your WHERE clause.

SQLite query planner thinks:

Q:Given a specific value from an em.name, how many rows would I expect to 
retrieve?
A: Since "name" is a UNIQUE PRIMARY KEY the answer is "not more than one"

The same logic holds for idv.id=mny.id

So to generate all the rows of the result, SQLITE has to

- LOOP over em
- SEEK the corresponding lo entry
- LOOP over idv
- SEEK the corresponding mny entry

Which means there are |em| * |idv| entries in the result set.

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Dienstag, 17. M?rz 2015 13:42
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] regarding loops in vdbe code

But in my example there are multiple rows which satisfy the condition.

My example:

sql="create table em(name int primary key,age int);"\
"create table idv(id int primary key,name text);"\
"create table mny(id int primary key,sal int);"\
"create table lo(name int primary key,addr text);";

sql="insert into em values(44,21);"\
"insert into em values(11,20);"\
"insert into em values(5,20);"\
"insert into idv values(11,44);"\
"insert into idv values(5,11);"\
"insert into idv values(44,180);"\
"insert into mny values(5,1);"\
"insert into mny values(11,5000);"\
"insert into mny values(44,5000);"\
"insert into lo values(5,'NY');"\
"insert into lo values(44,'che');"\
"insert into lo values(11,'NY');";

sql="select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id ";

VDBE:

   0 Init 0   410   00
   1 OpenRead 020 2 00
   2 OpenRead 190 2 00
   3 OpenRead 4   100 k(2,nil,nil)  00
   4 OpenRead 270 2 00
   5 OpenRead 350 2 00
   6 OpenRead 560 k(2,nil,nil)  00
   7 Rewind   0   340   00
   8 Column   001   00
   9 IsNull   1   330   00
  10 Affinity 110 D 00
  11 SeekGE   4   331 1 00
  12 IdxGT4   331 1 00
  13 IdxRowid 420   00
  14 Seek 120   00
  15 Rewind   2   330   00
  16 Column   203   00
  17 IsNull   3   320   00
  18 Affinity 310 D 00
  19 SeekGE   5   323 1 00
  20 IdxGT5   323 1 00
  21 IdxRowid 540   00
  22 Seek 340   00
  23 Column   005   00
  24 Column   016   00
  25 Column   407   00
  26 Column   118   00
  27 Column   209   00
  28 Column   21   10   00
  29 Column   50   11   00
  30 Column   31   12   00
  31 ResultRow580   00
  32 Next 2   160   01
  33 Next 080   01
  34 Close000   00
  35 Close100   00
  36 Close400   00
  37 Close200   00
  38 Close300   00
  39 Close500   00
  40 Halt 000   00
  41 Transaction  00 44729 0 01
  42 TableLock020 em00
  43 TableLock090 lo00
  44 TableLock070 mny   00
  45 TableLock050 idv   00
  46 Goto 010   00


How sqlite works in this case ?
because there are 4 tables and for only 2 tables(or indices) it opened loops ?
The next opcodes belongs to which tables in this case?


On Tue, Mar 17, 2015 at 5:47 PM, Hick Gunter  wrote:

> If there can be not more than one row that satisfies the constraints (i.e.
> the constraints specifiy a unique key) and there is an index (express
> or implied or autocreated) available, then a simple index lookup will suffice.
>
> How many rows do you expect to have a rowid of 1?
> How many rows do you expect to read from table a for each row of table
> b if a_rowid is a foreign key?
>
> create temp table a (f1 integer);
> create temp table b (a_rowid integer, f2 integer);
>
> .explain
> explain select * from a 

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Richard Hipp]

On 3/17/15, Bart Smissaert  wrote:
> Have a simple table with a primary integer key.
> Doing an insert (with no insert or ignore or insert or replace) with
> duplicate values
> for this primary integer key field produces zero on sqlite3_finalize, but
> 19 from sqlite3_errorcode.

I wrote a test program for this and I get 19 in both cases.  Maybe you
could share your test case with us?

> I thought that the result value of sqlite3_finalize also should produce a
> non-zero value.
> This is with the latest version.
> Anything I am overlooking here or misunderstanding?
>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] regarding loops in vdbe code

[Hick Gunter]

If there can be not more than one row that satisfies the constraints (i.e. the 
constraints specifiy a unique key) and there is an index (express or implied or 
autocreated) available, then a simple index lookup will suffice.

How many rows do you expect to have a rowid of 1?
How many rows do you expect to read from table a for each row of table b if 
a_rowid is a foreign key?

create temp table a (f1 integer);
create temp table b (a_rowid integer, f2 integer);

.explain
explain select * from a where rowid = 1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer1 1 000  NULL
2 Goto   0 10000  NULL
3 OpenRead   0 2 1 1  00  a
4 MustBeInt  1 8 000  NULL
5 NotExists  0 8 100  pk
6 Column 0 0 300  a.f1
7 ResultRow  3 1 000  NULL
8 Close  0 0 000  NULL
9 Halt   0 0 000  NULL
10Transaction1 0 000  NULL
11VerifyCookie   1 1 000  NULL
12TableLock  1 2 0 a  00  NULL
13Goto   0 3 000  NULL

explain select * from a join b on a.rowid=b.a_rowid where b.f2 = 1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer1 1 000  NULL
2 Goto   0 19000  NULL
3 OpenRead   1 3 1 2  00  b
4 OpenRead   0 2 1 1  00  a
5 Rewind 1 16000  NULL
6 Column 1 1 200  b.f2
7 Ne 1 152 collseq(BINARY)  6c  NULL
8 Column 1 0 300  b.a_rowid
9 MustBeInt  3 15000  NULL
10NotExists  0 15300  pk
11Column 0 0 400  a.f1
12Column 1 0 500  b.a_rowid
13Column 1 1 600  b.f2
14ResultRow  4 3 000  NULL
15Next   1 6 001  NULL
16Close  1 0 000  NULL
17Close  0 0 000  NULL
18Halt   0 0 000  NULL
19Transaction1 0 000  NULL
20VerifyCookie   1 2 000  NULL
21TableLock  1 3 0 b  00  NULL
22TableLock  1 2 0 a  00  NULL
23Goto   0 3 000  NULL

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Dienstag, 17. M?rz 2015 12:26
An: General Discussion of SQLite Database
Betreff: [sqlite] regarding loops in vdbe code

When joining a table in sqlite with some condition using where clause, sqlite 
sometimes generate less number of loops(Next opcodes) than the number of tables.
Can anyone explain how sqlite iterates through all the tables when it has less 
number of loops.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Safe use of custom collations that are not available in all tools

[Gerry Snyder]

The problem is that I want to make the data available to folks who do 
not have my  application (emergency-only sort of thing--my Tcl-based GUI 
works well for me, but is probably not ready for prime time). If 
something happens to me I want the data in the file to be recoverable. 
Losing the collation is fine, losing the table data is not.

Thank you for the suggestion, though. It got me thinking along the lines 
of making a starpack (Tcl executable plus my s/w in a single file) 
available, which may be the way to go if there does not turn out to be a 
safe way of handling missing collations.

Gerry

On 3/17/2015 11:15 AM, Hick Gunter wrote:
> How about coding the collation in C and statically linking it into the SQLite 
> library you provide with your application?
>
> -Urspr?ngliche Nachricht-
> Von: Gerry Snyder [mailto:mesmerizerfan at gmail.com]
> Gesendet: Dienstag, 17. M?rz 2015 18:02
> An: 'General Discussion of SQLite Database'
> Betreff: [sqlite] Safe use of custom collations that are not available in all 
> tools
>
> In one of my tables I need a collation that is an extension of NOCASE (it 
> ignores spaces and punctuation, translates a very limited set of accented 
> characters into their unaccented forms). I wrote it in Tcl, and it works well 
> in my tools.
>
> At first I used the collation only in SELECT statements, but more than once I 
> failed to specify it and did not get the desired ordering (of course). So, I 
> put the collation in the column definition in the table definition. This 
> works perfectly in my s/w, but other tools complain about a missing collation.
>
> So, two questions:
>
> 1) Any advice (other than take the collation out of the table definition and 
> always code carefully)?
>
> 2) How does SQLite handle missing collations? Are there any things that are 
> safe to do using tools that do not have the collation? (For instance, is 
> read-only access ok? It seems to be, and using DB Browser for SQLite gives a 
> warning but then handles the table correctly, including ordering by the 
> column with the missing collation. The Command Line Shell refuses to do 
> anything with the table except .dump it.)
>
> All suggestions welcome.
>
> Gerry Snyder
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When to disambiguate column names in queries?

[Hick Gunter]

When all names are disambiguated, simple typos and cut-and-paste errors become 
less likely to go undetected by the SQL Parser.

See the "No diagnostic given for missing column" thread.

-Urspr?ngliche Nachricht-
Von: Keith Medcalf [mailto:kmedcalf at dessus.com]
Gesendet: Dienstag, 17. M?rz 2015 00:53
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] When to disambiguate column names in queries?


Personally, I'd format it like this to be the most readable:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH,
 SAPSystemCompanyAX.dbo.PorMasteras PM,
 SAPSystemCompanyAX.dbo.PorMasterDetail  as PD,
 SAPSystemCompanyAX.dbo.AprClients   as CL
   WHERE PD.POrder = PH.POrder
 AND PM.POrder = PH.POrder
 AND PD.LineNo > 1
 AND CL.ClientCode = PH.Client
ORDER BY ClientName, POrder, LineNo

or, if you prefer the JOIN syntax:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH
JOIN SAPSystemCompanyAX.dbo.PorMasteras PM
  ON PM.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.PorMasterDetail  as PD
  ON PD.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.AprClients   as CL
  ON CL.ClientCode = PH.Client
   WHERE PD.LineNo > 1
ORDER BY ClientName, POrder, LineNo

I find gratuitous quoting of symbols somewhat obnoxious and distasteful, as 
well as the propensity to (include (extraneous) (brackets) just) for the (sake 
(of (adding (brackets.  I see that your original obviously-ms-generated 
query didn't generate its usual crapload of () -- or perhaps you removed them 
already :)

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org
>[mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
>R.Smith
>Sent: Monday, 16 March, 2015 11:26
>To: sqlite-users at mailinglists.sqlite.org
>Subject: Re: [sqlite] When to disambiguate column names in queries?
>
>
>On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
>> All,
>>
>> Some of my Select statements are pretty long and I'm starting to
>> think
>it'd be a good idea to always include table names of columns instead of
>just when they are not unique. This would make the Select statements
>longer, but perhaps easier to understand if the reader knows where each
>column is from.
>>
>> Any thoughts on this? I realize something like this can be highly
>subjective, but I'm wondering if there's a generally accepted practice
>in the SQLite world.
>
>I won't try to imagine this applies to the SQLite or any other SQL
>world, but I've had good success (In terms of always understanding
>queries that needs to be referred back to after some months and
>legibility to others plus zero SQL engine confusion or incorrect
>queries) when using very short aliasing as a standard.
>
>I've started the practice out of necessity using MS SQL where automated
>queries by default append the table-name to every column which made it
>almost illegible and fills up the screen with nonsense text making the
>query hard to read from among the riffraff.  (People using MS Query via
>Excel might be familiar with this example).
>
>A typical Query offered by some designer might start out like this:
>
>SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
>PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
>PorMasterDetail.LineNo, PorMasterDetail.StockCode,
>PorMasterDetail.Price, PorMasterDetail.OrderQty,
>(PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
>FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
>[SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
>[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
>[SAPSystemCompanyAX].[dbo].[AprClients] AprClients
>WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
>PorMaster.POrder=PorMasterHeader.POrder  AND
>PorMasterDetail.LineNo>1AND

[sqlite] sqlite memory limit issue

[Vadim Shashenko]

Sqlite provides us with sqlite3_soft_heap_limit64(sqlite3_int64 n) to set
its memory limit. It accepts int64.

I noticed that if I set the limit to any number greater than int32 can hold
(like 2.5GB, 3GB, etc) then sqlite outgrows the limits like there's no any.
If set the limit to a number in range of int32, then sqlite obeys the
setting.
My single binary with sqlite statically linked uses many databases at once
(22GB in total). The OS is Linux on a x64 bit machine.


I found the following piece of code in sqlite sources.

static int mallocWithAlarm(int n, void **pp){
  int nFull;
  void *p;
  assert( sqlite3_mutex_held(mem0.mutex) );
  nFull = sqlite3GlobalConfig.m.xRoundup(n);
  sqlite3StatusSet(SQLITE_STATUS_MALLOC_SIZE, n);
  if( mem0.alarmCallback!=0 ){
int nUsed = sqlite3StatusValue(SQLITE_STATUS_MEMORY_USED);
if( nUsed >= mem0.alarmThreshold - nFull ){
  mem0.nearlyFull = 1;
  sqlite3MallocAlarm(nFull);
}else{
  mem0.nearlyFull = 0;
}
// ...
}

It seems that current memory usage is reported through int32 variable. Then
it is comprared with the mem0.alarmThreshold, which is int64. Thus, current
memory usage will never be greater then the threshold, if the threshold is
set beyond int32 range.

Also, the variable which tracks currently allocated memory, is also int32
and can overflow if sqlite allocates more than 2GB of memory.

static SQLITE_WSD struct sqlite3StatType {
  int nowValue[10]; /* Current value */
  int mxValue[10];  /* Maximum value */
}

Are my findings correct? And what can I do to fix the issue I have with the
memory limit.

[sqlite] Safe use of custom collations that are not available in all tools

[Gerry Snyder]

In one of my tables I need a collation that is an extension of NOCASE 
(it ignores spaces and punctuation, translates a very limited set of 
accented characters into their unaccented forms). I wrote it in Tcl, and 
it works well in my tools.

At first I used the collation only in SELECT statements, but more than 
once I failed to specify it and did not get the desired ordering (of 
course). So, I put the collation in the column definition in the table 
definition. This works perfectly in my s/w, but other tools complain 
about a missing collation.

So, two questions:

1) Any advice (other than take the collation out of the table definition 
and always code carefully)?

2) How does SQLite handle missing collations? Are there any things that 
are safe to do using tools that do not have the collation? (For 
instance, is read-only access ok? It seems to be, and using DB Browser 
for SQLite gives a warning but then handles the table correctly, 
including ordering by the column with the missing collation. The Command 
Line Shell refuses to do anything with the table except .dump it.)

All suggestions welcome.

Gerry Snyder

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

>   On 3/16/15, Paul  wrote:
> >  when the usage is
> >
> > (1) open database
> > (2) execute 'PRAGMA synchronous = OFF;'
> > (3) attach database X
> > (4) execute 'PRAGMA X.synchronous = OFF;'
> > (5) begin transaction
> > (6) do some inserts/deletes/updates
> > (7) commit transaction
> > (8) close database
> >
> > I observe one fsync() for directory where 'main' database is located.
> > Why attaching database is causing this nasty fsync() during commit, even
> > when both databases have fsync() disabled?
> 
> Problem fixed in https://www.sqlite.org/src/info/018d7671402a0f81
> 

Thank you very much.

[sqlite] regarding loops in vdbe code

[Keith Medcalf]

Looks correct to me.  There are only two loops required.  One to loop through 
em (which will only find at most one matching row in lo, so no need for a loop 
there), and another to loop through mny (which will find at most one matching 
row in ldv, so no need to loop there).  The cross-product of those two loops is 
then returned.

SQLite version 3.8.9 2015-03-16 20:40:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table em(name int primary key,age int);
sqlite> create table idv(id int primary key,name text);
sqlite> create table mny(id int primary key,sal int);
sqlite> create table lo(name int primary key,addr text);
sqlite>
sqlite> insert into em values(44,21);
sqlite> insert into em values(11,20);
sqlite> insert into em values(5,20);
sqlite> insert into idv values(11,44);
sqlite> insert into idv values(5,11);
sqlite> insert into idv values(44,180);
sqlite> insert into mny values(5,1);
sqlite> insert into mny values(11,5000);
sqlite> insert into mny values(44,5000);
sqlite> insert into lo values(5,'NY');
sqlite> insert into lo values(44,'che');
sqlite> insert into lo values(11,'NY');
sqlite>
sqlite> .eqp on
sqlite> select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id;
--EQP-- 0,0,0,SCAN TABLE em
--EQP-- 0,1,1,SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1 (name=?)
--EQP-- 0,2,2,SCAN TABLE mny
--EQP-- 0,3,3,SEARCH TABLE idv USING INDEX sqlite_autoindex_idv_1 (id=?)
44|21|44|che|5|1|5|11
44|21|44|che|11|5000|11|44
44|21|44|che|44|5000|44|180
11|20|11|NY|5|1|5|11
11|20|11|NY|11|5000|11|44
11|20|11|NY|44|5000|44|180
5|20|5|NY|5|1|5|11
5|20|5|NY|11|5000|11|44
5|20|5|NY|44|5000|44|180
sqlite>
sqlite> .explain
sqlite> explain select * from em,lo,mny,idv where lo.name=em.name and 
idv.id=mny.id;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 55000  Start at 55
1 OpenRead   0 2 0 2  00  root=2 iDb=0; em
2 OpenRead   1 8 0 2  00  root=8 iDb=0; lo
3 OpenRead   4 9 0 k(2,nil,nil)   00  root=9 iDb=0; 
sqlite_autoindex_lo_1
4 OpenRead   2 6 0 2  00  root=6 iDb=0; mny
5 OpenRead   3 4 0 2  00  root=4 iDb=0; idv
6 OpenRead   5 5 0 k(2,nil,nil)   00  root=5 iDb=0; 
sqlite_autoindex_idv_1
7 Explain0 0 0 SCAN TABLE em  00
8 Noop   0 0 000  Begin WHERE-loop0: em
9 Rewind 0 47000
10  Explain0 1 1 SEARCH TABLE lo USING INDEX 
sqlite_autoindex_lo_1 (name=?)  00
11  Noop   0 0 000  Begin WHERE-loop1: 
lo
12  Column 0 0 100  r[1]=em.name
13  IsNull 1 45000  if r[1]==NULL goto 
45
14  Affinity   1 1 0 D  00  affinity(r[1])
15  SeekGE 4 451 1  00  key=r[1]
16  IdxGT  4 451 1  00  key=r[1]
17  IdxRowid   4 2 000  r[2]=rowid
18  Seek   1 2 000  intkey=r[2]
19  Explain0 2 2 SCAN TABLE mny  00
20  Noop   0 0 000  Begin WHERE-loop2: 
mny
21  Rewind 2 44000
22Explain0 3 3 SEARCH TABLE idv USING INDEX 
sqlite_autoindex_idv_1 (id=?)  00
23Noop   0 0 000  Begin 
WHERE-loop3: idv
24Column 2 0 300  r[3]=mny.id
25IsNull 3 42000  if r[3]==NULL 
goto 42
26Affinity   3 1 0 D  00  affinity(r[3])
27SeekGE 5 423 1  00  key=r[3]
28IdxGT  5 423 1  00  key=r[3]
29IdxRowid   5 4 000  r[4]=rowid
30Seek   3 4 000  intkey=r[4]
31Noop   0 0 000  Begin WHERE-core
32Column 0 0 500  r[5]=em.name
33Column 0 1 600  r[6]=em.age
34Column 4 0 700  r[7]=lo.name
35Column 1 1 800  r[8]=lo.addr
36Column 2 0 900  r[9]=mny.id
37Column 2 1 10   00  r[10]=mny.sal
38Column 5 0 11   00  r[11]=idv.id
39Column 

[sqlite] FAQ typo

[O.Zolotov]

Dear All,
the FAQ's item 21 ( http://www.sqlite.org/faq.html ) has a typo. The sentences

" Your can use PRAGMA integrity_check
Your can use PRAGMA quick_check ..."

are more likely to be
" You can use PRAGMA integrity_check
You can use PRAGMA quick_check ..."

PS
By the way, do I have a right to translate the FAQ into Russian?

Best Regards,
Oleg V. Zolotov