A rowset hierarchy is a collection of rowsets linked together via chapters. Hierarchies model 1:N relationships between tables, such as a master-detail relationship between Customers, Orders, and Items. A rowset hierarchy can be stored persistently in a data source or computed by a command. In this chapter we discuss only computed hierarchies.
Chapters are identifiers of groups of details within a rowset. These chapters work as distinct collections with a beginning and an end, but also share the facilities, such as accessors and notifications, of the rowset to which they belong.
The following example illustrates how a computed hierarchy is generated. The database schema contains the following three tables.
CREATE TABLE customer_table
( cust_no INTEGER,
cust_name VARCHAR(20),
city VARCHAR(20),
PRIMARY KEY (cust_no) )
CREATE TABLE order_table
( cust_no INTEGER,
ord_no INTEGER,
ord_date DATE,
total DECIMAL(9,2),
PRIMARY KEY (ord_no),
FOREIGN KEY (cust_no) REFERENCES customer_table )
CREATE TABLE order_item_table
( ord_no INTEGER,
item_no INTEGER,
description VARCHAR(30),
quantity SMALLINT,
unit_price DECIMAL(6,2),
PRIMARY KEY (ord_no, item_no),
FOREIGN KEY (ord_no, REFERENCES order_table )
The following extended SQL command generates a rowset hierarchy. The syntax of this query is used only for illustration purposes. Providers may use other syntaxes to generate hierarchy.
SELECT (cust_no,
cust_name,
city,
SELECT (ord_no,
ord_date,
total,
SELECT (item_no,
description,
quantity,
unit_price,
(quantity * unit_price) AS ext_price
)
FROM order_item_table
WHERE order_item_table.ord_no = order_table.ord_no
AS item_rows
)
FROM order_table
WHERE order_table.cust_no = customer_table.cust_no
AS order_rows
)
FROM customer_table