t/db_dependent/SQLHelper.t

   1 #!/usr/bin/perl
   2 #
   3 # This Koha test module is a stub!
   4 # Add more tests here!!!
   5
   6 use strict;
   7 use warnings;
   8 use YAML;
   9
  10 use C4::Debug;
  11 use C4::SQLHelper qw(:all);
  12
  13 use Test::More tests => 19;
  14
  15 #1
  16 BEGIN {
  17     use_ok('C4::SQLHelper');
  18 }
  19 use C4::Category;
  20 use C4::Branch;
  21 my @categories=C4::Category->all;
  22 my $insert;
  23 ok(($insert=InsertInTable("branches",{branchcode=>"ZZZZ",branchname=>"Brancheinconnue",city=>" ",zipcode=>" "},1))==0,"AddBranch (Insert In Table with primary key defined)");
  24 my $branches=C4::Branch->GetBranches;
  25 my @branchcodes=keys %$branches;
  26 my ($borrid, $borrtmp);
  27 ok($borrid=InsertInTable("borrowers",{firstname=>"Jean",surname=>"Valjean",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]}),"Insert In Table");
  28 $borrtmp=InsertInTable("borrowers",{firstname=>"Jean",surname=>"cocteau",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]});
  29 ok(my $status=UpdateInTable("borrowers",{borrowernumber=>$borrid,firstname=>"Jean",surname=>"Valjean",city=>"Dampierre",zipcode=>" ",email=>"email", branchcode=>$branchcodes[1]}),"Update In Table");
  30 my $borrowers=SearchInTable("borrowers");
  31 #4
  32 ok(@$borrowers>0, "Search In Table All values");
  33 $borrowers=SearchInTable("borrowers",{borrowernumber=>$borrid});
  34 #5
  35 ok(@$borrowers==1, "Search In Table by primary key on table");
  36 $borrowers=SearchInTable("borrowers",{firstname=>"Jean"});
  37 #6
  38 ok(@$borrowers>0, "Search In Table hashref");
  39 $borrowers=SearchInTable("borrowers",{firstname=>"Jean"},[{firstname=>1},{borrowernumber=>1}],undef, [qw(borrowernumber)]);
  40 #7
  41 ok(($$borrowers[0]{borrowernumber} + 0) > ($$borrowers[1]{borrowernumber} + 0), "Search In Table Order");
  42 $borrowers=SearchInTable("borrowers",{firstname=>"Jean"},[{surname=>0},{firstname=>1}], undef, [qw(firstname surname)]);
  43 #8
  44 ok(uc($$borrowers[0]{surname}) lt uc($$borrowers[1]{surname}), "Search In Table Order");
  45 $borrowers=SearchInTable("borrowers","Jean");
  46 #9
  47 ok(@$borrowers>0, "Search In Table string");
  48 #10
  49 #FIXME : When searching on All the fields of the table, seems to return Junk
  50 eval{$borrowers=SearchInTable("borrowers","Jean Valjean",undef,undef,undef,[qw(firstname surname borrowernumber cardnumber)],"start_with")};
  51 #eval{$borrowers=SearchInTable("borrowers","Jean Valjean",undef,undef,undef,undef,"start_with")};
  52 # This would not be much efficient because of "numbers" special treatment : We return stuff if empty or '' as soon as search is NOT exact
  53 # This behaviour is implemented because of branchcode and numbers can be null
  54 $debug && warn Dump(@$borrowers);
  55 ok(scalar(@$borrowers)==1 && !($@), "Search In Table does an implicit AND of all the words in strings");
  56 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}]);
  57 #11
  58 ok(@$borrowers>0, "Search In Table arrayref");
  59 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)]);
  60 #12
  61 ok(keys %{$$borrowers[0]} ==1, "Search In Table columns out limit");
  62 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname surname title)]);
  63 #13
  64 ok(@$borrowers>0, "Search In Table columns out limit to borrowernumber AND filter firstname surname title");
  65 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)]);
  66 #14
  67 ok(@$borrowers==0, "Search In Table columns filter firstname title limit Valjean not in other fields than surname ");
  68 $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(surname)],"start_with");
  69 #15
  70 ok(@$borrowers>0, "Search In Table columns filter surname  Val on a wide search found ");
  71 $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(surname)],"exact");
  72 #16
  73 ok(@$borrowers==0, "Search In Table columns filter surname  Val in exact search not found ");
  74 $borrowers=eval{SearchInTable("borrowers",["Val",{member=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)],"exact")};
  75 #17
  76 ok(@$borrowers==0 && !($@), "Search In Table fails gracefully when no correct field passed in hash");
  77 #18
  78 $borrowers=eval{SearchInTable("borrowers",["Jea"],undef,undef,undef,[qw(firstname surname borrowernumber)],"start_with")};
  79 ok(@$borrowers>0 && !($@), "Search on simple value in firstname");
  80
  81 $status=DeleteInTable("borrowers",{borrowernumber=>$borrid});
  82 #19
  83 ok($status>0 && !($@), "DeleteInTable OK");
  84 $status=DeleteInTable("borrowers",{borrowernumber=>$borrtmp});