NAME
Sub::Spec::Gen::ReadTable - Generate function (and its spec) to read table data
VERSION
version 0.01
SYNOPSIS
In list_countries.pl:
#!perl
use strict;
use warnings;
use Sub::Spec::CmdLine qw(run);
use Sub::Spec::Gen::ReadTable 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(
table_data => $countries,
table_spec => {
summary => 'List of countries',
columns => {
id => ['str*' => {
summary => 'ISO 2-letter code for the country',
column_index => 0,
column_sortable => 1,
}],
en_name => ['str*' => {
summary => 'English name',
column_index => 1,
column_sortable => 1,
}],
id_name => ['str*' => {
summary => 'Indonesian name',
column_index => 2,
column_sortable => 1,
}],
tags => ['array*' => {
summary => 'Keywords/tags',
column_index => 3,
column_sortable => 0,
}],
},
pk => 'id',
},
);
die "Can't generate function: $res->[0] - $res->[1]" unless $res->[0] == 200;
*list_countries = $res->[2]{code};
$SPEC{list_countries} = $res->[2]{spec};
run(load=>0, module=>'main', sub=>'list_countries');Now you can do:
# list all countries, by default only PK column is shown
$ list_countries.pl --nopretty
cn
id
sg
us
# show as json, randomize order
$ list_countries.pl --json --random
["id","us","sg","cn"]
# only list countries which are tagged as 'tropical', sort by id_name column in
# descending order, show all columns (--detail)
$ list_countries.pl --detail --sort -id_name --tags-has '[tropical]'
.---------------------------------------------.
| en_name | id | id_name | tags |
+-----------+----+-----------+----------------+
| Singapore | sg | Singapura | tropical |
| Indonesia | id | Indonesia | bali, tropical |
'-----------+----+-----------+----------------'
# show only certain fields, limit number of rows, return in YAML format
$ list_countries.pl --fields '[id, en_name]' --result-limit 2 --yaml
---
- id: cn
en_name: China
- id: id
en_name: IndonesiaDESCRIPTION
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 that accepts arguments for specifying fields, filtering, sorting, and paging; along with its Sub::Spec spec. The resulting function can then be run via command-line using Sub::Spec::CmdLine (as demonstrated in Synopsis), or served via REST using Sub::Spec::HTTP::Server, or consumed normally by Perl programs.
This module uses Log::Any for logging framework.
This module's functions has Sub::Spec specs.
FUNCTIONS
None are exported by default, but they are exportable.
gen_read_table_func(%args) -> [STATUS_CODE, ERR_MSG, RESULT]
Generate function (and its spec) to read table data.
The generated function acts like a simple single table SQL SELECT query, featuring filtering, sorting, and paging, but using arguments as the 'query language'. The generated function is suitable for exposing a table data from an API function.
The generated spec is pretty barebones currently. You can decorate with summary and description afterwards.
* Resulting function
The resulting function will accept these arguments. The naming of arguments are designed to be less Perl-/database-centric.
** *show_field_names* => BOOL (default 1)
By default function will return AoH. If this argument is set to 0, then function will return AoA instead.
** *detail* => BOOL (default 0)
This is a field selection option. By default, function will return PK column only. If this argument is set to true, then all columns will be returned.
** *fields* => ARRAY
This is a field selection option. If you only want certain fields, specify them here.
** *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.
** *random* => BOOL (default 0)
The random argument is an ordering option. If set to true, order of rows 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 will be searched using simple case-insensitive string search. In the future, a method to customize searching will be allowed.
** Filter arguments
They will be generated for each column, except when column has 'column_filterable' clause set to false.
Undef values will not match any filter, just like NULL in SQL.
If a field name clashes with a general argument name (e.g. *q* or *sort*) then it will be suffixed with '_field' (e.g. *q_field* or *sort_field*).
*** "FIELD" boolean argument for each boolean field
*** "has_FIELD" and "lacks_FIELD" array arguments for each set field
*** "min_FIELD"/"max_FIELD"/"FIELD" arguments for each int/float/str
*** "FIELD_contain" string argument for each str field
*** "FIELD_match" and "FIELD_not_match" regex argument for each str field
Will not be generated if column_filterable_regex clause in column specification is set to 0.
*** "FIELD_starts_with" string argument for each str field (not implemented)
*** "FIELD_ends_with" string argument for each str field (not implemented)
Returns a 3-element arrayref. STATUS_CODE is 200 on success, or an error code between 3xx-5xx (just like in HTTP). ERR_MSG is a string containing error message, RESULT is the actual result.
Arguments (* denotes required arguments):
case_insensitive_search => bool (default
1)Generated function will perform case-insensitive search.
custom_search => code
Supply custom searching for generated function.
Code will be supplied ($row, $q, $opts) where $q is the search term (the argument q) and $row the hashref row value. $opts is {ci=>0|1}. Code should return true if row matches search term.
default_detail => bool
Supply default 'detail' value for function spec.
default_fields => str
Supply default 'fields' value for function spec.
default_random => bool
Supply default 'random' value for function spec.
default_result_limit => int
Supply default 'result_limit' value for function spec.
default_show_field_names => bool
Supply default 'show_field_names' value for function spec.
default_sort => str
Supply default 'sort' value for function spec.
enable_search => bool (default
1)Generated function will support searching (argument q).
table_data* =>
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. The subroutine will be called with these arguments ($query) and is expected to return an AoA or AoH. $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=>'foo'}), '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), etc.
The subroutine can do filtering/ordering/paging beforehand for efficiency, e.g. SELECT-ing from a DBI table using the appropriate columns, ORDER, WHERE, and LIMIT clauses. Either way, for consistency, the generated function will still apply filtering/ordering/paging to the data returned by this subroutine, so the subroutine can choose to pass the complete table data anyway.
table_spec* => hash
Table specification.
A hashref with these required keys: columns, pk. Columns is a hashref of column specification with column name as keys, while table_pk specifies which column is to be designated as the primary key. Currently only single-column PK is allowed.
* Column specification
A Sah schema with these required clauses: column_index (an integer starting from 0 that specifies position of column in the data, especially required with AoA data) and these optional clauses: column_sortable (a boolean stating whether column can be sorted, default is true), column_filterable (a boolean stating whether column can be mentioned in filter options).
word_search => bool (default
0)Generated function will perform word searching instead of string searching.
For example, if search term is 'pine' and column value is 'green pineapple', search will match if word_search=false, but won't match under word_search.
FAQ
SEE ALSO
AUTHOR
Steven Haryanto <stevenharyanto@gmail.com>
COPYRIGHT AND LICENSE
This software is copyright (c) 2011 by Steven Haryanto.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.