SQLite select not returning all rows

GrandFather has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to use a SQLite2 DB to mediate multiple automated build managers. Each build manager requires a unique id, but the id is only required while the manager is running (ids can be reused). The sample code below is an attempt to allocate an id to a new manager and ensure that it is unique by using transacted access to the DB. The id should normally be deleted when the code terminates.

use strict;
use warnings;
use DBI;

my $db = new ();

$db->AddBuildManager ('fred');
print "Id is $db->{myId}\n";


sub new {
    my $dbh = DBI->connect("dbi:SQLite2:ManSched.sqlite2", "", "");

    $dbh->{AutoCommit} = 0;
    $dbh->{RaiseError} = 1;

    eval {
        my $sth = $dbh->table_info (undef, 'public', 'managers', 'TABL
+E');
        my @tables = $sth->fetchrow_array ();
    
        die if @tables;
        
        $dbh->do ("CREATE TABLE managers(name VARCHAR(20) not null, id
+ VARCHAR(24) not null primary key)");
        $dbh->commit ();
    };
    
    return bless {dbh => $dbh};
}


sub DESTROY {
    my $self = shift;
    my $dbh = $self->{dbh};
    
    return if ! defined $self->{myId};
    $dbh->do ("DELETE FROM managers WHERE id = '$self->{myId}'");
    $dbh->disconnect ();
}


sub AddBuildManager {
    my ($self, $managerName) = @_;
    my $dbh = $self->{dbh};
    my $sth = $dbh->prepare ("SELECT id FROM managers WHERE name = '$m
+anagerName'");
    
    $sth->execute ();
    my @managers = sort $sth->fetchrow_array ();

    for my $index (0 .. @managers) {
        eval {
            my $id = sprintf "%s-%03d", $managerName, $index + 1;
            
            die if exists ($managers[$index]) and $managers[$index] eq
+ $id;

            $dbh->do ("INSERT INTO managers(name, id) VALUES ('$manage
+rName', '$id')");
            $dbh->commit ();
            $self->{myId} = $id;
        };

        return $self->{myId} if ! $@ and defined $self->{myId};

        eval {$self->{dbh}->rollback ()};
    }
    
    return;
}
[download]

If I run the code three times I get the following output:

Id is fred-001
[download]

Id is fred-002
[download]

DBD::SQLite2::db do failed: column id is not unique(1) at dbdimp.c lin
+e 419 at noname.pl line 55.
Id is 
Use of uninitialized value in concatenation (.) or string at noname.pl
+ line 8.
[download]

What I expect to see is fred-001 reused on each run.

It looks like the DELETE doesn't at least some times, and that the SELECT doesn't return everything that is there, but I can't see why. Any ideas?

DWIM is Perl's answer to Gödel

Comment on SQLite select not returning all rows Select or Download Code

Replies are listed 'Best First'.
Re: SQLite select not returning all rows by varian (Chaplain) on Mar 07, 2007 at 22:24 UTC
As Autocommit is off you are required to finish every transaction with a commit or rollback. Unfinished transactions e.g. upon disconnect will not be committed. `$dbh->do ("DELETE FROM managers WHERE id = '$self->{myId}'"); $dbh->commit(); # <===== add this line $dbh->disconnect ();` [download] So just add the commit to your destroy code. Update: looks like you found out in parallel :-)	[reply] [d/l]
Re: SQLite select not returning all rows by GrandFather (Saint) on Mar 07, 2007 at 21:58 UTC
A partial answer (with thanks to Arunbear and the CB) is to replace fetch_array with fetchall_arrayref and make appropriate changes in AddBuildManager: `my @managers = sort {$a->[0] cmp $b->[0]} @{$sth->fetchall_arrayre +f ()}; for my $index (0 .. @managers) { eval { my $id = sprintf "%s-%03d", $managerName, $index + 1; die if exists ($managers[$index][0]) and $managers[$index] +[0] eq $id;` [download] However the current id is not being removed when the code exits, although I know the DELETE line is being executed without errors or warnings being generated. DWIM is Perl's answer to Gödel	[reply] [d/l]
Re^2: SQLite select not returning all rows by GrandFather (Saint) on Mar 07, 2007 at 22:14 UTC
and the last piece for this particular puzzle is a missing commit before the disconnect (thanks erix) in DESTROY: `$dbh->do ("DELETE FROM managers WHERE id = '$self->{myId}'"); $dbh->commit (); $dbh->disconnect ();` [download] DWIM is Perl's answer to Gödel	[reply] [d/l]