3 # Copyright 2009 Biblibre SARL
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 use List
::MoreUtils
qw(first_value any);
25 use C4
::Dates
qw(format_date_in_iso);
28 use vars
qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS);
31 # set the version for version checking
42 %EXPORT_TAGS = ( all
=>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)]
48 C4::SQLHelper - Perl Module containing convenience functions for SQL Handling
56 This module contains routines for adding, modifying and Searching Data in MysqlDB
62 $hashref = &SearchInTable($tablename,$data, $orderby, $limit,
63 $columns_out, $filtercolumns, $searchtype);
66 $tablename Name of the table (string)
71 - data_hashref : will be considered as an AND of all the data searched
73 - data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements
75 $orderby is an arrayref of hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
77 $limit is an array ref on 2 values in order to limit results to MIN..MAX
79 $columns_out is an array ref on field names is used to limit results on those fields (* by default)
81 $filtercolums is an array ref on field names : is used to limit expansion of research for strings
83 $searchtype is string Can be "start_with" or "exact"
85 This query builder is very limited, it should be replaced with DBIx::Class
87 Meanwhile adding support for special key '' in case of a data_hashref to
88 support filters of type
90 ( f1 = a OR f2 = a ) AND fx = b AND fy = c
92 Call for the query above is:
94 SearchInTable($tablename, {'' => a, fx => b, fy => c}, $orderby, $limit,
95 $columns_out, [f1, f2], 'exact');
97 NOTE: Current implementation may remove parts of the iinput hashrefs. If that is a problem
98 a copy needs to be created in _filter_fields() below
103 my ($tablename,$filters,$orderby, $limit, $columns_out, $filter_columns,$searchtype) = @_;
104 $searchtype||="exact";
105 my $dbh = C4
::Context
->dbh;
106 $columns_out||=["*"];
107 my $sql = do { local $"=', ';
108 qq{ SELECT @$columns_out from $tablename}
112 my ($keys,$values)=_filter_fields($tablename,$filters,$searchtype,$filter_columns);
114 my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
116 $sql.= do { local $"=') OR (';
117 qq{ WHERE
(@criteria) }
122 #Order by desc by default
124 foreach my $order ( ref($orderby) ? @
$orderby : $orderby ){
126 push @orders,map{ "$_".($order->{$_}?
" DESC " : "") } keys %$order;
131 $sql.= do { local $"=', ';
132 qq{ ORDER BY @orders}
136 $sql.=qq{ LIMIT }.join(",",@$limit);
139 $debug && $values && warn $sql," ",join(",",@$values);
140 $sth = $dbh->prepare_cached($sql);
141 eval{$sth->execute(@$values)};
142 warn $@ if ($@ && $debug);
143 my $results = $sth->fetchall_arrayref( {} );
149 $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
151 Insert Data in table and returns the id of the row inserted
156 my ($tablename,$data,$withprimarykeys) = @_;
157 my $dbh = C4::Context->dbh;
158 my ($keys,$values)=_filter_hash($tablename,$data,($withprimarykeys?"exact
":0));
159 my $query = qq{ INSERT INTO $tablename SET }.join(", ",@$keys);
161 $debug && warn $query, join(",",@$values);
162 my $sth = $dbh->prepare_cached($query);
163 eval{$sth->execute(@$values)};
164 warn $@ if ($@ && $debug);
166 return $dbh->last_insert_id(undef, undef, $tablename, undef);
171 $status = &UpdateInTable($tablename,$data_hashref);
173 Update Data in table and returns the status of the operation
178 my ($tablename,$data) = @_;
179 my @field_ids=GetPrimaryKeys($tablename);
180 my @ids=@$data{@field_ids};
181 my $dbh = C4::Context->dbh;
182 my ($keys,$values)=_filter_hash($tablename,$data,0);
183 return unless ($keys);
185 qq{ UPDATE $tablename
186 SET }.join(",",@$keys).qq{
187 WHERE }.join (" AND
",map{ "$_=?
" }@field_ids);
188 $debug && warn $query, join(",",@$values,@ids);
190 my $sth = $dbh->prepare_cached($query);
192 eval{$result=$sth->execute(@$values,@ids)};
193 warn $@ if ($@ && $debug);
199 $status = &DeleteInTable($tablename,$data_hashref);
201 Delete Data in table and returns the status of the operation
206 my ($tablename,$data) = @_;
207 my $dbh = C4::Context->dbh;
208 my ($keys,$values)=_filter_fields($tablename,$data,1);
210 my $query = do { local $"=') AND (';
211 qq{ DELETE FROM
$tablename WHERE
(@
$keys)};
213 $debug && warn $query, join(",",@
$values);
214 my $sth = $dbh->prepare_cached($query);
216 eval{$result=$sth->execute(@
$values)};
217 warn $@
if ($@
&& $debug);
222 =head2 GetPrimaryKeys
224 @primarykeys = &GetPrimaryKeys($tablename)
226 Get the Primary Key field names of the table
230 sub GetPrimaryKeys
($) {
232 my $hash_columns=_get_columns
($tablename);
233 return grep { $hash_columns->{$_}->{'Key'} =~/PRI/i} keys %$hash_columns;
238 _get_columns($tablename)
241 Returns a hashref of all the fieldnames of the table
249 sub _get_columns
($) {
251 my $dbh=C4
::Context
->dbh;
252 my $sth=$dbh->prepare_cached(qq{SHOW COLUMNS FROM
$tablename });
254 my $columns= $sth->fetchall_hashref(qw(Field));
257 =head2 _filter_columns
259 _filter_columns($tablename,$research, $filtercolumns)
263 - indicator on purpose whether all fields should be returned or only non Primary keys
264 - array_ref to columns to limit to
266 Returns an array of all the fieldnames of the table
267 If it is not for research purpose, filter primary keys
271 sub _filter_columns
($$;$) {
272 my ($tablename,$research, $filtercolumns)=@_;
274 return (@
$filtercolumns);
277 my $columns=_get_columns
($tablename);
279 return keys %$columns;
282 return grep {my $column=$_; any
{$_ ne $column }GetPrimaryKeys
($tablename) } keys %$columns;
286 =head2 _filter_fields
292 - a string or a hashref (containing, fieldnames and datatofilter) or an arrayref to one of those elements
293 - an indicator of operation whether it is a wide research or a narrow one
294 - an array ref to columns to restrict string filter to.
296 Returns a ref of key array to use in SQL functions
297 and a ref to value array
302 my ($tablename,$filter_input,$searchtype,$filtercolumns)=@_;
305 if (ref($filter_input) eq "HASH"){
307 if (my $special = delete $filter_input->{''}) { # XXX destroyes '' key
308 ($keys, $values) = _filter_fields
($tablename,$special, $searchtype,$filtercolumns);
310 my ($hkeys, $hvalues) = _filter_hash
($tablename,$filter_input, $searchtype);
312 push @
$keys, @
$hkeys;
313 push @
$values, @
$hvalues;
316 my $stringkey="(".join (") AND (",@
$keys).")";
317 return [$stringkey],$values;
322 } elsif (ref($filter_input) eq "ARRAY"){
323 foreach my $element_data (@
$filter_input){
324 my ($localkeys,$localvalues)=_filter_fields
($tablename,$element_data,$searchtype,$filtercolumns);
326 @
$localkeys=grep{defined($_) && $_ !~/^\W*$/}@
$localkeys;
332 push @values, @$localvalues;
337 $debug && warn "filterstring
: $filter_input";
338 my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns);
340 my $stringkey="(".join (") AND
(",@$keys).")";
341 return [$stringkey],$values;
348 return (\@keys,\@values);
352 my ($tablename,$filter_input, $searchtype)=@_;
354 my $columns= _get_columns($tablename);
355 my @columns_filtered= _filter_columns($tablename,$searchtype);
357 #Filter Primary Keys of table
358 my $elements=join "|",@columns_filtered;
359 foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
360 ## supposed to be a hash of simple values, hashes of arrays could be implemented
361 $filter_input->{$field}=format_date_in_iso($filter_input->{$field})
362 if $columns->{$field}{Type}=~/date/ &&
363 $filter_input->{$field} !~C4::Dates->regexp("iso
");
364 my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
366 push @values, @$localvalues;
367 push @keys, @$tmpkeys;
371 return (\@keys,\@values);
379 my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
380 return () unless($filter_input);
381 my @operands=split /\s+/,$filter_input;
383 # An act of desperation
384 $searchtype = 'contain' if @operands > 1 && $searchtype =~ /start_with/o;
386 my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
387 my $columns= _get_columns($tablename);
389 foreach my $operand (@operands){
391 foreach my $field (@columns_filtered){
392 my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
394 push @values,@$localvalues;
395 push @localkeys,@$tmpkeys;
398 my $sql= join (' OR ', @localkeys);
403 return (\@keys,\@values);
409 sub _Process_Operands{
410 my ($operand, $field, $searchtype,$columns)=@_;
415 $operand = [$operand] unless ref $operand eq 'ARRAY';
416 foreach (@$operand) {
417 push @tmpkeys, " $field = ?
";
420 #By default, exact search
421 if (!$searchtype ||$searchtype eq "exact
"){
422 return \@tmpkeys,\@values;
424 my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
425 if ($field=~/(?<!zip)code|(?<!card)number/ && $searchtype ne "exact
"){
426 push @tmpkeys,(" $field= '' ","$field IS NULL
");
428 if ($columns->{$col_field}->{Type}=~/varchar|text/i){
429 my @localvaluesextended;
430 if ($searchtype eq "contain
"){
431 foreach (@$operand) {
432 push @tmpkeys,(" $field LIKE ?
");
433 push @localvaluesextended,("\
%$_\
%") ;
436 if ($searchtype eq "field_start_with
"){
437 foreach (@$operand) {
438 push @tmpkeys,("$field LIKE ?
");
439 push @localvaluesextended, ("$_\
%") ;
442 if ($searchtype eq "start_with
"){
443 foreach (@$operand) {
444 push @tmpkeys,("$field LIKE ?
","$field LIKE ?
");
445 push @localvaluesextended, ("$_\
%", " $_\
%") ;
448 push @values,@localvaluesextended;
450 push @localkeys,qq{ (}.join(" OR
",@tmpkeys).qq{) };
451 return (\@localkeys,\@values);