NAME

DBIx::SearchProfiles - Access to SQL database via template query.

SYNOPSIS

    use DBIx::SearchProfiles;

    my $DB = new DBIx::SearchProfiles( $dbh, $profiles);

    my $record  = $DB->record_get( "customer", 1024 );

    my $records = $DB->template_search( "cust_low_balance",
                                        { low_balance => 50 } );

    $DB->record_insert( "customer", $customer_data );

DESCRIPTION

DBIx::SearchProfiles is a module which wraps around a DBI database handle and provides another way than raw SQL to access the database. Its aims is to take the SQL out of the code in well defined and documented search profiles which has easier to maintain than embedded SQL all over the application. Moreover, this decoupling of the application logic from the SQL programming makes it possible to review the SQL by a DBA which might not be a programmer. It may also makes the application's code more obvious and clearer which is a Good Thing (tm)

ACCESS METHODS

The DBIx::SearchProfiles module offers three method of access to the underlying database :

RAW SQL ACCESS

This is the lowest level and is thin wrapper around the underlying DBI methods. The caller specifies the SQL statement and the params to use for the query.

    Ex: $DB->sql_insert( "INSERT INTO customer (?,?,?)", @params );

RECORD ACCESS

This class of access generates automatically the SQL statement to use based on the fields present in the table and the fields passed as parameters. This type of access is very handy for insert or update where you don't want to specify all the fields.

    Ex: $DB->record_insert( "customer", $customer_data );

Where customer is the name of the profile definition to use and $customer_data is a reference to an hash which contains the customer's infos.

TEMPLATE ACCESS

This is the most interesting type of access. The problem with the previous type of access is that it is convenivent and efficient for simple query but when you want something more complex it fails miserably. (Say one where you want other operators than =, and where you are joining 6 tables together) In the template based access, you use a template query in which the parameters will be substituted. The query can be as complex as you want and the parameter subsitutions also.

    Ex: $DB->template_search( "troublesome_customers", $search_spec );

Each class of access provides 5 methods to access the data. (SQL has an extra one, but its the exception) :

*_get

The *_get methods ( sql_get(), record_get() and template_get() ) will return only one record in the form of an hash reference. Each keys corresponds to one column of the table. (So two columns must not have the same name.)

*_search

The *_search methods (sql_search(), record_search() and template_search()) will return a reference to an array of hash. Each hash is a table row where the keys are the column's names.

Also the record_search() and template_search() methods have support for limiting the number of rows returned and to results offset. (1-50,51-100,etc).

*_insert

The *_insert methods are for inserting one record in a table.

*_update

The *_update methods are for updating records in a table.

*_delete

The *_delete methods are for deleting records from the table.

INITIALIZATION

To get a database search profiles handle, you use the new method.

    Ex: my $DB = new DBIx::SearchProfiles( $dsn, $profiles );

The $dsn parameter can either be an already connected DBI handle or a reference to an hash which contains three parameters DataSource, UserName and Password which will be used to open one. Note that on destruction, the connection will only be closed if the connection was established by the DBIx::SearchProfiles modules.

The $profiles parameter can either be a reference to an hash which contains the search profiles, or the name of a file which will be evaluated and that must return a reference to an hash which will contains the search profiles. Note that whenever the search profiles' file changes on disk, the profiles are reloaded.

DBI WRAPPER METHODS

commit

Simply call commit on the underlying DBI handle.

rollback

Simply call rollback on the underlying DBI handle.

PROFILE DEFINITIONS

A search profiles collection is a reference to an hash where each key points ta profile definition. A search profile definition is an hash which contains several elements which will be used to build query automatically.

