On Tue, May 11, 2010 at 3:14 AM, Peter Rabbitson
<[email protected]<rabbit%[email protected]>
> wrote:
>
> > But, case in point: I'm using DBIx::Class::Ordered and it started to
> > fail today with a somewhat odd error. Turned out that "columns" had
> > been modified such that the position column was no longer returned in
> > the row. Ordered was assuming it was a NULL and tried to set the
> > position to a value that existed and the database complained that it was
> > not unique.
> >
>
> I don't see the attached test case so we can fix the bug in *Ordered*
> (by making sure we have the necessary info available before we start
> shuffling things around).
>
I didn't see this as a bug in Ordered. Ordered asked for the column data
and got an undefined value and assumed it was null. Seemed more like an
example of why might be good for DBIC to throw an exception if a column is
accessed w/o being fetched.
But, wrt Ordered, I am curious. If I have these ordered tracks and then
delete one:
Id = 30 Track = 1
Id = 31 Track = 2
Id = 32 Track = 3
Id = 33 Track = 4
Id = 34 Track = 5
Id = 35 Track = 6
Id = 36 Track = 7
Id = 37 Track = 8
Id = 38 Track = 9
Id = 39 Track = 10
removing id 33 position 4
SELECT position FROM track me WHERE ( ( position > ? AND ( position != ? AND
cd = ? ) ) ) ORDER BY position DESC LIMIT 1: '4', '4', '8'
UPDATE track SET position = position - 1 WHERE ( ( ( position BETWEEN ? AND
? ) AND cd = ? ) ): '5', '10', '8'
UPDATE track SET position = ? WHERE ( id = ? ): '10', '33'
DELETE FROM track WHERE ( id = ? ): '33'
Doesn't the order of the updates look incorrect? Wouldn't that set two rows
with the same position before the second update moves id=33 to position 10
(moves it to the end)?
Would this be more correct (ignoring the race condition):
($deleted_pos = $delete_row->position)
DELETE FROM track WHERE ( id = ? ): '33'
UPDATE track SET position = position - 1 WHERE position > ? AND cd = ?:
$deleted_pos, $cd_id
Because:
create unique index pos_idx on track (cd,position);
...
removing id 53 position 4
SELECT position FROM track me WHERE ( ( position > ? AND ( position != ? AND
cd = ? ) ) ) ORDER BY position DESC LIMIT 1: '4', '4', '10'
UPDATE track SET position = position - 1 WHERE ( ( ( position BETWEEN ? AND
? ) AND cd = ? ) ): '5', '10', '10'
DBIx::Class::Ordered::delete(): DBI Exception: DBD::Pg::st execute failed:
ERROR: duplicate key value violates unique constraint "pos_idx" [for
Statement "UPDATE track SET position = position - 1 WHERE ( ( ( position
BETWEEN ? AND ? ) AND cd = ? ) )" with ParamValues: 1='5', 2='10', 3='10']
at /home/moseley/dbic_music/test.pl line 46
my $cd = $schema->resultset( 'Cd' )->create({ name => 'New cd',
artist => $schema->resultset( 'Artist' )->first,
});
my @tracks;
for ( 1 .. 10 ) {
push @tracks, $cd->add_to_tracks( {
name => "Original track $_",
});
}
$schema->storage->debug(1);
printf( "Id = %3d Track = %3d\n", $_->id, $_->position ) for @tracks;
printf( "\nremoving id %d position %d\n\n", $tracks[3]->id,
$tracks[3]->position );
$tracks[3]->delete;
package MyApp::Result::Track;
use strict;
use warnings;
use base 'MyApp::Result';
__PACKAGE__->load_components(qw/ Ordered Core /);
__PACKAGE__->table("track");
__PACKAGE__->add_columns( qw/
id
name
position
cd
/);
__PACKAGE__->set_primary_key( 'id' );
__PACKAGE__->belongs_to("cd", "MyApp::Result::Cd", { id => "cd" });
__PACKAGE__->position_column('position');
__PACKAGE__->grouping_column('cd');
__PACKAGE__->null_position_value(undef); #?
1;
Oops -- sorry for the thread hijack.
--
Bill Moseley
[email protected]
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
