[sqlite] Corrupted database

[Simon Slavin]

On 4 Mar 2015, at 9:07pm, Scott Hess  wrote:

> In the time I've been involved with high-volume SQLite clients (Google
> Gears, then Chrome), what I've found is that the corruption invariably
> (*) ends up being a case where distinct pages were not written
> atomically, but where each page in isolation is completely valid when
> you inspect the data.  For instance, you update a row and the
> corresponding page for the table btree is updated but the page for the
> index btree is not.  Usually this correlated with power-failure
> events, but since corruptions of that sort aren't detected until
> SQLite actually compares the pages, the cause and detect can be
> separated by months.

Does SQLite automatically detect and uncorrupt these problems the next time it 
opens the database, or has something been done to the hardware to break 
in-order writing ?

Simon.

[sqlite] System.Data.SQLite and spellfix under VB.net

[sonypsx]

Hello Joe,

ok i'll tried:

gcc -s -O4 -I /path/to/sqlite/headers/ -shared -o spellfix.dll spellfix.c

and got this error:

c:\MinGW\bin>gcc -s -O4 -I c:\Sqlite\src\  -shared -o spellfix.dll
c:\Sqlite\ext
\misc\spellfix.c
In file included from c:\Sqlite\ext\misc\spellfix.c:17:0:
c:\Sqlite\src/sqlite3ext.h:20:21: fatal error: sqlite3.h: No such file or
direct
ory
 #include "sqlite3.h"
 ^
compilation terminated.

c:\MinGW\bin>

If I look into sqlite3ext.h i see the reference to the sqlite3.h file which
does not exist in the whole sqlite source (zip) which I downloaded from
http://www.sqlite.org/src/info/e693e11d1b926597


Can you help me please?

Best regards
sonypsx

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Joe
Mistachkin
Gesendet: Montag, 02. M?rz 2015 22:37
An: 'General Discussion of SQLite Database'
Betreff: Re: [sqlite] System.Data.SQLite and spellfix under VB.net


sonypsx wrote:
>
> could some please post a sample how to use the spellfix module with 
> System.Data.SQLite under VB.net?
> 

The first step would be to compile the spellfix extension as a loadable
module, as documented here:

https://www.sqlite.org/loadext.html

Next, you can load it using the SQLiteConnection.LoadExtension method.

Finally, you should be able to follow along with the normal spellfix docs,
here:

https://www.sqlite.org/spellfix1.html

--
Joe Mistachkin

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

[sqlite] System.Data.SQLite version 1.0.96.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.96.0 (with SQLite 3.8.8.3) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

[sqlite] static malloc_zone_t* _sqliteZone_

[Andy Rahn]

Ok, that's what I finally convinced myself, too.  Thanks for the confirmation.

 - Andy

On Wed, Mar 4, 2015 at 4:24 PM, Scott Perry  wrote:
> Good eye, thanks for reporting this.
>
> Pointers on the stack or in static storage are pointer-aligned by default on 
> all of Apple's platforms.
>
> On Mar 3, 2015, at 7:27 PM, Andy Rahn  wrote:
>>
>> Hi SQLite users;
>>
>> I have a question about _sqliteZone_ in mem1.c.  I notice that the
>> address of this static variable is used in a call to
>> OSAtomicCompareAndSwapPtrBarrier on MacOS and iOS.  That system call
>> is declared in OSAtomic.h, which includes a note about the pointer
>> alignment of its arguments:
>>
>>> * WARNING: all addresses passed to these functions must be "naturally 
>>> aligned",
>>> * i.e.  * int32_t pointers must be 32-bit aligned (low 2 bits 
>>> of
>>> * address are zeroes), and int64_t pointers must be 64-bit 
>>> aligned
>>> * (low 3 bits of address are zeroes.)
>>
>> I wonder, therefore, if it might be prudent to declare _sqliteZone_
>> with the alignment attribute, so that the compiler is sure to put it
>> at a 32 / 64 bit aligned address space? e .g.
>>
>> static __attribute__((aligned(8))) malloc_zone_t* _sqliteZone_;
>>
>> and also, because this local variable is used in that same function:
>>
>> __attribute__((aligned(8))) malloc_zone_t* newzone =
>> malloc_create_zone(4096, 0);
>>
>> I see that attribute is used one other place, so this may be an
>> important nuance.  On a 32-bit architecture, it would be safe to use
>> aligned(4) instead of aligned(8) but I'm not sure anyone will care
>> about the (possible) 4-byte savings.
>>
>> - Andy
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bitfield in Sqlite3-Table

