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 },
        last_insert_id => 1,
        do_dbi_set_first => 1,
    },

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.

`last_insert_id`

    last_insert_id => 1,
    last_insert_id => [
        $catalog,
        $schema,
        $table,
        $field,
        \%attr,
    ],

Optional. When set to a true value, the plugin will attempt to figure out the LAST_INSERT_ID() after processing dbi_set (see below). The result will be placed into d ZofCMS Template special key under key last_insert_id (currently there is no way to place it anywhere else). The value of last_insert_id argument can be either a true value or an arrayref. Having any true value but an arrayref is the same as having an arrayref with three undefs. That arrayref will be directly dereferenced into DBI's last_insert_id() method. See documentation for DBI for more information. By default is not specified (false)

`do_dbi_set_first`

    do_dbi_set_first => 1,

Optional. Takes either true or false values. If set to a true value, the plugin will first execute dbi_set and then dbi_get; if set to a false value, the order will be reversed (i.e. dbi_get first and then dbi_set will be executed. Defaults to: 1

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 => sub {
            my ( $query, $template, $config, $dbh ) = @_;
            return {
                sql     => [
                    'SELECT * FROM test WHERE id = ?',
                    { Slice => {} },
                    $query->{id},
                ],
                on_data => 'has_data',
            }
        },
    },

    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, a subref or an arrayref as a value. If the value is a subref, the subref will be evaluated and its value will be assigned to dbi_get; the @_ of that subref will contain the following (in that order): $query, $template, $config, $dbh where $query is query string hashref, $template is ZofCMS Template hashref, $config is the App::ZofCMS::Config object and $dbh is a DBI database handle (already connected).

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/ ],

Optional. 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. If the second element in your sql arrayref is a hashref with a key Slice whose value is a hashref, then layout specifies which keys to keep, since selectall_arrayref() (the only currently supported method) will return an arrayref of hashrefs where keys are column names and values are the values. Not specifying layout is only allowed when Slice is a hashref and in that case all column names will be kept. By default is not specified.

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

`single_prefix`

    single_prefix => 'dbi_',

Optional. Takes a scalar as a value. Applies only when single (see above) is set to a true value. The value you specify here will be prepended to any key names your dbi_get generates. This is useful when you're grabbing a single record from the database and dumping it directly into t special key; using the prefix helps prevent any name clashes. By default is not specified

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

REPOSITORY

Fork this module on GitHub: https://github.com/zoffixznet/App-ZofCMS

BUGS

To report bugs or request features, please use https://github.com/zoffixznet/App-ZofCMS/issues

If you can't access GitHub, you can email your request to bug-App-ZofCMS at rt.cpan.org

AUTHOR

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

LICENSE

You can use and distribute this module under the same terms as Perl itself. See the LICENSE file included in this distribution for complete details.

To install App::ZofCMS, copy and paste the appropriate command in to your terminal.

cpanm

cpanm App::ZofCMS

CPAN shell

perl -MCPAN -e shell
install App::ZofCMS

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

DSN AND CREDENTIALS

dsn

user and pass

opt

last_insert_id

do_dbi_set_first

RETRIEVING FROM AND SETTING DATA IN THE DATABASE

dbi_get

layout

sql

single

single_prefix

type

name

method

cell

on_data

process

dbi_set