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



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.



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



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