Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Keith Medcalf]

Unique is implemented (as it must be) via a unique index.

CREATE TABLE ItemTable (
  "key" text UNIQUE,
  value blob NOT NULL
);

is equivalent to

CREATE TABLE ItemTable (
  "key" text,
  value blob NOT NULL
);
CREATE UNIQUE INDEX sqlite_autoindex_ItemTable_1 on ItemTable (key);

You should be able to rebuild the corrupted index with:

REINDEX ItemTable;

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jerry Krinock
>Sent: Monday, 23 June, 2014 23:33
>To: SQLite SQLite Users
>Subject: Re: [sqlite] Adding WHERE to query --> database disk image is
>malformed
>
>Oh, I just realized that, although Igor's suggestion "fixed" the problem,
>this database definitely does not contain any indexes.  The schema dump
>is simply this...
>
>CREATE TABLE ItemTable (
>  "key" text UNIQUE,
>  value blob NOT NULL
>);
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

Oh, I just realized that, although Igor’s suggestion “fixed” the problem, this 
database definitely does not contain any indexes.  The schema dump is simply 
this…

CREATE TABLE ItemTable (
  "key" text UNIQUE,
  value blob NOT NULL
);


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

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

On 2014 Jun 23, at 21:49, Igor Tandetnik  wrote:

> One possibility: the query with WHERE clause attempts to use some index, 
> while the query without doesn't.
> 
> Try this query:
> 
> SELECT * FROM itemTable WHERE +key = 'profileName' ;
> 
> Note the + sign - this suppresses the use of index.

Very good, Igor!  Indeed, adding “+” to the query makes it work, no database 
corruption.

>  The data corruption happens to affect the area of the file where this index 
> is stored.

Ah, so now we still need to find the root cause.  Fortunately, I was able to 
dig up an older database file which contains exactly the same data, but works 
properly.  Performing a binary diff on the Good vs. Bad file found only four 
bytes different.

The first three differences are in the Header: the file change counter, the 
version_valid_for, and the SQLITE_VERSION_NUMBER.  As expected, changing these 
did not help.  (The Good file is 3.7.6, the Bad file is 3.7.13, and I am using 
the sqlite command-line tool version 3.7.13.)

The culprit is byte 2048, the first byte in the 3rd page, assuming the 100-byte 
Header is part of the first page.  In the Good file, it is 0x0A and in the Bad 
file, it’s 0x0D.  Kind of weird that this would be part of an index - looks 
like a Mac vs. Unix line ending clash.

But that’s the easy part.  I wonder how long I’d have to study the sqlite file 
format document to decode the purpose of Byte 2048.  For practical purposes, I 
could maybe just fix the bad byte and move on, but this database is a resource 
in an app I develop, and I sure would like to know how this happened.

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

Re: [sqlite] Adding WHERE to query --> database disk image is malformed

[Igor Tandetnik]

On 6/23/2014 11:16 PM, Jerry Krinock wrote:

How can it be that adding a WHERE clause to a successful query causes ‘database 
disk image is malformed’?


One possibility: the query with WHERE clause attempts to use some index, 
while the query without doesn't. The data corruption happens to affect 
the area of the file where this index is stored.


Try this query:

SELECT * FROM itemTable WHERE +key = 'profileName' ;

Note the + sign - this suppresses the use of index.
--
Igor Tandetnik

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

[sqlite] Adding WHERE to query --> database disk image is malformed

[Jerry Krinock]

How can it be that adding a WHERE clause to a successful query causes ‘database 
disk image is malformed’?

My database has one table named `itemTable`.  This table has two columns, `key` 
which is type text and `value` which is type blob.  There are two rows of data. 
 Their `value` blobs are actually strings encoded as UTF16 little endian.

The issue is demonstrated in the following transcript, using the sqlite command 
line tool in Mac OS X.

Air2: jk$ sqlite3 Test.sql 
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT key from itemTable ;
profileName
extoreName
sqlite> SELECT key FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'nonexistent' ;
Error: database disk image is malformed

// Same succeed,fail result if I change query to "SELECT *" instead of "SELECT 
key".

sqlite> SELECT * FROM `itemTable` ;
profileName|Fooobar
extoreName|Baah
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed

One thing I find rather surprising is that sqlite seems to know that the 
14-byte and 24-byte blobs are UTF16-LE encoded strings, and prints them as 
“Fooobar” and “Baah”.

