Re: [dancer-users] SimpleCRUD question

12 Apr 2014

      Hi David,

any news/updates on request/discussion above?

wkr
rene

On Sun, Feb 9, 2014 at 2:09 PM, Rene Stoutjesdijk
<r.stoutjesdijk@gmail.com>wrote:
...
Hi David,
you're correct that it's applicable for all users :( too bad, it was to
good to be true...
On Sun, Feb 9, 2014 at 1:19 PM, David Precious <davidp@preshweb.co.uk>wrote:
...
Hi Rene,
At a glance, that will explode if there is a JOIN in use (when using
foreign keys).  I also think it makes more sense to use a set of params
that could be passed to database->quick_select, so that quoting etc to
avoid SQL injection is handled for you.
One thing I'm not sure about - passing the where query as you have will
work, but I think you wanted it to use different queries for different
users - and I think that the query you pass in the simple_crud() call
will be used for all users, so wouldn't satisfy that requirement.
Will have another look and think soon though, as I'm about to head out
to play squash :)
Cheers
Dave P
On Sun, 9 Feb 2014 12:22:36 +0100 Rene Stoutjesdijk
<r.stoutjesdijk@gmail.com> wrote:
...
Hi David,
today i did some tests and took an approach which is maybe a bit
different then you suggested.
Please see below:
i've added two keywords in my dancer app, see example below:
basic_where => 1,
    basic_where_clause => " WHERE client.`client_name`LIKE'%%rene%%'",
the rule basic_where is used as a boolean identifier to specify or
there's a basic where clause which must always be used.
the second line identifies the where clause :)
in the SimpleCRUD.pm i've added a few lines of code starting at line
910/911 (just after you define the $query)
see snapshot below, the lines between rene start and rene stop have
been added :)
QUOTE
my $query = "SELECT $col_list $add_actions FROM $table_name";
### rene start
    if ($args->{basic_where}) {
        $query .= $args->{basic_where_clause};
    }
### rene stop
# If we have foreign key relationship info, we need to join on
those tables:
    if ($args->{foreign_keys}) {
        while (my ($col, $foreign_key) = each
%{ $args->{foreign_keys} }) { my $ftable =
$dbh->quote_identifier($foreign_key->{table}); my $lkey   =
$dbh->quote_identifier($col); my $rkey   =
$dbh->quote_identifier($foreign_key->{key_column});
# Identifiers quoted above, and $table_name quoted
further up, so
            # all safe to interpolate
            my $what_to_join = $ftable;
            my $join_reference = $ftable;
            if (my $alias = $fk_alias{$col}) {
                $what_to_join = " $ftable AS $alias ";
                $join_reference = $alias;
            }
            # If this join is not a left join, the list view only
shows rows where the
            # foreign key is defined and matching a row
            $query .= " LEFT JOIN $what_to_join ON $table_name.$lkey =
$join_reference.$rkey ";
        }
    }
# If we have a query, we need to assemble a WHERE clause...
    if (params->{'q'}) {
        my ($column_data)
            = grep { lc $_->{COLUMN_NAME} eq lc
params->{searchfield} } @{$columns};
        debug(
            "Searching on $column_data->{COLUMN_NAME} which is a "
            . "$column_data->{TYPE_NAME}"
        );
if ($column_data) {
            my $search_value = params->{'q'};
            if (params->{searchtype} eq 'c') {
                $search_value = '%' . $search_value . '%';
            }
### rene start
        if ($args->{basic_where}) {
                $query
                    .= " AND $table_name."
                    . $dbh->quote_identifier(params->{searchfield})
                    . (params->{searchtype} eq 'c' ? 'LIKE' : '=')
                    . $dbh->quote($search_value);
            }
            else {
### rene stop
            $query
                .= " WHERE $table_name."
                . $dbh->quote_identifier(params->{searchfield})
                . (params->{searchtype} eq 'c' ? 'LIKE' : '=')
                . $dbh->quote($search_value);
### rene start
            }
### rene stop
            $html
UNQUOTE
I think this is helping me with the functionality for having a
specific where clause which i can set from within the dancer app.
I did some basic tests and it looks to be working fine, even no
problem with download and/or pagination. Even when having the query
within the webpage it is working as expected.
(didn't do any tests with joins or other db engines besides MySQL
or ......)
Please let me know or this is working correctly according to you. And
if yes can this be somehow integrated within SimpleCRUD.
Thx in advance for your response and support
On Sat, Feb 8, 2014 at 10:19 AM, Rene Stoutjesdijk
<r.stoutjesdijk@gmail.com
...
wrote:
...
Hi David,
thx for the answers and explanations.
Looking forward to hear from you.
I'll start looking into the option you proposed for the
custom_callbacks option as it looks the best option for now.
thx
rene
On Mon, Feb 3, 2014 at 11:29 AM, David Precious
<davidp@preshweb.co.uk>wrote:
...
On Sun, 2 Feb 2014 11:43:05 +0100
Rene Stoutjesdijk <r.stoutjesdijk@gmail.com> wrote:
...
Hi All,
first let me say that i'm not an experienced dancer user.
My experience for now is that it really works :).
I've just started to use the SimpleCRUD plugin, which makes live
very easy for this kind of actions.
I do have 2 related questions which i don't know how to resolve:
1) can i remove the option to add an entry in the database, as i
would only want the users to do an edit action (delete was easy
to remove as it's a configurable parameter).
OTTOMH, I think adding and editing are lumped together at the
moment; I'll see how difficult it would be to add a config
parameter to disallow adding, too.
...
2) can i have somewhere the option to filter (eg by a where
clause in mysql) based upon the login credentials? I would like
to limit the table view by a where clause based upon the login
name.
Hmm, there's nothing like that, currently, but it may well be
possible to add a hook in the right place that would let you
modify the query about to run, or pass extra criteria to go in the
WHERE clause.
What you could do, is use the custom_callbacks option to pass
callbacks to HTML::Table::FromDatabase to modify/skip rows upon
output - that'd be a bit hacky though, as:
- if you used the download options, you'd get the original data
   unchanged
 - if you were using pagination, filtering out rows at that step
would throw the pagination out
So, a way to supply additional criteria for the where clause via a
hook may be the way to go.  I'll take a look at that when I have a
chance, but I'm afraid I can't make any guesses as to when that'll
be - $work, and life in general at the moment, is keeping me
rather busy!
Cheers
Dave P
--
David Precious ("bigpresh") <davidp@preshweb.co.uk>
http://www.preshweb.co.uk/     www.preshweb.co.uk/twitter
www.preshweb.co.uk/linkedin    www.preshweb.co.uk/facebook
www.preshweb.co.uk/cpan        www.preshweb.co.uk/github
_______________________________________________
dancer-users mailing list
dancer-users@dancer.pm
http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
--
David Precious ("bigpresh") <davidp@preshweb.co.uk>
http://www.preshweb.co.uk/     www.preshweb.co.uk/twitter
www.preshweb.co.uk/linkedin    www.preshweb.co.uk/facebook
www.preshweb.co.uk/cpan        www.preshweb.co.uk/github
_______________________________________________
dancer-users mailing list
dancer-users@dancer.pm
http://lists.preshweb.co.uk/mailman/listinfo/dancer-users