Re: [sqlite] Using sqlite3_errcode()

[Cezary H. Noweta]

On 2016-09-29 15:17, Otto Wallenius wrote:

[...]
To my understanding it follows that if you first call sqlite3_column_blob()
and then sqlite3_errcode(), the errcode might be SQLITE_NOMEM whether
memory allocation failed or not: either it failed and the error code was
set ot SQLITE_NOMEM, or it succeeded and the code happened to be
SQLITE_NOMEM because it is undefined.



Is this correct, and how can you check for errors in this case?


Actually, sqlite3_errcode() is unchanged in case of success (i.e. it 
returns the last failure's code). If you want to use the sole, pure 
``sqlite3_*'' API, then try to reset sqlite3_errcode() to SQLITE_OK by 
using the following NOP:


sqlite3_exec(db, NULL, NULL, NULL, NULL);

just before sqlite3_column_blob() or family is called and check a value 
of sqlite3_errcode() just after the called function has returned --- if 
the value is still SQLITE_OK (regardless of what the called function 
returned: 0/0.0/NULL or not) then an error has not occurred.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] *** suspected spam or bulk *** Re: Order of fields for insert

[Hick Gunter]

Here is the description of the internal record format

https://www.sqlite.org/fileformat.html#record_format

In comparison to a traditional C record structures it does a pretty good job 
when it comes to number of bytes stored. Especially if you have long string 
fields to accommodate insane xml content that is mostly very much shorter than 
the maximum size you are obliged to cater for.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jay Kreibich
Gesendet: Donnerstag, 06. Oktober 2016 16:49
An: SQLite mailing list 
Betreff: *** suspected spam or bulk *** Re: [sqlite] Order of fields for insert

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process
> and so should be placed at the end of the row. Often used fields -
> i.e. (foreign) key fields - should be placed at the front of the row.
> This will help most if your select field list is limited to the fields
> you actually need instead of "*".
>
>
Sorta, kinda, but not really.  SQLite does not use a traditional data 
compression algorithm in storing row data, but it does "pack" rows into a 
compact format (including variable size integers).  As such, a row's worth of 
data, as stored in the raw database, acts very similar to a compressed block of 
data... you have to read it from the start and can't directly jump to a field 
in a middle of it.

This is the issue with column ordering; the data engine will only read and 
unpack the columns it needs, but it has to read and unpack the columns in the 
order they're defined until it gets all the columns it needs.  This makes it 
generally better to put more frequently accessed and/or smaller values at the 
start of a row.

 -j
___
sqlite-users mailing list
sqlite-users@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: h...@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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple processes working on one database file

[Jay Kreibich]

On Wed, Oct 5, 2016 at 11:53 AM, Simon Slavin  wrote:

>
> On 5 Oct 2016, at 4:30pm, Jens Alfke  wrote:
>
> > I did find that SQLite’s own locking was less effective/fair than using
> a mutex, and in situations with a lot of write contention could lead to
> starvation and timeouts. It’s possible I was doing things wrong, as the
> wrapper library I started with included a SQLite busy-handler that would
> wait-and-retry. (But IIRC, if I removed the busy handler altogether, SQLite
> would just immediately return a db-busy error if another thread had a lock.)
>
> SQLite has its own built-in busy-handler which is written to work
> extremely efficiently with the things SQLite usually needs to do (though
> not necessarily with the way you're using SQLite).  It's not obvious
> whether the wrapper library you're using simply calls this one or
> implements its own, possibly less-efficient, one.
>


I'll admit I haven't done this low level type of SQLite programming in some
versions, but the traditional issue is that very, very few applications
handle deadlocking correctly.  Even with an intelligent busy-handler, there
are situations when multiple processes can get into a deadlock and the only
way out is for one or more processes to abort their current transactions.
I've seen very few applications that handle that situation correctly, and
it is generally something that can't be put in a wrapper or utility library
(unless it is abstracting out all transactions and database interactions).

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

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

All I/O is done via page sized blocks.  So the minimum amount of data to be
fetched will always be a page.  The bigger issue is, as you said, when you
need to follow a chain of pages to get a small value at the end.

 -j

On Thu, Oct 6, 2016 at 9:53 AM, Paul Sanderson  wrote:

> > Long columns, especially TEXT or BLOBs which may have lots of data in,
> should go at the end.  Because you don't want SQLite to have to fetch all
> that data from storage just to get at the column after it.
>
> To be pedantic SQLite does not need to "fetch" all of the data from
> strorage before a needed column, it just needs to be able to skip it -
> unless the data oveflows in to one or more overflow pages then it will
> need to fetch each page until it reaches the one with the data in it.
> If the complete row is held in one page and your query just needs the
> last column - SQLite just needs to know the size of all of the data
> that preceedes the column you want. There is still the overhead of
> decoding every serial type before the column you require.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jay Kreibich]

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
>
Sorta, kinda, but not really.  SQLite does not use a traditional data
compression algorithm in storing row data, but it does "pack" rows into a
compact format (including variable size integers).  As such, a row's worth
of data, as stored in the raw database, acts very similar to a compressed
block of data... you have to read it from the start and can't directly jump
to a field in a middle of it.