[Hick Gunter]

In order of preference

a) use FastBit software
b) normalize your database design to eliminate the array
c) use a BLOB of 125 bytes and user defined functions to operate on them
d) use a string of 1000 characters ('0' or '1') and the SUBSTR() function

-Urspr?ngliche Nachricht-
Von: Oskar Schneider [mailto:oskars93 at yahoo.com]
Gesendet: Mittwoch, 04. M?rz 2015 16:42
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Bitfield in Sqlite3-Table

Hello,
what is the best way to implement a Bitfield of size 1000 as a column in a 
Table. I need to make queries to select all rows which have the nth bit set?The 
bitfield describes for each day in about three years if a specific task needs 
to be done.
___
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] Bitfield in Sqlite3-Table

[Oskar Schneider]

Hello,
what is the best way to implement a Bitfield of size 1000 as a column in a 
Table. I need to make queries to select all rows which have the nth bit set?The 
bitfield describes for each day in about three years if a specific task needs 
to be done.

[sqlite] SQLite error(5): database is locked in System.Data.SQLite

[Drago, William @ CSG - NARDA-MITEQ]

There is some info here:

http://sqlite.org/threadsafe.html

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter
> Sent: Wednesday, March 04, 2015 8:45 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] SQLite error(5): database is locked in
> System.Data.SQLite
>
> Only one writer may be active at any one time. The other(s) will
> recieve an error return status. You can either wait a while and retry
> this in your application or set a busy timeout to handle the "usual"
> cases for you.
>
> -Urspr?ngliche Nachricht-
> Von: Rohit Savaliya [mailto:rohit.savaliya at matrixcomsec.com]
> Gesendet: Mittwoch, 04. M?rz 2015 07:46
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] SQLite error(5): database is locked in
> System.Data.SQLite
>
> Hi all,
>
> I am using System.Data.SQLite in my project.
> I have two threads which are updating database simultaneously.
> I get SQLite error(5): database is locked, System.Data.SQLite error.
>
> Why is it so!. What could be the reason? Does System.Data.SQLite is not
> thread safe?
> Please help me out.
>
> --
> Regards
> Rohit Savaliya
> ___
> 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
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Corrupted database

[Dave Dyer]

I'd be interested if you could characterize the corruption.  For 
example, can use still use .dump to dump the database, and if so
what kind of damage is there?

The cases I've encountered recently, the "corruption" was only 
a few duplicated records, which broke the uniqueness constraint
on an index.

Interestingly, and perhaps alarmingly, if the index hadn't existed,
no corruption would have been detected, although the internal events
that allowed the duplicate entries would still be an undetected error.

[sqlite] Corrupted database

[Dave Dyer]

I'd be interested if you could characterize the corruption.  For 
example, can use still use .dump to dump the database, and if so
what kind of damage is there?

The cases I've encountered recently, the "corruption" was only 
a few duplicated records, which broke the uniqueness constraint
on an index.

Interestingly, and perhaps alarmingly, if the index hadn't existed,
no corruption would have been detected, although the internal events
that allowed the duplicate entries would still be an undetected error.

[sqlite] static malloc_zone_t* _sqliteZone_

[Scott Perry]

Good eye, thanks for reporting this.

Pointers on the stack or in static storage are pointer-aligned by default on 
all of Apple's platforms.

On Mar 3, 2015, at 7:27 PM, Andy Rahn  wrote:
> 
> Hi SQLite users;
> 
> I have a question about _sqliteZone_ in mem1.c.  I notice that the
> address of this static variable is used in a call to
> OSAtomicCompareAndSwapPtrBarrier on MacOS and iOS.  That system call
> is declared in OSAtomic.h, which includes a note about the pointer
> alignment of its arguments:
> 
>> * WARNING: all addresses passed to these functions must be "naturally 
>> aligned",
>> * i.e.  * int32_t pointers must be 32-bit aligned (low 2 bits of
>> * address are zeroes), and int64_t pointers must be 64-bit 
>> aligned
>> * (low 3 bits of address are zeroes.)
> 
> I wonder, therefore, if it might be prudent to declare _sqliteZone_
> with the alignment attribute, so that the compiler is sure to put it
> at a 32 / 64 bit aligned address space? e .g.
> 
> static __attribute__((aligned(8))) malloc_zone_t* _sqliteZone_;
> 
> and also, because this local variable is used in that same function:
> 
> __attribute__((aligned(8))) malloc_zone_t* newzone =
> malloc_create_zone(4096, 0);
> 
> I see that attribute is used one other place, so this may be an
> important nuance.  On a 32-bit architecture, it would be safe to use
> aligned(4) instead of aligned(8) but I'm not sure anyone will care
> about the (possible) 4-byte savings.
> 
> - Andy
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?]

