Introducing References

Published by cce on 2011-05-12

Recently we introduced calculated attributes and table methods. Today we introduce references, denoted with a dollar-sign $ to provide a separate aliasing mechanism that cascades through scope boundaries.

Basics

In HTSQL, a scope is set of available names, which includes column names, links to other tables, and calculated attributes. Let’s deconstruct scopes in the following example.

/
The query starts with a scalar scope, which contains the names of all tables.
school.filter(code='art')
By choosing school, we changed the scope to the school table. Now we have access to columns such as code and table links such as department. Applying filter() does not change the scope.
department{name}
Traversing link department changes the scope again. Now we have access to columns of table department such as name. The selection operator department{name} does not affect the scope, and, if necessary, we could continue building the query.

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 introduce 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:

$school_name name
School of Engineering Bioengineering
School of Engineering Computer Science
School of Engineering Electrical Engineering
School of Engineering Mechanical Engineering

Of course in this example, you could use a forward link, school.name and get the same results.

However, this isn’t always the case.

Query Parameters

Often a request may have constants which you may want to define separately. For example, suppose you have a query returns all courses offered during a particular academic year.

department_code no title credits description
acc 200 Introduction to Accounting 3 The initial course in the theory and practice of financial accounting. Topics emphasized include the preparation, reporting, and analysis of financial data.
arthis 102 History of Art Criticism I 4 An introductory survey course of world art from Prehistoric through late-Medieval art history.
arthis 209 Antique Art: Greece and Rome 6 Visual art of antique period with emphasis on Greek and Roman art.
arthis 224 Arts of Asia 3 An introduction to the history and criticism of Far Eastern art, including the art of China and Japan, fine and decorative arts.

The problem with this query is that it embeds a hard-coded parameter deep inside the query. To move this parameter up-front, we write:

department_code no title credits description
acc 200 Introduction to Accounting 3 The initial course in the theory and practice of financial accounting. Topics emphasized include the preparation, reporting, and analysis of financial data.
arthis 102 History of Art Criticism I 4 An introductory survey course of world art from Prehistoric through late-Medieval art history.
arthis 209 Antique Art: Greece and Rome 6 Visual art of antique period with emphasis on Greek and Roman art.
arthis 224 Arts of Asia 3 An introduction to the history and criticism of Far Eastern art, including the art of China and Japan, fine and decorative arts.

Correlated Queries

Sometimes references are needed for correlated queries. Suppose we’ve got a business inquiry:

For each course across all engineering departments, count the number of courses in those departments with a higher number of credits.

Let’s build this incrementally.

title credits
Fundamentals of Biochemistry 3
Introduction to Biomedical Engineering 3
Introductory Microbiology 3
Introductory Toxicology 3
Bioengineering Seminar 2

This query returns courses we want, those in engineering departments, along with course title and number of credits.

title credits count(fork())
Fundamentals of Biochemistry 3 71
Introduction to Biomedical Engineering 3 71
Introductory Microbiology 3 71
Introductory Toxicology 3 71
Bioengineering Seminar 2 71

In this query we add a 3rd column, count(fork()). The fork() expression correlates each individual course with every other course from the school of engineering. We then count those courses.

title credits count(fork()?credits>$x)
Fundamentals of Biochemistry 3 13
Introduction to Biomedical Engineering 3 13
Introductory Microbiology 3 13
Introductory Toxicology 3 13
Bioengineering Seminar 2 13

Remember that we need to count not all correlated courses, but only those with a number of credits over a given value. To get closer to this goal, we filter the correlated courses to include only those with credits higher than $x, where the value of x is equal to 3.

title credits count(fork()?credits>$x)
Fundamentals of Biochemistry 3 13
Introduction to Biomedical Engineering 3 13
Introductory Microbiology 3 13
Introductory Toxicology 3 13
Bioengineering Seminar 2 64

Finally, we count only those correlated courses with credits greater than the current row’s credits. And we’re done. Alternatively, one could make a reference to the current record itself.

title credits count(fork()?credits>$c.credits)
Fundamentals of Biochemistry 3 13
Introduction to Biomedical Engineering 3 13
Introductory Microbiology 3 13
Introductory Toxicology 3 13
Bioengineering Seminar 2 64

Method Arguments

The reference mechanism is also needed for certain parameterized calculated attributes. Suppose we’ve got a business inquiry:

For each department calculate the average number of credits for freshman, sophomore, junior and senior level courses.

Here it is.

name freshman sophomore junior senior
Accounting 2.0 3.0 4.0 3.0
Art History 3.5 3.8 3.0 3.66666666667
Astronomy 3.2 3.0 3.0 2.8
Bioengineering 2.8 3.2 3.0 5.0
Bursar’s Office

In this query we define a calculated attribute avg_credits which depends upon parameter $level. This attribute produces the average credits over courses from the given level.

blog comments powered by Disqus