[Dbix-class] Lazy Columns

Bill Moseley moseley at hank.org
Tue May 11 13:23:56 GMT 2010


On Tue, May 11, 2010 at 3:14 AM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> 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 =3D  30  Track =3D   1
Id =3D  31  Track =3D   2
Id =3D  32  Track =3D   3
Id =3D  33  Track =3D   4
Id =3D  34  Track =3D   5
Id =3D  35  Track =3D   6
Id =3D  36  Track =3D   7
Id =3D  37  Track =3D   8
Id =3D  38  Track =3D   9
Id =3D  39  Track =3D  10

removing id 33 position 4

SELECT position FROM track me WHERE ( ( position > ? AND ( position !=3D ? =
AND
cd =3D ? ) ) ) ORDER BY position DESC LIMIT 1: '4', '4', '8'
UPDATE track SET position =3D position - 1 WHERE ( ( ( position BETWEEN ? A=
ND
? ) AND cd =3D ? ) ): '5', '10', '8'
UPDATE track SET position =3D ? WHERE ( id =3D ? ): '10', '33'
DELETE FROM track WHERE ( id =3D ? ): '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=3D33 to position 10
(moves it to the end)?

Would this be more correct (ignoring the race condition):

($deleted_pos =3D $delete_row->position)
DELETE FROM track WHERE ( id =3D ? ): '33'
UPDATE track SET position =3D position - 1 WHERE position > ? AND cd =3D ?:
$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 !=3D ? =
AND
cd =3D ? ) ) ) ORDER BY position DESC LIMIT 1: '4', '4', '10'
UPDATE track SET position =3D position - 1 WHERE ( ( ( position BETWEEN ? A=
ND
? ) AND cd =3D ? ) ): '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 =3D position - 1 WHERE ( ( ( position
BETWEEN ? AND ? ) AND cd =3D ? ) )" with ParamValues: 1=3D'5', 2=3D'10', 3=
=3D'10']
at /home/moseley/dbic_music/test.pl line 46



my $cd =3D $schema->resultset( 'Cd' )->create({    name =3D> 'New cd',
    artist =3D> $schema->resultset( 'Artist' )->first,
});

my @tracks;
for ( 1 .. 10 ) {
    push @tracks, $cd->add_to_tracks( {
        name =3D> "Original track $_",
    });
}

$schema->storage->debug(1);

printf( "Id =3D %3d  Track =3D %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 =3D> "cd" });

__PACKAGE__->position_column('position');
__PACKAGE__->grouping_column('cd');
__PACKAGE__->null_position_value(undef);  #?
1;


Oops -- sorry for the thread hijack.


-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100511/568=
6bf4d/attachment.htm


More information about the DBIx-Class mailing list