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

[Jerry Krinock]

On 2014 Jun 24, at 14:46, David Empson  wrote:

> The most likely explanation is that it got processed by something which 
> thought it should be treated as ASCII text and was doing a spurious LF-to-CR 
> translation. If there was only one 0x0A byte in the "good" file, then that is 
> the only one which would have been modified.

Yes, that makes sense, David.  I don’t think that happened.  But I’m still 
thinking :)

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

Re: [sqlite] Sequential numbers

[Simon Slavin]

On 24 Jun 2014, at 9:47pm, Dave Wellman  wrote:

> Included in that page is the following:
> 
> Note that "monotonically increasing" does not imply that the ROWID always
> increases by exactly one. One is the usual increment. However, if an insert
> fails due to (for example) a uniqueness constraint, the ROWID of the failed
> insertion attempt might not be reused on subsequent inserts, resulting in
> gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically
> chosen ROWIDs will be increasing but not that they will be sequential.
> 
> I need the values to be sequential.

If you are creating a new column then you're not going to hit any of the 
problems with generating sequential numbers.

You do need, for the future, to consider what you would want to happen if you 
deleted a row from that table.  Would you expect the numbers on the other rows 
to remain the same or would you expect rows after the deleted one to move down 
one ?

Simon.
___
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/24/2014 6:04 PM, Peter Haworth wrote:

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

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


That's really interesting - I've never seen that use of the "+" sign
mentioned in the docs.


http://www.sqlite.org/optoverview.html
To be usable by an index a term must be of one of the following forms: ...

Adding a unary + changes the term so it's no longer one of those forms.
--
Igor Tandetnik

___
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

[Peter Haworth]

>
>
> SELECT * FROM itemTable WHERE +key = 'profileName' ;
>
> Note the + sign - this suppresses the use of index.
> --
> Igor Tandetnik
>

That's really interesting - I've never seen that use of the "+" sign
mentioned in the docs.

Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [ANN] SQLiteAdmin V 1.3.5 Released

[Peter Haworth]

SqliteAdmin V 1.3.5 is now available for download at
www.lcsql.com/sqliteadmin.html.

This version includes support for several recent language additions such as
CREATE TABLE WITHOUT ROWID and the CREATE INDEX WHERE clause. Several other
enhancements are included and the full release notes can be found at
http://goo.gl/OLZsgI.

SQLiteAdmin is database administration tool which provides the ability to
make schema changes not possible with the SQlite Data Definition Language.
 It also includes a powerful general purpose Table data browse and
maintenance tool.

Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
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

[David Empson]

On 25/06/2014, at 8:29 am, Jerry Krinock  wrote:
> On 2014 Jun 24, at 00:06, Clemens Ladisch  wrote:
>> Is there any other 0x0A byte in the good file?
> 
> No, only that one.

That leads to a plausible theory: had you done anything with the "good" 
database along the lines of storing it in a version control system, or 
including it with source files in some other kind of bulk processing, or 
uploaded/downloaded it via FTP?

The most likely explanation is that it got processed by something which thought 
it should be treated as ASCII text and was doing a spurious LF-to-CR 
translation. If there was only one 0x0A byte in the "good" file, then that is 
the only one which would have been modified.

> According to the git history for my project, I committed the corrupt file 
> about 10 days ago.  Still trying to figure out how that happened.  Thank you 
> guys for all of the clues.

-- 
David Empson
demp...@emptech.co.nz
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

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

Re: [sqlite] Sequential numbers

[Petite Abeille]

On Jun 24, 2014, at 10:47 PM, Dave Wellman  wrote:

> I need the values to be sequential.

Well… if your data set is as small as you mentioned (20 records or less)… you 
could roll your own numbering schema with the simple expedient of attaching a 
trigger to your tables to auto -number them with 'select count( * ) + 1 from 
table’ or something.

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

Re: [sqlite] Sequential numbers

[Dave Wellman]

Hi Roger,
Many thanks for that, I think its close to what I'm looking for. However,
according to that document page the autoincr feature doesn't gaurantee
'sequential' numbers. Included in that page is the following:

Note that "monotonically increasing" does not imply that the ROWID always
increases by exactly one. One is the usual increment. However, if an insert
fails due to (for example) a uniqueness constraint, the ROWID of the failed
insertion attempt might not be reused on subsequent inserts, resulting in
gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically
chosen ROWIDs will be increasing but not that they will be sequential.

I need the values to be sequential.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: 24 June 2014 21:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sequential numbers

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/06/14 13:02, Dave Wellman wrote:
> I have some rows in a table (not very many, typically less than 20) 
> and I want to generate a unique, sequential number for each row.

http://www.sqlite.org/autoinc.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOp2ncACgkQmOOfHg372QQ+YgCgqGwsFXwzNCxtAzefmzhM/go7
x7AAoIy/aOigXqBZlG/wP4tH5LZdCW6z
=1Xna
-END PGP SIGNATURE-
___
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

[Richard Hipp]

On Tue, Jun 24, 2014 at 4:29 PM, Jerry Krinock  wrote:

>
> Thank you, Clemens.  0x0A=index.  0x0D=table.  I suppose that is a
> sensible re-use of “carrige return” and “line feed”.
>

Indexes and tables can have several different initial bytes, depending on
circumstances.   Any relationship to ASCII control characters is entirely a
coincidence.  In fact, you might be the first person to notice this
coincidence in the 10+ years that the SQLite3 file format has been defined.


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

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

