/ 
App-ZofCMS-Plugin-DBI-0.0311
/ App::ZofCMS::Plugin::DBI

NAME

App::ZofCMS::Plugin::DBI - DBI access from ZofCMS templates

SYNOPSIS

In your main config file or ZofCMS template:

dbi => {
    dsn     => "DBI:mysql:database=test;host=localhost",
    user    => 'test', # user,
    pass    => 'test', # pass
    opt     => { RaiseError => 1, AutoCommit => 0 },
},

In your ZofCMS template:

dbi => {
    dbi_get => {
        layout  => [ qw/name pass/ ],
        sql     => [ 'SELECT * FROM test' ],
    },
    dbi_set => sub {
        my $query = shift;
        if ( defined $query->{user} and defined $query->{pass} ) {
            return [
                [ 'DELETE FROM test WHERE name = ?;', undef, $query->{user}      ],
                [ 'INSERT INTO test VALUES(?,?);', undef, @$query{qw/user pass/} ],
            ];
        }
        elsif ( defined $query->{delete} and defined $query->{user_to_delete} ) {
            return [ 'DELETE FROM test WHERE name =?;', undef, $query->{user_to_delete} ];
        }
        return;
    },
},

In your HTML::Template template:

<form action="" method="POST">
    <div>
        <label for="name">Name: </label>
        <input id="name" type="text" name="user" value="<tmpl_var name="query_user">"><br>
        <label for="pass">Pass: </label>
        <input id="pass" type="text" name="pass" value="<tmpl_var name="query_pass">"><br>
        <input type="submit" value="Add">
    </div>
</form>

<table>
    <tmpl_loop name="dbi_var">
        <tr>
            <td><tmpl_var name="name"></td>
            <td><tmpl_var name="pass"></td>
            <td>
                <form action="" method="POST">
                    <div>
                        <input type="hidden" name="user_to_delete" value="<tmpl_var name="name">">
                        <input type="submit" name="delete" value="Delete">
                    </div>
                </form>
            </td>
        </tr>
    </tmpl_loop>
</table>

DESCRIPTION

Module is a App::ZofCMS plugin which provides means to retrieve and push data to/from SQL databases using DBI module.

Current functionality is limited. More will be added as the need arrises, let me know if you need something extra.

This documentation assumes you've read App::ZofCMS, App::ZofCMS::Config and App::ZofCMS::Template

DSN AND CREDENTIALS

dbi => {
    dsn     => "DBI:mysql:database=test;host=localhost",
    user    => 'test', # user,
    pass    => 'test', # pass
    opt     => { RaiseError => 1, AutoCommit => 0 },
},

You can set these either in your ZofCMS template's dbi key or in your main config file's dbi key. The key takes a hashref as a value. The keys/values of that hashref are as follows:

dsn

dsn => "DBI:mysql:database=test;host=localhost",

Specifies the DSN for DBI, see DBI for more information on what to use here.

user and pass

user    => 'test', # user,
pass    => 'test', # pass

The user and pass key should contain username and password for the database you will be accessing with your plugin.

opt

opt => { RaiseError => 1, AutoCommit => 0 },

The opt key takes a hashref of any additional options you want to pass to connect_cached DBI's method.

RETRIEVING FROM AND SETTING DATA IN THE DATABASE

In your ZofCMS template the first-level dbi key accepts a hashref two possible keys: dbi_get for retreiving data from database and dbi_set for setting data into the database. Note: you can also have your dsn, user, pass and opt keys here if you wish.

dbi_get

dbi => {
    dbi_get => {
        layout  => [ qw/name pass/ ],
        single  => 1,
        sql     => [ 'SELECT * FROM test' ],
        on_data => 'has_data',
        process => sub {
            my ( $data_ref, $template, $query, $config ) = @_;
        }
    },
}

dbi => {
    dbi_get => [
        {
            layout  => [ qw/name pass/ ],
            sql     => [ 'SELECT * FROM test' ],
        },
        {
            layout  => [ qw/name pass time info/ ],
            sql     => [ 'SELECT * FROM bar' ],
        },
    ],
}

The dbi_get key takes either a hashref or an arrayref as a value. If the value is a hashref it is the same as having just that hashref inside the arrayref. Each element of the arrayref must be a hashref with instructions on how to retrieve the data. The possible keys/values of that hashref are as follows:

layout

layout  => [ qw/name pass time info/ ],

Mandatory. Takes an arrayref as an argument. Specifies the name of <tmpl_var name="">s in your <tmpl_loop> (see type argument below) to which map the columns retrieved from the database, see SYNOPSIS section above.

sql

sql => [ 'SELECT * FROM bar' ],

