NAME
Perinci::Sub::Gen::AccessTable - Generate function (and its Rinci
metadata) to access table data
VERSION
This document describes version 0.42 of Perinci::Sub::Gen::AccessTable
(from Perl distribution Perinci-Sub-Gen-AccessTable), released on
2014-12-10.
SYNOPSIS
In list_countries.pl:
#!perl
use strict;
use warnings;
use Perinci::CmdLine;
use Perinci::Sub::Gen::AccessTable qw(gen_read_table_func);
our %SPEC;
my $countries = [
['cn', 'China', 'Cina', [qw/panda/]],
['id', 'Indonesia', 'Indonesia', [qw/bali tropical/]],
['sg', 'Singapore', 'Singapura', [qw/tropical/]],
['us', 'United States of America', 'Amerika Serikat', [qw//]],
];
my $res = gen_read_table_func(
name => 'list_countries',
summary => 'func summary', # opt
description => 'func description', # opt
table_data => $countries,
table_spec => {
summary => 'List of countries',
fields => {
id => {
schema => 'str*',
summary => 'ISO 2-letter code for the country',
pos => 0,
sortable => 1,
},
eng_name => {
schema => 'str*',
summary => 'English name',
pos => 1,
sortable => 1,
},
ind_name => {
schema => 'str*',
summary => 'Indonesian name',
pos => 2,
sortable => 1,
},
tags => {
schema => 'array*',
summary => 'Keywords/tags',
pos => 3,
sortable => 0,
},
},
pk => 'id',
},
);
die "Can't generate function: $res->[0] - $res->[1]" unless $res->[0] == 200;
Perinci::CmdLine->new(url=>'/main/list_countries')->run;
Now you can do:
# list all countries, by default only PK field is shown
$ list_countries.pl --format=text-simple
cn
id
sg
us
# show as json, randomize order
$ list_countries.pl --format=json --random
["id","us","sg","cn"]
# only list countries which are tagged as 'tropical', sort by ind_name field in
# descending order, show all fields (--detail)
$ list_countries.pl --detail --sort -ind_name --tags-has '[tropical]'
.---------------------------------------------.
| eng_name | id | ind_name | tags |
+-----------+----+-----------+----------------+
| Singapore | sg | Singapura | tropical |
| Indonesia | id | Indonesia | bali, tropical |
'-----------+----+-----------+----------------'
# show only certain fields, limit number of records, return in YAML format
$ list_countries.pl --fields '[id, eng_name]' --result-limit 2 --format=yaml
- 200
- OK
-
- id: cn
eng_name: China
- id: id
eng_name: Indonesia
DESCRIPTION
This module is useful when you want to expose a table data (an array of
hashrefs, an array of arrays, or external data like a SQL table) as an
API function. This module will generate a function (along with its Rinci
metadata) that accepts arguments for specifying fields, filtering,
sorting, and paging. The resulting function can then be run via
command-line using Perinci::CmdLine (as demonstrated in Synopsis), or
served via HTTP using Perinci::Access::HTTP::Server, or consumed
normally by Perl programs.
FUNCTIONS
gen_read_table_func(%args) -> [status, msg, result, meta]
Generate function (and its metadata) to read table data.
The generated function acts like a simple single table SQL SELECT query,
featuring filtering, ordering, and paging, but using arguments as the
'query language'. The generated function is suitable for exposing a
table data from an API function.
The resulting function returns an array of results/records and accepts
these arguments.
* *with_field_names* => BOOL (default 1)
If set to 1, function will return records of field values along with
field names (hashref), e.g. {id=>'ID', country=>'Indonesia',
capital=>'Jakarta'}. If set to 0, then function will return record
containing field values without field names (arrayref) instead,
e.g.: ['ID', 'Indonesia', 'Jakarta'].
* *detail* => BOOL (default 0)
This is a field selection option. If set to 0, function will return
PK field only. If this argument is set to 1, then all fields will be
returned (see also *fields* to instruct function to return some
fields only).
* *fields* => ARRAY
This is a field selection option. If you only want certain fields,
specify them here (see also *detail*).
* *result_limit* => INT (default undef)
* *result_start* => INT (default 1)
The *result_limit* and *result_start* arguments are paging options,
they work like LIMIT clause in SQL, except that index starts at 1
and not 0. For example, to return the first 20 records in the
result, set *result_limit* to 20 . To return the next 20 records,
set *result_limit* to 20 and *result_start* to 21.
* *random* => BOOL (default 0)
The random argument is an ordering option. If set to true, order of
records returned will be shuffled first. This happened before
paging.
* *sort* => STR
The sort argument is an ordering option, containing name of field. A
- prefix signifies descending instead of ascending order. Multiple
fields are allowed, separated by comma.
* *q* => STR
A filtering option. By default, all fields except those specified
with searchable=0 will be searched using simple case-insensitive
string search. There are a few options to customize this, using
these gen arguments: *word_search*, *case_insensitive_search*, and
*custom_search*.
* Filter arguments
They will be generated for each field, except when field has
'filterable' clause set to false.
Undef values will not match any filter, just like NULL in SQL.
* *FIELD.is* and *FIELD.isnt* arguments for each field. Only
records with field equalling (or not equalling) value exactly
('==' or 'eq') will be included. If doesn't clash with other
function arguments, *FIELD* will also be added as an alias for
*FIELD.is*.
* *FIELD.has* and *FIELD.lacks* array arguments for each set
field. Only records with field having or lacking certain value
will be included.
* *FIELD.min* and *FIELD.max* for each int/float/str field. Only
records with field greater/equal than, or less/equal than a
certain value will be included.
* *FIELD.contains* and *FIELD.not_contains* for each str field.
Only records with field containing (or not containing) certain
value (substring) will be included.
* *FIELD.matches* and *FIELD.not_matches* for each str field. Only
records with field matching (or not matching) certain value
(regex) (or will be included. Function will return 400 if regex
is invalid. These arguments will not be generated if
'filterable_regex' clause in field specification is set to 0.
Arguments ('*' denotes required arguments):
* case_insensitive_search => *bool* (default: 1)
Decide whether generated function will perform case-insensitive
search.
* custom_filters => *hash*
Supply custom filters.
A hash of filter name and definitions. Filter name will be used as
generated function's argument and must not clash with other
arguments. Filter definition is a hash containing these keys: *meta*
(hash, argument metadata), *code*, *fields* (array, list of table
fields related to this field).
Code will be called for each record to be filtered and will be
supplied ($r, $v, $opts) where $v is the filter value (from the
function argument) and $r the hashref record value. $opts is
currently empty. Code should return true if record satisfies the
filter.
* custom_search => *code*
Supply custom searching for generated function.
Code will be supplied ($r, $q, $opts) where $r is the record
(hashref), $q is the search term (from the function argument 'q'),
and $opts is {ci=>0|1}. Code should return true if record matches
search term.
* default_arg_values => *hash*
Specify defaults for generated function's arguments.
Can be used to supply default filters, e.g.
# limit years for credit card expiration date
{ "year.min" => $curyear, "year.max" => $curyear+10, }
* default_detail => *bool*
Supply default 'detail' value for function arg spec.
* default_fields => *str*
Supply default 'fields' value for function arg spec.
* default_random => *bool*
Supply default 'random' value in generated function's metadata.
* default_result_limit => *int*
Supply default 'result_limit' value in generated function's
metadata.
* default_sort => *str*
Supply default 'sort' value in generated function's metadata.
* default_with_field_names => *bool*
Supply default 'with_field_names' value in generated function's
metadata.
* description => *str*
Generated function's description.
* enable_field_selection => *bool* (default: 1)
Decide whether generated function will support field selection (the
`fields` argument).
* enable_filtering => *bool* (default: 1)
Decide whether generated function will support filtering (the FIELD,
FIELD.is, FIELD.min, etc arguments).
* enable_ordering => *bool* (default: 1)
Decide whether generated function will support ordering (the `sort`
& `random` arguments).
* enable_paging => *bool* (default: 1)
Decide whether generated function will support paging (the
`result_limit` & `result_start` arguments).
* enable_random_ordering => *bool* (default: 1)
Decide whether generated function will support random ordering (the
`random` argument).
Ordering must also be enabled ("enable_ordering").
* enable_search => *bool* (default: 1)
Decide whether generated function will support searching (argument
q).
Filtering must also be enabled ("enable_filtering").
* extra_args => *hash*
Extra arguments for the generated function.
* hooks => *hash*
Supply hooks.
You can instruct the generated function to execute codes in various
stages by using hooks. Currently available hooks are:
"before_parse_query", "after_parse_query", "before_fetch_data",
"after_fetch_data", "before_return". Hooks will be passed the
function arguments as well as one or more additional ones. All hooks
will get "_stage" (name of stage) and "_func_res" (function
arguments, but as hash reference so you can modify it).
"after_parse_query" and later hooks will also get "_parse_res"
(parse result). "before_fetch_data" and later will also get
"_query". "after_fetch_data" and later will also get "_data".
"before_return" will also get "_func_res" (the enveloped response to
be returned to user).
Hook should return nothing or a false value on success. It can abort
execution of the generated function if it returns an envelope
response (an array). On that case, the function will return with
this return value.
* install => *bool* (default: 1)
Whether to install generated function (and metadata).
By default, generated function will be installed to the specified
(or caller's) package, as well as its generated metadata into %SPEC.
Set this argument to false to skip installing.
* langs => *array* (default: ["en_US"])
Choose language for function metadata.
This function can generate metadata containing text from one or more
languages. For example if you set 'langs' to ['en_US', 'id_ID'] then
the generated function metadata might look something like this:
{
v => 1.1,
args => {
random => {
summary => 'Random order of results', # English
"summary.alt.lang.id_ID" => "Acak urutan hasil", # Indonesian
...
},
...
},
...
}
* name => *str*
Generated function's name, e.g. `myfunc`.
* package => *str*
Generated function's package, e.g. `My::Package`.
This is needed mostly for installing the function. You usually don't
need to supply this if you set "install" to false.
If not specified, caller's package will be used by default.
* summary => *str*
Generated function's summary.
* table_data* => *array|code*
Data.
Table data is either an AoH or AoA. Or you can also pass a Perl
subroutine (see below).
Passing a subroutine lets you fetch data dynamically and from
arbitrary source (e.g. DBI table or other external sources). The
subroutine will be called with these arguments ('$query') and is
expected to return a hashref like this {data => DATA, paged=>BOOL,
filtered=>BOOL, sorted=>BOOL, fields_selected=>BOOL}. DATA is AoA or
AoH. If paged is set to 1, data is assumed to be already paged and
won't be paged again; likewise for filtered, sorted, and fields
selected. These are useful for example with DBI result, where
requested data is already filtered/sorted (including
randomized)/field selected/paged via appropriate SQL query. This
way, the generated function will not attempt to duplicate the
efforts.
'$query' is a hashref which contains information about the query,
e.g. 'args' (the original arguments passed to the generated
function, e.g. {random=>1, result_limit=>1, field1_match=>'f.+'}),
'mentioned_fields' which lists fields that are mentioned in either
filtering arguments or fields or ordering, 'requested_fields'
(fields mentioned in list of fields to be returned), 'sort_fields'
(fields mentioned in sort arguments), 'filter_fields' (fields
mentioned in filter arguments).
* table_spec* => *hash*
Table specification.
See "TableDef" for more details.
A hashref with these required keys: 'fields', 'pk'. 'fields' is a
hashref of field specification with field name as keys, while 'pk'
specifies which field is to be designated as the primary key.
Currently only single-field PK is allowed.
Field specification. A hashref with these required keys: 'schema' (a
Sah schema), 'index' (an integer starting from 0 that specifies
position of field in the record, required with AoA data) and these
optional clauses: 'sortable' (a boolean stating whether field can be
sorted, default is true), 'filterable' (a boolean stating whether
field can be mentioned in filter options, default is true).
* word_search => *bool* (default: 0)
Decide whether generated function will perform word searching
instead of string searching.
For example, if search term is 'pine' and field value is 'green
pineapple', search will match if word_search=false, but won't match
under word_search.
This will not have effect under 'custom_search'.
Return value:
Returns an enveloped result (an array).
First element (status) is an integer containing HTTP status code (200
means OK, 4xx caller error, 5xx function error). Second element (msg) is
a string containing error message, or 'OK' if status is 200. Third
element (result) is optional, the actual result. Fourth element (meta)
is called result metadata and is optional, a hash that contains extra
information.
A hash containing generated function, metadata (hash)
CAVEATS
It is often not a good idea to expose your database schema directly as
API.
FAQ
I want my function to accept additional arguments.
You can use the "extra_args" argument:
gen_read_table_func(
name => 'myfunc',
extra_args => {
foo => {schema=>'int*'},
bar => {summary => 'Yet another arg for myfunc', schema=>'str*'},
},
);
As for the implementation, you can specify hooks to do things with the
extra arguments.
SEE ALSO
Rinci
Perinci::CmdLine
HOMEPAGE
Please visit the project's homepage at
<https://metacpan.org/release/Perinci-Sub-Gen-AccessTable>.
SOURCE
Source repository is at
<https://github.com/perlancar/perl-Perinci-Sub-Gen-AccessTable>.
BUGS
Please report any bugs or feature requests on the bugtracker website
<https://rt.cpan.org/Public/Dist/Display.html?Name=Perinci-Sub-Gen-Acces
sTable>
When submitting a bug or request, please include a test-file or a patch
to an existing test-file that illustrates the bug or desired feature.
AUTHOR
perlancar <perlancar@cpan.org>
COPYRIGHT AND LICENSE
This software is copyright (c) 2014 by perlancar@cpan.org.
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.