NAME

Nitesi::Query::DBI - DBI query engine for Nitesi

SYNOPSIS

            

              
              
$query = Nitesi::Query::DBI->new(dbh => $dbh);
$query->select(table => 'products',
               fields => [qw/sku name price/],
               where => {price < 5},
               order => 'name',
               limit => 10);
$query->insert('products', {sku => '9780977920150', name => 'Modern Perl'});
$query->update('products', {media_format => 'CD'}, {media_format => 'CDROM'});
$query->delete('products', {inactive => 1});
            
          

DESCRIPTION

This query engine is based on SQL::Abstract and SQL::Abstract::More and supports the following query types:

select

Retrieving data from one or multiple tables.

insert

Inserting data in one table.

update

Updating data in one table.

delete

Deleting data from one table.

SELECT QUERIES

Distinct example

            

              
              
@skus = $query->select_list_field(table => 'navigation_products',
               field => 'sku',
               distinct => 1,
               where => {navigation => 1});
            
          

Order and limit example

            

              
              
$products = $query->select(table => 'products', 
               fields => [qw/sku title price description media_type/],
 where => {inactive => 0}, 
               order => 'entered DESC', 
               limit => 10);
            
          

Join example

            

              
              
$roles = $query->select(join => [qw/user_roles rid=rid roles/],
                        fields => [qw/roles.rid roles.name/],
          where => {uid => 1});
            
          

ATTRIBUTES

dbh

DBI database handle.

sqla

SQL::Abstract::More object.

log_queries

Code reference use to log queries.

METHODS

select

Runs query and returns records as hash references inside a array reference.

            

              
              
$results = $query->select(table => 'products',
                          fields => [qw/sku name price/],
                          where => {price < 5});
print "Our cheap offers: \n\n";
for (@$results) {
    print "$_->{name} (SKU: $_->{sku}), only $_->{price}\n";
}
            
          

Example: List first 10 - sku, name and price from table products where price is lower than 5, order them by name.

            

              
              
$query->select(table => 'products',
               fields => [qw/sku name price/],
               where => {price < 5},
               order => 'name',
               limit => 10);
            
          

Example: Join user_roles and roles by rid and show rid and name from roles table.

            

              
              
  $query->select(join => 'user_roles rid=rid roles',
        where => { uid => 1 },
        fields => [qw/roles.rid roles.name],
);
            
          

Example: Where clause can be used as defined in SQL::Abstract and SQL::Abstract::More. In this example we find all roles whose name begins with "adm". -ilike is standard DB ILIKE ( minus sign is a sign for database operator and it's not related to negation of the query ).

            

              
              
  $query->select(join => 'user_roles rid=rid roles',
        where => { roles.name => {-ilike => 'adm%' },
        fields => [qw/roles.rid roles.name],
);
            
          

Example: Where clause can be used as defined in SQL::Abstract and SQL::Abstract::More. In this example we find all roles whose name is either "admin" or "super".

            

              
              
  $query->select(join => 'user_roles rid=rid roles',
        where => { roles.name => {-in => ['admin', 'super' },
        fields => [qw/roles.rid roles.name],
);
            
          

select_field

Runs query and returns value for the first field (or undef).

Example: Get name of product 9780977920150.

            

              
              
$name = $query->select_field(table => 'products', 
                                field => 'name', 
                                where => {sku => '9780977920150'});
            
          

select_list_field

Runs query and returns a list of the first field for all matching records, e.g.:

Example: Get all sku's from products where media_type is 'DVD'.

            

              
              
@dvd_skus = $query->select_list_field(table => 'products',
                                   field => 'sku',
                                   where => {media_type => 'DVD'});
            
          

insert

Runs insert query

Example:

            

              
              
$query->insert('products', {sku => '9780977920150', name => 'Modern Perl'});
            
          

update

Runs update query, either with positional or name parameters. Returns the number of matched/updated records.

Example: Positional parameters

            

              
              
$updates = $query->update('products', {media_format => 'CD'}, {media_format => 'CDROM'});
            
          

Example: Named parameters - similar to using SQL to update the table.

            

              
              
$updates = $query->update(table => 'products', 
                          set => {media_format => 'CD'}, 
                          where => {media_format => 'CDROM'});
            
          

delete

Runs delete query, either with positional or named parameters.

Example: Positional parameters

            

              
              
$query->delete('products', {inactive => 1});
            
          

Example: Named parameters - similar to using SQL to delete the record.

            

              
              
$query->delete(table => 'products', where => {inactive => 1});
            
          

dbh

Returns DBI database handle.

sqla

Returns embedded SQL::Abstract::More object.

CAVEATS

Please anticipate API changes in this early state of development.

We don't recommend to use Nitesi::Query::DBI with file backed DBI drivers like DBD::DBM, DBD::CSV, DBD::AnyData or DBD::Excel. In case you want to do this, please install SQL::Statement first, as the statements produced by this module are not understood by DBI::SQL::Nano.

AUTHOR

Stefan Hornburg (Racke), <racke@linuxia.de>

LICENSE AND COPYRIGHT

Copyright 2011-2013 Stefan Hornburg (Racke) <racke@linuxia.de>.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

cpanm Nitesi::DBI

perl -MCPAN -e shell
install Nitesi::DBI