Skip to content

Commit

Permalink
* The [jsonq] tag generates a record in a table (by default C<qc>)
Browse files Browse the repository at this point in the history 
  that allows users to access JSON records created by a query. The
  query associated with the record will be run with any parameters
  that are specified being taken either from 1) CGI variables or 2)
  the path info.

* Adds QueryCache directive that speficies the "introducer", the URL
  fragment which calls the query cache short circuit, the table to be
  used, the content-type,

* Adds hook early in the cycle to vet queries based on Session ID
  cookie and IP address, allowing the query to happen before sessions
  are attached or deep page initialization completes. This improves
  performance dramatically.

* Adds access routine in Vend::Data to actually process the query
  and access the tables in question.

* Hooks to allow an external program (eg will be added soon) to
  totally bypass IC for even lower overhead for queries.
  • Loading branch information
@perusionmike
perusionmike committed Nov 19, 2015
1 parent a260c44 commit 75fbcbe
Show file tree
Hide file tree
Showing 5 changed files with 586 additions and 1 deletion.
3 changes: 3 additions & 0 deletions WHATSNEW-5.8
View file
Expand Up @@ -280,4 +280,7 @@ External tool support

* Added jEdit support for [PREFIX-calc] blocks, now syntax highlights contents as perl code.

* Add secure JSON query generation for interface with AJAX widgets. Allows generation of
queries vetted by login, with either public access or access gated by session.

(end)
293 changes: 293 additions & 0 deletions code/UI_Tag/jsonq.coretag
View file
@@ -0,0 +1,293 @@
UserTag jsonq Order params public query
UserTag jsonq addAttr
UserTag jsonq Routine <<EOR
sub {
my ($params, $public, $query, $opt) = @_;
my $qc = $Vend::Cfg->{QueryCache} or return undef;
my $tab = $qc->{table};
my $db = dbref($tab)
or do {
::logError("%s: missing table %s", 'query-cache', $tab);
return;
};
my $intro = $qc->{intro};

## Need to undef it if wrong because of vendURL
my $external = $qc->{external_program} || $opt->{external_program};
undef $external unless $external =~ m{^\w+:};

#::logDebug("External=$external");
# QC Table fields
# code qid session qtext meta params public secure update_date expire_date results

my $exp_sess = '0';
my $exp_addr = '0';
my $exp_secure = '0';
my $exp_hash = '0';
my $exp_meta = '0';
my $exp_view = '0';
my $exp_term = '0';

$opt->{expire} ||= $public ? $qc->{default_public_expire} : $qc->{default_expire};

$public or $exp_sess = $Vend::SessionID;
$opt->{ip} and $exp_addr = $CGI::remote_addr;
$opt->{secure} and $exp_secure = 1;
$opt->{params} and $exp_term = $opt->{params};
$opt->{hash} and $exp_hash = $opt->{hash};
$opt->{meta} and $exp_meta = $opt->{meta};
$opt->{meta_view} and $exp_view = $opt->{meta_view};

#::logDebug("hash=$opt->{hash}");
my $qid = Vend::Util::generate_key(join '|', $query, $exp_sess, $exp_addr, $exp_term, $exp_secure, $exp_hash,$exp_meta,$exp_view);

CHECKEXIST: {
if(my $exist = $db->row_hash('qid',$qid) ) {
if(my $ed = $exist->{expire_date}) {
$ed =~ s/\D+//g;
last CHECKEXIST if $ed lt POSIX::strftime('%Y%m%d%H%M%S', localtime());
}
return Vend::Util::vendUrl("$intro/$qid", undef, $external, { secure => $exist->{secure}, add_dot_html => 0 });
}
}

my $rec = {
qtext => $query,
qid => $qid,
public => $public,
secure => $opt->{secure},
hash => $opt->{hash},
params => $params,
meta_view => $opt->{meta_view},
meta => $opt->{meta},
content_type => $opt->{content_type},
template => $opt->{template},
};

if($opt->{expire} =~ /\D/ or length($opt->{expire}) < 7) {
my $add = $opt->{expire} =~ /[a-z]/ ? Vend::Config::time_to_seconds($opt->{expire}) : $opt->{expire} ;
$rec->{expire_date} = POSIX::strftime('%Y%m%d%H%M%S', localtime( time() + $add ));
}
else {
$rec->{expire_date} = $opt->{expire};
}

$rec->{session} = $Vend::SessionID unless $public;
$rec->{ipaddr} = $CGI::remote_addr if $opt->{ip};
$db->set_slice($qid, $rec);
return Vend::Util::vendUrl("$intro/$qid", undef, $external, { secure => $rec->{secure}, no_session => 1, add_dot_html => 0 });
}
EOR


