Published by xi on 2011-05-02
This month we are going to add a linking operator (->), a concept of references ($ name), a shortcut syntax that combines selection and definition ({ attr := (expr) }) and automatically populate the namespace of projection.
HTSQL provides automatic linking based on foreign keys, so that an expression
generates a link from table school to table department joining them by values of school.code and department.school_code.
Sometimes, you may need to make a link not based on any foreign keys, either because the database administrator forgot to create the key or when the columns to join by are calculated. For this purpose, we are going to introduce a linking operator (->). Using a linking operator, the expression school.department could be written as
The linking operator has several forms. When the the source and the target columns coincide, use
as a shortcut for
This expression makes a link from student table to itself associating each student with a set of students with the same year of birth. Note that the same expression could also be written using fork() function:
A link may use more than one column, as in
or no columns at all:
The last form generates all possible pairings between student and instructor.
In HTSQL, a scope is a context which defines the set of available names. Typically, a scope is associated with some table and available names are table attributes and links.
Let’s deconstruct scopes in the following example.
Whenever a new scope is introduced, it completely hides the content of any previous scope. Thus, we cannot access attributes of school in the scope of department. It is an intentional limitation since otherwise attributes from different tables would pollute the namespace.
However, occasionally, we may still want to fetch an attribute from a previous scope. For instance, in the example above, we may need to add the school name to the output columns.
To overcome this limitation, we are going to introduce a concept of references. A reference is a name that ignores scope boundaries. A reference starts with $ followed by an identifier and could be defined using the regular define() function:
In this example, perhaps it is easier to make a new link to school than to fetch it from the previous context:
but it may not always be possible.
References are especially useful when defining parameterized attributes. Consider a query:
Found unknown attribute:
student.y
While translating:
/define(cohort(y) := student?year(start_date)=y)
^
In cohort(y), the argument y is defined in the top scope of the function body. However, choosing student changes the scope and y is no longer available, so we get an error in the expression year(start_date)=y. Using references, this query could be written as
Consider a query
Note how expression count(department) is repeated twice. To avoid duplication, we could factor this expression as a calculated attribute school.num_dept:
For the next beta release, we are going to introduce an even shorter form that combines selection and definition:
In general, an expression of the form
{attr := (expr), ...}
will be expanded to
define(attr := (expr)){attr, ...}
Consider a query
The projection operator (^) creates a new row set consisting of all distinct values of degree as it runs over program. The row set (program^degree) behaves like a table with a single column degree and a foreign key from program.
Currently, neither the columns of (program^degree), nor the link to program could be addressed by name. Instead, to address the columns by position, use:
*1, *2, ...
and to traverse the link to the original table, use:
^
For the next beta release, HTSQL will automatically name the columns and the link of a projection in cases where it could be done unambiguously. Thus, the query above could be written as:
When the projection kernel is an expression, we may use := to assign it a name: