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 "undef"s. 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.

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:

    *   RT: CPAN's request tracker

        <http://rt.cpan.org/NoAuth/Bugs.html?Dist=App-ZofCMS-Plugin-DBI>

    *   AnnoCPAN: Annotated CPAN documentation

        <http://annocpan.org/dist/App-ZofCMS-Plugin-DBI>

    *   CPAN Ratings

        <http://cpanratings.perl.org/d/App-ZofCMS-Plugin-DBI>

    *   Search CPAN

        <http://search.cpan.org/dist/App-ZofCMS-Plugin-DBI>

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.