sql-ferret

The SQL Ferret wraps SQLite into a navigational database style interface.

BEWARE: this is raw and ugly EXPERIMENTAL code, and its API is VERY LIKELY to change before 1.0.

== Overview

The [[Ferret::new]] constructor takes two arguments: a (multiline) string containing the Ferret Data Schema Description and a previously opened [[SQLite3::Database]] instance for accessing an SQLite database with such a schema. The resulting [[Ferret]] instance's primary useful method is [[go]]; it takes one mandatory argument -- the Ferret query string -- and may also take numbered and named arguments, as well as a block.

Note that Ferret has TWO distinct DSLs: one for defining the data model, one for querying and manipulating it. When the Ferret schema is stored in a text file, it's customarily given a name in the form of [[foo.fers]]. Ferret query expressions are typically inlined in Ruby code.

== Ferret query language

The simplest form of a Ferret query expression is a query over one table, filtering by one input column, and producing one output column:

':' '->'

Such an expression corresponds to the SQL of

SELECT FROM

WHERE = ?

Note that in order to process such an expression, [[Ferret#go]] requires an extra argument besides the expression -- the exemplar value for [[]]. The separation of expression from data is a deliberate design feature of Ferret: on one hand, it's believed to make the expressions clearer; on another, it provides a measure of protection against inadvertent XSS vulnerabilities.

The can be omitted. In such a case, the query fetches all rows from

. If more than one input field is supplied, they must be separated by commas and surrounded by parentheses, like this. (Rationale: Ferret's [[->]] operator, normally binds very weakly on its left hand side. While it would not be hard to write an exception into the parser, it is believed that permitting the surrounding parentheses to be omitted is likely to lead to confusing Ferret query expressions.)

Multiple output fields can be specified by separating them with commas. Surrounding parentheses are not necessary or permitted around the right-hand side of an arrow.

More complex queries involve multiple tables and what relational algebra calls /joins/. Since Ferret aims to provide a navigational rather than purely relational interface, it presents joins as /dereferencing/, denoted by a trailing [[->]] operator. That is, the query

houses: number -> resident -> name, phone

can correspond to the SQL of

SELECT house.number, house.resident, resident.name, resident.age FROM houses LEFT JOIN residents ON houses.resident = residents.id WHERE house.number = ?

provided that the data schema specifies that the column of [[houses.resident]] refers to [[resident]] through its [[id]]. (In SQL parlance, it needs to be defined as a foreign key.) If, instead of [[left join]], an [[inner join]] is desired, the two-ended dereferencing arrow [[<->]] needs to be used instead of [[->]].

A Ferret data schema permitting such translation might look roughly thus:

[houses] id: primary key, integer number: optional integer name: optional varchar street: varchar resident: optional ref residents(id)

[residents] id: primary key, integer name: varchar = 'John Smith' phone: optional varchar

Note that the columns are by default not nullable but they can be explicitly defined as nullable by the keyword [[optional]]. The [[=]] character followed by an SQL expression specifies the default value for a column.

Also note that in the definition of [[resident: optional ref residents(id)]], the [[(id)]] can be omitted because it's clear from context -- [[residents.id]] is the primary key of [[residents]].

This data schema permits only up to one resident per house. What if the house->resident relation needs to have an 1->n shape rather than 1->0..1? We could move the linking column from [[houses]] to [[residents]], like this:

[houses] id: primary key, integer number: optional integer name: optional varchar street: varchar resident: ghost ref residents(house)

[residents] id: primary key, integer name: varchar = 'John Smith' phone: optional varchar house: optional ref residents

Note that we're still defining [[houses.resident]] but it's no longer a /column/ -- that is, it does not have a matching SQL table column anymore --, but a /ghost field/.

Besides being primary keys, columns can be defined merely [[unique]]. Ferret does not currently support composite secondary keys, but a future version might.

How does [[Ferret#go]] deliver its results? It depends. If a block is given to it, it will call this block with each row; otherwise, it collects rows and returns them. (Actually, if Ferret can prove, using [[unique]] and [[primary key]] constraints, that the query necessarily produces 0 or 1 rows, it will return either [[nil]] or the one row; otherwise, it will return an array of the rows.) If the query specifies one column (which may be precededed by dereferences); each 'row' will be the value without encapsulation; otherwise, Ferret wraps rows into [[OpenStruct]] instances. The multicolumn behaviour can be forced by adding an explicit trailing comma after what would otherwise be the single requested column. (Rationale: while these rules are a bit clumsy to specify, they have proven intuitive, in a Perlish way.)

Each queried column can be given an explicit name, analogously to SQL's [[AS]] clause, by specifying it between apostrophes after the column appears in the expression. Note that this is not a string literal; rather, Ferret parses each apostrophe as a token, and the explicit name must parse as a valid Ferret identifier token.

Star topology joins can be specified by surrounding a joining arrow together with its right-hand side in parentheses, like this:

houses: number -> resident (-> name, phone), street

Such parentheses can be nested.

In addition to retrieval, Ferret query expressions also support modification and deletion of entries. This is notated by terminating an expression in a 'blank' dereference operator followed by a colon and a verb, like this:

houses: number -> resident ->: set

The fields to be changed will then be specified as named arguments to [[Ferret#go]]. The verb [[update]] can also be used instead of [[set]]; it has exactly the same meaning. When the verb [[delete]] is used, [[Ferret#go]] does not take any named arguments.

Outside the Ferret query expression mechanism, there's the [[Ferret#insert]] method that takes the target table's name as a mandatory argument and the values to be inserted as named arguments, like this:

$ferret.insert 'residents', house: 8, name: 'Jacob Doe', phone: '555-1212'

A future version of Ferret API is likely to provide record insertion through [[Ferret#go]]. The reason we're not doing it in this public release is that our autovivification mechanisms are nowhere near settling yet.

Also of note is [[Ferret#change]], whose signature matches [[Ferret#insert]] except that it performs the [[INSERT OR REPLACE INTO ...]] operation instead of plain [[INSERT]], and [[Ferret#transaction]], which supports recursive locking. (This is quirky. It's mainly intended for use in library functions that need to group Ferret or SQL operations for atomicity without assuming that an outer transaction exists or does not exist, and it needs care even then. Unless you know you need it, you're probably better off using [[SQLite3::Database#transaction]] directly.)

Instead of a single input value, it's permitted to pass a whole collection of input values to [[Ferret#go]] -- then, Ferret uses [[foo in (?, ...)]] instead of [[foo = ?]], and won't consider this column's possible declared uniquity when deciding whether the query is a single-row query --, or [[nil]], in which case Ferret uses [[foo is null]] for proper SQL-style nullity checking. (A 'collection' is defined through duck typing -- anything that produces more than one value when the [[*]] prefix operator is applied to it.)

When it's desired that [[Ferret#go]] produce distinct values, a trailing [[: distinct]] or [[: select distinct]] can be used. (These two are synonymous.) Note that for query-type verbs, the colon must not be preceded by a blank-RHS dereferencing arrow, unlike for mutation-type verbs, which require it.

Besides straight values, Ferret supports interpreted values. The set of such is currently hardcoded and is:

iso8601 unix_time subsecond_unix_time json pretty_json yaml ruby_marshal packed_hex

When a Ferret schema assigns an interpreted rather than straight data type to a column, [[Ferret#go]] will automatically interpret and 'deterpret' values for this column, unless the column's name is prefixed with a backslash in the expression. Note that [[Ferret#insert]] does not (currently?) support interpretation, and always processes raw values.

== Likely future development

• 'en passant' filters in addition to 'initial' filters. These will probably be notated by brackets, and may permit ordering comparison in addition to equality checks;
• use of [[Range]] values in addition to collections as filters passed to [[Ferret#go]];
• explicit ordering of the produced rows, probably via unary postfix operators;
• SQL grouping and aggregate functions;
• [[Ferret#go]] returning rows as a [[Hash]] instead of an [[Array]], using a given key or keys;
• Kleene dereferencing arrows [[-*>]] and [[-+>]] (and their double-ended [[INNER JOIN]] counterparts), implemented via SQLite's recently introduced [[WITH RECURSIVE]] construct;
• multi-column uniquity constraints and foreign keys;
• automated handling of SIKR (Strictly Incremental Knowledge Representation) packets so that Fossil-style multinode tracking could be implemented for (nearly) arbitrary data structures;
• accessing SQL's views;
• customisable autovivification in multistage insertions;
• defining [[Hash]]-like interfaces atop [[Ferret]] that would be backed with a custom, potentially joined, relation in the underlying SQL table;
• defining per-column access-controlled [[Ferret]]-like subAPIs;
• integration of [[insert]] and [[change]] into [[Ferret#go]];
• an interface for Android's built-in SQLite API as available through Ruboto, as an alternative to the [[sqlite3]] Rubygem which is not available on Ruboto;
• better documentation;
• renaming to avoid clashing with https://github.com/jkraemer/ferret.

== Possible future development

• tracking the underlying SQLite database's schema via [[PRAGMA user_version]] and automatically upgrading it;
• command line tools for database setup and data import and export;
• transparently joining another table so as to implement [[is-a]] type relation atop a SQL data model;
• transparently interpreting JSON or YAML data as extra payload fields of their containing table without explicit formal specification, akin to MongoDB;
• transparent compression of blob/YAML/JSON fields;
• extracting column type data, constraints, and foreign keys from the [[sqlite_master]] data so that the Ferret schema would only need to specify ghost fields and interpretations;
• a notation for /ad hoc/ joins;
• a third, hybrid DSL: Ferret dereference operator extensions to basic SQL queries, something like [[SELECT number, resident -> name, resident -> phone FROM houses WHERE id = ?]] or perhaps [[SELECT number, resident -> (name, phone) FROM ...]];
• custom enums as interpretations;
• global primary key definition in the schema.