3 # Copyright Liblime 2008
4 # Parts Copyright ACPL 2011
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it
9 # under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # Koha is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
29 use constant TAG_FIELDS => qw(tag_id borrowernumber biblionumber term language date_created);
30 use constant TAG_SELECT => "SELECT " . join(',', TAG_FIELDS) . "\n FROM tags_all\n";
32 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
37 &get_tag &get_tags &get_tag_rows
41 &delete_tag_rows_by_ids
47 &get_count_by_tag_status
52 my $ext_dict = C4::Context->preference('TagsExternalDictionary');
55 import Data::Dumper qw(:DEFAULT);
56 print STDERR __PACKAGE__ . " external dictionary = " . ($ext_dict||'none') . "\n";
59 require Lingua::Ispell;
60 import Lingua::Ispell qw(spellcheck add_word_lc save_dictionary);
64 =head1 C4::Tags.pm - Support for user tagging of biblios.
66 More verose debugging messages are sent in the presence of non-zero $ENV{"DEBUG"}.
71 my $ext_dict = C4::Context->preference('TagsExternalDictionary');
72 $ext_dict and $Lingua::Ispell::path = $ext_dict;
73 $debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n";
77 my $query = "SELECT * FROM tags_filters ";
80 $sth = C4::Context->dbh->prepare($query . " WHERE filter_id = ? ");
83 $sth = C4::Context->dbh->prepare($query);
86 return $sth->fetchall_arrayref({});
89 # (SELECT count(*) FROM tags_all ) as tags_all,
90 # (SELECT count(*) FROM tags_index ) as tags_index,
94 (SELECT count(*) FROM tags_approval WHERE approved= 1) as approved_count,
95 (SELECT count(*) FROM tags_approval WHERE approved=-1) as rejected_count,
96 (SELECT count(*) FROM tags_approval WHERE approved= 0) as unapproved_count
98 my $sth = C4::Context->dbh->prepare($query);
100 my $result = $sth->fetchrow_hashref();
101 $result->{approved_total} = $result->{approved_count} + $result->{rejected_count} + $result->{unapproved_count};
102 $debug and warn "counts returned: " . Dumper $result;
106 =head2 get_count_by_tag_status
108 get_count_by_tag_status($status);
110 Takes a status and gets a count of tags with that status
114 sub get_count_by_tag_status {
116 my $dbh = C4::Context->dbh;
118 "SELECT count(*) FROM tags_approval WHERE approved=?";
119 my $sth = $dbh->prepare($query);
120 $sth->execute( $status );
121 return $sth->fetchrow;
125 my $tag_id = shift or return;
126 my $user_id = (@_) ? shift : undef;
127 my $rows = (defined $user_id) ?
128 get_tag_rows({tag_id=>$tag_id, borrowernumber=>$user_id}) :
129 get_tag_rows({tag_id=>$tag_id}) ;
131 (scalar(@$rows) == 1) or return; # should never happen (duplicate ids)
132 my $row = shift(@$rows);
133 ($tag_id == $row->{tag_id}) or return 0;
134 my $tags = get_tags({term=>$row->{term}, biblionumber=>$row->{biblionumber}});
135 my $index = shift(@$tags);
136 $debug and print STDERR
137 sprintf "remove_tag: tag_id=>%s, biblionumber=>%s, weight=>%s, weight_total=>%s\n",
138 $row->{tag_id}, $row->{biblionumber}, $index->{weight}, $index->{weight_total};
139 if ($index->{weight} <= 1) {
140 delete_tag_index($row->{term},$row->{biblionumber});
142 decrement_weight($row->{term},$row->{biblionumber});
144 if ($index->{weight_total} <= 1) {
145 delete_tag_approval($row->{term});
147 decrement_weight_total($row->{term});
149 delete_tag_row_by_id($tag_id);
152 sub delete_tag_index {
154 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_index WHERE term = ? AND biblionumber = ? LIMIT 1");
156 return $sth->rows || 0;
158 sub delete_tag_approval {
160 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_approval WHERE term = ? LIMIT 1");
161 $sth->execute(shift);
162 return $sth->rows || 0;
164 sub delete_tag_row_by_id {
166 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_all WHERE tag_id = ? LIMIT 1");
167 $sth->execute(shift);
168 return $sth->rows || 0;
170 sub delete_tag_rows_by_ids {
174 $i += delete_tag_row_by_id($_);
176 ($i == scalar(@_)) or
177 warn sprintf "delete_tag_rows_by_ids tried %s tag_ids, only succeeded on $i", scalar(@_);
182 my $hash = shift || {};
183 my @ok_fields = TAG_FIELDS;
184 push @ok_fields, 'limit'; # push the limit! :)
188 foreach my $key (keys %$hash) {
189 $debug and print STDERR "get_tag_rows arg. '$key' = ", $hash->{$key}, "\n";
190 unless (length $key) {
191 carp "Empty argument key to get_tag_rows: ignoring!";
194 unless (1 == scalar grep {/^ $key $/x} @ok_fields) {
195 carp "get_tag_rows received unreconized argument key '$key'.";
198 if ($key eq 'limit') {
199 my $val = $hash->{$key};
200 unless ($val =~ /^(\d+,)?\d+$/) {
201 carp "Non-nuerical limit value '$val' ignored!";
204 $limit = " LIMIT $val\n";
206 $wheres .= ($wheres) ? " AND $key = ?\n" : " WHERE $key = ?\n";
207 push @exe_args, $hash->{$key};
210 my $query = TAG_SELECT . ($wheres||'') . $limit;
211 $debug and print STDERR "get_tag_rows query:\n $query\n",
212 "get_tag_rows query args: ", join(',', @exe_args), "\n";
213 my $sth = C4::Context->dbh->prepare($query);
215 $sth->execute(@exe_args);
219 return $sth->fetchall_arrayref({});
222 sub get_tags { # i.e., from tags_index
223 my $hash = shift || {};
224 my @ok_fields = qw(term biblionumber weight limit sort approved);
229 foreach my $key (keys %$hash) {
230 $debug and print STDERR "get_tags arg. '$key' = ", $hash->{$key}, "\n";
231 unless (length $key) {
232 carp "Empty argument key to get_tags: ignoring!";
235 unless (1 == scalar grep {/^ $key $/x} @ok_fields) {
236 carp "get_tags received unreconized argument key '$key'.";
239 if ($key eq 'limit') {
240 my $val = $hash->{$key};
241 unless ($val =~ /^(\d+,)?\d+$/) {
242 carp "Non-nuerical limit value '$val' ignored!";
245 $limit = " LIMIT $val\n";
246 } elsif ($key eq 'sort') {
247 foreach my $by (split /\,/, $hash->{$key}) {
249 $by =~ /^([-+])?(term)/ or
250 $by =~ /^([-+])?(biblionumber)/ or
251 $by =~ /^([-+])?(weight)/
253 carp "get_tags received illegal sort order '$by'";
259 $order = " ORDER BY ";
261 $order .= $2 . " " . ((!$1) ? '' : $1 eq '-' ? 'DESC' : $1 eq '+' ? 'ASC' : '') . "\n";
265 my $whereval = $hash->{$key};
266 my $longkey = ($key eq 'term' ) ? 'tags_index.term' :
267 ($key eq 'approved') ? 'tags_approval.approved' : $key;
268 my $op = ($whereval =~ s/^(>=|<=)// or
269 $whereval =~ s/^(>|=|<)// ) ? $1 : '=';
270 $wheres .= ($wheres) ? " AND $longkey $op ?\n" : " WHERE $longkey $op ?\n";
271 push @exe_args, $whereval;
275 SELECT tags_index.term as term,biblionumber,weight,weight_total
277 LEFT JOIN tags_approval
278 ON tags_index.term = tags_approval.term
279 " . ($wheres||'') . $order . $limit;
280 $debug and print STDERR "get_tags query:\n $query\n",
281 "get_tags query args: ", join(',', @exe_args), "\n";
282 my $sth = C4::Context->dbh->prepare($query);
284 $sth->execute(@exe_args);
288 return $sth->fetchall_arrayref({});
291 sub get_approval_rows { # i.e., from tags_approval
292 my $hash = shift || {};
293 my @ok_fields = qw(term approved date_approved approved_by weight_total limit sort borrowernumber);
298 foreach my $key (keys %$hash) {
299 $debug and print STDERR "get_approval_rows arg. '$key' = ", $hash->{$key}, "\n";
300 unless (length $key) {
301 carp "Empty argument key to get_approval_rows: ignoring!";
304 unless (1 == scalar grep {/^ $key $/x} @ok_fields) {
305 carp "get_approval_rows received unreconized argument key '$key'.";
308 if ($key eq 'limit') {
309 my $val = $hash->{$key};
310 unless ($val =~ /^(\d+,)?\d+$/) {
311 carp "Non-numerical limit value '$val' ignored!";
314 $limit = " LIMIT $val\n";
315 } elsif ($key eq 'sort') {
316 foreach my $by (split /\,/, $hash->{$key}) {
318 $by =~ /^([-+])?(term)/ or
319 $by =~ /^([-+])?(biblionumber)/ or
320 $by =~ /^([-+])?(borrowernumber)/ or
321 $by =~ /^([-+])?(weight_total)/ or
322 $by =~ /^([-+])?(approved(_by)?)/ or
323 $by =~ /^([-+])?(date_approved)/
325 carp "get_approval_rows received illegal sort order '$by'";
331 $order = " ORDER BY " unless $order;
333 $order .= $2 . " " . ((!$1) ? '' : $1 eq '-' ? 'DESC' : $1 eq '+' ? 'ASC' : '') . "\n";
337 my $whereval = $hash->{$key};
338 my $op = ($whereval =~ s/^(>=|<=)// or
339 $whereval =~ s/^(>|=|<)// ) ? $1 : '=';
340 $wheres .= ($wheres) ? " AND $key $op ?\n" : " WHERE $key $op ?\n";
341 push @exe_args, $whereval;
345 SELECT tags_approval.term AS term,
346 tags_approval.approved AS approved,
347 tags_approval.date_approved AS date_approved,
348 tags_approval.approved_by AS approved_by,
349 tags_approval.weight_total AS weight_total,
350 CONCAT(borrowers.surname, ', ', borrowers.firstname) AS approved_by_name
353 ON tags_approval.approved_by = borrowers.borrowernumber ";
354 $query .= ($wheres||'') . $order . $limit;
355 $debug and print STDERR "get_approval_rows query:\n $query\n",
356 "get_approval_rows query args: ", join(',', @exe_args), "\n";
357 my $sth = C4::Context->dbh->prepare($query);
359 $sth->execute(@exe_args);
363 return $sth->fetchall_arrayref({});
367 my $term = shift or return;
368 my $sth = C4::Context->dbh->prepare("SELECT approved FROM tags_approval WHERE term = ?");
369 $sth->execute($term);
370 my $ext_dict = C4::Context->preference('TagsExternalDictionary');
371 unless ($sth->rows) {
372 $ext_dict and return (spellcheck($term) ? 0 : 1); # spellcheck returns empty on OK word
375 return $sth->fetchrow;
379 my $term = shift or return;
382 $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?");
383 $sth->execute($term,shift);
385 $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ?");
386 $sth->execute($term);
388 return $sth->fetchrow_hashref;
392 my $operator = shift;
393 defined $operator or return; # have to test defined to allow =0 (kohaadmin)
394 my $ext_dict = C4::Context->preference('TagsExternalDictionary');
397 spellcheck($_) or next;
402 my $aref = get_approval_rows({term=>$_});
403 if ($aref and scalar @$aref) {
404 mod_tag_approval($operator,$_,1);
406 add_tag_approval($_,$operator);
411 # note: there is no "unwhitelist" operation because there is no remove for Ispell.
412 # The blacklist regexps should operate "in front of" the whitelist, so if you approve
413 # a term mistakenly, you can still reverse it. But there is no going back to "neutral".
415 my $operator = shift;
416 defined $operator or return; # have to test defined to allow =0 (kohaadmin)
418 my $aref = get_approval_rows({term=>$_});
419 if ($aref and scalar @$aref) {
420 mod_tag_approval($operator,$_,-1);
422 add_tag_approval($_,$operator,-1);
428 my $operator = shift;
429 defined $operator or return; # have to test defined to allow =0 (kohaadmin)
430 my $query = "INSERT INTO tags_blacklist (regexp,y,z) VALUES (?,?,?)";
431 # my $sth = C4::Context->dbh->prepare($query);
435 my $operator = shift;
436 defined $operator or return; # have to test defined to allow =0 (kohaadmin)
437 my $query = "REMOVE FROM tags_blacklist WHERE blacklist_id = ?";
438 # my $sth = C4::Context->dbh->prepare($query);
439 # $sth->execute($term);
443 sub add_tag_approval { # or disapproval
444 $debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")";
445 my $term = shift or return;
446 my $query = "SELECT * FROM tags_approval WHERE term = ?";
447 my $sth = C4::Context->dbh->prepare($query);
448 $sth->execute($term);
449 ($sth->rows) and return increment_weight_total($term);
450 my $operator = shift || 0;
451 my $approval = (@_ ? shift : 0); # default is unapproved
452 my @exe_args = ($term); # all 3 queries will use this argument
454 $query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,?,NOW())";
455 push @exe_args, $operator, $approval;
456 } elsif ($approval) {
457 $query = "INSERT INTO tags_approval (term,approved,date_approved) VALUES (?,?,NOW())";
458 push @exe_args, $approval;
460 $query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())";
462 $debug and print STDERR "add_tag_approval query: $query\nadd_tag_approval args: (" . join(", ", @exe_args) . ")\n";
463 $sth = C4::Context->dbh->prepare($query);
464 $sth->execute(@exe_args);
468 sub mod_tag_approval {
469 my $operator = shift;
470 defined $operator or return; # have to test defined to allow =0 (kohaadmin)
471 my $term = shift or return;
472 my $approval = (scalar @_ ? shift : 1); # default is to approve
473 my $query = "UPDATE tags_approval SET approved_by=?, approved=?, date_approved=NOW() WHERE term = ?";
474 $debug and print STDERR "mod_tag_approval query: $query\nmod_tag_approval args: ($operator,$approval,$term)\n";
475 my $sth = C4::Context->dbh->prepare($query);
476 $sth->execute($operator,$approval,$term);
480 my $term = shift or return;
481 my $biblionumber = shift or return;
482 my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?";
483 my $sth = C4::Context->dbh->prepare($query);
484 $sth->execute($term,$biblionumber);
485 ($sth->rows) and return increment_weight($term,$biblionumber);
486 $query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)";
487 $debug and print STDERR "add_tag_index query: $query\nadd_tag_index args: ($term,$biblionumber)\n";
488 $sth = C4::Context->dbh->prepare($query);
489 $sth->execute($term,$biblionumber);
493 sub get_tag { # by tag_id
495 my $sth = C4::Context->dbh->prepare(TAG_SELECT . "WHERE tag_id = ?");
496 $sth->execute(shift);
497 return $sth->fetchrow_hashref;
500 sub increment_weights {
501 increment_weight(@_);
502 increment_weight_total(shift);
504 sub decrement_weights {
505 decrement_weight(@_);
506 decrement_weight_total(shift);
508 sub increment_weight_total {
509 _set_weight_total('weight_total+1',shift);
511 sub increment_weight {
512 _set_weight('weight+1',shift,shift);
514 sub decrement_weight_total {
515 _set_weight_total('weight_total-1',shift);
517 sub decrement_weight {
518 _set_weight('weight-1',shift,shift);
520 sub _set_weight_total {
521 my $sth = C4::Context->dbh->prepare("
523 SET weight_total=" . (shift) . "
525 "); # note: CANNOT use "?" for weight_total (see the args above).
526 $sth->execute(shift); # just the term
529 my $dbh = C4::Context->dbh;
530 my $sth = $dbh->prepare("
532 SET weight=" . (shift) . "
539 sub add_tag { # biblionumber,term,[borrowernumber,approvernumber]
540 my $biblionumber = shift or return;
541 my $term = shift or return;
542 my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin
545 ($term) or return; # must be more than whitespace
546 my $rows = get_tag_rows({biblionumber=>$biblionumber, borrowernumber=>$borrowernumber, term=>$term, limit=>1});
547 my $query = "INSERT INTO tags_all
548 (borrowernumber,biblionumber,term,date_created)
549 VALUES (?,?,?,NOW())";
550 $debug and print STDERR "add_tag query: $query\n",
551 "add_tag query args: ($borrowernumber,$biblionumber,$term)\n";
553 $debug and carp "Duplicate tag detected. Tag not added.";
556 # add to tags_all regardless of approaval
557 my $sth = C4::Context->dbh->prepare($query);
558 $sth->execute($borrowernumber,$biblionumber,$term);
561 if (scalar @_) { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved.
562 my $approver = shift;
563 $debug and print STDERR "term '$term' pre-approved by borrower #$approver\n";
564 add_tag_approval($term,$approver,1);
565 add_tag_index($term,$biblionumber,$approver);
566 } elsif (is_approved($term) >= 1) {
567 $debug and print STDERR "term '$term' approved by whitelist\n";
568 add_tag_approval($term,0,1);
569 add_tag_index($term,$biblionumber,1);
571 $debug and print STDERR "term '$term' NOT approved (yet)\n";
572 add_tag_approval($term);
573 add_tag_index($term,$biblionumber);
577 # This takes a set of tags, as returned by C<get_approval_rows> and divides
578 # them up into a number of "strata" based on their weight. This is useful
579 # to display them in a number of different sizes.
582 # ($min, $max) = stratify_tags($strata, $tags);
583 # $stratum: the number of divisions you want
584 # $tags: the tags, as provided by get_approval_rows
585 # $min: the minimum stratum value
586 # $max: the maximum stratum value. This may be the same as $min if there
587 # is only one weight. Beware of divide by zeros.
588 # This will add a field to the tag called "stratum" containing the calculated
591 my ( $strata, $tags ) = @_;
592 return (0,0) if !@$tags;
595 my $w = $_->{weight_total};
596 $min = $w if ( !defined($min) || $min > $w );
597 $max = $w if ( !defined($max) || $max < $w );
600 # normalise min to zero
605 # if min and max are the same, just make it 1
606 my $span = ( $strata - 1 ) / ( $max || 1 );
608 my $w = $_->{weight_total};
609 $_->{stratum} = int( ( $w - $orig_min ) * $span );
611 return ( $min, $max );
617 =head2 add_tag(biblionumber,term[,borrowernumber])
619 =head3 TO DO: Add real perldoc
623 =head2 External Dictionary (Ispell) [Recommended]
625 An external dictionary can be used as a means of "pre-populating" and tracking
626 allowed terms based on the widely available Ispell dictionary. This can be the system
627 dictionary or a personal version, but in order to support whitelisting, it must be
628 editable to the process running Koha.
630 To enable, enter the absolute path to the ispell dictionary in the system
631 preference "TagsExternalDictionary".
633 Using external Ispell is recommended for both ease of use and performance. Note that any
634 language version of Ispell can be installed. It is also possible to modify the dictionary
635 at the command line to affect the desired content.
637 WARNING: The default Ispell dictionary includes (properly spelled) obscenities! Users
638 should build their own wordlist and recompile Ispell based on it. See man ispell for
641 =head2 Table Structure
643 The tables used by tags are:
649 Your first thought may be that this looks a little complicated. It is, but only because
650 it has to be. I'll try to explain.
652 tags_all - This table would be all we really need if we didn't care about moderation or
653 performance or tags disappearing when borrowers are removed. Too bad, we do. Otherwise
654 though, it contains all the relevant info about a given tag:
655 tag_id - unique id number for it
656 borrowernumber - user that entered it
657 biblionumber - book record it is attached to
658 term - tag "term" itself
659 language - perhaps used later to influence weighting
660 date_created - date and time it was created
662 tags_approval - Since we need to provide moderation, this table is used to track it. If no
663 external dictionary is used, this table is the sole reference for approval and rejection.
664 With an external dictionary, it tracks pending terms and past whitelist/blacklist actions.
665 This could be called an "approved terms" table. See above regarding the External Dictionary.
666 term - tag "term" itself
667 approved - Negative, 0 or positive if tag is rejected, pending or approved.
668 date_approved - date of last action
669 approved_by - staffer performing the last action
670 weight_total - total occurrence of term in any biblio by any users
672 tags_index - This table is for performance, because by far the most common operation will
673 be fetching tags for a list of search results. We will have a set of biblios, and we will
674 want ONLY their approved tags and overall weighting. While we could implement a query that
675 would traverse tags_all filtered against tags_approval, the performance implications of
676 trying to calculate that and the "weight" (number of times a tag appears) on the fly are drastic.
677 term - approved term as it appears in tags_approval
678 biblionumber - book record it is attached to
679 weight - number of times tag applied by any user
681 tags_blacklist - A set of regular expression filters. Unsurprisingly, these should be perl-
682 compatible (PCRE) for your version of perl. Since this is a blacklist, a term will be
683 blocked if it matches any of the given patterns. WARNING: do not add blacklist regexps
684 if you do not understand their operation and interaction. It is quite easy to define too
685 simple or too complex a regexp and effectively block all terms. The blacklist operation is
686 fairly resource intensive, since every line of tags_blacklist will need to be read and compared.
687 It is recommended that tags_blacklist be used minimally, and only by an administrator with an
688 understanding of regular expression syntax and performance.
690 So the best way to think about the different tables is that they are each tailored to a certain
691 use. Note that tags_approval and tags_index do not rely on the user's borrower mapping, so
692 the tag population can continue to grow even if a user (along with their corresponding
693 rows in tags_all) is removed.
697 If you want to auto-populate some tags for debugging, do something like this:
699 mysql> select biblionumber from biblio where title LIKE "%Health%";
730 26 rows in set (0.00 sec)
732 Then, take those numbers and type/pipe them into this perl command line:
733 perl -ne 'use C4::Tags qw(get_tags add_tag); use Data::Dumper;chomp; add_tag($_,"health",51,1); print Dumper get_tags({limit=>5,term=>"health",});'
735 Note, the borrowernumber in this example is 51. Use your own or any arbitrary valid borrowernumber.