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)]
51 C4::SQLHelper - Perl Module containing convenience functions for SQL Handling
59 This module contains routines for adding, modifying and Searching Data in MysqlDB
72 $hashref = &SearchInTable($tablename,$data, $orderby, $limit, $columns_out, $filtercolumns, $searchtype);
76 $tablename Name of the table (string)
81 - data_hashref : will be considered as an AND of all the data searched
83 - data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements
85 $orderby is an arrayref of hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
87 $limit is an array ref on 2 values in order to limit results to MIN..MAX
89 $columns_out is an array ref on field names is used to limit results on those fields (* by default)
91 $filtercolums is an array ref on field names : is used to limit expansion of research for strings
93 $searchtype is string Can be "start_with" or "exact"
98 my ($tablename,$filters,$orderby, $limit, $columns_out, $filter_columns,$searchtype) = @_;
99 $searchtype||="exact";
100 my $dbh = C4::Context->dbh;
101 $columns_out||=["*"];
102 my $sql = do { local $"=', ';
103 qq{ SELECT @$columns_out from $tablename}
107 my ($keys,$values)=_filter_fields($tablename,$filters,$searchtype,$filter_columns);
109 my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
111 $sql.= do { local $"=') AND (';
112 qq{ WHERE (@criteria) }
117 #Order by desc by default
119 foreach my $order (@$orderby){
120 push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order;
122 $sql.= do { local $"=', ';
123 qq{ ORDER BY @orders}
127 $sql.=qq{ LIMIT }.join(",",@$limit);
130 $debug && $values && warn $sql," ",join(",",@$values);
131 $sth = $dbh->prepare_cached($sql);
132 eval{$sth->execute(@$values)};
133 warn $@ if ($@ && $debug);
134 my $results = $sth->fetchall_arrayref( {} );
142 $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
147 and returns the id of the row inserted
151 my ($tablename,$data,$withprimarykeys) = @_;
152 my $dbh = C4::Context->dbh;
153 my ($keys,$values)=_filter_hash($tablename,$data,($withprimarykeys?"exact":0));
154 my $query = qq{ INSERT INTO $tablename SET }.join(", ",@$keys);
156 $debug && warn $query, join(",",@$values);
157 my $sth = $dbh->prepare_cached($query);
158 eval{$sth->execute(@$values)};
159 warn $@ if ($@ && $debug);
161 return $dbh->last_insert_id(undef, undef, $tablename, undef);
168 $status = &UpdateInTable($tablename,$data_hashref);
173 and returns the status of the operation
177 my ($tablename,$data) = @_;
178 my @field_ids=GetPrimaryKeys($tablename);
179 my @ids=@$data{@field_ids};
180 my $dbh = C4::Context->dbh;
181 my ($keys,$values)=_filter_hash($tablename,$data,0);
183 qq{ UPDATE $tablename
184 SET }.join(",",@$keys).qq{
185 WHERE }.join (" AND ",map{ "$_=?" }@field_ids);
186 $debug && warn $query, join(",",@$values,@ids);
188 my $sth = $dbh->prepare_cached($query);
190 eval{$result=$sth->execute(@$values,@ids)};
191 warn $@ if ($@ && $debug);
199 $status = &DeleteInTable($tablename,$data_hashref);
204 and returns the status of the operation
208 my ($tablename,$data) = @_;
209 my $dbh = C4::Context->dbh;
210 my ($keys,$values)=_filter_fields($tablename,$data,1);
212 my $query = do { local $"=') AND (';
213 qq{ DELETE FROM $tablename WHERE (@$keys)};
215 $debug && warn $query, join(",",@$values);
216 my $sth = $dbh->prepare_cached($query);
218 eval{$result=$sth->execute(@$values)};
219 warn $@ if ($@ && $debug);
224 =head2 GetPrimaryKeys
228 @primarykeys = &GetPrimaryKeys($tablename)
232 Get the Primary Key field names of the table
235 sub GetPrimaryKeys($) {
237 my $hash_columns=_get_columns($tablename);
238 return grep { $hash_columns->{$_}->{'Key'} =~/PRI/i} keys %$hash_columns;
245 _get_columns($tablename)
250 Returns a hashref of all the fieldnames of the table
258 sub _get_columns($) {
260 my $dbh=C4::Context->dbh;
261 my $sth=$dbh->prepare_cached(qq{SHOW COLUMNS FROM $tablename });
263 my $columns= $sth->fetchall_hashref(qw(Field));
266 =head2 _filter_columns
270 _filter_columns($tablename,$research, $filtercolumns)
276 - indicator on purpose whether all fields should be returned or only non Primary keys
277 - array_ref to columns to limit to
279 Returns an array of all the fieldnames of the table
280 If it is not for research purpose, filter primary keys
284 sub _filter_columns ($$;$) {
285 my ($tablename,$research, $filtercolumns)=@_;
287 return (@$filtercolumns);
290 my $columns=_get_columns($tablename);
292 return keys %$columns;
295 return grep {my $column=$_; any {$_ ne $column }GetPrimaryKeys($tablename) } keys %$columns;
299 =head2 _filter_fields
309 - a string or a hashref (containing, fieldnames and datatofilter) or an arrayref to one of those elements
310 - an indicator of operation whether it is a wide research or a narrow one
311 - an array ref to columns to restrict string filter to.
313 Returns a ref of key array to use in SQL functions
314 and a ref to value array
319 my ($tablename,$filter_input,$searchtype,$filtercolumns)=@_;
322 if (ref($filter_input) eq "HASH"){
323 my ($keys, $values) = _filter_hash($tablename,$filter_input, $searchtype);
325 my $stringkey="(".join (") AND (",@$keys).")";
326 return [$stringkey],$values;
331 } elsif (ref($filter_input) eq "ARRAY"){
332 foreach my $element_data (@$filter_input){
333 my ($localkeys,$localvalues)=_filter_fields($tablename,$element_data,$searchtype,$filtercolumns);
335 @$localkeys=grep{defined($_) && $_ !~/^\W*$/}@$localkeys;
341 push @values, @$localvalues;
346 $debug && warn "filterstring : $filter_input";
347 my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns);
349 my $stringkey="(".join (") AND (",@$keys).")";
350 return [$stringkey],$values;
357 return (\@keys,\@values);
361 my ($tablename,$filter_input, $searchtype)=@_;
363 my $columns= _get_columns($tablename);
364 my @columns_filtered= _filter_columns($tablename,$searchtype);
366 #Filter Primary Keys of table
367 my $elements=join "|",@columns_filtered;
368 foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
369 ## supposed to be a hash of simple values, hashes of arrays could be implemented
370 $filter_input->{$field}=format_date_in_iso($filter_input->{$field}) if ($columns->{$field}{Type}=~/date/ && $filter_input->{$field} !~C4::Dates->regexp("iso"));
371 my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
373 push @values, @$localvalues;
374 push @keys, @$tmpkeys;
378 return (\@keys,\@values);
386 my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
387 return () unless($filter_input);
388 my @operands=split / /,$filter_input;
389 my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
390 my $columns= _get_columns($tablename);
392 foreach my $operand (@operands){
394 foreach my $field (@columns_filtered){
395 my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
397 push @values,@$localvalues;
398 push @localkeys,@$tmpkeys;
401 my $sql= join (' OR ', @localkeys);
406 return (\@keys,\@values);
412 sub _Process_Operands{
413 my ($operand, $field, $searchtype,$columns)=@_;
417 push @tmpkeys, " $field = ? ";
418 push @values, $operand;
419 #By default, exact search
420 if (!$searchtype ||$searchtype eq "exact"){
421 return \@tmpkeys,\@values;
423 my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
424 if ($field=~/(?<!zip)code|(?<!card)number/ && $searchtype ne "exact"){
425 push @tmpkeys,(" $field= '' ","$field IS NULL");
427 if ($columns->{$col_field}->{Type}=~/varchar|text/i){
428 my @localvaluesextended;
429 if ($searchtype eq "contain"){
430 push @tmpkeys,(" $field LIKE ? ");
431 push @localvaluesextended,("\%$operand\%") ;
433 if ($searchtype eq "field_start_with"){
434 push @tmpkeys,("$field LIKE ?");
435 push @localvaluesextended, ("$operand\%") ;
437 if ($searchtype eq "start_with"){
438 push @tmpkeys,("$field LIKE ?","$field LIKE ?");
439 push @localvaluesextended, ("$operand\%", " $operand\%") ;
441 push @values,@localvaluesextended;
443 push @localkeys,qq{ (}.join(" OR ",@tmpkeys).qq{) };
444 return (\@localkeys,\@values);