Here is an example profiles :

    {
    category        =>
        {
            query   => q{ SELECT id,category FROM category 
                          WHERE category_id = ? },
            params  => [ "category_id" ],
        },
    product_srch    =>
      {
       query        => q{ SELECT DISTINCT code,code_manu,category_id,category,
                                 manufacturer_id,manufacturer,
                                 price,description 
                          FROM products p ,manufacturer m ,category c
                          WHERE ( ? = -1 OR c.id  = ? )    AND
                                ( ? = -1 OR m.id  = ? )    AND
                                category_id = c.id         AND
                                manufacturer_id = m.id     AND
                                ( code = ? OR code_manu    = ?
                                           OR category     LIKE ?
                                           OR manufacturer LIKE ?
                                           OR description  LIKE ?
                                )
                         },
       params       => [ qw( category_id category_id manufacturer_id
                             manufacturer_id
                             search search search search search ) ],
       order        => "category_id,manufacturer_id,code",
       defaults     => { category_id => -1, manufacturer_id => -1 },
       limit        => 25,
      },
    order_items     =>
      {
       fields    => [qw( quantity subtotal ) ],
       keys      => [ qw( order_no code ) ],
       table     => "order_items",
      },
    }

In this example, you have a simple query profile (category), a complex template search (product_srch) and an example of a profile for record based access.

Here is the meaning of the different fields :

table (RECORD ACCESS ONLY): The name of the table on which we will operate.
keys (RECORD ACCESS ONLY): A reference to an array which contains the name of the fields which are the primary key for the table.
fields (RECORD ACCESS ONLY): A reference to an array which contains the name of the fields which are not primary keys in the table.
defaults: Reference to an hash of parameter defaults. This will be used to complete when no values are present.
limit: Used by record_search and template_search as the default number of records to return at a time for this query.
max: Used by record_search and template_search as the default maximum total number of records to return for a query.
order: Used by record_search and template_search as the default ordering for the query.
query (TEMPLATE ACCESS ONLY): This is the query template. It should contains the SQL that will be executed with the standard DBI (?) placeholders embedded in it.
params (TEMPLATE ACCESS ONLY): A reference to an array which contains the name of the params that will be substituted in the template. There should be one element for every placeholder in the query.

SQL ACCESS METHODS

sql_do ( $statement, @params );

Thin wrapper around DBI do method. The first argument is the SQL to be executed and the remaining arguments are passed as params to the query.

sql_get ( $statement, @params );

This method will execute the SELECT query passed in the first argument using the remaining parameters as placeholder substitutions.

