/ 
Nitesi-DBI-0.0050
River stage one • 1 direct dependent • 1 total dependent
/ Nitesi::Query::DBI

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});

METHODS

init

Initializer, embeds SQL::Abstract::More object inside our Nitesi::Query::DBI object.

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";
}

select_field

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

$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.:

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

insert

Runs insert query, e.g.:

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

update

Runs update query, either with positional or name parameters, e.g.:

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

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

Returns the number of matched/updated records.

delete

Runs delete query, either with positional or named parameters, e.g.:

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

$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 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.