The whole scheme seems risky to me. What if your program crashes after you've deleted everything from the database but before you've written to your spreadsheet? Everything will be lost. Better to copy the data then delete only the rows you've successfully written.

Without knowing what sort of data you're working with it's difficult to suggest any other options. But you could move your rows into a temporary table, or use an extra column as a flag (set the flag for all rows, then select * where flag == t, delete * where flag == t).

Also it may be worth reading up on sqlite transaction handling.

Thanks - I think I didn't make it clear in the post that the moving is happening in the loop before the delete. I'm just using print in this example.

It still stands that if it crashed during that, I wouldn't know which records had made it and which hadn't.

I'm going to look into updating an extra column, as you suggest, and using that.

This will also mean that I can compare the number of records marked in the database as transferred and the number in the spreadsheet so will be a good check

Thanks for your help

Does the addresses table have a primary key or timestamp field ?

No it doesn't. From everything that's said, I think it either should, or it should have an extra column reporting on the status. I'm going to go with that

Thanks for your help

Can you simply move the db before you start working on it, rendering it unavailable for when you do the transactions?:

use warnings;
use strict;

use File::Copy;

my $db = 'test.db';
my $disabled_db = 'test-locked.db';

move $db, $disabled_db or die $!;

# your code here, using $disabled_db in your connect

...
[download]

-stevieb

Hi, thanks for this

I'd still be worried that I was pulling the rug out from under a process that was writing to the database if I moved it

I'm not sure if FILE::Copy would respect any sqlite locking

I'm going to add a column and have that called 'transferred' and write 'yes' when done

Thanks for your help though

Sorry it took me awhile to get to this. It’s longish but thread is dead so I’m skipping <readmore/>. Transactions are great and trivial to use and even nest with DBIx::Class; DBIx::Class::Storage::TxnScopeGuard. You can generate a schema from an existing DB—DBIx::Class::Schema::Loader—so you don’t have to write the BEGIN style code; it’s only there for an encapsulated example.

#!/usr/bin/env perl
use strictures;
use 5.010;
BEGIN { # Sample packages.
    package MyApp::Schema::Address {
        use parent "DBIx::Class::Core";

        __PACKAGE__->table("address");
        __PACKAGE__->add_columns( id => { data_type => 'integer',
                                          is_auto_increment => 1 },
                                  address => { data_type => 'varchar',
                                               size      => '100' } );
        __PACKAGE__->set_primary_key("id");
    };

    package MyApp::Schema {
        use parent "DBIx::Class::Schema";
        # For real -> __PACKAGE__->use_namespaces;
        __PACKAGE__->load_classes("Address"); # <- For this example.
    };
}

my $use_transaction = shift;
say "Doing it ", $use_transaction ? "with" : "without", " transaction.
+";

my $schema = MyApp::Schema->connect("dbi:SQLite::memory:");
$schema->deploy;

# Create some addresses.
my $addresses = $schema->resultset("Address");
for ( 1 .. 10 )
{
    $addresses->create({ address => "$_ Street" });
}

my $count = $addresses->count;
eval {
    $use_transaction ?
        process_with_txn_guard()
        :
        process_without_txn_guard();
};

if ( $@ ) {
    print "We had an error! $@";
    say "We have ", $addresses->count, " left in the DB";
    say "Sucks to be you..." unless $count == $addresses->count;
}

exit 0;

sub process_without_txn_guard {
    for my $address ( $addresses->all )
    {
        # Do some local processing with $address.
        say "Deleting ", $address->address;
        $address->delete;
        die "Bad stars" if rand(1) > .8; # Intermittent failure.
    }
    # Do remote "global" processing with all addresses.
}

sub process_with_txn_guard {
    my $guard = $schema->txn_scope_guard;
    process_without_txn_guard();
    $guard->commit;
}

__END__
moo@cow~>pm-1142097
Doing it without transaction.
Deleting 1 Street
Deleting 2 Street
Deleting 3 Street
We had an error! Bad stars at pm-1142097 line 57.
We have 8 left in the DB
Sucks to be you...

moo@cow~>pm-1142097 with_txn
Doing it with transaction.
Deleting 1 Street
Deleting 2 Street
Deleting 3 Street
We had an error! Bad stars at pm-1142097 line 58.
We have 10 left in the DB
[download]

Google search terms:

• sqlite transaction
• dbi sqlite autocommit

Oh, that's great - thanks

I agree with RichardK:   your strategy is not restartable.   I, too, would add a status-flag ... perhaps a timestamp that is NULL if the record has not yet been moved.   In a transaction (which is generally essential when doing anything-at-all with SQLite ...), select the records, move them, and UPDATE the record to show its new status.

Maybe you subsequently delete the records from the table, or maybe you never do.   There’s something to be said for having a history, and for having data in more than one place.   And not just in case the power goes out the midd0q9aw@Rq!afdz;@@@ ...   ;-)

If you are going to multithread this process (and I’m not sure that this will actually be beneficial ...), you will need to make sure that both threads do not select the same records at the same time.   To prevent this, you would need to somehow select-and-mark a group of records, COMMIT that change, then somehow be sure that you process only those records (even if a competing process has marked more).   This competition, and the extra steps needed to avoid conflict, just might obviate the actual need for concurrency.   SQLite is very fast ... provided(!) that transactions are always used, so that “lazy writing” takes place.

There'll only be one process doing the transfer, so I don't think I have to worry about multithreading.

Generally taking advice from this that I can't rely on computers, let alone my code...so going to add in the extra column, as mentioned here and above.

Thanks for your help