[Dbix-class] RFC: Round two for UNIQUE constraints
Daniel Westermann-Clark
daniel at acceleration.net
Sun Jan 22 03:44:38 CET 2006
So, I thought I'd give UNIQUE constraint support another try now that
the resultset refactoring is settling down. It was discussed briefly
before:
http://lists.rawmode.org/pipermail/dbix-class/2005-December/000385.html
It's pretty much the same code this time around, but now it's folded
into ResultSet and ResultSource.
Suggestions? Is it something to consider for 0.05?
One comment from last time around which I haven't taken into account:
> I think arguably find, find_or_create, etc. should all default to
> operating on the PK but be able to operate on any unique key, maybe
> with a { key => ... } attribute. The find methods don't currently
> take attributes, sadly, which will be Getting Fixed as I move them
> over onto the ResultSet object.
I'm concerned that this would require some slightly ugly API changes
to find and find_or_create, since currently they allow you to specify
a flat list of values.
--
Daniel Westermann-Clark
-------------- next part --------------
Auto-merging (0, 4384) /local/DBIx-Class to /mirror/bast/branches/DBIx-Class-resultset (base /mirror/bast/branches/DBIx-Class-resultset:4372).
Patching locally against mirror source http://dev.catalyst.perl.org/repos/bast.
A t/run/20unique.tl
A t/helperrels/20unique.t
U t/lib/DBICTest/Schema/CD.pm
U lib/DBIx/Class/ResultSourceInstance.pm
U lib/DBIx/Class/ResultSet.pm
U lib/DBIx/Class/ResultSource.pm
==== Patch <-> level 1
Source: 20164c6d-cd09-0410-925d-b4c4e616b846:/local/DBIx-Class:4384
Target: bd8105ee-0ff8-0310-8827-fb3f25b6796d:/branches/DBIx-Class-resultset:543
(http://dev.catalyst.perl.org/repos/bast)
Log:
r4375 at fortuna: dwc | 2006-01-21 19:38:25 -0500
r4376 at fortuna: dwc | 2006-01-21 20:13:17 -0500
First pass at UNIQUE constraints
r4382 at fortuna: dwc | 2006-01-21 20:58:40 -0500
Create the row directly
r4383 at fortuna: dwc | 2006-01-21 21:18:38 -0500
Add basic test
r4384 at fortuna: dwc | 2006-01-21 21:23:56 -0500
Spacing
=== t/run/20unique.tl
==================================================================
--- t/run/20unique.tl (revision 543)
+++ t/run/20unique.tl (patch - level 1)
@@ -0,0 +1,27 @@
+sub run_tests {
+my $schema = shift;
+
+plan tests => 4;
+
+my $title = 'UNIQUE Constraint';
+
+$schema->resultset('CD')->create({
+ artist => 1,
+ title => $title,
+ year => 2005,
+});
+
+my $cd = $schema->resultset('CD')->update_or_create({
+ artist => 1,
+ title => $title,
+ year => 2006,
+});
+
+ok(! $cd->is_changed, 'row is clean');
+is($cd->get_column('artist'), 1, 'artist is correct');
+is($cd->title, $title, 'title is correct');
+is($cd->year, 2006, 'year is correct');
+
+}
+
+1;
=== t/helperrels/20unique.t
==================================================================
--- t/helperrels/20unique.t (revision 543)
+++ t/helperrels/20unique.t (patch - level 1)
@@ -0,0 +1,7 @@
+use Test::More;
+use lib qw(t/lib);
+use DBICTest;
+use DBICTest::HelperRels;
+
+require "t/run/20unique.tl";
+run_tests(DBICTest->schema);
=== t/lib/DBICTest/Schema/CD.pm
==================================================================
--- t/lib/DBICTest/Schema/CD.pm (revision 543)
+++ t/lib/DBICTest/Schema/CD.pm (patch - level 1)
@@ -5,5 +5,6 @@
DBICTest::Schema::CD->table('cd');
DBICTest::Schema::CD->add_columns(qw/cdid artist title year/);
DBICTest::Schema::CD->set_primary_key('cdid');
+DBICTest::Schema::CD->add_unique_constraint(qw/artist title/);
1;
=== lib/DBIx/Class/ResultSourceInstance.pm
==================================================================
--- lib/DBIx/Class/ResultSourceInstance.pm (revision 543)
+++ lib/DBIx/Class/ResultSourceInstance.pm (patch - level 1)
@@ -35,6 +35,9 @@
sub set_primary_key { shift->result_source_instance->set_primary_key(@_); }
sub primary_columns { shift->result_source_instance->primary_columns(@_); }
+sub add_unique_constraint { shift->result_source_instance->add_unique_constraint(@_); }
+sub unique_constraints { shift->result_source_instance->unique_constraints(@_); }
+
sub add_relationship {
my ($class, $rel, @rest) = @_;
my $source = $class->result_source_instance;
=== lib/DBIx/Class/ResultSet.pm
==================================================================
--- lib/DBIx/Class/ResultSet.pm (revision 543)
+++ lib/DBIx/Class/ResultSet.pm (patch - level 1)
@@ -485,6 +485,51 @@
return defined($exists) ? $exists : $self->create($hash);
}
+=head2 update_or_create
+
+First, search for an existing row matching the unique constraints on
+this table. If a row is found, update it with the other given column
+values. If no row is found in the database, create one.
+
+Takes the same arguments as L</find_or_create>.
+
+=cut
+
+sub update_or_create {
+ my $self = shift;
+ my $hash = ref $_[0] eq 'HASH' ? shift : {@_};
+
+ my @unique_constraints = $self->{source}->unique_constraints;
+ unshift @unique_constraints, [ $self->{source}->primary_columns ]
+ if $self->{source}->primary_columns;
+
+ my @unique_hashes;
+ foreach my $unique_constraint (@unique_constraints) {
+ my %unique_hash =
+ map { $_ => $hash->{$_} }
+ grep { exists $hash->{$_} }
+ @$unique_constraint;
+
+ push @unique_hashes, \%unique_hash
+ if (scalar keys %unique_hash == scalar @$unique_constraint);
+ }
+
+ my $row;
+ if (@unique_hashes) {
+ $row = $self->search(\@unique_hashes, { rows => 1 })->first;
+ if ($row) {
+ $row->set_columns($hash);
+ $row->update;
+ }
+ }
+
+ unless ($row) {
+ $row = $self->create($hash);
+ }
+
+ return $row;
+}
+
=head1 ATTRIBUTES
The resultset takes various attributes that modify its behavior.
=== lib/DBIx/Class/ResultSource.pm
==================================================================
--- lib/DBIx/Class/ResultSource.pm (revision 543)
+++ lib/DBIx/Class/ResultSource.pm (patch - level 1)
@@ -11,7 +11,7 @@
__PACKAGE__->load_components(qw/AccessorGroup/);
__PACKAGE__->mk_group_accessors('simple' =>
- qw/_ordered_columns _columns _primaries name resultset_class result_class schema from _relationships/);
+ qw/_ordered_columns _columns _primaries _unique_constraints name resultset_class result_class schema from _relationships/);
=head1 NAME
@@ -166,6 +166,39 @@
return @{shift->_primaries||[]};
}
+=head2 add_unique_constraint
+
+Declare a unique constraint on this source. Call once for each unique
+constraint.
+
+ # For e.g. UNIQUE (column1, column2)
+ __PACKAGE__->add_unique_constraint(qw/column1 column2/);
+
+=cut
+
+sub add_unique_constraint {
+ my ($self, @cols) = @_;
+
+ for (@cols) {
+ $self->throw("Column $_ does not exist on " . $self->name)
+ unless $self->has_column($_);
+ }
+
+ my @unique_constraints = $self->unique_constraints;
+ push @unique_constraints, \@cols;
+ $self->_unique_constraints(\@unique_constraints);
+}
+
+=head2 unique_constraints
+
+Read-only accessor which returns the list of unique constraints on this source.
+
+=cut
+
+sub unique_constraints {
+ return @{shift->_unique_constraints||[]};
+}
+
=head2 from
Returns an expression of the source to be supplied to storage to specify
==== BEGIN SVK PATCH BLOCK ====
Version: svk 1.06 (linux)
eJyNWF1v29YZFrAh2IhsRVNgGAZsOMkUWNosizz8pmZNqWTHQbusi5NetOnYQ/LQIkKTCknZMSxv
kdK029ph3X7ALobuZj9gt/s1uyj2D3a195xD2ZJFWxMMW+Z53+f9/jjczR53esqk25UndUWe7L//
juO8Rwp/eFfRJnVjQoOoSLO6PonpEY3r6iROD+raJCGHFE7zdJz57EtBsgNasC+R/4wW3a4CcJaA
2+EQc1iO6pEiTfK6zeHdIqO0jieK0VPMSU9lP25dsSc5hTMO7Gb0KMqjNAHRmmppQFFXJgoApCOa
uFmaFqCaruk9zFjliR+nOXUZOsPTGTWug0WcPIgy6oNKJ/C04DiCF8hAC6WCDsyOvAVKrqBeSlmE
U3paKU6tgNEnRTsbJ1yZc5HKRK/SDAPtkMYjmmU0zi+zmFegCzXxOjUx+KlnMCCtbk1IELhhFNM6
uJ8r2MbyOImej+lWETNTXPBLS7kaThVw/JSMRvGJW9AXRUDjgjARrltXsQrJ8LBWq71yP5j++h+7
3/p0542/3n/rq0H79W5n0N51BtNvv96Z3vrdfe2TvVv7H//hNJ/en7Y/GUytv+3MfvrF7m8+uz/7
eGfW+/Td2YPd6ff3pt+b7Xbenx0Nprenu7MfzHamtx/Ojj7Lxx4CC0CBvMjRqXR4guq5P6SHBG2j
fBiFRUeSRjFJ0HYXaR2piIqYwtHGk4cPfvVkB/UhL4uMREmxAYStbkbzcVxAJjY2+oONZqvrZ5QU
tHEqIUSyIsoLZRO+nlCSISzL+qZ01vQDtD0eBUDmpplrpM8at1vdKHf9IUkOaLCJNrL0GEU58mNK
ko1mR4ryBstyP43Hh0ljY6O5iZRNlGbMxWgDGKQzSVI6EmTXQnqLmEEIVTzxVJvolq+rqqarOjFV
28S6bhErtC1F063zgBuLAYeoLGbZQtxZWq4Lu35t2A0Iu3Ie9umNN7wvB7U/7n73k8E3H97+597G
5+OcoscQJsf5RZrRjgS5i54fN3gSNwdvP+g7zh5X7RGoBsHI6PMxSEd3VnL0Tkc6j3mj1RXxbl7h
LoO5K5AxJmGghLKv6bqCTVsnnu5btq0rpoU1ZhkvNKuqNjVRaW2mJLOAF+SakjPPIe0KSKxcgmzv
cyO4BmuQrRIZepc874giumY1aLs/2Bodck3WQNsCujLCXBy40g9C2whtLFuK6lHLVBXTVDTT8Hwr
9DVLrluayIHp/uzWjX5t+svay/5cI8cRKjlOf9DqsrwUQYVSmNdh4/lxW1Qa4sXahsBeUQtzlWQl
NEJL13VKZUOzdEOVTZUYlhn4nq1jeuGwqqFgTUqfvRDj4FofMYoLuKqmD0/meO1+TPJcyF2HistJ
Al+1paiq1iW89iPeo/b5LH4AbiOJT1mAuUKLo02/nB7VQLSo5DaW1ZCvU+MSwNVmqtdnGO9tMlZM
nfpBoNLANFXDtg3TD3TV9H1FlWH/UCwsUuxPtS/lm9Mf1b64UXsJf17d/LxYySd0KubAvLe7Yolx
o9JzV6Vhz2120BliH4lNmRWKfD3yCk91EnOrfawbFlG0kBoakXWDqjg0NVu2LNuzTSO83nE6g8BY
9UzqaYpsy9gwfD/wCTE8QzM8K1BNA/qzbAjP/Vedvvrg5u9/WPv6O7WX8OfP8tffwOhiiomxJ0m7
UZYXmyiHeecPUZhmCEYpfQHVGSUHiM21Q7bmsX+KIS2dhBadlCZSMYThVxAvplvoQYgIKudhmI4T
GI9CKooKdBwVQ46Twq8MHURHNEFiSkpHJB7TnAMk6RICihLOBCjEIzndREJ7EE23JOkxeUZzTpDD
FgtT/GB8SJlmJEfv/qwdRklwYXEXGLb9cSGJmF/yB2JrAEJ8yaBxeLFizJ8OST5E8DijIaq7H8of
Ifocbezd29/bQD8XxMhBpz33DJpaydSryKxtIaDVPRU5dVaRS7kQO04EbAXMJvpwFWeURYckOymX
jxx9xFHYJwrXUq9qzSympSqQHpRAmjBPrNZho0LDZunREvTuAijaPj/hp2SEoODqLlvkuJ9Bzbp7
hs6WyA4yOgIynqH5NXS9VQVL29hnNAb5yxZuoqeL2i2BgeMauU9i2Amf0ZP8khmQJOKsQmZTOO7s
3K11SGzxjIEuq7DoLEZ3kSWiPBtPL6t8yuokZx5T0BkssyGr5s5iwBsMaBF4Ds5A5xtq3uCObHYq
qESJXJycLRo0TmKa56tClrUvV+xFGSV/RotxlnDy6s7J255iBroBG4Cnhdg0LJ3atqUqITFMSlVL
WbPU8P0Q9kEMvVfxTGiUhm34KgksannUVy1D9fS6qZdbzb+b/3nz1uvHtdlbtb/UvqrN3nz5+u3a
v37y99+ujpDKuSJJA+pDNkAjWu2W0LEQb5ai/rZQn8QxPPQpb728uASTdMG0xXz1Y7TLCLYOtlB5
s2mI2MG9QnzBTSBz3ffu9d+5d3/Hda9Zv0rOOSPfwC6aYiUbDy7kb4PHdBP1gBcSdhv1XF5XTP1G
+VDkQRn8YgjBbdzpc1GsvIMUunWSFqKGmUfuoK05NXsP0SzTqEyu8gSSZ36fqrs8i3gOreuv1V11
qf6XGupTbgOjKQFWXZFf1OFip+uwO932kJIAV+wSkvQITlppEp8g4vtgGDjseBhBwEURiAkWc5eE
1XN2KXWWxljF6iKdV1fvtNxiVk2pLjpeMdgKA92jqu1h7BMDG1gzA1jPqE/VUCe+eHuiKhOUaaqp
9yABYDcjDkLBsY8m7PJstGSlhRWk2I5qOVhHLVmXZQlJnMW4jgXLjqI6ijln4WsKGsFaikgxr4BF
9zJIC6+B1C1Hk+eQfTHxmddZtxJbbHwikNRrkRRHscCkOdK9IECwl0Q+YjdWAaCtAcCqoxtzgP0R
8WHDksCnrW4Xw3aOxau2x/xtmeM8SWBTynIS34U7vzYZkWIIm3hGj+Cf8TgK2JuedpzCGOILfEvc
S8S7tbpqTLCsGJpvBC0/kO2WDAtky8Z60PI0X6OGAuujZnSbeHIVPFwO2l4GOy9MnQUJrfO3Kfxe
oDJRXmApMlzUWnIYWi1ZBVGWhc1W6Kkh1j3DtI2grtv/l0rOik0Os+h/DdKsug==
==== END SVK PATCH BLOCK ====
More information about the Dbix-class
mailing list