[Dbix-class] SELECT .. FOR UPDATE with DBIx::Class
Oleg Pronin
syber.rus at gmail.com
Wed Jul 4 09:30:38 GMT 2007
Hello. I posted this solution only as a workaround.
If it goes to core then it would be much better to implement a callback
in Storage::DBI after $sql is generated but before $sth is generated.
(for example, $sql =3D $self->tune_sql($sql, $attrs) in sub _dbh_execute)
This would be the last change for Storage::DBI::* to implement some
database-specific features
like
Pg's for update/share
mysql's replace into / insert ignore / delayed updates / case-insensitive
select, etc
$attrs are needed to be passed to _dbh_execute to do that.
To DBIC developers: could you implement this (or similar) please?
2007/7/4, Daisuke Maki <daisuke at endeworks.jp>:
>
> Hi,
>
> I've been looking for a way to do SELECT FOR UPDATE via DBIx::Class, and
> found this:
>
> http://lists.rawmode.org/pipermail/dbix-class/2007-March/003568.html
>
> I don't think this is implemented yet, so I've given it a try.
>
> Attached is a patch (with tests) against trunk. The tests requires an
> extra module, Sys::SigAction, so the new tests will be skipped if
> Sys::SigAction is not available.
>
> Let me know if it look reasonable.
>
> Regards,
> --d
>
> Index: t/72pg.t
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> --- t/72pg.t (revision 3567)
> +++ t/72pg.t (working copy)
> @@ -27,7 +27,7 @@
> plan skip_all =3D> 'Set $ENV{DBICTEST_PG_DSN}, _USER and _PASS to run this
> test'
> . ' (note: creates and drops tables named artist and casecheck!)' unless
> ($dsn && $user);
>
> -plan tests =3D> 8;
> +plan tests =3D> 14;
>
> DBICTest::Schema->load_classes( 'Casecheck' );
> my $schema =3D DBICTest::Schema->connect($dsn, $user, $pass);
> @@ -87,6 +87,83 @@
> my $uc_name_info =3D $schema->source('Casecheck')->column_info( 'uc_name'=
);
> is( $uc_name_info->{size}, 3, "Case insensitive matching info for
> 'uc_name'" );
>
> +# Test SELECT ... FOR UPDATE
> +my $HaveSysSigAction =3D eval "require Sys::SigAction" && !$@;
> +if ($HaveSysSigAction) {
> + Sys::SigAction->import( 'set_sig_handler' );
> +}
> +
> +SKIP: {
> + skip "Sys::SigAction is not available", 3 unless $HaveSysSigAction;
> + # create a new schema
> + my $schema2 =3D DBICTest::Schema->connect($dsn, $user, $pass);
> + $schema2->source("Artist")->name("testschema.artist");
> +
> + $schema->txn_do( sub {
> + my $artist =3D $schema->resultset('Artist')->search(
> + {
> + artistid =3D> 1
> + },
> + {
> + locking =3D> 'update'
> + }
> + )->first;
> + is($artist->artistid, 1, "select for update returns artistid =3D
> 1");
> +
> + my $artist_from_schema2;
> + my $error_ok =3D 0;
> + eval {
> + my $h =3D set_sig_handler( 'ALRM', sub { die "DBICTestTimeou=
t"
> } );
> + alarm(2);
> + $artist_from_schema2 =3D
> $schema2->resultset('Artist')->find(1);
> + $artist_from_schema2->name('fooey');
> + $artist_from_schema2->update;
> + alarm(0);
> + };
> + if (my $e =3D $@) {
> + $error_ok =3D $e =3D~ /DBICTestTimeout/;
> + }
> +
> + # Make sure that an error was raised, and that the update failed
> + ok($error_ok, "update from second schema times out");
> + ok($artist_from_schema2->is_column_changed('name'), "'name'
> column is still dirty from second schema");
> + });
> +}
> +
> +SKIP: {
> + skip "Sys::SigAction is not available", 3 unless $HaveSysSigAction;
> + # create a new schema
> + my $schema2 =3D DBICTest::Schema->connect($dsn, $user, $pass);
> + $schema2->source("Artist")->name("testschema.artist");
> +
> + $schema->txn_do( sub {
> + my $artist =3D $schema->resultset('Artist')->search(
> + {
> + artistid =3D> 1
> + },
> + )->first;
> + is($artist->artistid, 1, "select for update returns artistid =3D
> 1");
> +
> + my $artist_from_schema2;
> + my $error_ok =3D 0;
> + eval {
> + my $h =3D set_sig_handler( 'ALRM', sub { die "DBICTestTimeou=
t"
> } );
> + alarm(2);
> + $artist_from_schema2 =3D
> $schema2->resultset('Artist')->find(1);
> + $artist_from_schema2->name('fooey');
> + $artist_from_schema2->update;
> + alarm(0);
> + };
> + if (my $e =3D $@) {
> + $error_ok =3D $e =3D~ /DBICTestTimeout/;
> + }
> +
> + # Make sure that an error was raised, and that the update failed
> + ok(! $error_ok, "update from second schema DOES NOT timeout");
> + ok(! $artist_from_schema2->is_column_changed('name'), "'name'
> column is NOT dirty from second schema");
> + });
> +}
> +
> END {
> if($dbh) {
> $dbh->do("DROP TABLE testschema.artist;");
> Index: lib/DBIx/Class/Storage/DBI/Pg.pm
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> --- lib/DBIx/Class/Storage/DBI/Pg.pm (revision 3567)
> +++ lib/DBIx/Class/Storage/DBI/Pg.pm (working copy)
> @@ -7,12 +7,35 @@
>
> use base qw/DBIx::Class::Storage::DBI/;
>
> +our $_LOCKING =3D undef;
> +
> # __PACKAGE__->load_components(qw/PK::Auto/);
>
> # Warn about problematic versions of DBD::Pg
> warn "DBD::Pg 1.49 is strongly recommended"
> if ($DBD::Pg::VERSION < 1.49);
>
> +sub _select {
> + my $self =3D shift;
> +
> + my $locking =3D delete $_[3]->{locking};
> + local $_LOCKING =3D $locking && $locking =3D~ /^(?:update|share)$/ ?
> $locking : undef;
> + $self->next::method(@_);
> +}
> +
> +sub sth {
> + my ($self, $sql) =3D @_;
> +
> + if ($_LOCKING) {
> + # XXX Should really be checking if this is a select statement
> + $sql .=3D $_LOCKING eq 'update' ? 'FOR UPDATE' :
> + $_LOCKING eq 'share' ? 'FOR SHARE' :
> + ''
> + ;
> + }
> + $self->SUPER::sth($sql);
> +}
> +
> sub _dbh_last_insert_id {
> my ($self, $dbh, $seq) =3D @_;
> $dbh->last_insert_id(undef, undef, undef, undef, {sequence =3D> $seq});
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070704/37d=
d7d16/attachment.htm
More information about the Dbix-class
mailing list