/ 
App-fsql-0.07
River stage one • 1 direct dependent • 1 total dependent
/ fsql

NAME

fsql - Perform SQL queries against files in CSV/TSV/LTSV/JSON/YAML formats

VERSION

This document describes version 0.07 of fsql (from Perl distribution App-fsql), released on 2014-04-29.

SYNOPSIS

fsql [OPTIONS] [ <QUERY> | --show-schema ]

DESCRIPTION

fsql lets you perform SQL queries against "flat" files of various formats. Each file will be regarded as a SQL table. The magic of all this is performed by DBD::CSV and SQL::Statement.

There must be at least one table specified (with --add or one of the --add-TYPE options). If none of those options are specified, a table is assumed in STDIN with name stdin.

EXAMPLES

Filter CSV (table from stdin is aptly named so):

% prog-that-produces-csv | fsql 'SELECT id,name FROM stdin WHERE id<=1000' > final.csv

Pick output format, produce array of hashes instead of the default array of arrays:

% fsql -a ~/book.pl 'SELECT title,name FROM book WHERE year>=2010' --hash -f json

You can perform joins, of course:

% fsql -a t.json -a 2.csv:t2 'SELECT * FROM t1 LEFT JOIN t2 ON t1.uid=t2.id'

Show schema:

% fsql -a table1.json -a 2.csv:table2 --show-schema

OPTIONS

  • --add=FILENAME[:TABLENAME], -a

    Add a table from a file. Type will be detected from filename extension (and some heuristics, if unspecified). Die if type cannot be detected.

    Alternatively, you can use one of the --add-TYPE options to add

  • --add-csv=FILENAME[:TABLENAME]

    Add a table from a CSV file. If TABLENAME is not specified, it will be taken from FILENAME (e.g. with filename foo-bar.csv, table name will be foo_bar). FILENAME can be - to mean the standard input (the default table name will be stdin). Will croak if duplicate table name is detected.

    Table name must match regex /\A[A-Za-z_][A-Za-z_0-9]*\z/.

  • --add-tsv=FILENAME[:TABLENAME]

    Like --add-csv, but will load file as TSV (tab-separated value).

  • --add-ltsv=FILENAME[:TABLENAME]

    Like --add-csv, but will load file as LTSV (labeled tab separated value, see Text::LTSV). Names of columns will be taken from the first row.

  • --add-json=FILENAME[:TABLENAME]

    Like --add-csv, but will load file as JSON.

    Data can be array, or array of arrays, or array of hashes, or an enveloped response (see Rinci::function), so it is suitable to accept piped output of Perinci::CmdLine-based programs.

  • --add-yaml=FILENAME[:TABLENAME]

    Like --add-json, but will load file as YAML.

  • --add-perl=FILENAME[:TABLENAME]

    Like --add-perl, but will load file as Perl.

  • --hash

    Return each row as a hash (dictionary/associated array) instead of an array. This is relevant to output formats like perl, json, yaml, text.

    Returning a hash is convenient when you want column name information on each row, but you can't specify the same column twice and order of columns are not guaranteed.

  • --format=FORMAT (default: text), -f

    Set output format.

    The value csv or tsv or ltsv will cause query results to be output as a comma-separated or TAB-separated list or labeled-TAB separated list, respectively. As this isn't very useful for a schema listing, these values will be silently converted to text if --show-schema is also present.

    The value of perl will format using Data::Format::Pretty::Perl.

    Any other value will be passed to Perinci::Result::Format's format().

    The default value is the most used table format. So if your tables are mostly CSV, fsql will also output CSV by default.

  • --show-schema

    Instead of running a query, show schema instead. This is useful for debugging.

EXIT CODES

0 on success.

255 on I/O or SQL error.

99 on command-line options or input data error.

FAQ

TODO

Allow customized CSV separator and quoting.

HOMEPAGE

Please visit the project's homepage at https://metacpan.org/release/App-fsql.

SOURCE

Source repository is at https://github.com/sharyanto/perl-App-fsql.

BUGS

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

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

Steven Haryanto <stevenharyanto@gmail.com>

COPYRIGHT AND LICENSE

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