Published by cce on 2012-06-27
With HTSQL locators, you may easily reference a single record in a database table. This feature has two parts:
We created locators to shorten common filtering cases and make it possible to access individual records without knowing the primary key structure of the table.
Locators will be included in our HTSQL 2.3.1 release.
In database applications, one often wishes to retrieve and manipulate a single record by a natural key provided by a user. For example, in our university database, to return the the “School of Natural Sciences”, you could write:
| code | name | campus |
|---|---|---|
| ns | School of Natural Sciences | old |
This approach has three disadvantages. First, you must remember that the primary key column for the school table is code. Second, even though it might be filtered to return at most one record, a prudent application developer should assert only one record is returned. Third, if the natural key structure is complex, filter construction is unwieldy and not easy to visually inspect.
For a specific database application, this could be solved by convention. Each table in the schema would have an integer id column that is always the primary key. This solves these issues: filter construction is easy, you know these filters are singular, and the index is always simple. However, this approach requires schema convention and you have to give up on access via natural key.
Since we intend HTSQL to be usable on top of any existing database schema, we have to solve this problem another way.
HTSQL’s locator mechanism uses an array index metaphor to provide succinct syntax for accessing individual records. For example, the following query returns the school with code ns:
| code | name | campus |
|---|---|---|
| ns | School of Natural Sciences | old |
This syntax is more than a replacement for the ?code='ns' predicate. For someone looking at the query, you know immediately that either zero or one records will be returned. Furthermore, they would know ns is the entity ID of the record requested; even if they might not know it comes from the code column.
To list the entity IDs, you could use the id() function which returns the corresponding entity ID for each record. For the course table, id() returns an entity ID consisting of the department code and course number:
| id() | department_code | no | title |
|---|---|---|---|
| acc.100 | acc | 100 | Practical Bookkeeping |
| acc.200 | acc | 200 | Introduction to Accounting |
| acc.234 | acc | 234 | Accounting Information Systems |
In particular, acc.100, locates the course that corresponds to “Practical Bookkeeping”. This value could then be parroted back to retrieve the entire record:
| department_code | no | title | credits | description |
|---|---|---|---|---|
| acc | 100 | Practical Bookkeeping | 2 |
The lovely thing about locators is that the entity IDs are not only human readable, but act as opaque identifiers. In this manner, generic applications could be written without needing to know the key structure of each and every table in the database schema.
Locators are particularly useful for navigating through the database. For example, if you’ve already selected /school[ns] you could list related departments by appending .department.
| code | name | school_code |
|---|---|---|
| astro | Astronomy | ns |
| chem | Chemistry | ns |
| mth | Mathematics | ns |
In combination with the unlink @ operator, you could include values from any arbitrary record in an expression. For example, you might wish to show all schools that are on the same campus as the “School of Natural Sciences”:
| code | name | campus |
|---|---|---|
| art | School of Art & Design | old |
| edu | College of Education | old |
| la | School of Arts and Humanities | old |
| ns | School of Natural Sciences | old |
This expression works because the result of a locator is singular. Hence, @school[ns].campus could be thought of as a scalar value ('old') for evaluation purposes.
This feature set includes three core concepts:
In general, HTSQL derives the entity ID structure from PRIMARY KEY. In cases where the PRIMARY KEY for a table contains a FOREIGN KEY relationship to another table, the locator’s structure includes the ID of the referenced entity.
For example, program IDs are composed of two parts: the ID of the program’s school, and the value of the program’s code column.
| id() | school_code | code | title | degree | part_of_code |
|---|---|---|---|---|---|
| ns.gmth | ns | gmth | Masters of Science in Mathematics | ms | pmth |
| ns.pmth | ns | pmth | Doctorate of Science in Mathematics | ph | |
| ns.uastro | ns | uastro | Bachelor of Science in Astronomy | bs | |
To address a specific program by its ID, we write:
| school_code | code | title | degree | part_of_code |
|---|---|---|---|---|
| ns | uastro | Bachelor of Science in Astronomy | bs |
In complex database schemas, where an entity’s ID may be composed of multiple labels, you may optionally use parentheses (()) for grouping. For example, the class ID is a combination of the course ID, the semester ID and attribute section. The course and semester entities also have composite IDs, so we use () to visually separate them:
| id() | course | semester | section |
|---|---|---|---|
| id() | id() | ||
| (acc.100).(2007.spring).001 | acc.100 | 2007.spring | 001 |
| (acc.100).(2008.spring).001 | acc.100 | 2008.spring | 001 |
| (acc.100).(2009.spring).001 | acc.100 | 2009.spring | 001 |
Locator labels are literal values and could be unquoted or quoted. Unquoted labels are limited to alphanumeric characters and hyphen (-). Quoted labels could represent any values:
| school_code | code | title | degree | part_of_code |
|---|---|---|---|---|
| ns | uastro | Bachelor of Science in Astronomy | bs |
In future efforts, we will add relative locations, comparison of id() values, and $reference substitution inside a locator.