Nitesi::Query::DBI - DBI query engine for Nitesi
$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});
This query engine is based on SQL::Abstract and SQL::Abstract::More and supports the following query types:
Retrieving data from one or multiple tables.
Inserting data in one table.
Updating data in one table.
Deleting data from one table.
@skus = $query->select_list_field(table => 'navigation_products', field => 'sku', distinct => 1, where => {navigation => 1});
$products = $query->select(table => 'products', fields => [qw/sku title price description media_type/], where => {inactive => 0}, order => 'entered DESC', limit => 10);
$roles = $query->select(join => [qw/user_roles rid=rid roles/], fields => [qw/roles.rid roles.name/], where => {uid => 1});
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], );
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'});
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'});
Runs insert query
Example:
$query->insert('products', {sku => '9780977920150', name => 'Modern Perl'});
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'});
Runs delete query, either with positional or named parameters.
$query->delete('products', {inactive => 1});
Example: Named parameters - similar to using SQL to delete the record.
$query->delete(table => 'products', where => {inactive => 1});
Returns DBI database handle.
Returns embedded SQL::Abstract::More object.
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.
Stefan Hornburg (Racke), <racke@linuxia.de>
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.
To install Nitesi::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Nitesi::DBI
CPAN shell
perl -MCPAN -e shell install Nitesi::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.