[Hick Gunter]

I have worked with numeric data and also "keyword" data, i.e. a small number of 
distinct keywords that appear in a string (or not).

-Urspr?ngliche Nachricht-
Von: Dominique Devienne [mailto:ddevienne at gmail.com]
Gesendet: Mittwoch, 04. M?rz 2015 10:39
An: General Discussion of SQLite Database
Betreff: [sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?]

On Wed, Mar 4, 2015 at 10:08 AM, Hick Gunter  wrote:

> Properly implemented virtual tables do support indexing, but you have
> to write the code to support that yourself.
>
> I have personally implemented an index based on the fastbit package
> which is ideally suited to retrieving large data sets via equality and
> range constraints.
>
> See https://sdm.lbl.gov/fastbit/


Sounds interesting. Is this index suited to numeric datatypes, or are 
strings/blobs also supported?

Can you share a little more details please? (if the code is not OSS and 
available somewhere to look at). This sounds very interesting. Thanks, --DD 
___
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] SQLite error(5): database is locked in System.Data.SQLite

[Simon Slavin]

On 4 Mar 2015, at 1:45pm, Hick Gunter  wrote:

> Only one writer may be active at any one time. The other(s) will recieve an 
> error return status. You can either wait a while and retry this in your 
> application or set a busy timeout to handle the "usual" cases for you.

Agreed.  The default timeout for SQLite is zero, which means that the first 
time both threads try to access the database at the same time an error will 
occur.  Setting a retry time will fix this.

If someone is using System.Data.SQLite how do they set a busy timeout ?  Do 
they have access to the C function or the PRAGMA ?

Simon.

[sqlite] SQLite error(5): database is locked in System.Data.SQLite

[Hick Gunter]

Only one writer may be active at any one time. The other(s) will recieve an 
error return status. You can either wait a while and retry this in your 
application or set a busy timeout to handle the "usual" cases for you.

-Urspr?ngliche Nachricht-
Von: Rohit Savaliya [mailto:rohit.savaliya at matrixcomsec.com]
Gesendet: Mittwoch, 04. M?rz 2015 07:46
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] SQLite error(5): database is locked in System.Data.SQLite

Hi all,

I am using System.Data.SQLite in my project.
I have two threads which are updating database simultaneously.
I get SQLite error(5): database is locked, System.Data.SQLite error.

Why is it so!. What could be the reason? Does System.Data.SQLite is not thread 
safe?
Please help me out.

--
Regards
Rohit Savaliya
___
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] Corrupted database

[Scott Hess]

On Wed, Mar 4, 2015 at 1:11 AM, Alexandr N?mec  wrote:
> we have a product that uses SQLite. Because it was running very stable for
> years, we are still using SQLite 3.7.17. Now we've seen on one of our
> installations that the database has been corrupted, we saw that there was a
> power failure around the time of the corruption. We tested power failures by
> ourselves in the past, the database had always survived. We are also aware
> of this https://www.sqlite.org/atomiccommit.html#sect_9_0
> 
>
> Nevertheless, I have the corrupted database and I could make it available if
> someone likes to have a look at it. Maybe it would be possible to inspect
> the kind of the corruption to see whether it might be an SQLite problem or
> if one of the "Things that can go wrong" (see the aforementioned link)
> kicked in...

In the time I've been involved with high-volume SQLite clients (Google
Gears, then Chrome), what I've found is that the corruption invariably
(*) ends up being a case where distinct pages were not written
atomically, but where each page in isolation is completely valid when
you inspect the data.  For instance, you update a row and the
corresponding page for the table btree is updated but the page for the
index btree is not.  Usually this correlated with power-failure
events, but since corruptions of that sort aren't detected until
SQLite actually compares the pages, the cause and detect can be
separated by months.