This is the issue with column ordering; the data engine will only read and
unpack the columns it needs, but it has to read and unpack the columns in
the order they're defined until it gets all the columns it needs.  This
makes it generally better to put more frequently accessed and/or smaller
values at the start of a row.

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

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

> Long columns, especially TEXT or BLOBs which may have lots of data in, should 
> go at the end.  Because you don't want SQLite to have to fetch all that data 
> from storage just to get at the column after it.


To be pedantic SQLite does not need to "fetch" all of the data from
strorage before a needed column, it just needs to be able to skip it -
unless the data oveflows in to one or more overflow pages then it will
need to fetch each page until it reaches the one with the data in it.
If the complete row is held in one page and your query just needs the
last column - SQLite just needs to know the size of all of the data
that preceedes the column you want. There is still the overhead of
decoding every serial type before the column you require.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

ok

On 6 Oct 2016 8:08 p.m., "Simon Slavin"  wrote:

>
> On 6 Oct 2016, at 3:37pm, Krishna Shukla 
> wrote:
>
> > Help how can i import exel file in sqlite and get result in c# desktop
> > application ...?
>
> Please start a new thread for this question.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 2:46pm, Jeff Archer  wrote:

> Are there any performance or other considerations of the order of the
> fields for an insert?

No.  Order of columns in the CREATE TABLE command matters.  Order they're named 
in operations after that doesn't.

When SQLite needs to fetch values from a table row, it has to start reading the 
row at the first column mentioned in CREATE TABLE, then go through it reading 
data until it has reached the last column it needs.  Because of this it's best 
to put short, frequently-needed columns first in your CREATE TABLE command.

Long columns, especially TEXT or BLOBs which may have lots of data in, should 
go at the end.  Because you don't want SQLite to have to fetch all that data 
from storage just to get at the column after it.

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

Re: [sqlite] Order of fields for insert

[Simon Slavin]

On 6 Oct 2016, at 3:37pm, Krishna Shukla  wrote:

> Help how can i import exel file in sqlite and get result in c# desktop
> application ...?

Please start a new thread for this question.

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

Re: [sqlite] Order of fields for insert

[Krishna Shukla]

Help how can i import exel file in sqlite and get result in c# desktop
application ...?

On 6 Oct 2016 7:55 p.m., "Hick Gunter"  wrote:

