Tql Column Table

Column Tables, a feature of Top's QueryLanguage (see TqlRoadmap for an introduction).

Column name lists and similar structures are tables themselves; a kind of DataDictionary. This is arguably the most distinguishing feature of this language. It gives it more dynamic "meta" ability and conceptual reuse. It generally replaces the column selectors in the SQL "SELECT" clause.

However, since it is not practical to create formal tables for such things in most environments, a conversion function, "cols" is provided that converts string expressions, a kind of sub-language similar to SQL's "SELECT" clause syntax, into a "column table". The "cols" syntax is controversial, and alternatives, such as the "[...]" alternative, are discussed in TqlOverloading and also used in many examples.

A typical column table has the following columns as a minimum:

The format pattern generally resembles:

  op(expr) col, op(expr) col, op(expr) col, etc...

However, there are shortcut conventions that can simplify such expressions. ("order" comes from the order in the column expression list). As an example, the following expression produces the following table:

  cols("compute(amt + tax) total, avg(qty * rate) avgPrice,
   (a || b) myConcat, productID") 

Alternative "bracket" style:

  [compute(amt + tax) total, avg(qty * rate) avgPrice,
    (a || b) myConcat, productID]

Resulting Column Table:

  op        expr         col       order
  ---------------------------------------
  compute   amt + tax   total        1
  avg       qty * rate  avgPrice     2
  compute   a || b      myConcat     3
  compute   productID   productID    4

The 3rd item assumes "compute" because an explicit operation is not given. This can help make typical column expressions more compact and SQL-like since "compute" is the most common operator. The first column could have also used the implied approach to simplify it.

Unlike SQL "SELECT" clause, parenthesis are required, however, even if an "op" is not specified. With the fourth item, both "op" and "expr" are implied. The column name is the default expression if an expression not supplied. ("||" is assumed to be a string concatenation operator in this example.)

Note that this example is highly contrived in order to show a mix of operations. Also, the "column table" is usually temporary or virtual in practice; no physical table may actually be created. The usage of this sublanguage will make more sense when specific operations are described.

The column sub-language might not be the ideal at this point, but does not matter as much as the resulting column table. It is simply a syntactic convenience. Doing it this way leaves the door open for other sub-language alternatives, yet provides something that works out-of-the-box. For example, somebody might want to create a version that uses SQL's "as" conventions instead for familiarity. TQL won't care as long as the result is a table in Column Table format.

So what column tables solve is the ability to use the result of selection as the expression to select itself?

 $ sqlite test.db
 SQLite version 3.1.1beta
 Enter ".help" for instructions
 sqlite> create table target(foo,bar,baz,mumble);
 sqlite> insert into target values(1,2,3,4);
 sqlite> insert into target values(2342,5473,23567236,123123);
 sqlite> insert into target values(-99,-98,-97,-96); 
 sqlite> select bar,mumble from target;
 2|4
 5473|123123
 -98|-96

Now assuming we have column tables, we'd like this to work (along with more complex constructions of selectors, it appears)

 sqlite> create table selector(rowname);
 sqlite> insert into selector values ('bar');
 sqlite> insert into selector values ('mumble');
 sqlite> select (select rowname from selector) from target;
 2|4
 5473|123123
 -98|-96

(Incidentally, the query does run, but it just returns three rows of 'bar').

This sort of thing can sorta be done in oracle through dynamic queries, though that's just a PL/SQL equivalent of 'eval'.

--AnonymousDonor

I don't understand. Why is it the order column needed? As far as I can see, the order is defined by the order of the rows in the column table. Why don't just use the column table below? Why is it needed to have an order for the columns?

  op        expr         col
  -------------------------------
  compute   amt + tax   total    
  avg       qty * rate  avgPrice 
  compute   a || b      myConcat 
  compute   productID   productID

Relational does not define an inherit row ordering because it is based on sets instead of lists, and I see no significant reason to deviate from that rule. --top

But why an order is needed? Isn't the col column a key in the table? And why did you define sort_by?

There was a note(s) about this somewhere. I'll see if I can find them. In general, column orders should generally be considered "display suggestions", and for the resulting column table we want to make the ordering value explicit. Plus, positional "arguments" and name-matched arguments are representationally equivalent, but one or the other may be more convenient in a given situation.

The "sort_by" operator can provide an integer sequence. These are useful for many common purposes, such as computing "difference from previous period" or "difference from most-recent previous item". See TqlExampleOne for a use of sequences.


In addition to the columns above, perhaps a standard set of "extra" columns should be defined for consistency across platforms. Although column tables are not limited to the set above, and it should be assumed that vendors can add their own, it is still helpful to have a standardized set of extras with reserved names to assist cross-platform movement. Here is a suggestion:

For a non-typed system, the length wouldn't necessarily matter.

--top
CategoryTql, TqlRoadmap

EditText of this page (last edited February 10, 2012) or FindPage with title or text search