After analyzing many dozens of these over time, I mostly stopped
tracking down and reviewing people's files, because it really wasn't
telling me anything actionable.  SQLite wasn't breaking the files, and
AFAICT SQLite's system to guarantee atomicity works, so if some
combination of the operating system and hardware are doing causing the
problem you just need to figure out ways to recognize and deal with it
at a higher layer.

-scott

(*) I have also seen cases where someone intentionally messed with
disk I/O and screwed SQLite up.  I don't really count those, because
there's not much you can do to protect yourself against clever but
mis-guided programmers.

[sqlite] SQLite error(5): database is locked in System.Data.SQLite

[Rohit Savaliya]

Hi all,

I am using System.Data.SQLite in my project.
I have two threads which are updating database simultaneously.
I get SQLite error(5): database is locked, System.Data.SQLite error.

Why is it so!. What could be the reason? Does System.Data.SQLite is not
thread safe?
Please help me out.

-- 
Regards
Rohit Savaliya

[sqlite] FastBit-based vtable impl [WAS: Multi-table index ersatz?]

[Dominique Devienne]

On Wed, Mar 4, 2015 at 10:08 AM, Hick Gunter  wrote:

> Properly implemented virtual tables do support indexing, but you have to
> write the code to support that yourself.
>
> I have personally implemented an index based on the fastbit package which
> is ideally suited to retrieving large data sets via equality and range
> constraints.
>
> See https://sdm.lbl.gov/fastbit/


Sounds interesting. Is this index suited to numeric datatypes, or are
strings/blobs also supported?

Can you share a little more details please? (if the code is not OSS and
available somewhere to look at). This sounds very interesting. Thanks, --DD

[sqlite] Corrupted database

[Alexandr Němec]

Hi all,
?
we have a product that uses SQLite. Because it was running very stable for 
years, we are still using SQLite 3.7.17. Now we've seen on one of our 
installations that the database has been corrupted, we saw that there was a 
power failure around the time of the corruption. We tested power failures by 
ourselves in the past, the database had always survived. We are also aware of 
this https://www.sqlite.org/atomiccommit.html#sect_9_0 

?
Nevertheless, I have the corrupted database and I could make it available if 
someone?likes to have a look at it. Maybe it would be possible to inspect the 
kind of the corruption to see?whether it might be an SQLite problem or if one 
of the "Things that can go wrong" (see the aforementioned link) kicked in...
?
Thanks
?
Alex

[sqlite] Multi-table index ersatz?

[Hick Gunter]

Properly implemented virtual tables do support indexing, but you have to write 
the code to support that yourself.

I have personally implemented an index based on the fastbit package which is 
ideally suited to retrieving large data sets via equality and range constraints.

See https://sdm.lbl.gov/fastbit/

-Urspr?ngliche Nachricht-
Von: Eric Grange [mailto:zarglu at gmail.com]
Gesendet: Mittwoch, 04. M?rz 2015 08:24
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Multi-table index ersatz?

> Rowids will be faster than primary keys.

My primary keys are ROWIDs ("INTEGER PRIMARY KEY" actually)

None of the index was exploited for the order by, and the matched records in 
table A are scattered in pages all over the database, so ordering them in 
memory has a tendency to "replace" the whole SQLite cache: first time a query 
is run, it's slow, second time, it's fast, but if you change the condition 
value (?1) then it's slow again as the page cache is invalidated (it is very 
visible in the resource monitor, with a disk access spike)

> You might be able to make the new table a WITHOUT ROWID table and set
> its
PRIMARY KEY up with the same (or a superset of the) fields of your "fairly 
large index" in order to save a bit of space.

I have been experimenting that way, and actually since A1 and B1 should fit 
32bits integers for the foreseeable future, combining them into a 64bit integer 
is possible, and I use (A1 << 32) | B1  as "INTEGER PRIMARY KEY"
(ROWID). This makes a separate composite index unnecessary as the primary key 
becomes the composite index: the equality condition becomes a range condition 
on the rowid, with an order by on the rowid, both being fast and cache-friendly.

It reduces disk usage significantly over the previous full-blown C table + 
composite index, it is still a sort of manually-managed hacky index, which 
involves extra queries to maintain it. But at the moment it seems to be the 
"better" solution.

> It might be possible to write a virtual table module that does the
> same as your index on C, but with C being a view.

I had a look that way, but AFAICT virtual tables do not support indexing, so I 
would have to index manually.


On Tue, Mar 3, 2015 at 4:57 PM, Dan Kennedy  wrote:

> On 03/03/2015 06:10 PM, Eric Grange wrote:
>
>> Hi,
>>
>> I have problem where I need a "multi-table index" ersatz, or maybe a
>> better data structure :-)
>>
>> The problem is as follow:
>>
>> - Table A : some fields plus fields A1 & A2
>> - Table B : some fields plus fields B1 & B2
>>
>> Both tables have several dozen millions of rows, and both are
>> accessed independently of each others by some queries, their current
>> structure has no performance issues for those queries.
>>
>> However I have a new query which is like
>>
>> select ...some fields of A & B...
>> from A join B on A.A2 = B.B2
>> where A.A1 = ?1
>> order by B.B1
>> limit 100
>>
>>
>> Without the limit, there can be tens of thousandths resulting rows,
>> without the A1 condition, there can be millions of resulting rows.
>>
>> With indexes on A & B, the performance of the above is not very good,
>> as indexing A1 is not enough, and indexing B1 is not enough either,
>> so no query plan is satisfying.
>>
>> I can make the query instantaneous by duplicating the A1 & B1 fields
>> in a dedicated C table (along with the primary keys of A & B), index
>> that table, and then join back the A & B table to get the other
>> fields.
>>
>> However this results in a fairly large table of duplicated data,
>> whose sole purpose is to allow the creation of a fairly large index,
>> which gets me the performance.
>>
>
> You might be able to make the new table a WITHOUT ROWID table and set
> its PRIMARY KEY up with the same (or a superset of the) fields of your
> "fairly large index" in order to save a bit of space.
>
>
>
>
>
>> Note that if the fields A1 & B1 are removed from their tables and
>> kept only in C, this has massive performance implication on other
>> queries running only against A & B, as those fields are leveraged in
>> other composite indexes.
>>
>> Is there a better way that would not involve duplicating the data?
>>
>> Eric
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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 

[sqlite] Multi-table index ersatz?

[Eric Grange]

> Rowids will be faster than primary keys.

My primary keys are ROWIDs ("INTEGER PRIMARY KEY" actually)

None of the index was exploited for the order by, and the matched records
in table A are scattered in pages all over the database, so ordering them
in memory has a tendency to "replace" the whole SQLite cache: first time a
query is run, it's slow, second time, it's fast, but if you change the
condition value (?1) then it's slow again as the page cache is invalidated
(it is very visible in the resource monitor, with a disk access spike)

> You might be able to make the new table a WITHOUT ROWID table and set its
PRIMARY KEY up with the same (or a superset of the) fields of your "fairly
large index" in order to save a bit of space.

I have been experimenting that way, and actually since A1 and B1 should fit
32bits integers for the foreseeable future, combining them into a 64bit
integer is possible, and I use (A1 << 32) | B1  as "INTEGER PRIMARY KEY"
(ROWID). This makes a separate composite index unnecessary as the primary
key becomes the composite index: the equality condition becomes a range
condition on the rowid, with an order by on the rowid, both being fast and
cache-friendly.

It reduces disk usage significantly over the previous full-blown C table +
composite index, it is still a sort of manually-managed hacky index, which
involves extra queries to maintain it. But at the moment it seems to be the
"better" solution.

> It might be possible to write a virtual table module that does the same
> as your index on C, but with C being a view.

I had a look that way, but AFAICT virtual tables do not support indexing,
so I would have to index manually.


On Tue, Mar 3, 2015 at 4:57 PM, Dan Kennedy  wrote:

