2 # This file is part of Koha.
4 # Koha is free software; you can redistribute it and/or modify it under the
5 # terms of the GNU General Public License as published by the Free Software
6 # Foundation; either version 2 of the License, or (at your option) any later
9 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
10 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
11 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
13 # You should have received a copy of the GNU General Public License along with
14 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
15 # Suite 330, Boston, MA 02111-1307 USA
25 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
26 use vars qw($ext_dict $select_all @fields);
32 &get_tag &get_tags &get_tag_rows
36 &delete_tag_rows_by_ids
40 $ext_dict = C4::Context->preference('TagsExternalDictionary');
43 import Data::Dumper qw(:DEFAULT);
44 print STDERR __PACKAGE__ . " external dictionary = " . ($ext_dict||'none') . "\n";
47 require Lingua::Ispell;
48 import Lingua::Ispell qw(spellcheck);
53 $ext_dict and $Lingua::Ispell::path = $ext_dict;
54 $debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n";
55 @fields = qw(tag_id borrowernumber biblionumber term language date_created);
56 $select_all = "SELECT " . join(',',@fields) . "\n FROM tags_all\n";
61 my $rows = get_tag_rows({tag_id=>$tag_id}) or return 0;
62 (scalar(@$rows) == 1) or return undef;
63 my $row = shift(@$rows);
64 ($tag_id == $row->{tag_id}) or return 0;
65 my $tags = get_tags({term=>$row->{term}, biblionumber=>$row->{biblionumber}});
66 my $index = shift(@$tags);
67 $debug and print STDERR
68 sprintf "remove_tag: tag_id=>%s, biblionumber=>%s, weight=>%s, weight_total=>%s\n",
69 $row->{tag_id}, $row->{biblionumber}, $index->{weight}, $index->{weight_total};
70 if ($index->{weight} <= 1) {
71 delete_tag_index($row->{term},$row->{biblionumber});
73 decrement_weight($row->{term},$row->{biblionumber});
75 if ($index->{weight_total} <= 1) {
76 delete_tag_approval($row->{term});
78 decrement_weight_total($row->{term});
80 delete_tag_row_by_id($tag_id);
83 sub delete_tag_index ($$) {
85 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_index WHERE term = ? AND biblionumber = ? LIMIT 1");
87 return $sth->rows || 0;
89 sub delete_tag_approval ($) {
91 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_approval WHERE term = ? LIMIT 1");
93 return $sth->rows || 0;
95 sub delete_tag_row_by_id ($) {
97 my $sth = C4::Context->dbh->prepare("DELETE FROM tags_all WHERE tag_id = ? LIMIT 1");
99 return $sth->rows || 0;
101 sub delete_tag_rows_by_ids (@) {
102 (@_) or return undef;
105 $i += delete_tag_row_by_id($_);
107 ($i == scalar(@_)) or
108 warn sprintf "delete_tag_rows_by_ids tried %s tag_ids, only succeeded on $i", scalar(@_);
112 sub get_tag_rows ($) {
113 my $hash = shift || {};
114 my @ok_fields = @fields;
115 push @ok_fields, 'limit'; # push the limit! :)
119 foreach my $key (keys %$hash) {
120 $debug and print STDERR "get_tag_rows arg. '$key' = ", $hash->{$key}, "\n";
121 unless (length $key) {
122 carp "Empty argument key to get_tag_rows: ignoring!";
125 unless (1 == scalar grep {/^ $key $/xi} @ok_fields) {
126 carp "get_tag_rows received unreconized argument key '$key'.";
129 if ($key =~ /^limit$/i) {
130 my $val = $hash->{$key};
131 unless ($val =~ /^\d+$/) {
132 carp "Non-nuerical limit value '$val' ignored!";
135 $limit = " LIMIT $val\n";
137 $wheres .= ($wheres) ? " AND $key = ?\n" : " WHERE $key = ?\n";
138 push @exe_args, $hash->{$key};
141 my $query = $select_all . ($wheres||'') . $limit;
142 $debug and print STDERR "get_tag_rows query:\n $query\n",
143 "get_tag_rows query args: ", join(',', @exe_args), "\n";
144 my $sth = C4::Context->dbh->prepare($query);
146 $sth->execute(@exe_args);
150 return $sth->fetchall_arrayref({});
153 sub get_tags (;$) { # i.e., from tags_index
155 my $hash = shift || {};
156 my @ok_fields = qw(term biblionumber weight limit sort);
161 foreach my $key (keys %$hash) {
162 $debug and print STDERR "get_tags arg. '$key' = ", $hash->{$key}, "\n";
163 unless (length $key) {
164 carp "Empty argument key to get_tags: ignoring!";
167 unless (1 == scalar grep {/^ $key $/xi} @ok_fields) {
168 carp "get_tags received unreconized argument key '$key'.";
171 if ($key =~ /^limit$/i) {
172 my $val = $hash->{$key};
173 unless ($val =~ /^\d+$/) {
174 carp "Non-nuerical limit value '$val' ignored!";
177 $limit = " LIMIT $val\n";
178 } elsif ($key =~ /^sort$/i) {
179 foreach my $by (split /\,/, $hash->{$key}) {
181 $by =~ /^([-+])?(term)/ or
182 $by =~ /^([-+])?(biblionumber)/ or
183 $by =~ /^([-+])?(weight)/
185 carp "get_tags received illegal sort order '$by'";
188 $order .= " ORDER BY $2 " . ($1 eq '-' ? 'DESC' : $1 eq '+' ? 'ASC' : '') . "\n";
193 ($key =~ /^term$/i) and $whereval = 'tags_index.term';
194 $wheres .= ($wheres) ? " AND $whereval = ?\n" : " WHERE $whereval = ?\n";
195 push @exe_args, $hash->{$key};
199 SELECT tags_index.term as term,biblionumber,weight,weight_total
201 LEFT JOIN tags_approval
202 ON tags_index.term = tags_approval.term
203 " . ($wheres||'') . $order . $limit;
204 $debug and print STDERR "get_tags query:\n $query\n",
205 "get_tags query args: ", join(',', @exe_args), "\n";
206 my $sth = C4::Context->dbh->prepare($query);
208 $sth->execute(@exe_args);
212 return $sth->fetchall_arrayref({});
215 sub is_approved ($) {
216 my $term = shift or return undef;
218 return (spellcheck($term) ? 0 : 1);
220 my $sth = C4::Context->dbh->prepare("SELECT approved FROM tags_approval WHERE term = ?");
221 $sth->execute($term);
222 $sth->rows or return undef;
226 sub get_tag_index ($;$) {
227 my $term = shift or return undef;
230 $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?");
231 $sth->execute($term,shift);
233 $sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ?");
234 $sth->execute($term);
236 return $sth->fetchrow_hashref;
239 sub add_tag_approval ($;$) {
240 my $term = shift or return undef;
241 my $query = "SELECT * FROM tags_approval WHERE term = ?";
242 my $sth = C4::Context->dbh->prepare($query);
243 $sth->execute($term);
244 ($sth->rows) and return increment_weight_total($term);
245 my $ok = (@_ ? shift : 0);
247 $query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,1,NOW())";
248 $debug and print STDERR "add_tag_approval query:\n$query\nadd_tag_approval args: ($term,$ok)\n";
249 $sth = C4::Context->dbh->prepare($query);
250 $sth->execute($term,$ok);
252 $query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())";
253 $debug and print STDERR "add_tag_approval query:\n$query\nadd_tag_approval args: ($term)\n";
254 $sth = C4::Context->dbh->prepare($query);
255 $sth->execute($term);
260 sub add_tag_index ($$;$) {
261 my $term = shift or return undef;
262 my $biblionumber = shift or return undef;
263 my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?";
264 my $sth = C4::Context->dbh->prepare($query);
265 $sth->execute($term,$biblionumber);
266 ($sth->rows) and return increment_weight($term,$biblionumber);
267 $query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)";
268 $debug and print "add_tag_index query:\n$query\nadd_tag_index args: ($term,$biblionumber)\n";
269 $sth = C4::Context->dbh->prepare($query);
270 $sth->execute($term,$biblionumber);
274 sub get_tag ($) { # by tag_id
275 (@_) or return undef;
276 my $sth = C4::Context->dbh->prepare("$select_all WHERE tag_id = ?");
277 $sth->execute(shift);
278 return $sth->fetchrow_hashref;
281 sub rectify_weights (;$) {
282 my $dbh = C4::Context->dbh;
285 SELECT term,biblionumber,count(*) as count
288 (@_) and $query .= " WHERE term =? ";
289 $query .= " GROUP BY term,biblionumber ";
290 $sth = $dbh->prepare($query);
292 $sth->execute(shift);
296 my $results = $sth->fetchall_arrayref({}) or return undef;
298 foreach (@$results) {
299 _set_weight($_->{count},$_->{term},$_->{biblionumber});
300 $tally{$_->{term}} += $_->{count};
302 foreach (keys %tally) {
303 _set_weight_total($tally{$_},$_);
305 return ($results,\%tally);
308 sub increment_weights ($$) {
309 increment_weight(@_);
310 increment_weight_total(shift);
312 sub decrement_weights ($$) {
313 decrement_weight(@_);
314 derement_weight_total(shift);
316 sub increment_weight_total ($) {
317 _set_weight_total('weight_total+1',shift);
319 sub increment_weight ($$) {
320 _set_weight('weight+1',shift,shift);
322 sub decrement_weight_total ($) {
323 _set_weight_total('weight_total-1',shift);
325 sub decrement_weight ($$) {
326 _set_weight('weight-1',shift,shift);
328 sub _set_weight_total ($$) {
329 my $sth = C4::Context->dbh->prepare("
331 SET weight_total=" . (shift) . "
334 $sth->execute(shift); # just the term
336 sub _set_weight ($$$) {
337 my $dbh = C4::Context->dbh;
338 my $sth = $dbh->prepare("
340 SET weight=" . (shift) . "
347 sub add_tag ($$;$$) { # biblionumber,term,[borrowernumber,approvernumber]
348 my $biblionumber = shift or return undef;
349 my $term = shift or return undef;
350 my $borrowernumber = (@_) ? shift : 0; # the user, default to kohaadmin
352 # first, add to tags regardless of approaval
353 my $query = "INSERT INTO tags_all
354 (borrowernumber,biblionumber,term,date_created)
355 VALUES (?,?,?,NOW())";
356 $debug and print STDERR "add_tag query:\n $query\n",
357 "add_tag query args: ($borrowernumber,$biblionumber,$term)\n";
358 my $sth = C4::Context->dbh->prepare($query);
359 $sth->execute($borrowernumber,$biblionumber,$term);
362 if (@_) { # if an arg remains, it is the borrowernumber of the approver: tag is pre-approved.
363 my $approver = shift;
364 add_tag_approval($term,$approver);
365 add_tag_index($term,$biblionumber,$approver);
366 } elsif (is_approved($term)) {
367 add_tag_approval($term,1);
368 add_tag_index($term,$biblionumber,1);
370 add_tag_approval($term);
371 add_tag_index($term,$biblionumber);
378 =head1 C4::Tags.pm - Support for user tagging of biblios.
380 More verose debugging messages are sent in the presence of non-zero $ENV{"DEBUG"}.
382 =head2 add_tag(biblionumber,term[,borrowernumber])
384 =head3 TO DO: Add real perldoc
388 If you want to auto-populate some tags for debugging, do something like this:
390 mysql> select biblionumber from biblio where title LIKE "%Health%";
421 26 rows in set (0.00 sec)
423 Then, take those numbers and type them into this perl command line:
424 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",});'