Is my database OK or malformed?

The same thing happens when I execute the failing query with the sqlite3 C 
Library, using years-old tested code.

Thank you!

Jerry Krinock

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

[sqlite] System.Data.SQLite version 1.0.93.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.93.0 (with SQLite 3.8.5) 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update a BLOB

[Simon Slavin]

> On 23 Jun 2014, at 4:10pm, Carlos Ferreira  wrote:
> 
> To access Blobs I use the direct access BLOB api from SQLite.
> 
> However, what is the best way to update a record that contains a BLOB, and 
> the only goals is to update the BLOB...
> 
> The BLOB may shrink or increase...and that is the reason why I cannot use the 
> BLOB Write and...
> 
> 
> It does not seem reasonable to create a Statement with the BLOB string ...

There is no reason not to use a normal UPDATE command, either including the 
BLOB as a hex string or binding the blob from memory using

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

as detailed here:



with location and length (it is stupid that that parameter is called 'n' since 
it always represents length).  The fifth parameter allows you to supply a 
destructor routine so you can decide exactly how to free the memory your BLOB 
is in.  You can provide your own routine, or tell SQLite that not to worry 
about it, or tell SQLite that it needs to make its own copy which it can then 
dispose of when it no longer needs it.

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

[sqlite] Update a BLOB

[Carlos Ferreira]

Hello,

One question.


To access Blobs I use the direct access BLOB api from SQLite.

However, what is the best way to update a record that contains a BLOB, and the 
only goals is to update the BLOB...

The BLOB may shrink or increase...and that is the reason why I cannot use the 
BLOB Write and...


It does not seem reasonable to create a Statement with the BLOB string ...

So what is the best way to update a record with a BLOB?

Thanks

Carlos
 


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Carlos Ferreira
Sent: segunda-feira, 23 de Junho de 2014 15:52
To: 'General Discussion of SQLite Database'
Subject: [sqlite] SQLite and BLOBs

Hello,

I found this reference in this link:

http://stackoverflow.com/questions/676524/how-to-update-piecewise-a-blob-in-sqlite


"
This is not directly an answer to your question, but I have some experience 
using random access for (big) blobs in SQLite, and I advise you against using 
it, if you can. Here's why:

Blobs break the SQL query format entirely. If your blob data needs any kind of 
processing, it will certainly at some point need filtering. Whatever mechanism 
you have in place to deal with filtering in SQL will be useless in this regard.

Dealing with binary blobs wrapped in databases opposed to binary data in raw 
files limits your options. You will not be able to randomly read and write to 
data at the same time from multiple processes, which is possible with files. 
You can't use any tool that deals with this data and provides only a file I/O 
interface. You can't truncate or resize the blob. Files are simply a lot more 
versatile.

It may seem convenient to have everything contained within one file, as it 
simplifies backup and transfer, but the pain of working with blobs is simply 
not worth it.

So as your attorney, I advise you to write your blobs as raw files to the file 
system, and merely store a reference to the filename in your database. If your 
blobs are rather small and guaranteed not to grow, forget my advice.
"

Do you agree with this information?

Regards.

Carlos



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: segunda-feira, 23 de Junho de 2014 15:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Planner chooses incorrect index

