NAME

DBIx::IO::Search - query a database and generate SQL

SYNOPSIS

use DBIx::IO::Search;

$searcher = DBIx::IO::Search->new($dbh,$table_name,[$critlist,$sortlist,$parent_id_name,$connect_by,$start_with]);

$critlist = $searcher->critlist();
$critlist = $searcher->critlist($critlist);

$sortlist = $searcher->sortlist();
$sortlist = $searcher->sortlist($sortlist);

$criteria = $searcher->add_crit($criteria);

$criteria =  $searcher->build_range_crit($field_name,$range_start,$range_end,[$logical_not]);

$crit = $searcher->build_scalar_crit($field_name,$operator,$value);

$crit = $searcher->build_list_crit($field_name,$value_list,[$logical_not]);

$sth = $searcher->execute();

$results = $searcher->results();

$results = $searcher->fetchall();

$results = $searcher->search([$critlist],[$sortlist]); # combines execute(), and fetchall()

DESCRIPTION

This class enables reports to be run on a particular table or view based on a set of criteria. The criteria are stored in an array - $critlist where each criterion is one of 3 types depending on the operator:

scalar - This type supports the <=, >=, =, !=, <, > and (NOT) LIKE operators; 1 scalar value is required for comparison. A special value, NULL is recognized as meaning a NULL value. In this case, the operators '=' and '!=' are mapped to 'IS' and 'IS NOT' respectively. This criterion is represented as a hash ref with keys: COLUMN, OPERATOR, and VALUE, which mean just what you'd think. The (NOT) LIKE operator allows wildcards `%' in the comparison value.

range - This type is used for camparison of ranges using the 'BETWEEN' operator. It is represented as a hash ref with keys: COLUMN, OPERATOR, START, END, which mean just what you'd think. OPERATOR has 2 allowed values: 'BETWEEN' and 'NOT BETWEEN'.

list - This type is used for comparison with a list of values where any of the values can match for the criteria to be met. The IN operator is used and the criterion is represented as a hash ref with keys: COLUMN, OPERATOR, LIST, which mean just what you'd think. OPERATOR has 2 allowed values: 'IN' and 'NOT IN'. LIST must be an array ref of the values intended for comparison.

The result set is returned via results() or search(). The result set is an array ref where each element is a record represented by a hash ref of column => value pairs (see fetchall_arrayref({}) in DBI); all columns are returned. If sorting is desired then populate the sortlist attribute via new(), sortlist() or search(). $sortlist is an array ref of column_names where the result set is returned sorted by these columns in order.

For tables with a hierarchical relationship, START WITH and CONNECT BY clauses are required in the constructor in order to reveal that relationship (review SELECT statements in an SQL reference). Normally SQL does not support additional sorting of the results when these clauses are given. However, if these clauses are given and sorting is desired, then sorting will be done at the top level of the hierarchy only. In order for this to work, the table has to have a single column primary key.

The criteria list can be given on construction, or through the crit_list() or search() methods. The list can be built with individual criterion with add_crit(). Also, just to make things easier, there is a builder method for each type of criterion, which helps to assemble the data into the required record format: build_scalar_crit(), build_list_crit() and build_range_crit().

The values in each criterion that are used for comparison are qualified automagically (see DBIx::IO::qualify()) and dates are assumed to be normalized (see DBIx::IO::GenLib). This lets you build searches without having to worry about quoting text vs numbers.

Some special values are recognized for dates: <int> hours, <int> days, <int> weeks, <int> months where <int> should be an appropriate integer. These date formats allow values to be specified relative to now, e.g. an operator of '<' and a value of '2 weeks' infer a value of any date less than 2 weeks from now.

If a single table or view is not enough for reporting purposes, like needing to do a join with 2 or more tables, then ask your friendly neighborhood DBA to create another view. If that doesn't work then write your own damn SQL! :<>

