File Coverage

=head1 NAME

C4::SQLHelper - Perl Module containing convenience functions for SQL Handling

=head1 SYNOPSIS

use C4::SQLHelper;

=head1 DESCRIPTION

This module contains routines for adding, modifying and Searching Data in MysqlDB 

=head1 FUNCTIONS

=head2 SearchInTable

  $hashref = &SearchInTable($tablename,$data, $orderby, $limit, 
                      $columns_out, $filtercolumns, $searchtype);


$tablename Name of the table (string)

$data may contain 
	- string

	- data_hashref : will be considered as an AND of all the data searched

	- data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements

$orderby is an arrayref of hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)

$limit is an array ref on 2 values in order to limit results to MIN..MAX

$columns_out is an array ref on field names is used to limit results on those fields (* by default)

$filtercolums is an array ref on field names : is used to limit expansion of research for strings

$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

=head2 InsertInTable

  $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);

Insert Data in table and returns the id of the row inserted

=cut

=head2 UpdateInTable

  $status = &UpdateInTable($tablename,$data_hashref);

Update Data in table and returns the status of the operation

=cut

=head2 DeleteInTable

  $status = &DeleteInTable($tablename,$data_hashref);

Delete Data in table and returns the status of the operation

=cut

=head2 GetPrimaryKeys

  @primarykeys = &GetPrimaryKeys($tablename)

Get the Primary Key field names of the table

=cut

=head2 clear_columns_cache

  C4::SQLHelper->clear_columns_cache();

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

=head2 _get_columns

    _get_columns($tablename)

Given a tablename 
Returns a hashref of all the fieldnames of the table
With 
	Key
	Type
	Default

=cut

=head2 _filter_columns

=over 4

_filter_columns($tablename,$research, $filtercolumns)

=back

Given 
	- a tablename 
	- indicator on purpose whether all fields should be returned or only non Primary keys
	- array_ref to columns to limit to

Returns an array of all the fieldnames of the table
If it is not for research purpose, filter primary keys

=cut

=head2 _filter_fields

  _filter_fields

Given 
	- a tablename
	- a string or a hashref (containing, fieldnames and datatofilter) or an arrayref to one of those elements
	- an indicator of operation whether it is a wide research or a narrow one
	- an array ref to columns to restrict string filter to.

Returns a ref of key array to use in SQL functions
and a ref to value array

=cut