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

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