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:

op - Operation such as "compute", "avg", "sum", etc. If blank, "compute" is assumed. Not all operations are valid under every context.
expr - Expression (passed as a string)
col - Column name.
order - Display or sort order. It is usually optional. Implemented as a double-precision (for typed systems).

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.)

Some may notice that Column Tables somewhat resemble the "query builder" or "query-by-example" tabular forms found in some GUI-centric and RAD database tools. In this case, however, it's a regular table like any other table, instead of some hidden or proprietary binary thing.
"As" can optionally be specified to fit SQL habits.

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.

YUK YUK YUK, passing strings like that is nasty. -- anonymous comment
No, you don't understand TOP's philosophy. We don't need no stinking syntax, we need only to pass strings that will be eval'ed. Strings are the ultimate goal in flexibility. Now we finally know what he wanted to say with "syntax is a string". Don't ask him what's the syntax for valid strings, cause BNF is nasty and we can do much better by the way of edifying examples, if you know what I mean. Plus if the string is invalid, the runtime will throw an error so who cares about syntax ? The syntax for strings is what the runtime engine, if ever implemented, will accept.
See "native" comments near bottom of TqlOverloading. Because it is controversial, I'm open to alternative ways to approach this.

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:

custom_t - A custom text column of at least 80 characters. (The std. only guarantees 80 as a minimum. Portable code should not use more than 80.)
custom_i - A custom long integer (if using a typed system)
custom_n - A custom double-precision number (if using a typed system)
custom_d - A custom date or date/time (if using a typed system)

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

--top

CategoryTql, TqlRoadmap