NAME

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

VERSION

version 0.07

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.

TODO

• Generate table_spec from database schema, if unspecified

FAQ

SEE ALSO

Perinci::Sub::Gen::AccessTable

AUTHOR

Steven Haryanto <stevenharyanto@gmail.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2013 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.

FUNCTIONS

None are exported by default, but they are exportable.

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

No arguments.

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.

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

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