It returns an hash ref (or undef if the query didn't match any record) corresponding to the first row returned.

sql_search ( $statement, @params );

This method will execute the SELECT query passed in the first argument using the remaining parameters as placeholder substitutions.

It returns a reference to an array of hash.

sql_insert ( $statement, @params );

This method will execute the INSERT query passed in the first argument using the remaining parameters as placeholder substitutions.

Return value is undefined.

sql_update ( $statement, @params );

This method will execute the UPDATE query passed in the first argument using the remaining parameters as placeholder substitutions.

Return value is undefined.

sql_delete ( $statement, @params );

This method will execute the DELETE query passed in the first argument using the remaining parameters as placeholder substitutions.

Return value is undefined.

RECORD BASED ACCESS

record_get ( $name, params );

This method will return an hash reference to a record. The first argument is the name of the profile where the table information will be found. The params argument can either be :

ARRAY OR ARRAY REF

Each element of the array is mapped to an element of the keys field of the profile. It is an error if the number of elements is different than the number of keys defined in the table.

HASH REF

The key will be built by using the name of the keys as specified in the keys field of the profile, or by using the defaults hash if present.

It is an error if some portion of the key is missing.

record_search ( $name, \%params );

This method will build a search on the table specified in the profile $name. $params is a reference to an hash where each keys that is present in the fields or keys of the profile will be used as a constraint in the query. The test is for equality, if you want something more complex, use template_search.

There are a few magic parameters :

dbix_sp_order: Will override the order clause of the query. If not present the order field of the profile will be used.
dbix_sp_limit: Limit the number of records returned by the query. If not present the limit field of the profile will be used.
dbix_sp_max: Set the maximum number of records that the query may fetch, this override the max field of the profile but cannot be set higher.
dbix_sp_start: If there is a limit set for the query, this parameter will start returning records from that offset in the result. Offset is 0 indexed.

The params argument is modified on return. Here is a list of the modified elements :

dbix_sp_found: The number of record returned.
dbix_sp_total: The total number of record matching the query.

Like all *_search methods record_search will return a reference to an array of hash.

record_insert ( $name, \%params );

This method will insert a record in the table specified by the profile $name. The params argument is a reference to an hash which contains the record data to be inserted. The hash should contains one element for each key specified in the keys field of the profile. Each elements in the fields that is a valid table fields (as specified by the fields element of the profile) will be inserted. Any elements specified defaults and not present in the params hash will also be inserted.

Return value is undefined.

record_update ( $name, \%params );

This method will update a record in the table specified by the profile $name. The params argument is a reference to an hash which contains the record data to be updated. The hash should contains one element for each key specified in the keys field of the profile. Each elements in the fields that is a valid table fields (as specified by the fields element of the profile) will be updated. Any elements specified defaults and not present in the params hash will also be updated.

Return value is undefined.

record_delete ( $name, $keys );

This method will delete a record in the table specified by the profile $name. The keys argument is a reference to an hash which contains the keys to the record to delete. The hash should contains one element for each key specified in the keys field of the profile.

Return value is undefined.

TEMPLATE BASED ACCESS

All of the template_* methods accepts two parameters, $name and params. The $name parameter specified the profile to use as a template for the operation (get,search,insert,update or delete). The other parameter is used as substitutions for the placeholders of the template. Those substitutions can be specified in three manners :

ARRAY OR ARRAY REF: Each element of the array is mapped to an element of the params field of the profile. It is an error if the number of elements is different than the number of params defined in the profile.
HASH REF: Each substitutions will be mapped to one of the element of the params hash in the order specified by the params element of the profile. If a params element isn't present, a default one will be used. (Either the value specified in the profile's defaults element or NULL).

template_get ( $name, params )

This method will return an hash reference to a record using the profile $name.

template_search ( $name, params )

This method will run a search using the query template specified in the profile named $name and return the results in a reference to an array of hashes.

This methods accept the same magic parameters in the %params element as the record_search method. It also modifies the same element in %params as that method.

template_insert ( $name, params )

This method will insert a record according to the profile in $name. Normal template substitutions will be used.

Return value is undefined.

template_update ( $name, params )

This method will update records according to the profile $name and using standard template's placholders substitutions semantics.

Return value is the number of rows updated.

template_delete ( $name, params )

This method will delete records according to the template $name and using regular template's placeholders substitutions semantics.

Return value is the number of records deleted.

BUGS AND LIMITATIONS

Please report bugs, suggestions, patches and thanks to <bugs@iNsu.COM>.

The search limitations and offset SQL generation is probably not completely portable. It uses LIMIT and OFFSET which are maybe not supported across SQL92 implementation. (PostgreSQL supports it so...)

To find the number of records that will be returned by a query (in *_search) we use count(*). This could cause a number of problems.

AUTHOR

This program is free software; you can redistribute it and/or modify it under the terms as perl itself.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

ACCESS METHODS

INITIALIZATION

DBI WRAPPER METHODS

commit

rollback

PROFILE DEFINITIONS

SQL ACCESS METHODS

sql_do ( $statement, @params );

sql_get ( $statement, @params );

sql_search ( $statement, @params );

sql_insert ( $statement, @params );

sql_update ( $statement, @params );

sql_delete ( $statement, @params );

RECORD BASED ACCESS

record_get ( $name, params );

record_search ( $name, \%params );

record_insert ( $name, \%params );

record_update ( $name, \%params );

record_delete ( $name, $keys );

TEMPLATE BASED ACCESS

template_get ( $name, params )

template_search ( $name, params )

template_insert ( $name, params )

template_update ( $name, params )

template_delete ( $name, params )

BUGS AND LIMITATIONS

AUTHOR

SEE ALSO

Module Install Instructions