[Jerry Krinock]

(I think it’s cleaner to reply to three replies in one message; here goes…)

On 2014 Jun 23, at 22:43, Keith Medcalf  wrote:

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

Very good, Keith.  So my database *does* have an index.

> You should be able to rebuild the corrupted index with:
> REINDEX ItemTable;

Nope.  If I try that on the “bad” file, I get Error: Database disk image is 
malformed.  If I manually change byte 2048 to 0x0A first, then the command 
succeeds.  Looking at the resulting database, it seemed to change the order of 
the columns listed in the last page, which I assume is not significant.

On 2014 Jun 24, at 00:06, Clemens Ladisch  wrote:

>  says …

Thank you, Clemens.  0x0A=index.  0x0D=table.  I suppose that is a sensible 
re-use of “carrige return” and “line feed”.

> So the bad index claims to be a table.


> Is there any other 0x0A byte in the good file?

No, only that one.

On 2014 Jun 24, at 02:10, Eduardo Morras  wrote:

> Did you create the db schema with newer version of sqlite3? Perhaps you did 
> that and create a partial index, not supported on older sqlite3 versions.

I see that the Partial Index feature was added in August 2013, in sqlite 3.8.  
I don’t think I have anything greater than 3.7.x on this Mac, but I’m looking 
into it.

* * *

According to the git history for my project, I committed the corrupt file about 
10 days ago.  Still trying to figure out how that happened.  Thank you guys for 
all of the clues.

Jerry

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

Re: [sqlite] Sequential numbers

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/06/14 13:02, Dave Wellman wrote:
> I have some rows in a table (not very many, typically less than 20) and
> I want to generate a unique, sequential number for each row.

http://www.sqlite.org/autoinc.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOp2ncACgkQmOOfHg372QQ+YgCgqGwsFXwzNCxtAzefmzhM/go7
x7AAoIy/aOigXqBZlG/wP4tH5LZdCW6z
=1Xna
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sequential numbers

[Dave Wellman]

Hi all,

 

I have some rows in a table (not very many, typically less than 20) and I
want to generate a unique, sequential number for each row. In another dbms
I've used a row_number function (amongst others) to achieve this but I can't
see anything with equivalent functionality in sqlite3. My apologies if I've
missed something.

 

I thought about using the 'rowid' and in some simple testing that seems to
give me what I want. But I need to check a couple of things.

 

1)  Is there a function that will give me unique, sequential numbers?



2)  Assuming that my processing follows this pattern: empty table T1
completely, insert a number of rows, insert/select from T1 into T2. On the
'select' processing will the 'rowid' 
** always ** start at 1?



3)  If I repeat the processing pattern shown in #2 above, will
subsequent selects always have rowid that starts from 1?

 

Yes, I know that I could select the rows back to my application, generate
the numbers and then insert rows back into the table but I'm trying to do
this within the dbms.

 

All help or ideas gratefully received.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

___
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

[Eduardo Morras]

On Mon, 23 Jun 2014 20:16:44 -0700
Jerry Krinock  wrote:

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

Did you create the db schema with newer version of sqlite3? Perhaps you did 
that and create a partial index, not supported on older sqlite3 versions.

Please, check index schemas for a where clause in them.

> 
> The same thing happens when I execute the failing query with the
> sqlite3 C Library, using years-old tested code.
> 
> Thank you!
> 
> Jerry Krinock

---   ---
Eduardo Morras 
___
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

[Clemens Ladisch]

Jerry Krinock wrote:
> On 2014 Jun 23, at 21:49, Igor Tandetnik  wrote:
>>  The data corruption happens to affect the area of the file where
>> this index is stored.
>
> The culprit is byte 2048, the first byte in the 3rd page.  In the Good
> file, it is 0x0A and in the Bad file, it’s 0x0D.

 says:
| B-tree Page Header Format: Offset: 0, Size: 1, Description:
| A flag indicating the b-tree page type [...]  A value of 10 means the
| page is a leaf index b-tree page. A value of 13 means the page is
| a leaf table b-tree page.

So the bad index claims to be a table.

> looks like a Mac vs. Unix line ending clash.

Is there any other 0x0A byte in the good file?


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

Re: [sqlite] How to get access to SQLite Test Harness #3(TH3)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/06/14 23:09, Kishore Reddy wrote:
> a)I want to use sqlite library in my project software.In the website it
> is listed as TH3 achieves 100% branch test coverage.

The SQLite software as released has already been tested with TH3 and
passed.  You do not need the TH3 test suite just to use SQLite.  Really:

 https://sqlite.org/testing.html

> b)Do we have requirements for this SQLite software which provides 
> *traceability* to the test cases provided by TH3 framework?

You can purchase a license for TH3.  See the last section of
https://sqlite.org/th3.html

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOpHNMACgkQmOOfHg372QQEZACeIlRc/4JICpbQNbajdWfTlffr
0WUAoMRjzQcDTpz9PHGWk5oxrpRauwX/
=paUV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get access to SQLite Test Harness #3(TH3)

[Kishore Reddy]

Hi,
a)I want to use sqlite library in my project software.In the website it is
listed as TH3 achieves 100% branch test coverage.I want all the test
results of the SQLite software which shows that the software has *100%
statement+branch coverage*.Can any one tell me how to get these test
results ?? or how to get this TH3 framework and run those test cases to get
results?
b)Do we have requirements for this SQLite software which provides
*traceability* to the test cases provided by TH3 framework?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users