/ 
Nitesi-DBI-0.0092
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});

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.