Filtering and Ordering: SQL Support in DataView Reader

The DataView Reader supports a subset of the SQL syntax that lets developers dynamically modify data views defined in the Inspector by customizing the instance format and the filter and ordering definitions. The DataView Reader supports these statements:

SELECT
FROM
WHERE
ORDER BY DESC/ASC
AS

Suppose you are inspecting a data view called my_data_view, based on a collection of Customer instances:

Customer is a class containing the name and address data members, as well as a relation to the Vehicle class, implemented by the cars data member.
Vehicle is a class containing the make and model data members and a reverse relation to the class Customer called owner.

The DataView Reader would accept SQL statements such as these:

SELECT * FROM my_data_view
This statement maintains the default settings of my_data_view.
SELECT name, address FROM my_data_view
This statement modifies the instance format used in my_data_view to include only the name and address of each customer.
SELECT name, address FROM my_data_view WHERE name like \Qjohn*'
This statement modifies the instance format and filter defined in my_data_view. The resulting table contains only those customers whose names begin with john and also displays the customers' names and addresses. The DataView Reader translates the filter defined in the SQL statement into an ObjectStore query. This allows you to perform efficient ObjectStore queries through an SQL statement.
SELECT name, address FROM my_data_view ORDER BY name DESC
This SQL statement modifies the instance format and order defined in my_data_view. The resulting table shows the customers' names and addresses displayed alphabetically in descending name order.
SELECT name, cars#make FROM my_data_view
The DataView Reader maps the SQL column names to data member names. To navigate implicitly from one class to another, you can specify a column name containing the concatenation of the navigated data members separated by the # character. This lets you build a table based on the persistent collection used to define my_data_view. This adopts the same filtering and ordering settings, but it contains a column that shows the customer's name and the makes of the cars the customer owns. In general, you can concatenate any number of relations. For example, cars#owner#cars#model would be a valid column name. It would contain the models of the cars, which are owned by the owner of the cars, which are owned by each customer.
SELECT name, cars#make, cars#model FROM my_data_ view WHERE cars#make='Ford'
This SQL statement uses the navigated data members to build a new filter in my_data_view. As the filter is translated into a native ObjectStore query expression, the ObjectStore query executed by the above SQL statement iscars[: !strcmp(make,"Ford") :] This query is satisfied by any customer who owns at least one Ford car. This result is different from what you could expect reading the SQL expression.

*SELECT FROM my_data_view**	This statement maintains the default settings of my_data_view.
SELECT name, address FROM my_data_view	This statement modifies the instance format used in my_data_view to include only the name and address of each customer.
*SELECT name, address FROM my_data_view WHERE name like \Qjohn'**	This statement modifies the instance format and filter defined in my_data_view. The resulting table contains only those customers whose names begin with john and also displays the customers' names and addresses. The DataView Reader translates the filter defined in the SQL statement into an ObjectStore query. This allows you to perform efficient ObjectStore queries through an SQL statement.
SELECT name, address FROM my_data_view ORDER BY name DESC	This SQL statement modifies the instance format and order defined in my_data_view. The resulting table shows the customers' names and addresses displayed alphabetically in descending name order.
SELECT name, cars#make FROM my_data_view	The DataView Reader maps the SQL column names to data member names. To navigate implicitly from one class to another, you can specify a column name containing the concatenation of the navigated data members separated by the # character. This lets you build a table based on the persistent collection used to define my_data_view. This adopts the same filtering and ordering settings, but it contains a column that shows the customer's name and the makes of the cars the customer owns. In general, you can concatenate any number of relations. For example, cars#owner#cars#model would be a valid column name. It would contain the models of the cars, which are owned by the owner of the cars, which are owned by each customer.
SELECT name, cars#make, cars#model FROM my_data_ view WHERE cars#make='Ford'	This SQL statement uses the navigated data members to build a new filter in my_data_view. As the filter is translated into a native ObjectStore query expression, the ObjectStore query executed by the above SQL statement iscars[: !strcmp(make,"Ford") :] This query is satisfied by any customer who owns at least one Ford car. This result is different from what you could expect reading the SQL expression.

[previous] [next]