> SQLite compresses rows before storing and decompresses rows before
> returning fields. BLOB fields are the most time consuming to process and so
> should be placed at the end of the row. Often used fields - i.e. (foreign)
> key fields - should be placed at the front of the row. This will help most
> if your select field list is limited to the fields you actually need
> instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a
> general answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the
> fields for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> ​mytable(
> wid1,cnt,
> ​dat,​
> wid3,wid2) VALUES (?,?,?,?)
> ​ - VS - ​
>
> INSERT INTO
> ​mytable(
> wid1,wid2,wid3,cnt
> ​,dat​
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> ​mytable
> (
> ​id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> ​wid1
>  INTEGER REFERENCES
> ​othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid2
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​wid3
>  INTEGER REFERENCES
> othertable
> (
> ​id
> ) ON DELETE CASCADE
> ,
> ​cnt
>  INTEGER DEFAULT
> ​1
> ,dat TEXT
> )
>
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users@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: h...@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@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

SQLite does not use any compression when storing data.

Occasionally rows have so much data that they overflow to an additonal
page(s) so the advice about defining tables so that blobs are at the
end of the definition is good - also columns that store long strings
might be better at the end of a table definition to avoid the same
sort of overflow.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 6 October 2016 at 15:25, Hick Gunter  wrote:
> SQLite compresses rows before storing and decompresses rows before returning 
> fields. BLOB fields are the most time consuming to process and so should be 
> placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
> should be placed at the front of the row. This will help most if your select 
> field list is limited to the fields you actually need instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some 
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a general 
> answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the fields 
> for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> mytable(
> wid1,cnt,
> dat,
> wid3,wid2) VALUES (?,?,?,?)
> - VS -
>
> INSERT INTO
> mytable(
> wid1,wid2,wid3,cnt
> ,dat
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> mytable
> (
> id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> wid1
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid2
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid3
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> cnt
>  INTEGER DEFAULT
> 1
> ,dat TEXT
> )
>
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users@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: h...@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@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Hick Gunter]

SQLite compresses rows before storing and decompresses rows before returning 
fields. BLOB fields are the most time consuming to process and so should be 
placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
should be placed at the front of the row. This will help most if your select 
field list is limited to the fields you actually need instead of "*".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jeff Archer
Gesendet: Donnerstag, 06. Oktober 2016 15:46
An: SQLite mailing list 
Betreff: [sqlite] Order of fields for insert

Just a quick question.  I am actually deciding if I need to do some performance 
testing of this but thought I might gain some quick insight.
My specific insert and table are below but really I am looking for a general 
answer to the question not just this specific case.

Are there any performance or other considerations of the order of the fields 
for an insert?
Are the following equivalent?  regardless of number of values inserting?
 regardless of size of the data being inserted?

INSERT INTO
​mytable(
wid1,cnt,
​dat,​
wid3,wid2) VALUES (?,?,?,?)
​ - VS - ​

INSERT INTO
​mytable(
wid1,wid2,wid3,cnt
​,dat​
) VALUES (?,?,?,?)


CREATE TABLE
​mytable
(
​id
 INTEGER PRIMARY KEY AUTOINCREMENT"
,
​wid1
 INTEGER REFERENCES
​othertable
(
​id
) ON DELETE CASCADE
,
​wid2
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​wid3
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​cnt
 INTEGER DEFAULT
​1
,dat TEXT
)

Jeff
___
sqlite-users mailing list
sqlite-users@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: h...@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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jeff Archer]

Sorry, that was just mistake in reducing the code for the email, please
ignore.

What do you mean "what matters is order of columns in table"?  or was that
just referring to the typo?


Jeff Archer
jeffarch...@gmail.com 


On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch  wrote:

> Jeff Archer wrote:
> > Are there any performance or other considerations of the order of the
> > fields for an insert?
>
> No; what matters is the order of columns in the table.
>
> > INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
> ​> - VS - ​
> > INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)
>
> Both statements will result in exactly the same error.  ;-)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Clemens Ladisch]

Jeff Archer wrote:
> Are there any performance or other considerations of the order of the
> fields for an insert?

No; what matters is the order of columns in the table.

> INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
​> - VS - ​
> INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)

Both statements will result in exactly the same error.  ;-)


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

[sqlite] Order of fields for insert

[Jeff Archer]

Just a quick question.  I am actually deciding if I need to do some
performance testing of this but thought I might gain some quick insight.
My specific insert and table are below but really I am looking for a
general answer to the question not just this specific case.

Are there any performance or other considerations of the order of the
fields for an insert?
Are the following equivalent?  regardless of number of values inserting?
 regardless of size of the data being inserted?

INSERT INTO
​mytable(
wid1,cnt,
​dat,​
wid3,wid2) VALUES (?,?,?,?)
​ - VS - ​

INSERT INTO
​mytable(
wid1,wid2,wid3,cnt
​,dat​
) VALUES (?,?,?,?)


CREATE TABLE
​mytable
(
​id
 INTEGER PRIMARY KEY AUTOINCREMENT"
,
​wid1
 INTEGER REFERENCES
​othertable
(
​id
) ON DELETE CASCADE
,
​wid2
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​wid3
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​cnt
 INTEGER DEFAULT
​1
,dat TEXT
)

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

Re: [sqlite] Default ordering of SELECT query

[Clemens Ladisch]

Andrii Motsok wrote:
> I cannot force my users to add ORDER BY to all queries especially for
> non UNIQUE indexes.

Randomly flip this setting before each query; they'll learn:
http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects


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

Re: [sqlite] Default ordering of SELECT query

[Simon Slavin]

On 6 Oct 2016, at 1:41pm, Andrii Motsok  wrote:

 How do you mean "follow index ordering"?
> This is only my observation that sqlite usually returns rows in the order in 
> which they are sorted in by index. So if I have two indexes and SELECT with 
> WHERE followed by AND choice of indexes can be different. This choice depends 
> on query planner and statistics. So there is always a chance that ORDER BY 
> will require additional sorting.

