require Exporter;
use vars qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS);
+eval {
+ my $servers = C4::Context->config('memcached_servers');
+ if ($servers) {
+ require Memoize::Memcached;
+ import Memoize::Memcached qw(memoize_memcached);
+
+ my $memcached = {
+ servers => [$servers],
+ key_prefix => C4::Context->config('memcached_namespace') || 'koha',
+ expire_time => 600
+ }; # cache for 10 mins
+
+ memoize_memcached( '_get_columns', memcached => $memcached );
+ memoize_memcached( 'GetPrimaryKeys', memcached => $memcached );
+ }
+};
+
BEGIN {
# set the version for version checking
- $VERSION = 0.5;
+ $VERSION = 3.07.00.049;
require Exporter;
@ISA = qw(Exporter);
@EXPORT_OK=qw(
SearchInTable
UpdateInTable
GetPrimaryKeys
+ clear_columns_cache
);
%EXPORT_TAGS = ( all =>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)]
);
}
my $tablename;
-my $hash;
+my $hashref;
=head1 NAME
=head1 FUNCTIONS
-=over 2
-
-=back
-
-
=head2 SearchInTable
-=over 4
-
- $hashref = &SearchInTable($tablename,$data, $orderby, $limit, $columns_out, $filtercolumns, $searchtype);
+ $hashref = &SearchInTable($tablename,$data, $orderby, $limit,
+ $columns_out, $filtercolumns, $searchtype);
-=back
$tablename Name of the table (string)
$searchtype is string Can be "start_with" or "exact"
+This query builder is very limited, it should be replaced with DBIx::Class
+or similar very soon
+Meanwhile adding support for special key '' in case of a data_hashref to
+support filters of type
+
+ ( f1 = a OR f2 = a ) AND fx = b AND fy = c
+
+Call for the query above is:
+
+ SearchInTable($tablename, {'' => a, fx => b, fy => c}, $orderby, $limit,
+ $columns_out, [f1, f2], 'exact');
+
+NOTE: Current implementation may remove parts of the iinput hashrefs. If that is a problem
+a copy needs to be created in _filter_fields() below
+
=cut
sub SearchInTable{
if ($keys){
my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
if (@criteria) {
- $sql.= do { local $"=') AND (';
+ $sql.= do { local $"=') OR (';
qq{ WHERE (@criteria) }
};
}
if ($orderby){
#Order by desc by default
my @orders;
- foreach my $order (@$orderby){
- push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order;
+ foreach my $order ( ref($orderby) ? @$orderby : $orderby ){
+ if (ref $order) {
+ push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order;
+ } else {
+ push @orders,$order;
+ }
}
$sql.= do { local $"=', ';
qq{ ORDER BY @orders}
=head2 InsertInTable
-=over 4
-
$data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
-=back
+Insert Data in table and returns the id of the row inserted
- Insert Data in table
- and returns the id of the row inserted
=cut
sub InsertInTable{
=head2 UpdateInTable
-=over 4
-
$status = &UpdateInTable($tablename,$data_hashref);
-=back
+Update Data in table and returns the status of the operation
- Update Data in table
- and returns the status of the operation
=cut
sub UpdateInTable{
my @ids=@$data{@field_ids};
my $dbh = C4::Context->dbh;
my ($keys,$values)=_filter_hash($tablename,$data,0);
+ return unless ($keys);
my $query =
qq{ UPDATE $tablename
SET }.join(",",@$keys).qq{
=head2 DeleteInTable
-=over 4
-
$status = &DeleteInTable($tablename,$data_hashref);
-=back
+Delete Data in table and returns the status of the operation
- Delete Data in table
- and returns the status of the operation
=cut
sub DeleteInTable{
=head2 GetPrimaryKeys
-=over 4
-
@primarykeys = &GetPrimaryKeys($tablename)
-=back
+Get the Primary Key field names of the table
- Get the Primary Key field names of the table
=cut
-sub GetPrimaryKeys($) {
+sub GetPrimaryKeys {
my $tablename=shift;
my $hash_columns=_get_columns($tablename);
return grep { $hash_columns->{$_}->{'Key'} =~/PRI/i} keys %$hash_columns;
}
-=head2 _get_columns
-=over 4
+=head2 clear_columns_cache
-_get_columns($tablename)
+ C4::SQLHelper->clear_columns_cache();
-=back
+cleans the internal cache of sysprefs. Please call this method if
+you update a tables structure. Otherwise, your new changes
+will not be seen by this process.
+
+=cut
+
+sub clear_columns_cache {
+ %$hashref = ();
+}
+
+
+
+=head2 _get_columns
+
+ _get_columns($tablename)
Given a tablename
Returns a hashref of all the fieldnames of the table
=cut
-sub _get_columns($) {
- my ($tablename)=@_;
- my $dbh=C4::Context->dbh;
- my $sth=$dbh->prepare_cached(qq{SHOW COLUMNS FROM $tablename });
- $sth->execute;
- my $columns= $sth->fetchall_hashref(qw(Field));
+sub _get_columns {
+ my ($tablename) = @_;
+ unless ( exists( $hashref->{$tablename} ) ) {
+ my $dbh = C4::Context->dbh;
+ my $sth = $dbh->prepare_cached(qq{SHOW COLUMNS FROM $tablename });
+ $sth->execute;
+ my $columns = $sth->fetchall_hashref(qw(Field));
+ $hashref->{$tablename} = $columns;
+ }
+ return $hashref->{$tablename};
}
=head2 _filter_columns
=cut
-sub _filter_columns ($$;$) {
+sub _filter_columns {
my ($tablename,$research, $filtercolumns)=@_;
if ($filtercolumns){
return (@$filtercolumns);
}
=head2 _filter_fields
-=over 4
-
-_filter_fields
-
-=back
+ _filter_fields
Given
- a tablename
my @keys;
my @values;
if (ref($filter_input) eq "HASH"){
- my ($keys, $values) = _filter_hash($tablename,$filter_input, $searchtype);
+ my ($keys, $values);
+ if (my $special = delete $filter_input->{''}) { # XXX destroyes '' key
+ ($keys, $values) = _filter_fields($tablename,$special, $searchtype,$filtercolumns);
+ }
+ my ($hkeys, $hvalues) = _filter_hash($tablename,$filter_input, $searchtype);
+ if ($hkeys){
+ push @$keys, @$hkeys;
+ push @$values, @$hvalues;
+ }
if ($keys){
- my $stringkey="(".join (") AND (",@$keys).")";
- return [$stringkey],$values;
+ my $stringkey="(".join (") AND (",@$keys).")";
+ return [$stringkey],$values;
}
else {
- return ();
+ return ();
}
} elsif (ref($filter_input) eq "ARRAY"){
foreach my $element_data (@$filter_input){
my $elements=join "|",@columns_filtered;
foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
## supposed to be a hash of simple values, hashes of arrays could be implemented
- $filter_input->{$field}=format_date_in_iso($filter_input->{$field}) if ($columns->{$field}{Type}=~/date/ && $filter_input->{$field} !~C4::Dates->regexp("iso"));
+ if ( $columns->{$field}{Type}=~/date/ ) {
+ if ( defined $filter_input->{$field} ) {
+ if ( $filter_input->{$field} eq q{} ) {
+ $filter_input->{$field} = undef;
+ } elsif ( $filter_input->{$field} !~ C4::Dates->regexp("iso") ) {
+ $filter_input->{$field} = format_date_in_iso($filter_input->{$field});
+ }
+ }
+ }
my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
if (@$tmpkeys){
push @values, @$localvalues;
sub _filter_string{
my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
return () unless($filter_input);
- my @operands=split / /,$filter_input;
+ my @operands=split /\s+/,$filter_input;
+
+ # An act of desperation
+ $searchtype = 'contain' if @operands > 1 && $searchtype =~ /start_with/o;
+
my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
my $columns= _get_columns($tablename);
my (@values,@keys);
my @values;
my @tmpkeys;
my @localkeys;
- push @tmpkeys, " $field = ? ";
- push @values, $operand;
+
+ $operand = [$operand] unless ref $operand eq 'ARRAY';
+ foreach (@$operand) {
+ push @tmpkeys, " $field = ? ";
+ push @values, $_;
+ }
#By default, exact search
if (!$searchtype ||$searchtype eq "exact"){
return \@tmpkeys,\@values;
if ($columns->{$col_field}->{Type}=~/varchar|text/i){
my @localvaluesextended;
if ($searchtype eq "contain"){
- push @tmpkeys,(" $field LIKE ? ");
- push @localvaluesextended,("\%$operand\%") ;
+ foreach (@$operand) {
+ push @tmpkeys,(" $field LIKE ? ");
+ push @localvaluesextended,("\%$_\%") ;
+ }
}
if ($searchtype eq "field_start_with"){
- push @tmpkeys,("$field LIKE ?");
- push @localvaluesextended, ("$operand\%") ;
+ foreach (@$operand) {
+ push @tmpkeys,("$field LIKE ?");
+ push @localvaluesextended, ("$_\%") ;
+ }
}
if ($searchtype eq "start_with"){
- push @tmpkeys,("$field LIKE ?","$field LIKE ?");
- push @localvaluesextended, ("$operand\%", " $operand\%") ;
+ foreach (@$operand) {
+ push @tmpkeys,("$field LIKE ?","$field LIKE ?");
+ push @localvaluesextended, ("$_\%", " $_\%") ;
+ }
}
push @values,@localvaluesextended;
}