NAME

Perinci::Sub::Gen::AccessTable - Generate function (and its metadata) to read table data

VERSION

This document describes version 0.594 of Perinci::Sub::Gen::AccessTable (from Perl distribution Perinci-Sub-Gen-AccessTable), released on 2024-05-14.

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_def   => {
        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

Usage:

gen_read_table_func(%args) -> [$status_code, $reason, $payload, \%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 => array of str

The sort argument is an ordering option, containing names of field. A - prefix before the field name signifies descending instead of ascending order. Multiple fields are allowed for secondary sort fields.
q => ARRAY[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, custom_search, default_query_boolean.
query_boolean => STR

Either and or or. Default can be set with gen argument default_query_boolean. With and, all the words in q argument must match. With or, only one of the words in q argument must match.
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.

This function is not exported by default, but exportable.

Arguments ('*' denotes required arguments):

case_insensitive_comparison => bool (default: 1)

Decide whether generated function will perform case-insensitive comparison (e.g. for FIELD.is).
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_exclude_fields => str

Supply default 'exclude_fields' value for function arg spec.
default_fields => str

Supply default 'fields' value for function arg spec.
default_query_boolean => str (default: "and")

Specify default for --query-boolean option.
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 => array[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.
detail_aliases => hash

(No 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).
exclude_fields_aliases => hash

(No description)
extra_args => hash

Extra arguments for the generated function.
extra_props => hash

Extra metadata properties for the generated function metadata.
fields_aliases => hash

(No description)
hooks => hash

Supply hooks.

You can instruct the generated function to execute codes in various stages by using hooks. Currently available hooks are (in execution order):
- 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_args (function arguments, but as hash reference so you can modify it).
- after_parse_query and later hooks will also get _parse_res (parse enveloped result)
- before_fetch_data and later will also get _query (parsed query, which is just the payload a.k.a. third element of _parse_res).
- 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). In 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[str] (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.
queries_aliases => hash

(No description)
random_aliases => hash

(No description)
result_limit_aliases => hash

(No description)
result_start_aliases => hash

(No description)
sort_aliases => hash

(No description)
summary => str

Generated function's summary.
table_data* => array|code|obj

Data.

Table data is either an AoH/AoA, or a TableData object, or a coderef.

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_def => hash

Table definition.

Required, unless table_data argument is a TableData object, in which case table definition will be retrieved from the object if not supplied.

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).
with_field_names_aliases => hash

(No description)
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'.

Returns an enveloped result (an array).

First element ($status_code) is an integer containing HTTP-like status code (200 means OK, 4xx caller error, 5xx function error). Second element ($reason) is a string containing error message, or something like "OK" if status is 200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth element (%result_meta) is called result metadata and is optional, a hash that contains extra information, much like how HTTP response headers provide additional metadata.

Return value: A hash containing generated function, metadata (hash)

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.

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.

AUTHOR

perlancar <perlancar@cpan.org>

CONTRIBUTOR

Steven Haryanto <stevenharyanto@gmail.com>

CONTRIBUTING

To contribute, you can send patches by email/via RT, or send pull requests on GitHub.

Most of the time, you don't need to build the distribution yourself. You can simply modify the code, then test via:

% prove -l

If you want to build the distribution (e.g. to try to install it locally on your system), you can install Dist::Zilla, Dist::Zilla::PluginBundle::Author::PERLANCAR, Pod::Weaver::PluginBundle::Author::PERLANCAR, and sometimes one or two other Dist::Zilla- and/or Pod::Weaver plugins. Any additional steps required beyond that are considered a bug and can be reported to me.

COPYRIGHT AND LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

BUGS

Please report any bugs or feature requests on the bugtracker website https://rt.cpan.org/Public/Dist/Display.html?Name=Perinci-Sub-Gen-AccessTable

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.

CAVEATS

It is often not a good idea to expose your database schema directly as API.

To install Perinci::Sub::Gen::AccessTable, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Perinci::Sub::Gen::AccessTable

CPAN shell

perl -MCPAN -e shell
install Perinci::Sub::Gen::AccessTable

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)