SQLite indexes work like this: every UNIQUE index (and primary key indices 
must, of course, be unique) ends in the table's primary key.  Even if did not 
specify it yourself, SQLite has added it invisibly and those primary key values 
are stored with the other values for each index entry.  So even a short index, 
on a table with a long primary key, means that the index takes up lots of 
filespace.

This has the effect you've noticed on anything with an ORDER BY clause: when 
SQLite decides to use an index to handle the clause, it automatically gets the 
primary key at the end of the index, and this governs the order in which rows 
are returned.

So yes, assuming an uncorrupt unchanging set of rows, assuming the same WHERE 
and ORDER BY clauses, and assuming nothing happens to change the query plan (as 
ANALYZE might) the same SELECT will always return them in the same order.  But 
this is still not documented as a requirement for SQL so it's still not a good 
idea to depend on it if you don't have to.  It may change in SQLite 4 or 
perhaps even before then.

There is one exception to the above which was created precisely to debug this 
point:  "PRAGMA reverse_unordered_selects = ON".  This is used specifically to 
test whether a programmer has made the above assumption and to warn them to fix 
their code.  And it can be done by any thread/process which has access to your 
database connection.

 Why not simply Order by your non-unique field AND then by row_id
> Unfortunately this is not simple. We don't have row_id. I am the library on 
> top of sqlite which accepts and executes SQL queries. I cannot force my users 
> to add ORDER BY to all queries especially for non UNIQUE indexes. And I need 
> to do some work under cover (checkpoint and reopen in DELETE mode) and then I 
> need to restore queries statues to their previous position.

Yep.  If it doesn't work there's nothing you can do about it.  It might be 
possible to parse all ORDER BY clauses passed to your library and add ",rowid" 
to the end of each one.  But I have no idea if that would be compatible with 
the way your library works.

Simon.

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

Re: [sqlite] transaction during reading on read-only database

[Klaas Van B.]

Ciao Andri,

To keep the results of a query consistent one better starts a transaction. A 
database can be updated by one thread at a time, but read by many.
Suppose you need a list of all employees of a department. If you start to 
search one at a time a lot of changes can happen. An employee can be moved to 
another department, fired or a new one entered. Another reason is speed. All 
(or most if it is a huge department and/or a tiny cache) employees are in cache 
so you're sure you have the requested list showing the situation on the moment 
of creation.

Andrii Motsok wrote Thu, 6 Oct 2016 11:43:10:

>My understanding is that any reading from database automatically starts read 
>transaction.
>The question: why does sqlite need to do it on read-only database?


Kind regards | Vriendelijke groeten | Cordiali saluti,
Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy
https://www.linkedin.com/in/klaas-van-buiten-0325b2102
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Default ordering of SELECT query

[Heather, James (ELS-LON)]

> > > > Any DB update or insert or vacuum or analyze might alter the
> > > > row order produced where the ordering isn't explicit.
> I am interested only in the same ordering between two subsequent
> query executions. The things which can happen in between are:
>  * checkpointing
>  * close/open in with different journal mode: DELETE->WAL/WAL->DELETE

You can't assume that it'll give you the same ordering even if nothing
at all happens in between.

You can't assume even that two executions on exactly the same database,
under exactly the same conditions, will return in the same order.
There's no contractual requirement for the implementation to be
deterministic.

If you write code and it works today, it might not work tomorrow.
Imagine: perhaps the internals of sqlite will be rewritten tomorrow;
perhaps the query will be executed on two threads, and the results of
the two threads will be merged; perhaps they'll be merged arbitrarily
if there's no reason to return the results in a specified order. This
won't be a breaking change as far as the sqlite API is concerned; but
it'll break your code.

The only safe way to write the code is to go by the strict semantics of
the statements you're executing; i.e., trust it to fulfil its contract,
but nothing else.

James



Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, 
Oxford, OX5 1GB, United Kingdom, Registration No. 1982084, Registered in 
England and Wales.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Default ordering of SELECT query

[Andrii Motsok]

>On 2016/10/05 5:27 PM, Andrii Motsok wrote:

>> Date: Mon, 3 Oct 2016 16:25:09 +0200
>>
>>
>> Hi,
>>
>> 1) In case of ORDER BY if we choose ordering which does not follow "index 
>> ordering" we will get performance degradation.
>> 2) If we use ORDER BY can we be sure that order of rows is the same for two 
>> subsequent calls (without any writes to database) for non UNIQUE index?
>> 3) Just from curiosity - could you please provide any real world scenario 
>> which shows how two similar subsequent calls can return different order of 
>> rows?
>>
>> Regards,
>> Andrii