Oracle users: LOB columns won't be retreived because they aren't supported in DBD::Oracle (as of v1.19). LONG columns seem to work fine though so if you can get away with using a LONG over a LOB, do that. $DBIx::IO::GenLib::LONG_READ_LENGTH gives the limit size of a long that will be returned.

METHOD DETAILS

new (constructer)

$searcher = DBIx::IO::Search->new($dbh,$table_name,[$critlist,$sortlist,$parent_id_name,$connect_by,$start_with]);

Create a new $searcher based on $table_name. $critlist and/or $sortlist can be given on construction as well. To express hierarchical relationships, include $parent_id_name or ($connect_by and/or $start_with). The START WITH clause defaults to 'START WITH $parent_id_name IS NULL'. The CONNECT BY clause defaults to 'CONNECT BY PRIOR ${table_name}_ID = $parent_id_name'.

Return undef if error, return 0 if $table_name does not exist.

critlist

$critlist = $searcher->critlist();
$critlist = $searcher->critlist($critlist);

Get or set $critlist.

sortlist

$sortlist = $searcher->sortlist();
$sortlist = $searcher->sortlist($sortlist);

Get or set $sortlist.

add_crit

$criteria = $searcher->add_crit($criteria);

Add an assembled criterion to the criteria list.

build_range_crit

$criteria =  $searcher->build_range_crit($field_name,$range_start,$range_end,[$logical_not]);

Build a range criterion (for use with the 'BETWEEN' operator) by supplying the following arguments:

$field_name                    -> name of the column that is restricted to the specified range.
$range_start                   -> scalar repesenting the beginning of the range.
$range_end                     -> scalar repesenting the ending of the range.
$logical_not                   -> if this is true the SQL expression looks like 'AND NOT BETWEEN ...'
                                  (a little confusing since the default, undef is true).

Returns the assembled criterion and adds it to $searcher.

build_scalar_crit

$crit = $searcher->build_scalar_crit($field_name,$operator,$value);

Build a scalar criterion (for use with the <=, >=, =, !=, <, > operators) by providing the $field_name, an $operator and a single scalar $value.

Returns the assembled criterion and adds it to $searcher.

build_list_crit

$crit = $searcher->build_list_crit($field_name,$value_list,$logical_not);

Build a list criterion (for use with the IN operator) by providing:

$field_name                    -> name of the column that is restricted to the specified $value_list
$value_list                    -> an array ref of values that the criterion is restricted to.
$logical_not                   -> if this is true the SQL expression looks like 'AND NOT IN ...'
                                  (a little confusing since the default, undef is true).

Returns the assembled criterion and adds it to $searcher.

execute

$sth = $searcher->execute();

Build an SQL statement from the list of criteria in $searcher and execute it. Return the resulting statement handle (see DBI) or false if error. Also see search() explained elsewhere.

results

$results = $searcher->results();

After search() or fetchall() is called, an array of hashes representing the result set is stored in the object. This method returns that result set.

search

$results = $searcher->search([$critlist],[$sortlist]);

Generate SQL, execute, and return the result set. $critlist and $sortlist will override those already assigned to $searcher.

Return false if error. NOTE: The returned value could be true but also be an emtpy array ref with no rows returned.

fetchall

$results = $searcher->fetchall();

After execute() is called, $searcher will contain a loaded statement handle. This method gets the result set, stores it and returns it (see also the results() and search methods elsewhere). Return false if error. NOTE: The returned value could be true but also be an emtpy array ref with no rows returned.

TODO

Doesn't strip leading or trailing spaces from criteria values. This should be done because CGI UI's were the intention for this module.

BUGS

No known bugs.

SEE ALSO

DBIx::IO::Table, DBIx::IO

AUTHOR

Reed Sandberg, <reed_sandberg Ó’ yahoo>

COPYRIGHT AND LICENSE

Copyright (C) 2000-2008 Reed Sandberg

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

The full text of the license can be found in the LICENSE file included with this module.

cpanm DBIx::IO

perl -MCPAN -e shell
install DBIx::IO