NAME

DBIx::DR - easy DBI helper (named placeholders and blessed results)

SYNOPSIS

my $dbh = DBIx::DR->connect($dsn, $login, $passed);

$dbh->dr_do('SELECT * FROM tbl WHERE id = ?{id}', id => 123);

my $rowset = $dbh->dr_rows(-f => 'sqlfile.sql', ids => [ 123, 456 ]);

while(my $row = $rowset->next) {
    print "id: %d, value: %s\n", $row->id, $row->value;
}

DESCRIPTION

The package extends DBI and allows You:

to use named placeholders;
to bless resultsets into Your package;
to place Your SQL's into dedicated directory;
to use usual DBI methods.

Additional 'connect' options.

dr_iterator

A string describes iterator class. Default value is 'dbix-dr-iterator#new'.

dr_item

A string describes item (one row) class. Default value is 'dbix-dr-iterator-item#new'.

dr_sql_dir

Directory path to seek sql files (If You use dedicated SQLs).

dr_no_cache_sql

If this param is true, DBIx::DR wont cache SQLs that were read from external files.

METHODS

All methods receives the following arguments:

-f => $sql_file_name

It will load SQL-request from file. It will seek file in directory that was defined in dr_no_cache_sql param of connect.

You needn't to use suffixes (.sql) here, but You can.

-item => 'decamelized_obj_define'

It will bless (or construct) row into specified class. See below.

-iterator => 'decamelized_obj_define'

It will bless (or construct) rowset into specified class.

-dbi => HASHREF

Additional DBI arguments.

-hash => FIELDNAME

Selects into HASH. Iterator will operate by names (not numbers).

Decamelized strings

Are strings that represent class [ and method ].

foo_bar                => FooBar
foo_bar#subroutine     => FooBar->subroutine
foo_bar-baz            => FooBar::Baz

dr_do

Does SQL-request like 'UPDATE', 'INSERT', etc.

$dbh->dr_do($sql, value => 1, other_value => 'abc');
$dbh->dr_do(-f => $sql_file_name, value => 1m other_value => 'abc');

dr_rows

Does SQL-request, pack results into iterator class. By default it uses DBIx::DR::Iterator class.

my $res = $dbh->dr_rows(-f => $sql_file_name, value => 1);
while(my $row = $res->next) {
    printf "RowId: %d, RowValue: %s\n", $row->id, $row->value;
}

my $row = $row->get(15);  # row 15

my $res = $dbh->dr_rows(-f => $sql_file_name,
        value => 1, -hash => 'name');
while(my $row = $res->next) {
    printf "RowId: %d, RowName: %s\n", $row->id, $row->name;
}

my $row = $row->get('Vasya');  # row with name eq 'Vasya'

dr_get

Does SQL-request that returns one row. Pack results into item class. Does SQL-request, pack results (one row) into item class. By default it uses DBIx::DR::Iterator::Item class.

SQL placeholders

There are a few types of substitution:

`?{path}`

General substitution. It will be replaced by item defined by 'path'.

Example 1

$sql = q[ SELECT * FROM tbl WHERE id = ?{id} ];
$rows = $dbh->dr_rows($sql, id => 123);

Result:

SELECT * FROM tbl WHERE id = 123

Example 2

$sql = q[ SELECT * FROM tbl WHERE id = ?{ids.id_important} ];
$rows = $dbh->dr_rows($sql, ids => { id_important => 123 });

Result:

SELECT * FROM tbl WHERE id = 123

Example 3

$sql = q[ SELECT * FROM tbl WHERE id = ?{ids:id_important} ];
# object MUST have 'id_important' method
$rows = $dbh->dr_rows($sql, ids => $object);

Result like:

sprintf "SELECT * FROM tbl WHERE id = %s", $object->id_important;

`?fmt{path}{string}`

Formatted substitution. All symbols '?' in 'string will be replaced by value defined by 'path'.

Example 1

$sql = q[ SELECT * FROM tbl where col like ?fmt{filter}{%?%} ]
$rows = $dbh->dr_rows($sql, filter => 'abc');

Result:

SELECT * FROM tbl where col like '%abc%'

`?@{path}`

Array substitution. It will be replaced by items from array defined by 'path'.

Example 1

$sql = q[ SELECT * FROM tbl WHERE id IN ( ?@{ids} ) ];
$rows = $dbh->dr_rows($sql, ids => [ 1, 2, 3, 4 ]);

Result:

SELECT * FROM tbl WHERE id IN ( 1, 2, 3, 4 )

`?@{(path)}`

Array substitution. It will be replaced by items from array defined by 'path'. Each element will be in brackets.

Example 1

$sql = q[ INSERT INTO tbl (value) VALUES ?@{(values)} ];
$dbh->dr_do($sql, values => [ 1, 2, 3, 4 ]);

Result:

INSERT INTO tbl (value) VALUES (1), (2), (3), (4);

