NAME
fsql - Perform SQL queries against files in CSV/TSV/LTSV/JSON/YAML formats
VERSION
This document describes version 0.10 of fsql (from Perl distribution App-fsql), released on 2014-05-17.
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' --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 --show-schema
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 fromFILENAME
(e.g. with filenamefoo-bar.csv
, table name will befoo_bar
).FILENAME
can be-
to mean the standard input (the default table name will bestdin
). 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
ortsv
orltsv
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 totext
if--show-schema
is also present.The other values
perl
,json
,yaml
, andtext
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
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.