From 9364e475f97227ac61244831ae7dc135ffc2268c Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 18 Jul 2014 12:38:21 -0400 Subject: [PATCH] Bug 12610 - Replace use of DBI in C4::Tags with DBIx::Class Test Plan: 1) Apply this patch 2) prove t/db_dependent/Tags.t 3) Note all unit tests pass --- C4/Tags.pm | 357 +++++++++++++++++++++++++++++------------------------------- 1 files changed, 173 insertions(+), 184 deletions(-) diff --git a/C4/Tags.pm b/C4/Tags.pm index b6b319b..5b61b5c 100644 --- a/C4/Tags.pm +++ b/C4/Tags.pm @@ -40,7 +40,6 @@ BEGIN { &add_tags &add_tag &delete_tag_row_by_id &remove_tag - &delete_tag_rows_by_ids &get_approval_rows &blacklist &whitelist @@ -74,34 +73,19 @@ INIT { $debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n"; } -sub get_filters { - my $query = "SELECT * FROM tags_filters "; - my ($sth); - if (@_) { - $sth = C4::Context->dbh->prepare($query . " WHERE filter_id = ? "); - $sth->execute(shift); - } else { - $sth = C4::Context->dbh->prepare($query); - $sth->execute; - } - return $sth->fetchall_arrayref({}); -} - -# (SELECT count(*) FROM tags_all ) as tags_all, -# (SELECT count(*) FROM tags_index ) as tags_index, - sub approval_counts { - my $query = "SELECT - (SELECT count(*) FROM tags_approval WHERE approved= 1) as approved_count, - (SELECT count(*) FROM tags_approval WHERE approved=-1) as rejected_count, - (SELECT count(*) FROM tags_approval WHERE approved= 0) as unapproved_count - "; - my $sth = C4::Context->dbh->prepare($query); - $sth->execute; - my $result = $sth->fetchrow_hashref(); - $result->{approved_total} = $result->{approved_count} + $result->{rejected_count} + $result->{unapproved_count}; - $debug and warn "counts returned: " . Dumper $result; - return $result; + my $rs = Koha::Database->new()->schema()->resultset('TagsApproval')->search( + { + approved => [ 1, -1, 0 ] + } + ); + + return { + approved_total => $rs->count(), + approved_count => $rs->count( { approved => 1 } ), + rejected_count => $rs->count( { approved => -1 } ), + unapproved_count => $rs->count( { approved => 0 } ), + } } =head2 get_count_by_tag_status @@ -112,14 +96,13 @@ Takes a status and gets a count of tags with that status =cut -sub get_count_by_tag_status { +sub get_count_by_tag_status { my ($status) = @_; - my $dbh = C4::Context->dbh; - my $query = - "SELECT count(*) FROM tags_approval WHERE approved=?"; - my $sth = $dbh->prepare($query); - $sth->execute( $status ); - return $sth->fetchrow; + return Koha::Database->new()->schema()->resultset('TagsApproval')->count( + { + approved => $status + } + ); } sub remove_tag { @@ -151,32 +134,25 @@ sub remove_tag { } sub delete_tag_index { - (@_) or return; - my $sth = C4::Context->dbh->prepare("DELETE FROM tags_index WHERE term = ? AND biblionumber = ? LIMIT 1"); - $sth->execute(@_); - return $sth->rows || 0; + my ( $term, $biblionumber ) = @_; + return Koha::Database->new()->schema()->resultset('TagsIndex')->search( + { + term => $term, + biblionumber => $biblionumber, + } + )->delete(); } + sub delete_tag_approval { - (@_) or return; - my $sth = C4::Context->dbh->prepare("DELETE FROM tags_approval WHERE term = ? LIMIT 1"); - $sth->execute(shift); - return $sth->rows || 0; + my ($term) = @_; + my $t = Koha::Database->new()->schema()->resultset('TagsApproval')->find( $term ); + return $t ? $t->delete() : 0; } + sub delete_tag_row_by_id { - (@_) or return; - my $sth = C4::Context->dbh->prepare("DELETE FROM tags_all WHERE tag_id = ? LIMIT 1"); - $sth->execute(shift); - return $sth->rows || 0; -} -sub delete_tag_rows_by_ids { - (@_) or return; - my $i=0; - foreach(@_) { - $i += delete_tag_row_by_id($_); - } - ($i == scalar(@_)) or - warn sprintf "delete_tag_rows_by_ids tried %s tag_ids, only succeeded on $i", scalar(@_); - return $i; + my ( $tag_id ) = @_; + my $t = Koha::Database->new()->schema()->resultset('TagAll')->find( $tag_id ); + return $t ? $t->delete() : 0; } sub get_tag_rows { @@ -365,27 +341,18 @@ sub get_approval_rows { # i.e., from tags_approval } sub is_approved { - my $term = shift or return; - my $sth = C4::Context->dbh->prepare("SELECT approved FROM tags_approval WHERE term = ?"); - $sth->execute($term); - unless ($sth->rows) { - $ext_dict and return (spellcheck($term) ? 0 : 1); # spellcheck returns empty on OK word - return 0; - } - return $sth->fetchrow; -} + my $term = shift or return; -sub get_tag_index { - my $term = shift or return; - my $sth; - if (@_) { - $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?"); - $sth->execute($term,shift); - } else { - $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ?"); - $sth->execute($term); - } - return $sth->fetchrow_hashref; + my $tag = Koha::Database->new()->schema()->resultset('TagsIndex')->find( + { term => $term }, + { result_class => 'DBIx::Class::ResultClass::HashRefInflator' } + ); + + unless ($tag) { + return $ext_dict ? spellcheck($term) ? 0 : 1 : 0; + } + + return $tag; } sub whitelist { @@ -407,6 +374,7 @@ sub whitelist { } return scalar @_; } + # note: there is no "unwhitelist" operation because there is no remove for Ispell. # The blacklist regexps should operate "in front of" the whitelist, so if you approve # a term mistakenly, you can still reverse it. But there is no going back to "neutral". @@ -423,154 +391,175 @@ sub blacklist { } return scalar @_; } -sub add_filter { - my $operator = shift; - defined $operator or return; # have to test defined to allow =0 (kohaadmin) - my $query = "INSERT INTO tags_blacklist (regexp,y,z) VALUES (?,?,?)"; - # my $sth = C4::Context->dbh->prepare($query); - return scalar @_; -} -sub remove_filter { - my $operator = shift; - defined $operator or return; # have to test defined to allow =0 (kohaadmin) - my $query = "REMOVE FROM tags_blacklist WHERE blacklist_id = ?"; - # my $sth = C4::Context->dbh->prepare($query); - # $sth->execute($term); - return scalar @_; -} sub add_tag_approval { # or disapproval $debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")"; - my $term = shift or return; - my $query = "SELECT * FROM tags_approval WHERE term = ?"; - my $sth = C4::Context->dbh->prepare($query); - $sth->execute($term); - ($sth->rows) and return increment_weight_total($term); - my $operator = shift || 0; - my $approval = (@_ ? shift : 0); # default is unapproved - my @exe_args = ($term); # all 3 queries will use this argument - if ($operator) { - $query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,?,NOW())"; - push @exe_args, $operator, $approval; - } elsif ($approval) { - $query = "INSERT INTO tags_approval (term,approved,date_approved) VALUES (?,?,NOW())"; - push @exe_args, $approval; - } else { - $query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())"; - } - $debug and print STDERR "add_tag_approval query: $query\nadd_tag_approval args: (" . join(", ", @exe_args) . ")\n"; - $sth = C4::Context->dbh->prepare($query); - $sth->execute(@exe_args); - return $sth->rows; + my $term = shift or return; + my $operator = shift || 0; + my $approval = ( @_ ? shift : 0 ); # default is unapproved + + my $rs = Koha::Database->new()->schema()->resultset('TagsApproval'); + + my $count = $rs->count( { term => $term } ); + return increment_weight_total($term) if $count; + + return $rs->create( + { + term => $term, + approved => $approval, + approved_by => $operator || undef, + date_approved => \'NOW()', + } + ); } sub mod_tag_approval { my $operator = shift; - defined $operator or return; # have to test defined to allow =0 (kohaadmin) + defined $operator or return; # have to test defined to allow = 0 (kohaadmin) my $term = shift or return; my $approval = (scalar @_ ? shift : 1); # default is to approve - my $query = "UPDATE tags_approval SET approved_by=?, approved=?, date_approved=NOW() WHERE term = ?"; - $debug and print STDERR "mod_tag_approval query: $query\nmod_tag_approval args: ($operator,$approval,$term)\n"; - my $sth = C4::Context->dbh->prepare($query); - $sth->execute($operator,$approval,$term); + + return Koha::Database->new()->schema()->resultset('TagsApproval') + ->search( { term => $term } )->update( + { + approved_by => $operator, + approved => $approval, + date_approved => \'NOW()', + } + ); } sub add_tag_index { - my $term = shift or return; - my $biblionumber = shift or return; - my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?"; - my $sth = C4::Context->dbh->prepare($query); - $sth->execute($term,$biblionumber); - ($sth->rows) and return increment_weight($term,$biblionumber); - $query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)"; - $debug and print STDERR "add_tag_index query: $query\nadd_tag_index args: ($term,$biblionumber)\n"; - $sth = C4::Context->dbh->prepare($query); - $sth->execute($term,$biblionumber); - return $sth->rows; + my $term = shift or return; + my $biblionumber = shift or return; + + my $rs = + Koha::Database->new()->schema()->resultset('TagsIndex'); + + my $count = $rs->count( + { + term => $term, + biblionumber => $biblionumber, + } + ); + + return increment_weight( $term, $biblionumber ) if $count; + + return $rs->create( + { + term => $term, + biblionumber => $biblionumber + } + ); } -sub get_tag { # by tag_id - (@_) or return; - my $sth = C4::Context->dbh->prepare(TAG_SELECT . "WHERE tag_id = ?"); - $sth->execute(shift); - return $sth->fetchrow_hashref; +sub get_tag { # by tag_id + my ($tag_id) = @_; + + return Koha::Database->new()->schema()->resultset('TagAll')->find( + $tag_id, + { + result_class => 'DBIx::Class::ResultClass::HashRefInflator', + } + ); } sub increment_weights { increment_weight(@_); increment_weight_total(shift); } + sub decrement_weights { decrement_weight(@_); decrement_weight_total(shift); } + sub increment_weight_total { _set_weight_total('weight_total+1',shift); } + sub increment_weight { _set_weight('weight+1',shift,shift); } + sub decrement_weight_total { _set_weight_total('weight_total-1',shift); } + sub decrement_weight { _set_weight('weight-1',shift,shift); } + sub _set_weight_total { - my $sth = C4::Context->dbh->prepare(" - UPDATE tags_approval - SET weight_total=" . (shift) . " - WHERE term=? - "); # note: CANNOT use "?" for weight_total (see the args above). - $sth->execute(shift); # just the term + my ( $weight_total, $term ) = @_; + + $term = + Koha::Database->new()->schema()->resultset('TagsApproval')->find($term); + + $term->update( { weight_total => $weight_total } ) if $term; } + sub _set_weight { - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(" - UPDATE tags_index - SET weight=" . (shift) . " - WHERE term=? - AND biblionumber=? - "); - $sth->execute(@_); + my ( $weight, $term, $biblionumber ) = @_; + + my $t = + Koha::Database->new()->schema()->resultset('TagsIndex') + ->find( { term => $term, biblionumber => $biblionumber } ); + + $term->update( { weight => $weight } ) if $t; } -sub add_tag { # biblionumber,term,[borrowernumber,approvernumber] - my $biblionumber = shift or return; - my $term = shift or return; - my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin - $term =~ s/^\s+//; - $term =~ s/\s+$//; - ($term) or return; # must be more than whitespace - my $rows = get_tag_rows({biblionumber=>$biblionumber, borrowernumber=>$borrowernumber, term=>$term, limit=>1}); - my $query = "INSERT INTO tags_all - (borrowernumber,biblionumber,term,date_created) - VALUES (?,?,?,NOW())"; - $debug and print STDERR "add_tag query: $query\n", - "add_tag query args: ($borrowernumber,$biblionumber,$term)\n"; - if (scalar @$rows) { - $debug and carp "Duplicate tag detected. Tag not added."; - return; - } - # add to tags_all regardless of approaval - my $sth = C4::Context->dbh->prepare($query); - $sth->execute($borrowernumber,$biblionumber,$term); - - # then - if (scalar @_) { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved. - my $approver = shift; - $debug and print STDERR "term '$term' pre-approved by borrower #$approver\n"; - add_tag_approval($term,$approver,1); - add_tag_index($term,$biblionumber,$approver); - } elsif (is_approved($term) >= 1) { - $debug and print STDERR "term '$term' approved by whitelist\n"; - add_tag_approval($term,0,1); - add_tag_index($term,$biblionumber,1); - } else { - $debug and print STDERR "term '$term' NOT approved (yet)\n"; - add_tag_approval($term); - add_tag_index($term,$biblionumber); - } +sub add_tag { # biblionumber,term,[borrowernumber,approvernumber] + my $biblionumber = shift or return; + my $term = shift or return; + my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin + + $term =~ s/^\s+//; + $term =~ s/\s+$//; + ($term) or return; # must be more than whitespace + + my $rows = get_tag_rows( + { + biblionumber => $biblionumber, + borrowernumber => $borrowernumber, + term => $term, + limit => 1 + } + ); + + if ( scalar @$rows ) { + $debug and carp "Duplicate tag detected. Tag not added."; + return; + } + + my $t = Koha::Database->new()->schema()->resultset('TagAll')->create( + { + borrowernumber => $borrowernumber, + biblionumber => $biblionumber, + term => $term, + date_created => \'NOW()', + } + ); + + + if ( scalar @_ ) + { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved. + my $approver = shift; + $debug + and print STDERR "term '$term' pre-approved by borrower #$approver\n"; + add_tag_approval( $term, $approver, 1 ); + add_tag_index( $term, $biblionumber, $approver ); + } + elsif ( is_approved($term) >= 1 ) { + $debug and print STDERR "term '$term' approved by whitelist\n"; + add_tag_approval( $term, 0, 1 ); + add_tag_index( $term, $biblionumber, 1 ); + } + else { + $debug and print STDERR "term '$term' NOT approved (yet)\n"; + add_tag_approval($term); + add_tag_index( $term, $biblionumber ); + } } # This takes a set of tags, as returned by C and divides -- 1.7.2.5