bigpresh / dancer-plugin-simplecrud Goto Github PK

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

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     }

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.

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.

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

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

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

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?

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?

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.

Is it possible to add a where clause?

Thanks!

If you define multiple custom_columns for a table, the order they appear in table displays seems random.

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.

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.

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.

Please provide a way to restrict which columns show up in the list of rows. For example:

columns => [qw/user_id user_name/]

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

It's not possible to setup a simplecrud interface that allows rows to be deleted but not added

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.

FYI, turning on sortable means that the smart column name translations no longer happen.

for consistency.
recorded here for tracking.

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.

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.

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

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.

See #104

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?

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

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.

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.

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

Steps to reproduce:

• Setup D:P:SC on a table with M rows and enable sorting and pagination for N rows, where N<M.
• Perform a search returning more than N rows, and attempt to follow the 'next page' link to the second page

Expected Behavior:

• Next page shown

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

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&nbsp;$direction_char</a>";
+            $col =>
+                "<a href=\"$url\">$col&nbsp;$direction_char</a>";
         } @$columns;

         $query
             .= " ORDER BY $table_name."

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

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

vmamn14glbsaqh:/.cpan/build/Dancer-Plugin-SimpleCRUD-0.60-Eed1B4 # perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Dancer::Plugin::SimpleCRUD
Writing MYMETA.yml and MYMETA.json
vmamn14glbsaqh:/.cpan/build/Dancer-Plugin-SimpleCRUD-0.60-Eed1B4 # make test
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

Test Summary Report

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

Sorry no time to write a patch, but just wanted to put this out there.

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.

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

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

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.