On 06/23/2014 05:48 AM, João Ramos wrote:
> Here you go:
>
> sqlite_stat1 (before - good planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5 HistoryEntry 
> idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 HistoryEntry 
> idx_HistoryEntry_sourceType_sourceId 14992 2999 2
>
> sqlite_stat1 (after - bad planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5 HistoryEntry 
> idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 HistoryEntry 
> idx_HistoryEntry_sourceType_sourceId 15492 3099 2

Unfortunately the last column of the sqlite_stat4 data is missing, likely 
because it contains embedded 0x00 bytes. And without the
sqlite_stat4 data, it seems that SQLite picks the "good" query plan in either 
case.

Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell ".dump" 
command instead?

Thanks,
Dan.



>
> sqlite_stat4 (before - good planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17
> 17 81 HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566
> 661 38 96
> 661
> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665
> 56
> 352 1665
> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569
> 2899 106
> 447 2899
> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331
> 106
> 462 3331
> HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825
> 4997 131
> 660 4997
> HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178
> 6232 154
> 931 6232
> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412
> 6477 162
> 974 6477
> HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663
> 169 984
> 6663
> HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953
> 7488 186
> 1062 7488
> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260
> 8329 195
> 1361 8329
> HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965
> 218
> 1439 8965
> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128
> 9129 227
> 1501 9129
> HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622
> 9650 237
> 1579 9650
> HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954
> 9995 244
> 1709 9995
> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192
> 11322 246
> 1898 11322
> HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503
> 11661 252
> 1963 11661
> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921
> 12037 258
> 1994 12037
> HistoryEntry idx_HistoryEntry_uri_historyOrder 

[sqlite] SQLite and BLOBs

[Carlos Ferreira]

Hello,

I found this reference in this link:

http://stackoverflow.com/questions/676524/how-to-update-piecewise-a-blob-in-sqlite


"
This is not directly an answer to your question, but I have some experience 
using random access for (big) blobs in SQLite, and I advise you against using 
it, if you can. Here's why:

Blobs break the SQL query format entirely. If your blob data needs any kind of 
processing, it will certainly at some point need filtering. Whatever mechanism 
you have in place to deal with filtering in SQL will be useless in this regard.

Dealing with binary blobs wrapped in databases opposed to binary data in raw 
files limits your options. You will not be able to randomly read and write to 
data at the same time from multiple processes, which is possible with files. 
You can't use any tool that deals with this data and provides only a file I/O 
interface. You can't truncate or resize the blob. Files are simply a lot more 
versatile.

It may seem convenient to have everything contained within one file, as it 
simplifies backup and transfer, but the pain of working with blobs is simply 
not worth it.

So as your attorney, I advise you to write your blobs as raw files to the file 
system, and merely store a reference to the filename in your database. If your 
blobs are rather small and guaranteed not to grow, forget my advice.
"

Do you agree with this information?

Regards.

Carlos



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: segunda-feira, 23 de Junho de 2014 15:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Planner chooses incorrect index

On 06/23/2014 05:48 AM, João Ramos wrote:
> Here you go:
>
> sqlite_stat1 (before - good planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5 HistoryEntry 
> idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 HistoryEntry 
> idx_HistoryEntry_sourceType_sourceId 14992 2999 2
>
> sqlite_stat1 (after - bad planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5 HistoryEntry 
> idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 HistoryEntry 
> idx_HistoryEntry_sourceType_sourceId 15492 3099 2

Unfortunately the last column of the sqlite_stat4 data is missing, likely 
because it contains embedded 0x00 bytes. And without the
sqlite_stat4 data, it seems that SQLite picks the "good" query plan in either 
case.

Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell ".dump" 
command instead?

Thanks,
Dan.



>
> sqlite_stat4 (before - good planning)
> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 
> 17 81 HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 
> 661 38 96
> 661
> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 
> 56
> 352 1665
> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 
> 2899 106
> 447 2899
> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 
> 106
> 462 3331
> HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825 
> 4997 131
> 660 4997
> HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 
> 6232 154
> 931 6232
> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 
> 6477 162
> 974 6477
> HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 
> 169 984
> 6663
> HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 
> 7488 186
> 1062 7488
> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 
> 8329 195
> 1361 8329
> HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965 
> 218
> 1439 8965
> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 
> 9129 227
> 1501 9129
> HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 
> 9650 237
> 1579 9650
> HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954 
> 9995 244
> 1709 9995
> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192 
> 11322 246
> 1898 11322
> HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503 
> 11661 252
> 1963 11661
> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921 
> 12037 258
> 1994 12037
> HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574 
> 12586 261
> 2013 12586
> HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862 
> 12973 265
> 2187 12973
> HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101 
> 13327 270
> 2291 13327
> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722 
> 13973 295
> 2388 13973
> HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 
> 14530 331
> 2749 14530
> HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 
> 14849 333
> 3067 14849
> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 
> 0
> 1665 1665 0 1663 1665
> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 

Re: [sqlite] Data visibility problem

[Simon Slavin]

On 23 Jun 2014, at 11:17am, João Ramos  wrote:

> On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin  wrote:
> 
>> Are you finalizing all these SQL commands (or using _exec() which amounts
>> to the same thing) ?
> 
> Yes, I double checked and everything is being terminated properly (e.g.:
> sqlite3_reset, etc.) and without any error codes.

Excellent.

>> What do you mean by 'releases' here ?  If you have other threads using the
>> same connection, you're obviously not closing it.
> 
> No, the SQLite connection isn't closed. By "release" I mean release back to
> the thread pool.

Okay.  I would temporarily, for testing purposes, turn off shared cache mode.  
Another thing to try messing with is read_uncommitted:



Which of these does or doesn't change how your app behaves might help you 
diagnose the problem.

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

Re: [sqlite] Planner chooses incorrect index

[Dan Kennedy]

On 06/23/2014 05:48 AM, João Ramos wrote:

Here you go:

sqlite_stat1 (before - good planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2

sqlite_stat1 (after - bad planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2
HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2


Unfortunately the last column of the sqlite_stat4 data is missing, 
likely because it contains embedded 0x00 bytes. And without the 
sqlite_stat4 data, it seems that SQLite picks the "good" query plan in 
either case.


Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell 
".dump" command instead?


Thanks,
Dan.





sqlite_stat4 (before - good planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 17 81
HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 661 38 96
661
HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 56
352 1665
HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 2899 106
447 2899
HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 106
462 3331
HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825 4997 131
660 4997
HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 6232 154
931 6232
HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 6477 162
974 6477
HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 169 984
6663
HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 7488 186
1062 7488
HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 8329 195
1361 8329
HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965 218
1439 8965
HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 9129 227
1501 9129
HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 9650 237
1579 9650
HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954 9995 244
1709 9995
HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192 11322 246
1898 11322
HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503 11661 252
1963 11661
HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921 12037 258
1994 12037
HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574 12586 261
2013 12586
HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862 12973 265
2187 12973
HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101 13327 270
2291 13327
HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722 13973 295
2388 13973
HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 14530 331
2749 14530
HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 14849 333
3067 14849
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
1665 1665 0 1663 1665
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
3331 3331 0 3329 3331
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
4997 4997 0 4995 4997
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
6663 6663 0 6661 6663
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
8329 8329 0 8327 8329
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
9995 9995 0 9993 9995
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
11661 11661 0 11659 11661
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392
12392 12392 377 12390 12392
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 12557
12558 12558 530 12556 12558
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042
13044 13044 998 13042 13044
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13049
13049 13049 1000 13047 13049
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13061
13061 13061 1006 13059 13061
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210
13212 13212 1150 13210 13212
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13327
13327 13327 1261 13325 13327
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13411
13411 13411 1344 13409 13411
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 6 1 1 13572
13573 13573 1498 13571 13573
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13582
13585 13585 1503 13583 13585
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13726
13727 13727 1643 13725 13727
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 8 1 1 13978
13978 13978 1890 13976 13978
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14203
14206 14206 2098 14204 14206
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 

Re: [sqlite] Data visibility problem

[Igor Tandetnik]

On 6/23/2014 5:43 AM, João Ramos wrote:

If multiple connections in shared cache mode are treated as a single "real"
connection, should I change the thread mode to Serialized?


What mode do you use now?

In any case, I doubt the thread mode has any bearing on the problem. The 
scenario may occur even if no two threads ever run in parallel. It's 
only necessary that SELECTs on different connections to shared cache 
interleave: you call sqlite3_step on connection A, then later call 
sqlite3_step on connection B (while the statement in A has not been 
reset) - that second traversal would see the data as of the start of the 
first traversal.



I tough this
"single" connection was in terms of page cache


Well, yes. So you have a version of a page in this cache - that version 
must be as of the time of the oldest read transaction still outstanding. 
So all connections to that shared cache see the same version of that 
page, and cannot observe any changes made to it later.



and would not have any
implications on how the connections are accessed (or not) concurrently,
especially transaction control.


Concurrent access and transaction control are unrelated concepts. I'm 
not sure why you are bringing them up together. You can observe 
transaction isolation effects with a single thread interleaving access 
to two database connections. Start traversing a SELECT statement on one 
connection; between two calls to sqlite3_step, modify the data on the 
other (WAL journal mode would let you); continue traversal of the 
statement - it would not observe the changes the same thread has just made.



Either way, the example you gave does not apply. What was happening with my
code was that a transaction A started at T1, inserted a row and was
committed successfully at T2. Another transaction B started at T3 and
didn't see the new row.


Was there, by any chance, a seemingly unrelated transaction C using the 
same shared cache that started reading at T0, and has not completed by T3?



I can't see how the fact that transaction A was in
shared cache mode and B in private cache mode


Shouldn't it be the other way round? I thought you said all readers (B 
among them) use shared cache, while a writer (like A) uses a private 
connection.



could influence the new row
not being seen in transaction B, when its transaction started after
transaction A commit. Doesn't this break ACID?


ACID provides guarantees on when changes made in one transaction will 
*not* be seen by another. It says nothing about when changes in one 
transaction *will* be seen by another.


Yes, connections sharing a cache are not quite as isolated from each 
other as independent private connections (it's even possible to enable 
read-uncommitted mode, whereby one such connection can see 
not-yet-committed changes made by another). As any other option, shared 
cache brings some benefits and some limitations (if it were all 
benefits, it wouldn't be an option).

--
Igor Tandetnik

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

[sqlite] Where can I find JDBC for 3.8.2 or later?

[Hadashi, Rinat]

I am looking for a JAVA sqlite JDBC version 3.8.2 or later, for Linux

Rinat Hadashi


-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Planner chooses incorrect index

[João Ramos]

Here you go:

sqlite_stat1 (before - good planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2

sqlite_stat1 (after - bad planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2
HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2

sqlite_stat4 (before - good planning)
HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 17 81
HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 661 38 96
661
HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 56
352 1665
HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 2899 106
447 2899
HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 106
462 3331
HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825 4997 131
660 4997
HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 6232 154
931 6232
HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 6477 162
974 6477
HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 169 984
6663
HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 7488 186
1062 7488
HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 8329 195
1361 8329
HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965 218
1439 8965
HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 9129 227
1501 9129
HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 9650 237
1579 9650
HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954 9995 244
1709 9995
HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192 11322 246
1898 11322
HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503 11661 252
1963 11661
HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921 12037 258
1994 12037
HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574 12586 261
2013 12586
HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862 12973 265
2187 12973
HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101 13327 270
2291 13327
HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722 13973 295
2388 13973
HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 14530 331
2749 14530
HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 14849 333
3067 14849
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
1665 1665 0 1663 1665
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
3331 3331 0 3329 3331
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
4997 4997 0 4995 4997
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
6663 6663 0 6661 6663
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
8329 8329 0 8327 8329
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
9995 9995 0 9993 9995
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
11661 11661 0 11659 11661
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392
12392 12392 377 12390 12392
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 12557
12558 12558 530 12556 12558
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042
13044 13044 998 13042 13044
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13049
13049 13049 1000 13047 13049
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13061
13061 13061 1006 13059 13061
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210
13212 13212 1150 13210 13212
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13327
13327 13327 1261 13325 13327
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13411
13411 13411 1344 13409 13411
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 6 1 1 13572
13573 13573 1498 13571 13573
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13582
13585 13585 1503 13583 13585
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13726
13727 13727 1643 13725 13727
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 8 1 1 13978
13978 13978 1890 13976 13978
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14203
14206 14206 2098 14204 14206
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14317
14318 14318 2208 14316 14318
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14452
14454 14454 2332 14452 14454
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14687
14688 14688 2559 14686 14688
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14836
14838 14838 2697 14836 14838
HistoryEntry 

[sqlite] System.Data.SQLite.EF6.SQLiteProviderServices should be public

[nesvarbu Pavarde]

Hi,

.NET provider for sqlite should have public Instance property of
System.Data.SQLite.EF6.SQLiteProviderServices. Currently it is private.
In order to use it with Entity Framework code configuration.
Why this is a problem: http://stackoverflow.com/a/23237737/1107786

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

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin  wrote:

>
> On 22 Jun 2014, at 11:33pm, João Ramos  wrote:
>
> > What happens is this: - a thread acquires the write DB connection - that
> > thread inserts a new row in a table and gets its id -
>
> Are you finalizing all these SQL commands (or using _exec() which amounts
> to the same thing) ?
>
>
Yes, I double checked and everything is being terminated properly (e.g.:
sqlite3_reset, etc.) and without any error codes.


> > the thread releases
> > the write DB connection (so that other threads can use it)
>
> What do you mean by 'releases' here ?  If you have other threads using the
> same connection, you're obviously not closing it.
>
>
No, the SQLite connection isn't closed. By "release" I mean release back to
the thread pool.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[Simon Slavin]

On 22 Jun 2014, at 11:33pm, João Ramos  wrote:

> What happens is this: - a thread acquires the write DB connection - that
> thread inserts a new row in a table and gets its id -

Are you finalizing all these SQL commands (or using _exec() which amounts to 
the same thing) ?

> the thread releases
> the write DB connection (so that other threads can use it) 

What do you mean by 'releases' here ?  If you have other threads using the same 
connection, you're obviously not closing it.

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

Re: [sqlite] Data visibility problem

[João Ramos]

If multiple connections in shared cache mode are treated as a single "real"
connection, should I change the thread mode to Serialized? I tough this
"single" connection was in terms of page cache and would not have any
implications on how the connections are accessed (or not) concurrently,
especially transaction control.

Either way, the example you gave does not apply. What was happening with my
code was that a transaction A started at T1, inserted a row and was
committed successfully at T2. Another transaction B started at T3 and
didn't see the new row. I can't see how the fact that transaction A was in
shared cache mode and B in private cache mode could influence the new row
not being seen in transaction B, when its transaction started after
transaction A commit. Doesn't this break ACID?


On Mon, Jun 23, 2014 at 3:34 AM, Igor Tandetnik  wrote:

> On 6/22/2014 6:33 PM, João Ramos wrote:
>
>> What happens is this: - a thread acquires the write DB connection - that
>> thread inserts a new row in a table and gets its id - the thread releases
>> the write DB connection (so that other threads can use it) - the same
>> thread acquires (its own) read-only DB connection - thread tries to read
>> the row it just inserted and it returns NULL
>>
>
> Well, it's a *shared* cache. All connections reading from the cache must
> needs see the same data - namely, the data observed by the oldest
> still-open transaction.
>
> A shared cache is an in-memory construct that holds a single "real"
> connection to the actual database file; all connections to the shared cache
> go through this single "real" connection. Say, connection A starts reading
> at time T - this initiates a read transaction on the "real" connection.
> Connection B starts reading at time T+2 - that becomes part of the same
> transaction. A may stop reading (e.g. reset its statement) at time T+3 -
> but B still reads the data as it existed at time T, and cannot observe any
> changes made at T+1.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about opening database files

[Adil Hasan]

Hello Richard,
thanks for the response and explanation. 
adil

On Sun, Jun 22, 2014 at 04:51:27PM -0400, Richard Hipp wrote:
> On Sun, Jun 22, 2014 at 10:47 AM, Adil Hasan  wrote:
> 
> > Hello,
> > I would like to add sqlite to a workflow. But, I cannot directly open the
> > database in C using the filename as the application I'm using places a
> > layer
> > inbetween the filesystem and my workflow. I can open the file with
> > a command from the middle-layer and get a file descriptor. Is there a way
> > that I can just pass the file descriptor to the open command instead
> > of the database filename?
> >
> 
> No.  The SQLite transaction control and recovery mechanism depends on
> knowing the filename.  So you cannot open an SQLite database file by
> passing just a file descriptor.
> 
> Theoretically, if the file were immutable and known to be in a consistent
> state (it does not need recovery because the last writer did not crash)
> then it might be possible to open an SQLite database read-only using just a
> file descriptor.  But no mechanism exists to do this at the current time.
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Link error using SQLite 3.8.5

[163]

> Hi Experts,
> Did you try to compile and link your program with SQLite 3.8.5 with 
> SQLITE_OMIT_WAL macro predefined? The compilation was successfully complete. 
> But it would report a link error and complain it could not find the 
> definition for unixGetpagesize function. I checked the souce code and find 
> unixGetpagesize function was defined inside #ifndef SQLITE_OMIT_WAL…..#endif. 
> But it is used outside of #ifndef SQLITE_OMIT_WAL. Is it a bug? Can I just 
> move unixGetpagesize out side of #ifndef SQLITE_OMIT_WAL … #endif?
>  
>  
>  
> Version information:
> #define SQLITE_VERSION"3.8.5"
> #define SQLITE_VERSION_NUMBER 3008005
> #define SQLITE_SOURCE_ID  "2014-06-04 14:06:34 
> b1ed4f2a34ba66c29b130f8d13e9092758019212"
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users