Queries

A query in the 5800 system query language is translated into an equivalent query for the underlying database that implements the query engine. The database used in a live 5800 system is Sun's High Availability Database (HADB). The database used by the 5800 system emulator is Apache^TM Derby. Since the SQL query language used by HADB and Derby differ in substantial ways, a subset of the query language is provided for portability between the cluster and the 5800 system emulator to enable application development in the emulator environment and subsequent deployment of the applications to a live 5800 system.

Translating a Query to the Underlying Database

The following provides a summary of the translation of the 5800 system queries to SQL queries that are presented to the underlying database.

The metadata schema specifies the layout of fields into tables and columns. When the schema is committed, a particular set of actual tables and columns is created in the underlying database that matches the format of the table layout in the schema.

When translating a 5800 system query to SQL, each field name in the query is translated into a reference to the particular column and particular table that represents that field. Typed literal values are translated into a form that the extended metadata cache knows how to deal with. Specifically, most literal values are replaced with an equivalent dynamic parameter. Thus, the list of dynamic parameters that the underlying database uses combines both the dynamic parameters and also many of the literal values from the 5800 system query. Finally, an implicit INNER JOIN is introduced between all the tables containing the translated query fields. Everything else (usually database expression syntax) is left unchanged, allowing almost all the database engine's powerful query syntax to be used with 5800 system queries.

The presence of the INNER JOIN has important consequences when queries are evaluated. An object is only returned by a query when all of the fields referenced by the query itself and all of the fields referenced in the select list of the query all have non-null values. Queries with OR clauses, in particular, can produce non-intuitive results. As an extreme example, consider a query: "fieldA is not null OR fieldB is not null." This query will not select an object unless both fieldA and fieldB are non-null, because of this implicit inner join.

Attribute Format in Queries

Any string in double quotes (for example, "filename") and any dotted string in Java Identifier format (for example, mp3.title) will automatically be treated as an attribute name. The double quotes can optionally be omitted even on a non-dotted name as long as the attribute does not match an SQL reserved word in any of the Sun StorageTek 5800 underlying metadata databases.

Attribute names must appear in the current 5800 system schema to be used in a query. This is because the proper type information about each attribute must be derived to build the query.

SQL Syntax in 5800 System Queries

General Unicode characters outside of the ASCII range in queries are allowed in only two places to the 5800 system. Specifically, both attribute names and literal values may contain general Unicode characters. All text that is not either an attribute name nor a literal value is passed unchanged to the underlying query engine, and must consist of ASCII characters only. An attempt to pass non-ASCII characters in a query will result in an error.