The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
App-fsql-0.14
River stage one • 1 direct dependent • 1 total dependent
8 ++
/ fsql

NAME

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

VERSION

This document describes version 0.14 of fsql (from Perl distribution App-fsql), released on 2015-01-11.

SYNOPSIS

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

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' --aoh -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 -s

OPTIONS

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

    Add a table from a file. Type will be detected from filename extension (and some heuristics, if there is no file extension or extension is unrecognized). Die if type cannot be detected.

    Sometimes the detection will miss. Alternatively, you can use one of the --add-TYPE options to add a specific table type.

  • --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-json, but will load file as Perl.

  • --aoa

    Return array of array (the default). Only relevant to outputs like perl, json, yaml, text.

  • --aoh

    Return array of hashes instead of the default array of array, where each row is represented as a hash (dictionary/associated array) instead of an array. Only 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 (-s) is also present.

    The other values perl, json, yaml, and text will be formatted using appropriate Data::Format::Pretty formatter.

    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, -s

    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

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

perlancar <perlancar@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2015 by perlancar@cpan.org.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.