Good thinking @tlhackque.

Hadn't known the sha3sum command even existed in the CLI. Sounds useful. 😄

On the other hand, I'm not sure how effective the integrity_check pragma really is. From memory of testing it last year briefly, when purposely corrupting databases (random blocks of data written into the file interior) the integrity check never spotted problems. Didn't seem to be a very comprehensive check.

Still, I added it to our server side part of things at the time, just in case. After all "it shouldn't make things worse". 😉

Only semi-related, but this is kind of nifty:

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

Oh wow, DB4S is even mentioned directly in the referenced US Library of Congress page:

    https://www.loc.gov/preservation/digital/formats/fdd/fdd000461.shtml#sustainability

Under "Adoption".

I agree that the integrity checks can provide false confidence - but what they can do, they should.

Randomly throwing data at a database is surprisingly unlikely to be detected. A well functioning database will be mostly data (especially as the page size increases) - and the integrity checks are for metadata. (well, except for the one where the user has to supply code).

What they should detect are cases where indexs, page free counts, and the like go wrong. When they pass, you may retrieve the wrong data, but it will be well organized. :-)

It's sort of like throwing space junk at the planet. The odds are it will hit water (~75% of the planet's surface) - and if it hits land, it's still not likely to be inhabited. Despite all the stuff that's fallen uncontrolled from orbit, I believe only one person has been it - and fortunately, not seriously injured.

That's for testing. For production, O'Toole's observation on Murphy's Law applies.[1]

If you want to cause corruption, aim low - or high. Typlically a database will have configuration parameters, the root of indicies and storage maps in the first few pages. (Or with some, as with .ZIP files, the last few.) The beginning and the end of a file are the only two places that are easy to find.

Seriously, metadata checks provide the assurance that you should not have sqlite panic or hang following its metadata. User level consisency is a different issue. My observation was merely that SQLite offers the tool, so it would be good to expose it in the browser. Improving the tool's quality/applicability is more an issue for the SQLite developers...

There's a note in the description of the checksum command that it uses an extension - it's actually clever in that the checksum is impervious to VACUUM, but does protect the data and schema. You can probably bundle the same extension into your code - but be sure to keep it up-to-date :-)

Happy coding.

--
[1] Murphy: "Whatever can go wrong, will."
Murphy's corollaries: "at the worst possible time", "with the worst possible audience", "for the maximum destructive effect" ...

O'Toole's observation: "That's all true. But ... Murphy was an optimist."

As I noted parenthetically in #1481 - another good candidate for "Tools" would be "Backup Database".

