NAME

Dancer::Plugin::SimpleCRUD - very simple CRUD (create/read/update/delete)

DESCRIPTION

A plugin for Dancer web applications, to use a few lines of code to create appropriate routes to support creating/editing/deleting/viewing records within a database table. Uses CGI::FormBuilder to generate, process and validate forms, Dancer::Plugin::Database for database interaction and HTML::Table::FromDatabase to display lists of records.

Setting up forms and code to display and edit database records is a very common requirement in web apps; this plugin tries to make something basic trivially easy to set up and use.

SYNOPSIS

The following assumes that you already have a working Dancer app and have put your database connection details in your config.yml to be read by Dancer::Plugin::Database, which this plugin uses in order to obtain a database connection.

    # In your Dancer app,
    use Dancer::Plugin::SimpleCRUD;

    # Simple example:
    simple_crud(
        record_title => 'Widget',
        prefix => '/widgets',
        db_table => 'widgets',
        editable => 1,
    );

    # The above would create a route to handle C</widgets>, listing all widgets,
    # with options to add/edit entries (linking to C</widgets/add> and
    # C</widgets/edit/:id> respectively) where a form to add a new entry or edit
    # an existing entry will be created.
    # All fields in the database table would be editable.
    #
    # There is also a view route, C</widgets/view/:id>, which shows all the values
    # for the fields of a single database entry.

    # A more in-depth synopsis, using all options (of course, usually you'd only
    # need to use a few of the options where you need to change the default
    # behaviour):

    simple_crud(
        record_title => 'Team',
        prefix => '/teams',
        db_table => 'team',
        labels => {     # More human-friendly labels for some columns
            venue_id => 'Home Venue',
            name     => 'Team Name', 
        },  
        validation => {  # validate values entered for some columns
            division => qr/\d+/,
        },
        input_types => {  # overriding form input type for some columns
            supersecret => 'password',
            lotsoftext' => 'textarea',
        },
        key_column => 'id', # id is default anyway
        editable_columns => [ qw( venue_id name division )    ],
        display_columns  => [ qw( id venue_id name division ) ],
        deleteable => 1,
        editable => 1,
        addable => 0,   # does not allow adding rows
        sortable => 1,
        paginate => 300,
        template => 'simple_crud.tt',
        query_auto_focus => 1,
        downloadable => 1,
        foreign_keys => {
            columnname => {
                table => 'venues',
                key_column => 'id',
                label_column => 'name',
            },
        },
	table_class => 'table table-bordered',
	paginate_table_class => 'table table-borderless',
        custom_columns => [
            {
                name => "division_news",
                raw_column => "division",
                transform  => sub {
                    my $division_name = shift;
                    my $label = "News about $division_name";
                    $division_name =~ s/([^-_.~A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
                    my $search = qq{http://news.google.com/news?q="$division_name"};
                    return "<a href='$search'>$label</a>";
                },
                column_class => "column-class",
            },
        ],
        auth => {
            view => {
                require_login => 1,
            },
            edit => {
                require_role => 'Admin',
            },
        },
    );

USAGE

This plugin provides a simple_crud keyword, which takes a hash of options as described below, and sets up the appropriate routes to present add/edit/delete options.

OPTIONS

The options you can pass to simple_crud are:

record_title (required)

What we're editing, for instance, if you're editing widgets, use 'Widget'. Will be used in form titles (for instance "Add a ...", "Edit ..."), and button labels.

prefix (required)

The prefix for the routes which will be created. Given a prefix of /widgets, then you can go to /widgets/new to create a new Widget, and /widgets/42 to edit the widget with the ID (see key_column) 42.

Don't confuse this with Dancer's prefix setting, which would be prepended before the prefix you pass to this plugin. For example, if you used:

prefix '/foo';
simple_crud(
    prefix => 'bar',
    ...
);

... then you'd end up with e.g. /foo/bar as the record listing page.

db_table (required)

The name of the database table.

key_column (optional, default: 'id')

Specify which column in the table is the primary key. If not given, defaults to id.

where_filter (optional)

Specify one or more 'where' clauses to use to filter the table. For example:

simple_crud(
    prefix => 'bar',
    where_filter => {user_id => 1000},
    ...
);

This would cause only rows with an user_id of 1000 to be displayed in listings and search results, viewed, edited etc.

The where_filter parameter takes a hashref describing the WHERE clause, as used by Dancer::Plugin::Database's quick_select convenience method for example - see the where clause documentation in Dancer::Plugin::Database::Core::Handle.

Alternatively, if the filter condition needs to be calculated at runtime (for example, based on the logged in user calling it), then you can provide a coderef which returns the WHERE clause hashref - for instance:

where_filter => sub { { customer_id => logged_in_user()->{customer_id} } },

db_connection_provider (optional)

By default, we use Dancer::Plugin::Database to obtain database connections.

If the module Dancer::Plugin::DBIC is installed and you set this option to 'DBIC', the database connection will be created using Dancer::Plugin::DBIC and its corresponding configuration options for database connections. Note that in DBIC, the default connection is named 'dafault', not ''.

db_connection_name (optional)

By default, we use Dancer::Plugin::Database to obtain database connections. (You can override this using the db_connection_provider option.) The db_connection_name option allows you to specify the name of a connection defined in the config file to use. See the documentation for Dancer::Plugin::Database (or Dancer::Plugin::DBIC) for how multiple database configurations work.

If this is not supplied or is empty, the default database connection details in your config file will be used - this is often what you want, so unless your app is dealing with multiple DBs, you probably won't need to worry about this option.

Note that in Dancer::Plugin::Database, the default connection is named '', but in Dancer::Plugin::DBIC the default connection is named 'default'.

labels (optional)

A hashref of field_name => 'Label', if you want to provide more user-friendly labels for some or all fields. As we're using CGI::FormBuilder, it will do a reasonable job of figuring these out for itself usually anyway - for instance, a field named first_name will be shown as First Name.

input_types (optional)

A hashref of field_name => input type, if you want to override the default type of input which would be selected by CGI::FormBuilder or by our DWIMmery (by default, password fields will be used for field names like 'password', 'passwd' etc, and text area inputs will be used for columns with type 'TEXT').

Valid values include anything allowed by HTML, e.g. text, select, textarea, radio, checkbox, password, hidden.

Example:

input_types => {
    first_name => 'text',
    secret     => 'password',
    gender     => 'radio',
}

validation (optional)

A hashref of field_name => validation criteria which should be passed to CGI::FormBuilder.

Example:

validation => {
    email_address => 'EMAIL',
    age => '/^\d+$/',
}

message (optional)

A hashref of field_name => messages to show if validation failed.

Default is "Invalid entry".

Example:

message => {
    age   => 'Please enter your age in years',
    email => 'That is not a valid email address',
},

jsmessage (optional)

A hashref of field_name => message to show when Javascript validation fails.

Default message is "- Invalid entry for the "$fieldname" field". See above for example.

sort_options (optional)

A hashref of field_name => optionspec indicating how select options should be sorted

This is currently a passthrough to CGI::FormBuilder's sortopts. There are several built-in values:

NAME            Sort option values by name
NUM             Sort option values numerically
LABELNAME       Sort option labels by name
LABELNUM        Sort option labels numerically

See the documentation for "sortopts" in CGI::FormBuilder for more.

acceptable_values (optional)

A hashref of arrayrefs to declare that certain fields can take only a set of acceptable values.

Example:

acceptable_values => {
    gender => ['Male', 'Female'],
    status => [qw(Alive Dead Zombie Unknown)],
}

You can automatically create option groups (on a field of type select) by specifying the acceptable values in CGI::FormBuilder's [value, label, category] format, like this:

acceptable_values => {
    gender => ['Male', 'Female'],
    status => [qw(Alive Dead Zombie Unknown)],
    threat_level => [
        [ 'child_puke',   'Regurgitation',       'Child'],
        [ 'child_knee',   'Knee Biter',          'Child'],
        [ 'teen_eye',     'Eye Roll',            'Adolescent'],
        [ 'teen_lip',     'Withering Sarcasm',   'Adolescent'],
        [ 'adult_silent', 'Pointedly Ignore',    'Adult'],
        [ 'adult_freak',  'Become Very Put Out', 'Adult'],
    ],
}

If you are letting FormBuilder choose the field type, you won't see these categories unless you have enough options that it makes the field into a select. If you want to see the categories all the time, you can use the "input_types" option to force your field to be rendered as a select.

default_value (optional)

A hashref of default values to have pre-selected on the add form.

Example:

default_value => {
    gender => 'Female',
    status => 'Unknown',
}

editable_columns (optional)

Specify an arrayref of fields which the user can edit. By default, this is all columns in the database table, with the exception of the key column.

not_editable_columns (optional)

Specify an arrayref of fields which should not be editable.

required (optional)

Specify an arrayref of fields which must be completed. If this is not provided, DWIMmery based on whether the field is set to allow null values in the database will be used - i.e. if that column can contain null, then it doesn't have to be completed, otherwise, it does.

deletable

Specify whether to support deleting records. If set to a true value, a route will be created for /prefix/delete/:id to delete the record with the ID given, and the edit form will have a "Delete $record_title" button.

editable

Specify whether to support editing records. Defaults to true. If set to a false value, it will not be possible to add or edit rows in the table. See also addable.

addable

Specify whether to support adding records. Defaults to the value of editable if set, or true otherwise. If set to a false value, it will not be possible to add rows in the table.

sortable

Specify whether to support sorting the table. Defaults to false. If set to a true value, column headers will become clickable, allowing the user to sort the output by each column, and with ascending/descending order.

paginate

Specify whether to show results in pages (with next/previous buttons). Defaults to undef, meaning all records are shown on one page (not useful for large tables). When defined as a number, only this number of results will be shown.

display_columns

Specify an arrayref of columns that should show up in the list. Defaults to all.

template

Specify a template that will be applied to all output. This template must have a "simple_crud" placeholder defined or you won't get any output. This template must be located in your "views" directory.

Any global layout will be applied automatically because this option causes the module to use the template keyword. If you don't use this option, the template keyword is not used, which implies that any before_template_render and after_template_render hooks won't be called.

query_auto_focus

Specify whether to automatically set input focus to the query input field. Defaults to true. If set to a false value, focus will not be set. The focus is set using a simple inlined javascript.

downloadable

Specify whether to support downloading the results. Defaults to false. If set to a true value, The results show on the HTML page can be downloaded as CSV/TSV/JSON/XML. The download links will appear at the top of the page.

foreign_keys

A hashref to specify columns in the table which are foreign keys; for each one, the value should be a hashref containing the keys table, key_column and label_column.

custom_columns

An arrayref of hashrefs to specify custom columns to appear in the list view of an entity. (Previously, this was just a hashref of column names and specs, and this style is still supported for backwards compatibility, but is deprecated because it leaves the order of the columns unpredictable.)

The keys of each hash are name, the name to use for this custom column, raw_column indicating a column from the table that should be selected to build the custom column from, transform, a subref to be used as a HTML::Table::FromDatabase callback on the resulting column, and column_class, to specify a CSS class for the the column. column_class is optional, and if no transform is provided, sub { return shift; } will be used.

If your custom column has the same name as an existing column, your customizations will be used in-place to override the display of the content in that column. If sorting is enabled, the column will be sorted by the underlying database content for that row, and not by the output of your transform function.

For a somewhat spurious example:

...
custom_columns => [
    {
        name => 'email_provider',
        raw_column => 'email',
        transform => sub {
            my $value = shift;
            return (split /@/, 1)[1];
        },
        column_class => 'column-class',
    },
],
...

The transform code ref is passed to HTML::Table::FromDatabase as a callback for that column, so it can do anything a HTML::Table::FromDatabase callback can do. In particular, the coderef will receive the value of the column as the first parameter, but also a reference to the whole row hashref as the second parameter, so you can do a variety of cunning things.

An example of a custom column whose transform coderef uses the row hashref to get other values for the same row could be:

...
custom_columns => [
    {
        name => 'salutation',
        raw_column => 'name',
        transform => sub {
            my ($name_value, $row) = @_;
            return "Hi, $row->{title} $name_value!";
        },
    }
],
...

auth

You can require that users be authenticated to view/edit records using the auth option to enable authentication powered by Dancer::Plugin::Auth::Extensible.

You can set different requirements for viewing and editing, for example:

auth => {
    view => {
        require_login => 1,
    },
    edit => {
        require_role => 'Admin',
    },
},

The example above means that any logged in user can view records, but only users with the 'Admin' role are able to create/edit/delete records.

Or, to just require login for anything (same requirements for both viewing and editing), you can use the shorthand:

auth => {
    require_login => 1,
},

table_class

This provides a CSS class for the tables.

paginate_table_class

This provides a CSS class for the tables paginate buttons.

DWIMmery

This module tries to do what you'd expect it to do, so you can rock up your web app with as little code and effort as possible, whilst still giving you control to override its decisions wherever you need to.

Field types

CGI::FormBuilder is excellent at working out what kind of field to use by itself, but we give it a little help where needed. For instance, if a field looks like it's supposed to contain a password, we'll have it rendered as a password entry box, rather than a standard text box.

If the column in the database is an ENUM, we'll limit the choices available for this field to the choices defined by the ENUM list. (Unless you've provided a set of acceptable values for this field using the acceptable_values option to simple_crud, in which case what you say goes.)

Hooks

Hooks are provided, which can be used in the normal Dancer way, using the hook keyword.

add_edit_row (deprecated, use add_edit_row_pre_save)

You can use the same code from your add_edit_row hook in an add_edit_row_pre_save hook. The only modification is that the new hook passes the editable params as a key of the first argument (called params), rather than as the first argument itself. So, if your hook had my $args = shift;, it could just use my $args = shift->{params}; and it should work the same way.

add_edit_row_pre_save, add_edit_row_post_save

These fire right before and after a row is added/edited; a hashref is passed with metadata such as the name of the table (in table_name), the args from the original route setup (args), the table's key column (key_column), and the values of the editable params (params).

In the post-save hook, you are also sent success (the return value of quick_insert or quick_update) telling you if the save was successful (which is a little redundant because your post-save hook won't be called unless the insert or update was successful). You'll also get dbh giving you the instance of the handle used to save the entity (so you can access last_insert_id()), and verb (currently either 'create new' or 'update').

For instance, if you were dealing with a users table, you could use the pre_save hook to hash the password before storing it - assuming for the sake of example that you have a hash_pw() function to return a hashed password:

hook add_edit_row_pre_save => sub {
    my $args = shift;
    if ($args->{table_name} eq 'user') {
        $args->{params}{password} = hash_pw($args->{params}{password});
    }
};

delete_row_pre_delete, delete_row_post_delete

These fire right before and after a row is deleted. As with the add_edit_row_pre_save and add_edit_row_post_save hooks, these are passed a hashref with metadata such as the name of the table (in table_name), the args from the original route setup (args), the table's key column (key_column), and the values of the editable params (params). As with the post-save hook, delete_row_post_delete hook won't be called if we weren't able to delete the row.

You could use these to clean up ancillary data associated with a database row when it was deleted, for example.

AUTHOR

David Precious, <davidp@preshweb.co.uk>

ACKNOWLEDGEMENTS

Alberto Simões (ambs)

Johnathan Barber

saberworks

jasonjayr

Paul Johnson (pjcj)

Rahul Kotamaraju

Michael J South (msouth)

Martijn Lievaart

Josh Rabinowitz

Phil Carmody (thefatphil)

BUGS

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

CONTRIBUTING

This module is developed on Github:

http://github.com/bigpresh/Dancer-Plugin-SimpleCRUD

Bug reports, ideas, suggestions, patches/pull requests all welcome.

Even just a quick "Hey, this is great, thanks" or "This is no good to me because..." is greatly appreciated. It's always good to know if people are using your code, and what they think.

SUPPORT

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

perldoc Dancer::Plugin::SimpleCRUD

You may find help with this module on the main Dancer IRC channel or mailing list - see http://www.perldancer.org/

You can also look for information at:

RT: CPAN's request tracker

http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer-Plugin-SimpleCRUD
AnnoCPAN: Annotated CPAN documentation

http://annocpan.org/dist/Dancer-Plugin-SimpleCRUD
CPAN Ratings

http://cpanratings.perl.org/d/Dancer-Plugin-SimpleCRUD
Search CPAN

http://search.cpan.org/dist/Dancer-Plugin-SimpleCRUD/

LICENSE AND COPYRIGHT

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 Dancer::Plugin::SimpleCRUD::ParamsObject, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Dancer::Plugin::SimpleCRUD::ParamsObject

CPAN shell

perl -MCPAN -e shell
install Dancer::Plugin::SimpleCRUD::ParamsObject

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

DESCRIPTION

SYNOPSIS

USAGE

OPTIONS

DWIMmery

Field types

Hooks

add_edit_row (deprecated, use add_edit_row_pre_save)

add_edit_row_pre_save, add_edit_row_post_save

delete_row_pre_delete, delete_row_post_delete

AUTHOR

ACKNOWLEDGEMENTS

BUGS

CONTRIBUTING

SUPPORT

LICENSE AND COPYRIGHT

Module Install Instructions