bigpresh / dancer-plugin-simplecrud Goto Github PK
View Code? Open in Web Editor NEWQuick and effortless CRUD (create/read/update/delete) operations based on database tables
Quick and effortless CRUD (create/read/update/delete) operations based on database tables
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_name" (optional) We use Dancer::Plugin::Database to obtain database connections. This option allows you to specify the name of a connection defined in the config file to use. See the documentation for Dancer::Plugin::Database 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. "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, "<[email protected]>" ACKNOWLEDGEMENTS Alberto Simões (ambs) WK Johnathan Barber saberworks jasonjayr Paul Johnson (pjcj) Rahul Kotamaraju Michael J South (msouth) Martijn Lievaart Josh Rabinowitz 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 Copyright 2010-16 David Precious. 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.
If you have a table with a foreign key and a name column defined, it would be nice for the filter for that column in the filter to give you a dropdown showing the name values to select from--or at least let you type that in instead of knowing the id of the row in the foreign key.
(I am just putting this here because I thought of it, I didn't look to see if there is already a capability or something in the works. Just didn't want to forget.)
Steps to reproduce:
Expected Behavior:
Observed Behavior:
Working up a patch now
Hello,
After =head1 SYNOPSIS
, you need to add another newline. The first line of that code is being parsed as part of the heading.
Thanks,
-Mike
Sorry no time to write a patch, but just wanted to put this out there.
Right now when "adding" a new row it attempts to insert a null value for the primary key. This works in MySQL when auto_increment is used. I would like to see support for a new argument (maybe called "sequence") that specifies which sequence to use to generate the primary key before the insert happens. This will help with PostgreSQL support.
In case you're not familiar with sequences the idea is that I can SELECT NEXTVAL('sequencename'); to grab the next available ID for a specific sequence. It's common for non-MySQL dbs to use sequences instead of auto_increment columns (yes I know Postgres has a "serial" option that does the same thing but I am not using it).
I may be able to work on a patch for this in the next week or so, just wanted to document it as a request.
vmamn14glbsaqh:/.cpan/build/Dancer-Plugin-SimpleCRUD-0.60-Eed1B4 # perl Makefile.PL/.cpan/build/Dancer-Plugin-SimpleCRUD-0.60-Eed1B4 # make test
Checking if your kit is complete...
Looks good
Writing Makefile for Dancer::Plugin::SimpleCRUD
Writing MYMETA.yml and MYMETA.json
vmamn14glbsaqh:
cp lib/Dancer/Plugin/SimpleCRUD.pm blib/lib/Dancer/Plugin/SimpleCRUD.pm
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00-load.t ....... 1/1 # Testing Dancer::Plugin::SimpleCRUD 0.60, Perl 5.010000, /usr/bin/perl
t/00-load.t ....... ok
t/manifest.t ...... skipped: Author tests not required for installation
t/pod-coverage.t .. ok
t/pod.t ........... 1/1
Failed test 'POD test for blib/lib/Dancer/Plugin/SimpleCRUD.pm'
at /usr/lib/perl5/site_perl/5.10.0/Test/Pod.pm line 182.
blib/lib/Dancer/Plugin/SimpleCRUD.pm (1054): Non-ASCII character seen before =encoding in 'Simões'. Assuming UTF-8
Looks like you failed 1 test of 1.
t/pod.t ........... Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/1 subtests
t/pod.t (Wstat: 256 Tests: 1 Failed: 1)
Failed test: 1
Non-zero exit status: 1
Files=4, Tests=3, 1 wallclock secs ( 0.03 usr 0.00 sys + 0.42 cusr 0.05 csys = 0.50 CPU)
Result: FAIL
Failed 1/4 test programs. 1/3 subtests failed.
make: *** [test_dynamic] Error 1
The labels option is not used for non foreign key columns. The following patch fixes that. It has received only light testing but seems to work for me.
--- /home/martijn/projects/netdb/netdb/lib/perl5/Dancer/Plugin/SimpleCRUD.pm.sav 2014-03-28 12:43:04.935484105 +0000
+++ /home/martijn/projects/netdb/netdb/lib/perl5/Dancer/Plugin/SimpleCRUD.pm 2014-03-28 12:38:38.427484073 +0000
@@ -897,9 +897,11 @@
}
my $col_list = join(
',',
- map({ $table_name . "." . $dbh->quote_identifier($_) }
+ map({ $table_name . "." . $dbh->quote_identifier($_) . " AS " .
+ $dbh->quote_identifier($args->{labels}{$_} || $_)
+ }
@select_cols),
@foreign_cols, # already assembled from quoted identifiers
@custom_cols,
);
@@ -999,18 +1001,19 @@
= ($order_by_direction eq "asc") ? "desc" : "asc";
%columns_sort_options = map {
my $col_name = $_->{COLUMN_NAME};
+ my $col = $args->{labels}{$col_name} || $col_name;
my $direction = $order_by_direction;
my $direction_char = "";
if ($col_name eq $order_by_column) {
$direction = $opposite_order_by_direction;
$direction_char = ($direction eq "asc") ? "↑" : "↓";
}
my $url = _external_url($args->{dancer_prefix}, $args->{prefix})
. "?o=$col_name&d=$direction&q=$q&searchfield=$sf";
- $col_name =>
- "<a href=\"$url\">$col_name $direction_char</a>";
+ $col =>
+ "<a href=\"$url\">$col $direction_char</a>";
} @$columns;
$query
.= " ORDER BY $table_name."
Sadly, Dancer::Plugin::SimpleCRUD is currently D1-only.
I very much want to make it work with Dancer2, but just haven't had the time to spend on it yet - my spare time is spread thinly, and my paying work involves D1 primarily currently.
I would ideally like to consider trying to implement a D1/D2 plugin base class which provides a DSL for plugins to use that handles the D1/D2 differences under the hood, but I'm not sure how feasible that is.
for consistency.
recorded here for tracking.
It would be great if this module could support composite primary keys. For example:
key_column => [qw/key_1 key_2 key_3/];
I have a few tables I can't use this module for because they have composite primary keys. (I may be able to write a patch for this in the next week or so.)
It looks like the module is trying to apply any layout that's defined for the app. In my case I'm using the Template::Toolkit WRAPPER functionality because variables created in templates can't be shared with the "layout" in Dancer. Thus my "layout" is disabled.
It would be nice if I could define a simple template and then reference it in the call to simple_crud. If defined, it should be used in the route that is created. That will automatically pull in the layout or the wrapper without having to have any special logic.
See #104
If I set up users, roles and user_roles and suggested, then perform:
simple_crud( record_title => "user_roles", prefix=> "/admin/user_roles", db_table=>"user_roles",
editable => 1, sortable => 1, paginate => 500, template => 'crud',
foreign_keys => {
role_id => { table => 'roles', key_column => 'id', label_column => 'role', },
user_id => { table => 'users', key_column => 'id', label_column => 'handle', },
});
Then when I attempt to edit or add a new row to user_roles via D:P:SC, I get a 'Query was empty' error:
[16636] core @0.002779> [hit #7] --> named tokens are: id in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Route.pm l. 124
[16636] debug @0.003333> [hit #7]Executing SELECT query SELECT * FROM `user_roles` WHERE `id`=? with params 1 in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Plugin/Database.pm l. 28
[16636] debug @0.005090> [hit #7]Looking for rows for foreign relation: {'key_column' => 'id','label_column' => 'handle','table' => 'users'} in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Plugin/SimpleCRUD.pm l. 669
[16636] debug @0.005235> [hit #7]Executing SELECT query with params in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Plugin/Database.pm l. 28
[16636] error @0.009021> [hit #7]request to GET /admin/user_roles/edit/1 crashed: DBD::mysql::db selectall_arrayref failed: Query was empty at /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Plugin/Database/Core/Handle.pm line 298. in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Handler.pm l. 98
[16636] core @0.013953> [hit #7]response: 500 in /opt/perlbrew/perls/perl-5.18.4/lib/site_perl/5.18.4/Dancer/Handler.pm l. 181
Removing the foreign_keys segment stops the error from occurring.
Sometimes when I define a custom_column I'd like to combine data from multiple columns, but the custom_columns spec only accepts one input column name.
I'd love to be able to show a custom column that aggregates data from multiple columns. Perhaps something like:
custom_columns => {
result => { raw_columns=>[qw(id,name,other)], transform=>sub { ... }},
# or overload meaning of 'raw_column' if it's an arrayref
},
If there's no pushback I can work up a patch @bigpresh
Currently you can allow permission to delete rows, and to add/edit rows.
But you cannot make a simplecrud interface to a table addable but not editable, or vice-versa
If you set up a simple_crud like this:
simple_crud(
prefix => '/objects',
auth => { view => { require_role => 'admin' }, edit => { require_role => 'admin' } },
record_title => 'objects',
db_table => 'objects',
...
};
Then DPSC will allow unauthorized access to /objects/edit/:id
If I take current master and perform a 'make test', I get the following warnings:
% make test
PERL_DL_NONLAZY=1 "/opt/perlbrew/perls/perl-5.24.0/bin/perl" "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00-load.t ........ 1/1
# Testing Dancer::Plugin::SimpleCRUD 1.14, Perl 5.024000, /opt/perlbrew/perls/perl-5.24.0/bin/perl
t/00-load.t ........ ok
t/01-basic-dpsc.t .. 1/?
Use of uninitialized value in concatenation (.) or string at /home/user/gitsrc/Dancer-Plugin-SimpleCRUD/blib/lib/Dancer/Plugin/SimpleCRUD.pm line 728.
Use of uninitialized value in concatenation (.) or string at /home/user/gitsrc/Dancer-Plugin-SimpleCRUD/blib/lib/Dancer/Plugin/SimpleCRUD.pm line 728.
Use of uninitialized value in concatenation (.) or string at /home/user/gitsrc/Dancer-Plugin-SimpleCRUD/blib/lib/Dancer/Plugin/SimpleCRUD.pm line 728.
t/01-basic-dpsc.t .. ok
t/manifest.t ....... skipped: Author tests not required for installation
t/pod-coverage.t ... ok
t/pod.t ............ ok
The "editable" option doesn't work. When I set editable => [qw/some_col/] I still can edit all columns. I looked at the source and internally it's looking for "editable_columns" instead. I changed my code to say editable_columns => [qw/some_col/] and it works properly. So I think you should either change the docs to say editable_columns or change the code to use the "editable" key. I prefer the latter since "editable" is shorter.
On my FreeBSD smokers tests fail like this:
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db do failed: database is locked at t/lib/TestApp.pm line 29.
DBD::SQLite::db column_info failed: database is locked at /usr/home/eserte/.cpan/build/2016091500/Dancer-Plugin-SimpleCRUD-1.00_01-UBT3m_/blib/lib/Dancer/Plugin/SimpleCRUD.pm line 1488.
Usually this is an indication that both SQLite and File::Temp are trying to lock a temporary file. This can be fixed by using EXLOCK => 0
when creating a temporary file. On systems without EXLOCK support (most notably Linux) this is a no-op.
The current where_filter stuff is handy - but it's only used when listing records.
I think if it's there, it needs to also apply to fetching/updating/deleting records everywhere.
Otherwise, for example, if you had, for example, where_filter => { category => 'public' }
set, then only records in the "public" category would be listed or found in searches. However, if you click on one, then change the ID in the URL, you can easily retrieve/edit/delete other ones, too.
As i see it, if you're using that option, it's to enforce only a subset of records being operated on, so that should be the case everywhere.
As an aside, I'm implementing support for where_filter
being a coderef that's called at route execution time, in order to be able to, for e.g. limit the records based on the logged-in user's permissions- for instance:
...
where_filter => sub { return { company_id => logged_in_user()->{company_id} } },
...
I think that will make this feature much more useful, but until it works securely it's not that useful yet :)
@joshrabinowitz - a mention just so you'll see this, interested in your thoughts.
Greetings, thanks for your work. I need to use this plugin Dancer::Plugin::SimpleCRUD with Dancer::Plugin::Auth::RBAC because only the admin users can modify the information. However, i didn't find any way to do this. Can you help me?
I haven't worked up a simple reproducible case yet but on some tables I see this intermittently (about 50% of the time) when performing a GET on a route like 'localhost:3000/item/add'
Here's the error I see:
Warning caught during route execution: CGI::param called in list context from
/opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/CGI/FormBuilder/Field.pm line 192,
this can lead to vulnerabilities. See the warning in "Fetching the value or values of a single
named parameter" at /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/CGI.pm line 412.
/opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/CGI.pm around line 412
409 my ( $package, $filename, $line ) = caller;
410 if ( $package ne 'CGI' ) {
411 $LIST_CONTEXT_WARN++; # only warn once
412 warn "CGI::param called in list context from $filename line $line, this can lead to vulnerabilities. "
413 . 'See the warning in "Fetching the value or values of a single named parameter"';
414 }
415 }
Hello:
This is happening with a MySQL back end.
In the query below, story_id is an int data type, yet when it is rendered in simplecrud it wraps the id with % signs as if it wasa looking for a "like" string. This also happens with the id field.
Please let me know if more detail is needed.
Thanks!
[67443] debug @0.015657> [hit #3]Running query: SELECT fcs_tracker.id
,fcs_tracker.story_id
,fcs_tracker.type
,fcs_tracker.location
,fcs_tracker.asset_id
,fcs_tracker.filename
,fcs_tracker.created
, fcs_tracker.id AS actions FROM fcs_tracker WHERE story_id
= '%2904%' ORDER BY id
asc LIMIT 5 OFFSET 0 in /Library/Perl/5.8.8/Dancer/Plugin/SimpleCRUD.pm l. 754
Haven't dug into this but just noticed that if I enable auth on D:P:SC views, I get a lot of sql queries like 'SELECT * FROM users
WHERE username
=?' -- one per displayed row.
[6044] core @0.004223> [hit #1]entering before hook in /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/Dancer/Hook.pm l. 60
[6044] debug @0.012981> [hit #1]Executing SELECT query SELECT * FROM `users` WHERE `username`=? with params examp in /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/Dancer/Plugin/Database.pm l. 28
[6044] debug @0.029054> [hit #1]Running query: SELECT STUFF HERE REMOVED
[6044] debug @0.037809> [hit #1]Executing SELECT query SELECT * FROM `users` WHERE `username`=? with params examp in /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/Dancer/Plugin/Database.pm l. 28
<<< 498 repeats removed >>
[6044] debug @1.173099> [hit #1]Executing SELECT query SELECT * FROM `users` WHERE `username`=? with params examp in /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/Dancer/Plugin/Database.pm l. 28
[6044] core @1.203109> [hit #1]entering after hook in /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/Dancer/Hook.pm l. 60
Not sure if this is a D:P:SC or Dancer::Plugin::Auth::Extensible issue
show the html table from /table/view/:id route below the "are you sure you want to delete this row?" question.
recorded here for tracking.
Our Main.pm has Dancer's 'database' working just fine, but SimpleCRUD doesn't seem to find it, dying with:
Error while loading .../bin/app.psgi: Can't get a database connection without settings supplied!
Please check you've supplied settings in config as per the Dancer::Plugin::Database documentation at /Library/Perl/5.18/Dancer/Plugin/Database/Core.pm line 206, line 1.
Compilation failed in require at .../bin/app.psgi line 13, line 1.
BEGIN failed--compilation aborted at .../bin/app.psgi line 13, line 1.
I've "force" fixed it by passing in the dbh as an argument (ie. everywhere that SimpleCRUD calls database, it now says:
my $dbh = $args{dbh} // database($args{db_connection_name});
However, SimpleCRUD still doesn't work, my specified prefix:
prefix => '/edit_foo',
generates a 404 error.
Also, it's not clear to me from what format the simple_crud placeholder should be in the template file?
[ It's possible that all these issues relate to SimpleCRUD's Dancer1-ness, cf. Dancer2 which we are running? ]
I have the entire SimpleCrud sitting under prefix '/admin' and then the simple crud looks like this:
package Fan::Web::Admin;
use Dancer;
use Dancer::Plugin::SimpleCRUD qw/simple_crud/;
prefix '/admin';
simple_crud(
record_title => 'User',
prefix => '/users',
db_table => 'users',
key_column => 'user_id',
);
Accessing /admin/users shows the user list. Clicking on the link to add or edit a user results in a 404 because it's pointing to /users/add instead of /admin/users/add. I think this module should take into account the Dancer prefix it's running under.
It would be nice to be able to mark certain tables as not editable (for example, just to see the list easily). We have a "deletable" option I think it would be nice to have "editable" also (and it would default to 1 to be backwards compatible). If set, there would be no "edit" under the "actions" column. Maybe also not an "insert" link either.
Right now I set editable_columns => [] which effectively makes the add/edit form useless (it just shows a submit button) but it would be nice to not have the links as well.
Steps to reproduce:
Setup D:P:SC on a table with rows and enable sorting
Perform a search
Attempt to follow click on a column to re-sort
Expected Behavior:
Display sorts
Observed Behavior:
Error: "Warning caught during route execution: Use of uninitialized value in string eq at .../Dancer/Plugin/SimpleCRUD.pm line 968.
Working up a patch now
FYI, turning on sortable means that the smart column name translations no longer happen.
I have a PSGI application that's mounting a dancer app using SimpleCRUD. The _construct_url function doesn't detect the prefix, and generates urls on the form incorrectly.
IE:
I have SimpleCRUD setup @ http://server/app/db/client
The 'root' psgi app uses:
builder {
mount '/app'=>load_psgi "app/bin/app.pl"
}
Using this function call:
prefix '/db';
simple_crud(
record_title=>'Client',
key_column=>'id',
prefix=>'/client',
db_table=>'Client',
);
it will emit urls in the form as: http://server/db/client
phew. The explination is longer than the fix:
diff --git a/lib/Dancer/Plugin/SimpleCRUD.pm b/lib/Dancer/Plugin/SimpleCRUD.pm
index 70cb5df..ff456ca 100644
--- a/lib/Dancer/Plugin/SimpleCRUD.pm
+++ b/lib/Dancer/Plugin/SimpleCRUD.pm
@@ -642,7 +642,7 @@ sub _construct_url {
# slashes, but that shouldn't be an issue here.
my $url = '/' . join '/', @url_parts;
$url =~ s{/{2,}}{/}g;
- return $url;
+ return uri_for($url);
}
=back
If I have a two tables and a simple_crud interface like:
create table things ( id int, name varchar, category_id int);
create table categories ( id int, name varchar, );
simple_crud(
prefix=> '/things',
db_table=>'things',
foreign_keys => { strategy_id => { table=>'categories', key_column=>'id', label_column=>'name' }},
labels => { category_id=>"Category" },
);
Then when viewing the /things/add form , then for the 'category_id' field of the things table will show an input labelled 'category' even though you have to enter a 'category_id'.
In some cases this is correct (when there's only one row in the categories table, then it seems like it'll show a checkbox for that category), but if asking for a category id this is misleading.
Please provide a way to restrict which columns show up in the list of rows. For example:
columns => [qw/user_id user_name/]
At first, I'd like to say that SimpleCRUD is a great solution for quick CRUD tasks, especially as an alternative for popular ways of easy sharing of structured data (like Google Spreadsheets).
However, first advantage of DB against spreadsheet is relations between tables. If we have only id-by-id relation, it's easy and everything works like a charm, but how to deal with a one-to-many relations? E.g. if one designs a task list and needs to relate a task to several persons. I can't find a way to do it easily, without implementing all this logics manually.
Because the code naievely said if ($id) { # fetch existing data }
, if you tried to edit a record with id 0, no existing data would be fetched.
If you define multiple custom_columns for a table, the order they appear in table displays seems random.
If I have a dpsc interface on a table like 'create table users (id int, name varchar, input_element varchar)', and I visit:
http://example.com:3000/table/add?input_element=default_value
I think the form should used 'default_value' as the default for the 'input_element' input element.
Currently the /table/add route seems to ignore params. Thoughts? Easy/hard/desireable feature?
See https://travis-ci.org/joshrabinowitz/Dancer-Plugin-SimpleCRUD/jobs/203478149#L227
and
joshrabinowitz#2
Excerpt:
$ make test
...
t/00-load.t ........ 1/1 # Testing Dancer::Plugin::SimpleCRUD 1.11, Perl 5.010001, /home/travis/perl5/perlbrew/perls/5.10/bin/perl
t/00-load.t ........ ok
t/01-basic-dpsc.t .. 2/?
t/01-basic-dpsc.t:setSectionColClass: Invalid table reference <<<<----
t/01-basic-dpsc.t .. ok
t/manifest.t ....... skipped: Author tests not required for installation
t/pod-coverage.t ... ok
t/pod.t ............ ok
All tests successful.
Files=5, Tests=32, 1 wallclock secs ( 0.03 usr 0.00 sys + 0.67 cusr 0.06 csys = 0.76 CPU)
Result: PASS
The actual stderr output comes from inside HTML::Table
Is it possible to add a where clause?
Thanks!
Thanks for your module. I'm using it during a hackathon at work, so I can quickly import and edit some data in my project.
Use case: three small tables: projects, users, and project_user_xref. project_user_xref has two fields, project_id and user_id, which are foreign keys pointing to projects.id and users.id. I am currently stumped on how to modify this table via D-P-SimpleCRUD.
Similarly, I had a problem with my original users table, which had a primary key of "users.name". Since that didn't work well, I added a synthetic id field (autoinc integer primary key), and I was able to use SimpleCRUD for my users and my projects.
Thanks for any tips.
I'm not sure if this is a documentation request or a feature request.
Is there a way to customize the display of content in cells?
I could imagine specifying a subref to be called for cell content display that would receive ($cell_data, $row_data) like transform() does. Is anything like this currently possible?
in the main app:
load_app 'app_admin';
on app_admin:
prefix '/admin';
simple_crud(....
causes double prefix with newer versions:
Trying to match 'GET /admin/foo' against /^/admin/admin/foo$/ (generated from '/admin/admin/foo')
It's not possible to setup a simplecrud interface that allows rows to be deleted but not added
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.