>1) - How do you mean "follow index ordering"? If you order (Ascending or
>descending) on fields that are contained in an index, you will get full
>performance.
>
>2) No you can't be sure, well, currently that should be the case because
>the QP and sort orderer won't change algorithms between two sorts, but
>this is a very unsafe assumption. Any DB update or insert or vacuum or
>analyze might alter the row order produced where the ordering isn't
>explicit.
>
>3) We can show you what /might/ produce different row orders, but I
>don't know how to show what will definitely guarantee a different order.
>For the same reason it is unsafe to "assume" a static ordering, I can
>also not "assume" a different ordering unless specifically asking the
>sorter to adhere to another ordering. Point is, if you depend on the
>order, you have to specify it precisely. You can always order by more
>than 1 column, or even a function on a column. Why not simply Order by
>your non-unique field AND then by row_id. This will guarantee the same
>order.
>SELECT * FROM t ORDER BY t.NonUniqueField, t.row_id
>(That is, unless you are using WITHOUT ROWID tables, in which case use
>the PK.)

>>> How do you mean "follow index ordering"?
This is only my observation that sqlite usually returns rows in the order in 
which they are sorted in by index. So if I have two indexes and SELECT with 
WHERE followed by AND choice of indexes can be different. This choice depends 
on query planner and statistics. So there is always a chance that ORDER BY will 
require additional sorting.

>>>Any DB update or insert or vacuum or analyze might alter the row order 
>>>produced where the ordering isn't explicit.
I am interested only in the same ordering between two subsequent query 
executions. The things which can happen in between are:
 * checkpointing
 * close/open in with different journal mode: DELETE->WAL/WAL->DELETE

>>>Why not simply Order by your non-unique field AND then by row_id
Unfortunately this is not simple. We don't have row_id. I am the library on top 
of sqlite which accepts and executes SQL queries. I cannot force my users to 
add ORDER BY to all queries especially for non UNIQUE indexes. And I need to do 
some work under cover (checkpoint and reopen in DELETE mode) and then I need to 
restore queries statues to their previous position.

Regards,
Andrii




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

Re: [sqlite] transaction during reading on read-only database

[Dominique Pellé]

Andrii Motsok  wrote:

> Hi,
>
>
> My understanding is that any reading from database automatically
> starts read transaction. The question: why does sqlite need to do it on 
> read-only database?
>
>
> Regards,
>
> Andrii

You may open a database as read-only, but another process can open it
as read-write.  So SQLite needs to check the lock even when the DB
is opened as read-only.

Now if the database is on a read-only file system, it is possible to
tell SQLite by
opening with file:foo.sqlite?immutable=1  (see https://www.sqlite.org/uri.html)
and that can save some time. But make sure to read the caveats at above URL.

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

[sqlite] transaction during reading on read-only database

[Andrii Motsok]

Hi,


My understanding is that any reading from database automatically starts read 
transaction. The question: why does sqlite need to do it on read-only database?


Regards,

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

Re: [sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

Hello Richard !

Looking at the documentation, code and code comments I found that what 
I'm looking for somehow already exists in sqlite3, I mean I want the 
functionality of "temp" database as permanent let's call this database 
as "meta" anything created inside "meta" database would persist on disk 
and can be read again.


Can this be done ?

Cheers !


On 05/10/16 16:44, Richard Hipp wrote:

On 10/5/16, Domingo Alvarez Duarte  wrote:

I just found that the changes I made to sqlite3 to allow reference
objects on attached databases does not work properly,

Where in the view execution path the table/view qualifiers could be
discarded ?


I'm not sure exactly what you are looking for, perhaps you are seeking
the "sqlite3Fix()" routines found in attach.c.



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

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

Yes, fixed in pre-release snapshot 201610041220.


Thank you.

 
> On 10/5/16, Richard Hipp  wrote:
> > On 10/5/16, Clemens Ladisch  wrote:
> >>   stop
> >>
> >> This looks like a bug.
> >>
> >
> > I think it might be fixed on trunk.  I was just trying to bisect...
> 
> I think this may be a repeat of the problem described by ticket
> https://sqlite.org/src/info/0eab1ac759 and fixed on 2016-09-16 by
> check-in https://sqlite.org/src/info/a92aee5520cfaf85
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users