Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Simon Slavin

On 25 Nov 2010, at 5:25pm, Tito Ciuro wrote:

> 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would 
> fail right away is it wasn't a valid database.
> 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds.
> 
> However, I'm wondering whether (1) is redundant. If the integrity check 
> detects right away that the file is not a SQLite3 database, then we're good. 
> But if the integrity check reads the whole file before failing, perhaps 
> running (1) would not be such a bad after all.
> 
> Also, if there is a need to know whether a database is corrupted, an 
> optimization seems to be to issue "PRAGMA integrity_check (1)" statement, 
> which would bail out at the first sign of trouble. As stated in the docs: 
> http://www.sqlite.org/pragma.html

I believe that if Nick's going to do (2) anyway, (1) is redundant.  In my own 
case I couldn't do (2) because some of the databases I was dealing with would 
have made it take too long.  And my hardware were extremely trustworthy anyway: 
I was really just checking that the files were the right files, and someone 
hadn't (for example) renamed a journal file as the database file WHICH IS 
SOMETHING SOMEONE ACTUALLY DID TO ME ONCE ARGH !

And I agree Nick only needs to see the first error from 'PRAGMA 
integrity_check' so he can use 1 as a parameter.

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


Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Tito Ciuro
Hi Nick,

On Nov 25, 2010, at 2:09 PM, Nick Shaw wrote:

> As Igor points out, that may not fail if the corruption is in a table
> you don't query.
> 
> In this case, the databases I'm working with are either small enough for
> the speed of an integrity check not to be an issue, or the integrity of
> the database is more important than a delayed DB open (which happens in
> a seprate thread to my apps' main GUI, so the user won't notice an issue
> anyway).
> 
> Thanks for all the help peeps,
> Nick.

Agreed.

However, the original email looks like it's a two-part question: opening 
non-SQLite files and detecting corrupted databases. Looks like the solution 
would be to integrate a method combining both Igor and Simon's solution:

1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would fail 
right away is it wasn't a valid database.
2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds.

However, I'm wondering whether (1) is redundant. If the integrity check detects 
right away that the file is not a SQLite3 database, then we're good. But if the 
integrity check reads the whole file before failing, perhaps running (1) would 
not be such a bad after all.

Also, if there is a need to know whether a database is corrupted, an 
optimization seems to be to issue "PRAGMA integrity_check (1)" statement, which 
would bail out at the first sign of trouble. As stated in the docs: 
http://www.sqlite.org/pragma.html

> PRAGMA integrity_check; 
> PRAGMA integrity_check(integer)
> 
> This pragma does an integrity check of the entire database. It looks for 
> out-of-order records, missing pages, malformed records, and corrupt indices. 
> If any problems are found, then strings are returned (as multiple rows with a 
> single column per row) which describe the problems. At most integer errors 
> will be reported before the analysis quits. The default value for integer is 
> 100. If no errors are found, a single row with the value "ok" is returned.


Cheers,

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


Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body

2010-11-25 Thread Nick Shaw
Tito Ciuro wrote:
> On 25 Nov 2010, at 12:51, Igor Tandetnik  wrote:
> > Run "PRAGMA integrity_check" right after opening.
>
> That could be a potentially slow operation if the database is valid
and contains lots of records.
> Wouldn't be better to issue a SELECT statement and let SQLite come
back with a SQLITE_NOTADB error right away?

As Igor points out, that may not fail if the corruption is in a table
you don't query.

In this case, the databases I'm working with are either small enough for
the speed of an integrity check not to be an issue, or the integrity of
the database is more important than a delayed DB open (which happens in
a seprate thread to my apps' main GUI, so the user won't notice an issue
anyway).

Thanks for all the help peeps,
Nick.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users