Как я могу ОБНОВИТЬ строки, возвращаемые SELECT в цикле?

Question

У меня есть цикл в строках, возвращаемых оператором SQL SELECT, и после некоторой обработки данных строки я иногда хочу ОБНОВИТЬ значение строки. Обработка в теле цикла нетривиальна, и я не могу написать ее на SQL. Когда я пытаюсь выполнить UPDATE для выбранной строки, я получаю сообщение об ошибке (в Perl DBD::SQLite::st выполнить не удалось: таблица базы данных заблокирована). Есть ли удобный для чтения и эффективный способ достичь того, что я пытаюсь сделать? В случае неудачи, есть ли способ сделать это для DBD или SQLite?

Очевидно, что я могу помещать обновления в отдельную структуру данных и выполнять их после цикла, но я бы не хотел, чтобы код выглядел после этого.

Если вам интересно, вот соответствующий код Perl.

my $q = $dbh->prepare(q{
  SELECT id, confLoc FROM Confs WHERE confLocId ISNULL});
$q->execute or die;
my $u = $dbh->prepare(q{
  UPDATE Confs SET confLocId = ? WHERE id = ?});
while (my $r = $q->fetchrow_hashref) {
    next unless ($r->{confLoc} =~ m/something-hairy/);
    next unless ($locId = unique_name_state($1, $2));
    $u->execute($locId, $r->{id}) or die;
}

Answer 1

Временно включить AutoCommit:

sqlite>.header on
sqlite> select * from test;
поле
один
два

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:test.db', undef, undef,
    { RaiseError => 1, AutoCommit => 0}
);

test_select_with_update($dbh);

sub test_select_with_update {
    my ($dbh) = @_;
    local $dbh->{AutoCommit} = 1;
    my $q = $dbh->prepare(q{SELECT field FROM test});
    my $u = $dbh->prepare(q{UPDATE test SET field = ? WHERE field = ?});
    $q->execute or die;
    while ( my $r = $q->fetchrow_hashref ) {
        if ( (my $f = $r->{field}) eq 'one') {
            $u->execute('1', $f) or die;
        }
    }
}

После запуска кода:

sqlite>.header on
sqlite> select * from test;
поле
1
два

Answer 2

Более подробно в ответ на комментарий Зойдберга, но если вы смогли переключиться на ORM, такой как Perl DBIx::Class, то вы обнаружите, что можете написать что-то вроде этого:

my $rs = $schema->resultset('Confs')->search({ confLocId => undef });

while ( my $data = $rs->next ) {
    next unless $data->confLoc =~ m/(something)-(hairy)/;
    if ( my $locId = unique_name_state( $1, $2 ) ) {
        $data->update({ confLocID => $locid });
    }
}

И если DBIx::Class не захватывает ваше воображение, на CPAN есть несколько других, например, Fey::ORM и Rose::DB.

Answer 3

Ваша проблема в том, что вы используете тот же обработчик базы данных, чтобы выполнить обновление, пока вы в цикле выборки.

Итак, предоставьте другой экземпляр вашего обработчика базы данных для выполнения обновлений:

my $dbh = DBI->connect(...);
my $dbhForUpdate = DBI->connect(...) ;

Затем используйте dbhForUpdate в вашем цикле:

while(my $row = $sth->fetch()){
   ...
   $dbhForUpdate->do(...) ;
}

В любом случае, я бы не советовал делать это, так как есть хорошие шансы, что вы столкнетесь с проблемами параллелизма на уровне базы данных.