UserTag jsonq Documentation <<EOD
=head2 NAME

[jsonq] - Ajax query generation with security

=head2 SYNOPSIS

[jsonq
query="select field1,field2,field3 ..."
expire="30min|3 days|86400|20170511"
public="0|1"
hash="0|1|field"
meta="option=value"
meta-view="metaview"
ip="0|1"
]

NOTE: only the query is required

=head2 CONFIGURATION

QueryCache enabled 1
QueryCache table qc
QueryCache intro qc
QueryCache default_expire 30min
QueryCache default_public_expire 48hours
QueryCache default_return {}

=head2 PREREQUISITES

Module JSON
Module Digest::MD5
Module SQL::Statement
Module SQL::Parser

=head2 DESCRIPTION

The [jsonq] tag generates a record in a table (by default C<qc>) that allows users to access JSON records
created by a query. The query associated with the record will be run with any parameters that are specified
being taken either from 1) CGI variables or 2) the path info.

The return value of [jsonq] is a URL to access the query.

The URL used short circuits the usual Interchange session and catalog configuration mechanisms in Dispatch.pm,
allowing fast (up to 3 times faster) access to JSON records. Alternatively, there can be an external handler
for requests that could increase speed dramatically.

The tag is standard, and is in the UserTag code area. It is enabled by specifying any setting for the
QueryCache directive, by default "enabled 1".

=head2 The table

The C<qc> table has the following structure (in MySQL, other databases could be used):

+--------------+--------------+------+-----+-------------------+
| Field | Type | Null | Key | Default |
+--------------+--------------+------+-----+-------------------+
| qid | varchar(32) | NO | PRI | NULL |
| session | varchar(64) | YES | | NULL |
| ipaddr | varchar(16) | YES | | NULL |
| qtext | text | NO | | |
| verbatim | tinyint(1) | YES | | |
| meta_view | varchar(255) | YES | | NULL |
| meta | text | YES | | NULL |
| cols | varchar(255) | YES | | NULL |
| content_type | varchar(128) | YES | | NULL |
| params | text | YES | | NULL |
| template | text | YES | | NULL |
| public | char(1) | YES | | |
| secure | char(1) | YES | | |
| hash | varchar(32) | YES | | |
| update_date | timestamp | NO | | CURRENT_TIMESTAMP |
| expire_date | datetime | YES | | NULL |
| results | text | YES | | NULL |
+--------------+--------------+------+-----+-------------------+

When the [jsonq] tag is run, the parameters act on the table in this way:

=over 4

=item query

Enters the table as C<qtext>. This is the actual query that will run, and
is possibly affected by CGI paramers C<mv_matchlimit> and C<mv_first_match>.

=item public

Enters table as C<public> field. If this is set, query is accessible to anyone.
Do not use on private data sets.

=item params

The name of the CGI variables that will be inserted in place of any placeholders
in the query. This uses DBI methodology, so it is secure and will not allow
SQL injection. If you wish to use the parameter in a C<LIKE> query, then append
a C<%> character to the parameter, i.e.

[jsonq params="q%" query="select * from products where description like ?"]

This causes the value of C<$CGI->{q} / [cgi q]> to be inserted surrounded by
the percent signs, causing LIKE to work with partial strings.

If you wish to use the parameter in a C<LIKE> query but only match the beginning
of the string, then I<prepend> a C<^> character to the parameter, i.e.

[jsonq params="^q" query="select * from products where description like ?"]

This causes the value of C<$CGI->{q} / [cgi q]> to be inserted followed by
the percent signs, causing LIKE to work with the first part of the string
anchored.

By default, searches are rejected (returning C<default_return>) until the
search parameter is 3 characters long. This prevents large query returns
early in parameter typing, possibly overloading the database server.
If you wish to start searching at a lower threshold (or a higher one)
then append a colon followed by a digit:

[jsonq params="^q:1" query="select * from products where description like ?"]

