C4::SQLHelper - Perl Module containing convenience functions for SQL Handling
use C4::SQLHelper;
This module contains routines for adding, modifying and Searching Data in MysqlDB
$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
$data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
Insert Data in table and returns the id of the row inserted
$status = &UpdateInTable($tablename,$data_hashref);
Update Data in table and returns the status of the operation
$status = &DeleteInTable($tablename,$data_hashref);
Delete Data in table and returns the status of the operation
@primarykeys = &GetPrimaryKeys($tablename)
Get the Primary Key field names of the table
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.
_get_columns($tablename)
Given a tablename Returns a hashref of all the fieldnames of the table With Key Type Default
_filter_columns($tablename,$research, $filtercolumns)
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
_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