[mb-commits] branch, mbs-5732, updated. MBS-5732: Refactor the 'used in relationship' SQL
MusicBrainz Git Server <[email protected]> Tue, 29 Jan 2013 12:21:28 +0000
| Newsgroups | gmane.comp.audio.musicbrainz.cvs |
|---|---|
| Message-ID | <E1U0ABs-0001cT-QZ@wiley> |
The branch, mbs-5732 has been updated
via http://git.musicbrainz.org/gitweb/?p=musicbrainz-server/core.git;a=commit;h=328cfed59ea638085689a567562a1d9255e75fe0 (commit)
from http://git.musicbrainz.org/gitweb/?p=musicbrainz-server/core.git;a=commit;h=0d61474a88681ac1f14d88c94c229adc840d38f8 (commit)
Summary of changes:
lib/MusicBrainz/Server/Data/Artist.pm | 39 ++----------------------
lib/MusicBrainz/Server/Data/Label.pm | 40 ++-----------------------
lib/MusicBrainz/Server/Data/Utils/Cleanup.pm | 25 ++++++++++++++++
lib/MusicBrainz/Server/Data/Work.pm | 40 ++-----------------------
4 files changed, 37 insertions(+), 107 deletions(-)
create mode 100644 lib/MusicBrainz/Server/Data/Utils/Cleanup.pm
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 328cfed59ea638085689a567562a1d9255e75fe0
Author: Oliver Charles <[email protected]>
Date: Tue Jan 29 12:24:36 2013 +0000
MBS-5732: Refactor the 'used in relationship' SQL
diff --git a/lib/MusicBrainz/Server/Data/Artist.pm b/lib/MusicBrainz/Server/Data/Artist.pm
index 6fe95b5..00e1959 100644
--- a/lib/MusicBrainz/Server/Data/Artist.pm
+++ b/lib/MusicBrainz/Server/Data/Artist.pm
@@ -21,6 +21,7 @@ use MusicBrainz::Server::Data::Utils qw(
placeholders
query_to_list_limited
);
+use MusicBrainz::Server::Data::Utils::Cleanup qw( used_in_relationship );
extends 'MusicBrainz::Server::Data::CoreEntity';
with 'MusicBrainz::Server::Data::Role::Annotation' => { type => 'artist' };
@@ -360,7 +361,8 @@ sub load_for_artist_credits {
sub is_empty {
my ($self, $artist_id) = @_;
- return $self->sql->select_single_value(<<'EOSQL', $artist_id, $STATUS_OPEN);
+ my $used_in_relationship = used_in_relationship($self->c, artist => 'artist_row.id');
+ return $self->sql->select_single_value(<<EOSQL, $artist_id, $STATUS_OPEN);
SELECT TRUE
FROM artist artist_row
WHERE id = ?
@@ -375,40 +377,7 @@ sub is_empty {
WHERE artist = artist_row.id
LIMIT 1
) OR
- EXISTS (
- SELECT TRUE FROM l_artist_recording
- WHERE entity0 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_work
- WHERE entity0 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_url
- WHERE entity0 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_artist
- WHERE entity0 = artist_row.id OR entity1 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_label
- WHERE entity0 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_release
- WHERE entity0 = artist_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_release_group WHERE entity0 = artist_row.id
- LIMIT 1
- )
+ $used_in_relationship
)
EOSQL
}
diff --git a/lib/MusicBrainz/Server/Data/Label.pm b/lib/MusicBrainz/Server/Data/Label.pm
index d6837cd..0be0554 100644
--- a/lib/MusicBrainz/Server/Data/Label.pm
+++ b/lib/MusicBrainz/Server/Data/Label.pm
@@ -19,6 +19,7 @@ use MusicBrainz::Server::Data::Utils qw(
query_to_list
query_to_list_limited
);
+use MusicBrainz::Server::Data::Utils::Cleanup qw( used_in_relationship );
extends 'MusicBrainz::Server::Data::CoreEntity';
with 'MusicBrainz::Server::Data::Role::Annotation' => { type => 'label' };
@@ -295,7 +296,8 @@ sub load_meta
sub is_empty {
my ($self, $label_id) = @_;
- return $self->sql->select_single_value(<<'EOSQL', $label_id, $STATUS_OPEN);
+ my $used_in_relationship = used_in_relationship($self->c, label => 'label_row.id');
+ return $self->sql->select_single_value(<<EOSQL, $label_id, $STATUS_OPEN);
SELECT TRUE
FROM label label_row
WHERE id = ?
@@ -309,41 +311,7 @@ sub is_empty {
SELECT TRUE FROM release_label
WHERE label = label_row.id
) OR
- EXISTS (
- SELECT TRUE FROM l_label_recording
- WHERE entity0 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_work
- WHERE entity0 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_url
- WHERE entity0 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_label
- WHERE entity0 = label_row.id OR entity1 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_artist_label
- WHERE entity1 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_release
- WHERE entity0 = label_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_release_group
- WHERE entity0 = label_row.id
- LIMIT 1
- )
+ $used_in_relationship
)
EOSQL
}
diff --git a/lib/MusicBrainz/Server/Data/Utils/Cleanup.pm b/lib/MusicBrainz/Server/Data/Utils/Cleanup.pm
new file mode 100644
index 0000000..7f721b4
--- /dev/null
+++ b/lib/MusicBrainz/Server/Data/Utils/Cleanup.pm
@@ -0,0 +1,25 @@
+package MusicBrainz::Server::Data::Utils::Cleanup;
+use Moose;
+
+use Sub::Exporter -setup => {
+ exports => [qw( used_in_relationship )]
+};
+
+sub used_in_relationship {
+ my ($c, $t, $return) = @_;
+ join(
+ ' OR ',
+ map {
+ my ($t0, $t1) = @$_;
+ my $predicates = join(
+ ' OR ',
+ ($t0 eq $t ? "entity0 = $return" : ()),
+ ($t1 eq $t ? "entity1 = $return" : ()),
+ );
+ "EXISTS ( SELECT TRUE FROM l_${t0}_${t1} WHERE $predicates LIMIT 1)";
+ } grep { $_->[0] eq $t || $_->[1] eq $t }
+ $c->model('Relationship')->all_pairs
+ );
+}
+
+1;
diff --git a/lib/MusicBrainz/Server/Data/Work.pm b/lib/MusicBrainz/Server/Data/Work.pm
index 606a59d..e2fa01b 100644
--- a/lib/MusicBrainz/Server/Data/Work.pm
+++ b/lib/MusicBrainz/Server/Data/Work.pm
@@ -14,6 +14,7 @@ use MusicBrainz::Server::Data::Utils qw(
query_to_list
query_to_list_limited
);
+use MusicBrainz::Server::Data::Utils::Cleanup qw( used_in_relationship );
use MusicBrainz::Server::Entity::Work;
extends 'MusicBrainz::Server::Data::CoreEntity';
@@ -388,7 +389,8 @@ sub _find_recording_artists
sub is_empty {
my ($self, $work_id) = @_;
- return $self->sql->select_single_value(<<'EOSQL', $work_id, $STATUS_OPEN);
+ my $used_in_relationship = used_in_relationship($self->c, work => 'work_row.id');
+ return $self->sql->select_single_value(<<EOSQL, $work_id, $STATUS_OPEN);
SELECT TRUE
FROM work work_row
WHERE id = ?
@@ -399,41 +401,7 @@ sub is_empty {
FROM edit_work JOIN edit ON edit_work.edit = edit.id
WHERE status = ? AND work = work_row.id
) OR
- EXISTS (
- SELECT TRUE FROM l_artist_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_label_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_recording_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_release_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_release_group_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_url_work
- WHERE entity1 = work_row.id
- LIMIT 1
- ) OR
- EXISTS (
- SELECT TRUE FROM l_work_work
- WHERE entity0 = work_row.id OR entity1 = work_row.id
- LIMIT 1
- )
+ $used_in_relationship
)
EOSQL
}
-----------------------------------------------------------------------
hooks/post-receive
--
mb_server