Mandatory. Takes an arrayref as an argument which will be directly dereferenced into the DBI's method call specified by method argument (see below). See App::ZofCMS::Plugin::Tagged for possible expansion of possibilities you have here.

single

single => 1,

Optional. Takes either true or false values. Normally, the plugin will make a datastructure suitable for a <tmpl_loop name="">; however, if you expecting only one row from the table to be returned you can set single parameter to a true value and then the plugin will stuff appropriate values into {t} special hashref where keys will be the names you specified in the layout argument and values will be the values of the first row that was fetched from the database. By default is not specified (false)

type

dbi_get => {
    type    => 'loop'
...

Optional. Specifies what kind of a HTML::Template variable to generate from database data. Currently the only supported value is loop which generates <tmpl_loop> for yor HTML::Template template. Defaults to: loop

name

dbi_get => {
    name    => 'the_var_name',
...

Optional. Specifies the name of the key in the cell (see below) into which to stuff your data. With the default cell argument this will be the name of a HTML::Template var to set. Defaults to: dbi_var

method

dbi_get => {
    method => 'selectall',
...

Optional. Specifies with which DBI method to retrieve the data. Currently the only supported value for this key is selectall which uses selectall_arrayref. Defaults to: selectall

cell

dbi_get => {
    cell => 't'
...

Optional. Specifies the ZofCMS template's first-level key in which to create the name key with data from the database. cell must point to a key with a hashref in it (though, keep autovivification in mind). Possibly the sane values for this are either t or d. Defaults to: t (the data will be available in your HTML::Template templates)

on_data

dbi_get => {
    on_data => 'has_data',
...

Optional. Takes a string as an argument. When specified will set the key in {t} name of which is specified on_data to 1 when there are any rows that were selected. Typical usage for this would be to display some message if no data is available; e.g.:

dbi_get => {
    layout => [ qw/name last_name/ ],
    sql => [ 'SELECT * FROM users' ],
    on_data => 'has_users',
},

<tmpl_if name="has_users">
    <p>Here are the users:</p>
    <!-- display data here -->
<tmpl_else>
    <p>I have no users for you</p>
</tmpl_if>

process

dbi_get => {
    process => sub {
        my ( $data_ref, $template, $query, $config ) = @_;
        # do stuff
    }
...

Optional. Takes a subref as a value. When specified the sub will be executed right after the data is fetched. The @_ will contain the following (in that order): $data_ref - the return of DBI's selectall_arrayref call, this may have other options later on when more methods are supported, the ZofCMS Template hashref, query hashref and App::ZofCMS::Config object.

dbi_set

dbi_set => sub {
    my $query = shift;
    if ( defined $query->{user} and defined $query->{pass} ) {
        return [
            [ 'DELETE FROM test WHERE name = ?;', undef, $query->{user}      ],
            [ 'INSERT INTO test VALUES(?,?);', undef, @$query{qw/user pass/} ],
        ];
    }
    elsif ( defined $query->{delete} and defined $query->{user_to_delete} ) {
        return [ 'DELETE FROM test WHERE name =?;', undef, $query->{user_to_delete} ];
    }
    return;
},

dbi_set => [
    'DELETE FROM test WHERE name = ?;', undef, 'foos'
],

dbi_set => [
    [ 'DELETE FROM test WHERE name = ?;', undef, 'foos' ],
    [ 'INSERT INTO test VALUES(?, ?);', undef, 'foos', 'bars' ],
]

Note: the dbi_set will be processed before dbi_get. Takes either a subref or an arrayref as an argument. Multiple instructions can be put inside an arrayref as the last example above demonstrates. Each arrayref will be directly dereferenced into DBI's do() method. Each subref must return either a single scalar, an arrayref or an arrayref of arrayrefs. Returning a scalar is the same as returning an arrayref with just that scalar in it. Returning just an arrayref is the same as returning an arrayref with just that arrayref in it. Each arrayref of the resulting arrayref will be directly dereferenced into DBI's do() method. The subrefs will have the following in their @_ when called: $query, $template, $config, $dbh. Where $query is a hashref of query parameters in which keys are the name of the parameters and values are values. $template is a hashref of your ZofCMS template. $config is the App::ZofCMS::Config object and $dbh is DBI's database handle object.

AUTHOR

Zoffix Znet, <zoffix at cpan.org> (http://zoffix.com, http://haslayout.net)

BUGS

Please report any bugs or feature requests to bug-app-zofcms-plugin-dbi at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=App-ZofCMS-Plugin-DBI. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc App::ZofCMS::Plugin::DBI

You can also look for information at:

COPYRIGHT & LICENSE

Copyright 2008 Zoffix Znet, all rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.