Alf in Shell
This page provides an introduction to using Alf in Shell. Please refer to
alf help COMMAND for further details. Note that, Alf does not
currently provide ways to perform updates using the shell interface. The
following section describe the most typical use cases of the commandline tool:
- Querying data from files (.json, .csv and the like)
- Querying SQL databases around
- Exporting data in various format
- Piping with alf itself... and other processes
- Keeping configuration in a .alfrc file
- Executing and analyzing complex queries
Querying data files
Alf recognizes various data formats such as
.yaml and so
on. It allows you to query such files and export data in those formats.
Suppose that you have the suppliers.csv file in the current folder. Visualizing the content of this file as a relation is as simple as:
$ alf show suppliers
Please note that the
.csv extension must not be specified. In fact, Alf
connects to the current folder as if it was a database, so that recognized
files are seen as candidate relations. In other words, the example below
works as soon as
shipments can be tracked to recognized files.
Try it yourself using suppliers.csv
$ alf show "join(suppliers, shipments)"
Querying databases is not different. The
--db option allows you to specify
which database to connect to. For instance, the invocations above are
$ alf --db=. show suppliers
Therefore, querying a SQLite database sap.sqlite3 in the current folder:
alf --db=sap.sqlite3 show "restrict(suppliers, city: 'London')"
And a postgresql database:
alf --db=postgres://user:password@host/database show parts
Alf example database (the suppliers and parts examplar) can also be used when learning alf:
alf --examples show suppliers
Exporting data in various formats
alf show outputs relations as a plain/text ascii table. You can
of course specify other data output formats. For instance, exporting a query
in .json can be done as follows:
alf --json show "restrict(suppliers, city: 'London')"
Other data formats are available through
(ruby hashes, one per line).
Piping and data conversion
Alf also supports receiving data from its standard input through a special
stdin operand. Suppose for example that you want to restrict some csv
tuples outputted by a process, here a simple
cat suppliers.csv | alf --stdin=csv show "restrict(stdin, city: 'London')"
That means that you can very easily use alf to convert from one data format to another one. Converting a .csv file to .json?
cat suppliers.csv | alf --stdin=csv --json show stdin
And some extra. Want to search over content served by a RESTful web service? What countries use euros, for instance?
curl http://restcountries.eu/rest | alf --stdin=json show "restrict(stdin, currency: 'EUR')"
Using an .alfrc file
When invoked from the command line, Alf looks after a
.alfrc in the current
folder and its ancestors and loads its default configuration from there if
found. A typical
.alfrc file looks as follows:
alfrc do |c| # Adapter to use, same as `--db=...` c.adapter = "postgres://user:password@host/database" # additional load paths, same as `-Ilib -Ispec` c.load_paths |= [ "lib", "spec" ] # additional libraries to require, same as `-rfoo -rbar` c.requires |= ["foo", "bar"] end
Alf::Shell::Alfrc for more information and available options.
Evaluating and analyzing complex queries
Passing queries between quotes may become cumbersome when they get complex.
Alf recognizes files with an
.alf extension and treat them
in a special way. Suppose you have a complex query in a
suppliers_by_city = group(suppliers, [:sid, :name, :status], :suppliers) parts_by_city = group(parts, [:city], :parts, allbut: true) joined = join(suppliers_by_city, parts_by_city) restrict(joined, city: "London")
Then, passing this file to
show simply executes the complex query:
$ alf --db=... show complex-query.alf
In addition, you can always verify how Alf optimize and execute your queries
$ alf --db=... explain complex-query.alf