> On 03/03/2015 06:10 PM, Eric Grange wrote:
>
>> Hi,
>>
>> I have problem where I need a "multi-table index" ersatz, or maybe a
>> better
>> data structure :-)
>>
>> The problem is as follow:
>>
>> - Table A : some fields plus fields A1 & A2
>> - Table B : some fields plus fields B1 & B2
>>
>> Both tables have several dozen millions of rows, and both are accessed
>> independently of each others by some queries, their current structure has
>> no performance issues for those queries.
>>
>> However I have a new query which is like
>>
>> select ...some fields of A & B...
>> from A join B on A.A2 = B.B2
>> where A.A1 = ?1
>> order by B.B1
>> limit 100
>>
>>
>> Without the limit, there can be tens of thousandths resulting rows,
>> without
>> the A1 condition, there can be millions of resulting rows.
>>
>> With indexes on A & B, the performance of the above is not very good, as
>> indexing A1 is not enough, and indexing B1 is not enough either, so no
>> query plan is satisfying.
>>
>> I can make the query instantaneous by duplicating the A1 & B1 fields in a
>> dedicated C table (along with the primary keys of A & B), index that
>> table,
>> and then join back the A & B table to get the other fields.
>>
>> However this results in a fairly large table of duplicated data, whose
>> sole
>> purpose is to allow the creation of a fairly large index, which gets me
>> the
>> performance.
>>
>
> You might be able to make the new table a WITHOUT ROWID table and set its
> PRIMARY KEY up with the same (or a superset of the) fields of your "fairly
> large index" in order to save a bit of space.
>
>
>
>
>
>> Note that if the fields A1 & B1 are removed from their tables and kept
>> only
>> in C, this has massive performance implication on other queries running
>> only against A & B, as those fields are leveraged in other composite
>> indexes.
>>
>> Is there a better way that would not involve duplicating the data?
>>
>> Eric
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug: Command Line Shell: Control-C interrupt handler not enabled on Windows

[contactda...@softwariness.com]

Hello,

The SQLite Command Line Shell is documented
(http://www.sqlite.org/sessions/sqlite.html) to support interrupting a
long-running SQL statement by using the interrupt character (Control-C).

On Windows, the shell is terminated immediately by Control-C, rather than
interrupting the SQL statement.

Looking at the code in the "C source code as an amalgamation, version
3.8.8.3" from the current SQLite download page, I see that in shell.c, there
is a handler for SIGINT registered if SIGINT is defined:

#ifdef SIGINT
  signal(SIGINT, interrupt_handler);
#endif

SIGINT is defined in the signal.h header, but that header has been
deliberately excluded from shell.c on Windows:

#if !defined(_WIN32) && !defined(WIN32)
# include 
# if !defined(__RTP__) && !defined(_WRS_KERNEL)
#  include 
# endif
# include 
# include 
#endif

Including signal.h in shell.c causes the signal handler to be registered,
and I do get the interruption without process termination to a long-running
SQL command with Control-C as expected. However, this only works for the
first Control-C; a Control-C during a subsequent long-running SQL command
causes immediate process termination. signal(SIGINT) is also documented as
not being supported on MSDN, so I tried using the Windows-specific
SetConsoleCtrlHandler command to register a handler instead, and this
approach works correctly for subsequent interrupts too.

Incidentally, the handler will execute on a separate thread on Windows,
something to bear in mind if the shell isn't intended to be multi-threaded
(I know the SQLite library itself is documented to be thread-safe).

This is the diff for shell.c for the source version mentioned (first time
posting, I don't know whether attachments are permitted on this list, so
including inline):


757c757
< #ifdef SIGINT
---
> #if defined(SIGINT) || defined(_WIN32) || defined(WIN32)
766a767,782
>
> #if defined(_WIN32) || defined(WIN32)
> /*
> ** Windows event handler
> */
> BOOL WINAPI CtrlHandler(DWORD dwType){
>   switch( dwType ){
> case CTRL_C_EVENT:
>   interrupt_handler(0);
>   return TRUE;
>
> default:
>   return FALSE;
>   }
> }
> #endif
4207a4224,4225
> #elif defined(WIN32) || defined(_WIN32)
>   SetConsoleCtrlHandler(CtrlHandler, TRUE);


Kind regards,
David

[sqlite] full table scan ignores PK sort order?

[Mohit Sindhwani]

On 3/3/2015 6:59 PM, Jean-Christophe Deschamps wrote:
> At 11:27 03/03/2015, you wrote:
> 
>> - the full table scan returns rows in rowID order, which is the order 
>> in which the rows were added to the table
> `---
>
> No and no.
>
> An SQL engine doesn't guarantee any row "order" unless you explicitely 
> force an ORDER BY clause. Think of row order as random, where rowid 
> order is just a possibility among zillions others. Of course neither 
> SQLite nor other engines willingly use random() to foil your 
> expectations but you should never rely on such an implementation detail.
>
> Also rowids are technically independant of insertion order: you may 
> feed any valid random literal rowids at insert time.
>

If it wasn't a performance issue, I wish that SQLite would sometimes 
actually return values that are not in the order of insertion or by 
rowid just so that people would learn this lesson earlier :)

For sure, it took me a while... actually, till I read a book about SQlite.

Cheers,
Mohit.