This causes the query to be done the moment the C<q> parameter has a single
character. A C<4> would delay return until four characters are reached,
etc.

=item hash

Enters table as C<hash> field. If this is blank, the query when run returns an "array of
arrays" in JSON. If it is set to digits only, normally 1, then the query will return
an array of hashes. If it is set to a field name, this is the field that will be used
to create a hash of hashes. Normally you would only use a unique key for that.

=item meta_view

Selects the I<meta view> which will operate on the JSON query output. This allows
you, typically, to run Interchange filters on the output which will transform the
output data from the query.

NOTE: If you are using the external CGI delivery mechanism, this will be ignored.

=item meta

Metadata options which will operate on the JSON query output. This allows
setting other values (such as jui_datagrid to sculpt response).

NOTE: If you are using the external CGI delivery mechanism, this will be ignored.

=item template

If you don't want JSON out, you can iterate over any array that you produce
and output text or HTML based on the Interchange I<attr_list> format. The
special areas

{PRE_TEMPLATE} Pre text {/PRE_TEMPLATE}
{POST_TEMPLATE} Post text {/POST_TEMPLATE}

allow you to add text to the template that will not be iterted over.
This invocation:

[tmpn tpl]
{PRE_TEMPLATE}<ul>{/PRE_TEMPLATE}
<li>{SKU} - {DESCRIPTION}</li>
{POST_TEMPLATE}</ul>{/POST_TEMPLATE}
[/tmpn]
[jsonq
query="select sku,description from products where description like '%Nails%'"
template="[scratch tpl]" hash=1 content-type="text/html"
]

Will produce something like this when the query is run:

<ul>
<li>os28057a - 16 Penny Nails</li>
<li>os28057b - 10 Penny Nails</li>
<li>os28057c - 8 Penny Nails</li>
</ul>

This will work no matter the state of the C<hash> parameter, as the fields are
determined. (It is probably best to use hash=1 for this query.)

=item content-type

This parameter will allow you to change the MIME type of the output from
the default of C<application/json>.

=back

=head2 URL

Here is a typical URL generated (for a catalog with a VendURL of http://www.perusion.com/c/strap):

http://www.perusion.com/c/strap/qc/059aba1aaee1debb4ecd3c67dd039e80

You can specify the URL intro with the C<intro> configuration parameter.
When it is set to C<qc>, it disables any URLs in the catalog that
begin with /qc/ and short circuits their delivery to the routine which
generates JSON.

You can manage the presentation of the query with the C<mv_matchlimit>
CGI parameter. If you specify C<mv_first_match> in addition, you can
set up paging. (Note those are remapped to C<ml> and C<fm> in most
standard Interchange catalogs. You should take account of this if
using the external CGI method.)

NOTE: mv_first_match will not work without mv_matchlimit.

=head2 AUTHOR

Mike Heins, <mikeh@perusion.com>

=cut

EOD
12 changes: 12 additions & 0 deletions lib/Vend/Config.pm
View file
Expand Up @@ -171,6 +171,7 @@ my %HashDefaultBlank = (qw(
Mail 1
Accounting 1
Levy 1
QueryCache 1
));

my %DumpSource = (qw(
Expand Down Expand Up @@ -722,6 +723,7 @@ sub catalog_directives {
['BounceReferralsRobot', 'yesno', 'no'],
['BounceRobotSessionURL', 'yesno', 'no'],
['OrderCleanup', 'routine_array', ''],
['QueryCache', 'hash', ''],
['SessionCookieSecure', 'yesno', 'no'],
['SessionHashLength', 'integer', 1],
['SessionHashLevels', 'integer', 2],
Expand Down Expand Up @@ -3598,6 +3600,16 @@ sub set_default_search {
push @Dispatches, 'DiscountSpaces';
return 1;
},
QueryCache => sub {
my $qc;
return 1 unless $qc = $C->{QueryCache};
$qc->{table} ||= 'qc';
$qc->{intro} ||= 'qc';
$qc->{default_expire} ||= '30min';
$qc->{default_public_expire} ||= '48hours';
$qc->{default_return} ||= '{}';
return 1;
},
CookieLogin => sub {
return 1 unless $C->{CookieLogin};
push @Dispatches, 'CookieLogin';
Expand Down

0 comments on commit 75fbcbe

Please sign in to comment.