ExoSQL

Not everything described here is working yet

Universal SQL engine for Elixir.

This library implements the SQL engine to perform queries on user provided databases using a simple interface based on Foreign Data Wrappers from PostgreSQL.

This allows to use SQL on your own data and virtual tables.

For example it includes a CSV reader and an HTTP client, so that you can do queries as:

SELECT url, status_code
  FROM urls
  INNER JOIN request
  ON urls.url = request.url

Installation

The package can be installed by adding exosql to your list of dependencies in mix.exs:

def deps do
  [
    {:exosql, "~> 0.2"}
  ]
end

Features

SELECT over external databases
SELECT over several tables
WHERE
INNER JOIN
GROUP BY
ORDER BY
Aggregation functions: COUNT, SUM, AVG
Builtin functions and operators: * / + - || or and round concat..
Basic Reflection over self.tables

Check the tests for current features available.

INNER JOIN

Because some columns may need to be autogenerated depending on the query, if you want to access those columns you may need to use INNER JOINS. This way the planner asks for those specific column values.

For example:

SELECT * FROM request

does not know to which URL you want to access, but:

SELECT * FROM request WHERE url = &#39;http://serverboards.io&#39;

knows the URL and can get the data.

Then same way, on INNER JOINS this can be used to access to auto generated data:

SELECT url, status_code
  FROM urls
  INNER JOIN request
  ON urls.url = request.url

Included extractors

ExoSQL has been developed with the idea of connecting to Serverboards services, and as such it does not provide more than some test extractors:

CSV files
HTTP requests

Creating new ones is a very straightforward process. The HTTP example can be followed.

Using ExoSQL

There is no formal documentation yet, but you can check the esql_test.exs file to get an idea of how to use ExoSQL.

Example:

context = %{
  "A" => {ExoSQL.Csv, path: "test/data/csv/"},
  "B" => {ExoSQL.HTTP, []}
}
{:ok, result} = ExoSQL.query("
  SELECT urls.url, request.status_code
    FROM urls
   INNER JOIN request
      ON urls.url = request.url
", context)

%ExoSQL.Result{
  columns: [{"A", "urls", "url"}, {"B", "request", "status_code"}],
  rows: [
    ["https://serverboards.io/e404", 404],
    ["http://www.facebook.com", 302],
    ["https://serverboards.io", 200],
    ["http://www.serverboards.io", 301],
    ["http://www.google.com", 302]
  ]}

Known BUGS

When doing ORDER BY [column id], [column name], it reverses the order. To avoid use one or the other, dont mix order by column name and result column position.
This is becasue the planner does the ordering on column name first, then the select which limits the columns and reorder them and then the ordering by column position.