C4/SQLHelper.pm

   1 package C4::SQLHelper;
   2
   3 # Copyright 2009 Biblibre SARL
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  16 # You should have received a copy of the GNU General Public License along with
  17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  18 # Suite 330, Boston, MA  02111-1307 USA
  19
  20
  21 use List::MoreUtils qw(first_value);
  22 use C4::Context;
  23 use C4::Dates qw(format_date_in_iso);
  24 use C4::Debug;
  25 use strict;
  26 use warnings;
  27 require Exporter;
  28 use vars qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS);
  29
  30 BEGIN {
  31         # set the version for version checking
  32         $VERSION = 0.5;
  33         require Exporter;
  34         @ISA    = qw(Exporter);
  35 @EXPORT_OK=qw(
  36         InsertInTable
  37         SearchInTable
  38         UpdateInTable
  39         GetPrimaryKey
  40 );
  41         %EXPORT_TAGS = ( all =>[qw( InsertInTable SearchInTable UpdateInTable GetPrimaryKey)]
  42                                 );
  43 }
  44
  45 my $tablename;
  46 my $hash;
  47
  48 =head1 NAME
  49
  50 C4::SQLHelper - Perl Module containing convenience functions for SQL Handling
  51
  52 =head1 SYNOPSIS
  53
  54 use C4::SQLHelper;
  55
  56 =head1 DESCRIPTION
  57
  58 This module contains routines for adding, modifying and Searching Data in MysqlDB
  59
  60 =head1 FUNCTIONS
  61
  62 =over 2
  63
  64 =back
  65
  66
  67 =head2 SearchInTable
  68
  69 =over 4
  70
  71   $hashref = &SearchInTable($tablename,$data, $orderby);
  72
  73 =back
  74
  75 $data may contain
  76         - string
  77         - data_hashref : will be considered as an AND of all the data searched
  78         - data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements
  79
  80 $orderby is a hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
  81
  82 =cut
  83
  84 sub SearchInTable{
  85     my ($tablename,$filters,$orderby) = @_;
  86     my $dbh      = C4::Context->dbh;
  87     my $sql      = "SELECT * from $tablename";
  88     my $row;
  89     my $sth;
  90     my ($keys,$values)=_filter_fields($filters,$tablename, "search");
  91     if ($filters) {
  92         $sql.= do { local $"=' AND ';
  93                 qq{ WHERE @$keys }
  94                };
  95     }
  96     if ($orderby){
  97         my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby;
  98         $sql.= do { local $"=', ';
  99                 qq{ ORDER BY @orders}
 100                };
 101     }
 102
 103     $debug && warn $sql," ",join(",",@$values);
 104     $sth = $dbh->prepare($sql);
 105     $sth->execute(@$values);
 106     my $results = $sth->fetchall_arrayref( {} );
 107     return $results;
 108 }
 109
 110 =head2 InsertInTable
 111
 112 =over 4
 113
 114   $data_id_in_table = &InsertInTable($tablename,$data_hashref);
 115
 116 =back
 117
 118   Insert Data in table
 119   and returns the id of the row inserted
 120 =cut
 121
 122 sub InsertInTable{
 123     my ($tablename,$data) = @_;
 124     my $dbh      = C4::Context->dbh;
 125     my ($keys,$values)=_filter_fields($data,$tablename);
 126
 127     my $query = do { local $"=',';
 128     qq{
 129             INSERT $tablename
 130             SET  @$keys
 131         };
 132     };
 133
 134         $debug && warn $query, join(",",@$values);
 135     my $sth = $dbh->prepare($query);
 136     $sth->execute( @$values);
 137
 138         return $dbh->last_insert_id(undef, undef, $tablename, undef);
 139 }
 140
 141 =head2 UpdateInTable
 142
 143 =over 4
 144
 145   $status = &UpdateInTable($tablename,$data_hashref);
 146
 147 =back
 148
 149   Update Data in table
 150   and returns the status of the operation
 151 =cut
 152
 153 sub UpdateInTable{
 154     my ($tablename,$data) = @_;
 155         my $field_id=GetPrimaryKey($tablename);
 156     my $id=$$data{$field_id};
 157     my $dbh      = C4::Context->dbh;
 158     my ($keys,$values)=_filter_fields($data,$tablename);
 159
 160     my $query = do { local $"=',';
 161     qq{
 162             UPDATE $tablename
 163             SET  @$keys
 164             WHERE  $field_id=?
 165         };
 166     };
 167         $debug && warn $query, join(",",@$values,$id);
 168
 169     my $sth = $dbh->prepare($query);
 170     return $sth->execute( @$values,$id);
 171
 172 }
 173
 174 =head2 GetPrimaryKey
 175
 176 =over 4
 177
 178   $primarykeyname = &GetPrimaryKey($tablename)
 179
 180 =back
 181
 182         Get the Primary Key field name of the table
 183 =cut
 184
 185 sub GetPrimaryKey($) {
 186         my $tablename=shift;
 187         my $hash_columns=_columns($tablename);
 188         return  first_value { $$hash_columns{$_}{'Key'} =~/PRI/}  keys %$hash_columns;
 189 }
 190
 191 =head2 _get_columns
 192
 193 =over 4
 194
 195 _get_columns($tablename)
 196
 197 =back
 198
 199 Given a tablename
 200 Returns a hashref of all the fieldnames of the table
 201 With
 202         Key
 203         Type
 204         Default
 205
 206 =cut
 207
 208 sub _columns($) {
 209         my $tablename=shift;
 210         $debug && warn $tablename;
 211         my $dbh=C4::Context->dbh;
 212         my $sth=$dbh->prepare(qq{SHOW COLUMNS FROM $tablename });
 213         $sth->execute;
 214     return $sth->fetchall_hashref(qw(Field));
 215 }
 216
 217 =head2 _filter_fields
 218
 219 =over 4
 220
 221 _filter_fields
 222
 223 =back
 224
 225 Given
 226         - a tablename
 227         - a hashref of data
 228         - an indicator on operation
 229
 230 Returns a ref of key array to use in SQL functions
 231 and a ref to value array
 232
 233 =cut
 234
 235 sub _filter_fields{
 236         my ($data_to_filter,$tablename,$research)=@_;
 237         warn "$tablename";
 238     my @keys;
 239         my @values;
 240         my $columns= _columns($tablename);
 241         #Filter Primary Keys of table
 242     my $elements=join "|",grep {$$columns{$_}{'Key'} ne "PRI"} keys %$columns;
 243         if (ref($data_to_filter) eq "HASH"){
 244                 foreach my $field (grep {/\b($elements)\b/} keys %$data_to_filter){
 245                         ## supposed to be a hash of simple values, hashes of arrays could be implemented
 246                         $$data_to_filter{$field}=format_date_in_iso($$data_to_filter{$field}) if ($$columns{$field}{Type}=~/date/ && $$data_to_filter{$field} !~C4::Dates->regexp("iso"));
 247                         my $strkeys= " $field = ? ";
 248                         if ($field=~/code/ && $research){
 249                                 $strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
 250                         }
 251                         push @values, $$data_to_filter{$field};
 252                         push @keys, $strkeys;
 253                 }
 254         } elsif (ref($data_to_filter) eq "ARRAY"){
 255                 foreach my $element (@$data_to_filter){
 256                         my (@localkeys,@localvalues)=_filter_fields($element);
 257                         push @keys, join(' AND ',@localkeys);
 258                         push @values, @localvalues;
 259                 }
 260         }
 261         else{
 262                         my @operands=split / /,$data_to_filter;
 263                         foreach my $operand (@operands){
 264                                 my @localvalues=($operand,"\%$operand\%") ;
 265                                 foreach my $field (keys %$columns){
 266                                         my $strkeys= " ( $field = ? OR $field LIKE ? )";
 267                                         if ($field=~/code/){
 268                                                 $strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
 269                                         }
 270                                         push @values, @localvalues;
 271                                         push @keys, $strkeys;
 272                                 }
 273                         }
 274         }
 275
 276         return (\@keys,\@values);
 277 }
 278
 279 1;
 280