SQLite has an on-line Backup API that makes this pretty easy. See [the doc] here(https://sqlite.org/backup.html) and here.

I've exposed it (via the Perl DBD::SQLite wrapper) in several of my applications - it's pretty fast & convenient -- and it doesn't take the database offline. It also lets you save or populate a temporary database.

Making backup functions easily available is always a good thing.

Making it easy to snapshot a production database is a particularly good thing when you provide functions that might modify it in unexpected ways.

And this is one more sqlite3 function that you can make convenient. (More than one if you separate Backup & Restore - though I think Backup is the more important.)

A side note on the coding examples - as you're a GUI, you'll be tempted to use the second coding example to provide progress and aliveness for large databases. That's fine, but I would not use the example's 5 page quantum. Instead, run one backup_step, and pick a quantum that's a reasonable fraction of backup_pagecount. A small pagecount is inefficient - and creates many more opportunities for a write by another process to restart the backup operation. Too large, and the progress is chunky & other processes may time out. Off the top of my head, I'd start with something like 1-5% of backup_pagecount, with a floor of 10 pages... That said, in a low contention environment, backing up a 1/2GB database in one step (same spindle) takes less than 10 seconds. So you might elect to go that route.

Still, I added it to our server side part of things at the time, just in case. After all "it shouldn't make things worse"

The linked commit seems to be related to a web server version of DB4S, which I've never had reason to use.

I think that "integrity check" (and the quick/foreign keys checks) should be available on demand (from the "Tools" menu) for any open database in the standalone browser. One may wish to run them after a crash/power event - or before submitting a SQLite bug report.

Whenever enough round TUITs accumulate...

I've thought that we don't need anything fancy for executing the integrity and the foreing key checks. When the user clicks the menu option, we open a new tab in Execute SQL, insert the corresponding pragma and execute the query. The result would be this:

And for foreign key check (we can use a comment in the SQL for explaining the user the pragma result):

How do you all find this simple approach?

The linked commit seems to be related to a web server version of DB4S ...

Ahhh yeah. Started creating an optional cloud based storage solution for SQLite databases (checksummed, version controlled, traceability-enabled, etc) so people can share data sets and collaborate on them. Most of the basic bits are in place, and DB4S can send/receive data from it, but it still needs work before it'll be ready for "real" production usage.

That being said it is online and works. It just has... some rough edges. I haven't put time into it recently, but intend to in a while after getting some higher personal priority stuff taken care of.

How do you all find this simple approach?

Using an Execute SQL tab seems like a good idea. Cryptic output with references to tech documentation seems at odds with the DB4S philosophy of simplicity:

This program is not a visual shell for the sqlite command line tool. It does not require
familiarity with SQL commands. It is a tool to be used both by developers and by end
users, and it must remain as simple to use as possible in order to achieve its goals.

I don't think you need the explanation of the function in the results window - it can be the "What IS" text for the item on the tools menu.

The integrity check has a default limit of reporting up to 100 errors; If you get 100, you should note that more errors may be unreported. My application allows the user to specify the limit.

I also expand the foreign key check results to make it easier for the user to comprehend. I think you already have the foreign_key_list results in the data base structure tab, so reporting violations more helpfully wouldn't be hard.

Doesn't take much code either way...

Below is the (Perl) code that I use - I think it should be readable even if you don't speak Perl.

sub verify {
    my $db = shift;

    my $dbh = $db->{dbh};

    $db->log( "Basic integrity test starting" );
    $db->{options}{update}($db) if( $db->{options}{update} );

    my $limit = 100;
    my $log = $dbh->selectall_arrayref( << "SQL" );
pragma integrity_check( $limit )
SQL

    foreach my $row (@$log) {           # Single column, error string
        $db->log( " %s\n", $row->[0] );
    }
    if( @$log >= $limit ) {
        $db->log( " ... limit reached, additional errors may be present\n" );
    }

    $db->log( "Cross-reference test starting" );
    $db->{options}{update}($db) if( $db->{options}{update} );

    $log = $dbh->selectall_arrayref( << "SQL" );
pragma foreign_key_check
SQL
    if( @$log ) {
        $db->log( " Cross-reference errors detected:\n" );
        my %tblcon;
        foreach my $row (@$log) {
            my( $fromtbl, $fromrow, $totbl, $rule ) = @$row;
            unless( $tblcon{$totbl} ) { # Rules for target table
                my $rules = $dbh->selectall_arrayref( << "SQL" );
pragma foreign_key_list("$fromtbl")
SQL
                foreach my $con (@$rules) {
                    $tblcon{$totbl}[$con->[1]] =  # Seq
                      {
                       table  => $con->[2],       # Table name
                       fcol   => $con->[3],       # Column making ref
                       tcol   => $con->[4],       # Target column
                       onupd  => $con->[5],       # On Update action
                       ondel  => $con->[6],       # On Delete action
                       match  => $con->[7] };     #
                }
            }
            $db->log(
             " Reference from \"%s\" in row %u of %s to \"%s\" in %s fails constraint\n ",
                     $tblcon{$totbl}[$rule]{fcol}, $fromrow, $fromtbl,
                     $tblcon{$totbl}[$rule]{tcol}, $totbl );
        }
    } else {
        $db->log( "ok\n" );
    }
    $db->log( "Test complete\n" );
}

You probably already have the rules - which means your version would be shorter. OTOH, I don't deal with "without_rowid" tables (the reported row is NULL in that case).

$db->log writes to the UI status window.
The {update} just refreshes the screen (this runs in a callback of an Tk applications).

I suppose there should be a limit on how many foreign key ("cross reference") errors are reported.