`?%{path}{subpath1,subpath2...}`

Array substitution. Array (path) of hashes will be expanded.

Example 1

$sql = q[ INSERT INTO
        tbl (id, value)
    VALUES (?%{values}{id,value})
];
$dbh->dr_do($sql, values => [ { id => 1, value => 'abc' } ]);

Result:

INSERT INTO tbl (id, value) VALUES (1, 'abc')

`?%{(path)}{subpath1,subpath2...}`

Array substitution. Array (path) of hashes will be expanded. Each elementset will be in brackets.

Example 1

$sql = q[
    INSERT INTO
        tbl (id, value)
    VALUES (?%{values}{id,value})
];
$dbh->dr_do(
    $sql,
    values => [
        { id => 1, value => 'abc' },
        { id => 2, value => 'cde' }
    ]
);

Result:

INSERT INTO tbl (id, value) VALUES (1, 'abc'), (2, 'cde')

`?sub{ perl code }`

Eval perl code.

Example 1

$sql = q[ INSERT INTO tbl (time) VALUES ?sub{time} ];
$dbh->dr_do(q[ INSERT INTO tbl (time) VALUES (?sub{time})  ]);

Result:

INSERT INTO tbl (time) VALUES (1319638498)

`?qsub{ perl code }`

Eval perl code and quote result.

Example 1

$sql = q[ INSERT INTO tbl (time) VALUES ?qsub{scalar localtime} ];
$dbh->dr_do(q[ INSERT INTO tbl (time) VALUES (?sub{time})  ]);

Result:

INSERT INTO tbl (time) VALUES ('Thu Oct 27 00:19:14 2011')

Conditional blocks

`?if{path}{block}[{else-block}]` | `?ifd{path}{block}[{else-block}]` | `?ife{path}{block}[{else-block}]`

If variable defined by 'path' is true (if), defined (ifd) or exists (ife), 'block' will be expanded. Otherwise 'else-block' will be expanded (if it is present).

Example 1

$sql = q[
    SELECT
        *
    FROM
        tbl
    WHERE
        sid = 1
        ?if{filter}{ AND filter = ?{ filter_value } }
];

$dbh->dr_rows($sql, filter => 0, filter_value = 123);

Result:

SELECT * FROM tbl WHERE sid = 1

Example 2

$sql = q[
    SELECT
        *
    FROM
        tbl
    WHERE
        sid = 1
        ?if{filter}{ AND filter = ?{ filter_value } }
];

$dbh->dr_rows($sql, filter => 1, filter_value = 123);

Result:

SELECT * FROM tbl WHERE sid = 1 AND filter = 123

Example 3

$sql = q[
    SELECT
        *
    FROM
        tbl
    WHERE
        sid = 1
        AND filter
            ?ifd{filter}{ = ?{ filter } }{ IS NULL }
];

$dbh->dr_rows($sql, filter => 1)

Result:

SELECT * FROM tbl WHERE sid = 1 AND filter = 1

Example 4

$sql = q[
    SELECT
        *
    FROM
        tbl
    WHERE
        sid = 1
        AND filter
            ?ifd{filter}{ = ?{ filter } }{ IS NULL }
];

$dbh->dr_rows($sql, filter => undef);

Result:

SELECT * FROM tbl WHERE sid = 1 AND filter IS NULL

COPYRIGHT

Copyright (C) 2011 Dmitry E. Oboukhov <unera@debian.org>
Copyright (C) 2011 Roman V. Nikolaev <rshadow@rambler.ru>

This program is free software, you can redistribute it and/or
modify it under the terms of the Artistic License version 2.0.

To install DBIx::DR, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::DR

CPAN shell

perl -MCPAN -e shell
install DBIx::DR

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

Additional 'connect' options.

dr_iterator

dr_item

dr_sql_dir

dr_no_cache_sql

METHODS

Decamelized strings

dr_do

dr_rows

dr_get

SQL placeholders

?{path}

Example 1

Example 2

Example 3

?fmt{path}{string}

Example 1

?@{path}

Example 1

?@{(path)}

Example 1

?%{path}{subpath1,subpath2...}

Example 1

?%{(path)}{subpath1,subpath2...}

Example 1

?sub{ perl code }

Example 1

?qsub{ perl code }

Example 1

Conditional blocks

?if{path}{block}[{else-block}] | ?ifd{path}{block}[{else-block}] | ?ife{path}{block}[{else-block}]

Example 1

Example 2

Example 3

Example 4

COPYRIGHT

Module Install Instructions

`?{path}`

`?fmt{path}{string}`

`?@{path}`

`?@{(path)}`

`?%{path}{subpath1,subpath2...}`

`?%{(path)}{subpath1,subpath2...}`

`?sub{ perl code }`

`?qsub{ perl code }`

`?if{path}{block}[{else-block}]` | `?ifd{path}{block}[{else-block}]` | `?ife{path}{block}[{else-block}]`