NAME Sub::Spec::Gen::ReadTable - Generate function (and its spec) to read table data VERSION version 0.06 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: Indonesia DESCRIPTION NOTICE: This module and the Sub::Spec standard is deprecated as of Jan 2012. Rinci is the new specification to replace Sub::Spec, it is about 95% compatible with Sub::Spec, but corrects a few issues and is more generic. "Perinci::*" is the Perl implementation for Rinci and many of its modules can handle existing Sub::Spec sub specs. 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 except those specified with column_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 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 a hashref like this {data => DATA, paged=>BOOL, filtered=>BOOL, sorted=>BOOL, columns_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 and sorted. $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. * 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 Sub::Spec Sub::Spec::CmdLine AUTHOR Steven Haryanto COPYRIGHT AND LICENSE This software is copyright (c) 2012 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.