NAME

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

VERSION

This document describes version 0.18 of Perinci::Sub::Gen::AccessTable::DBI (from Perl distribution Perinci-Sub-Gen-AccessTable-DBI), released on 2017-07-10.

SYNOPSIS

Your database table countries:

| id | eng_name                 | ind_name        |
|----+--------------------------+-----------------|
| cn | China                    | Cina            |
| id | Indonesia                | Indonesia       |
| sg | Singapore                | Singapura       |
| us | United States of America | Amerika Serikat |

In list_countries.pl:

#!perl
use strict;
use warnings;
use Perinci::CmdLine;
use Perinci::Sub::Gen::AccessTable::DBI qw(gen_read_dbi_table_func);

our %SPEC;

my $res = gen_read_dbi_table_func(
    name        => 'list_countries',
    summary     => 'func summary',     # opt
    description => 'func description', # opt
    dbh         => ...,
    table_name  => 'countries',
    table_spec  => {
        summary => 'List of countries',
        fields => {
            id => {
                schema => 'str*',
                summary => 'ISO 2-letter code for the country',
                index => 0,
                sortable => 1,
            },
            eng_name => {
                schema => 'str*',
                summary => 'English name',
                index => 1,
                sortable => 1,
            },
            ind_name => {
                schema => 'str*',
                summary => 'Indonesian name',
                index => 2,
                sortable => 1,
            },
        },
        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 contain 'Sin', show all fields (--detail)
$ list_countries.pl --q=Sin --detail
.----------------------------.
| eng_name  | id | ind_name  |
+-----------+----+-----------+
| Singapore | sg | Singapura |
'-----------+----+-----------+

# 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 just like Perinci::Sub::Gen::AccessTable, except that table data source is from DBI. gen_read_dbi_table_func() accept mostly the same arguments as gen_read_table_func(), except: 'table_name' instead of 'table_data', and 'dbh'.

Supported databases: SQLite, MySQL, PostgreSQL.

Early versions tested on: SQLite.

CAVEATS

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

FUNCTIONS

gen_read_dbi_table_func

Usage:

gen_read_dbi_table_func(%args) -> [status, msg, result, meta]

Generate function (and its metadata) to read DBI table.

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. Please see Perinci::Sub::Gen::AccessTable's documentation for more details on what arguments the generated function will accept.

This function is not exported by default, but exportable.

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.
dbh => obj

DBI database handle.
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 => 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
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.
fields_aliases => hash
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[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.
query_aliases => hash
random_aliases => hash
result_limit_aliases => hash
result_start_aliases => hash
sort_aliases => hash
summary => str

Generated function's summary.
table_name* => str

DBI table name.
table_spec* => hash

Table specification.

Just like Perinci::Sub::Gen::AccessTable's table_spec, except that each field specification can have a key called db_field to specify the database field (if different). Currently this is required. Future version will be able to generate table_spec from table schema if table_spec is not specified.
with_field_names_aliases => hash
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) 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.

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

FAQ

HOMEPAGE

Please visit the project's homepage at https://metacpan.org/release/Perinci-Sub-Gen-AccessTable-DBI.

SOURCE

Source repository is at https://github.com/perlancar/perl-Perinci-Sub-Gen-AccessTable-DBI.

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-DBI

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 is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

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

cpanm

cpanm Perinci::Sub::Gen::AccessTable::DBI

CPAN shell

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

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)