NestedView

The NestedView is a TStore view which operates on multiple tables according to how they are linked by foreign key relationships. It uses xdata::Tables which contain other xdata:Tables, each child table containing the records linked by foreign key to the row in the parent table. Each of these sub-tables can have its own child tables, and so on, until the specified recursion depth is reached. Foreign key relationships are followed in both directions, so if a column in table A references a column in table B , then a NestedTable query on table A will contain child tables with rows from table B , and a query on table B will contain child tables with rows from Table A .

To create a NestedView, add a view with a name beginning with urn:tstore-view-Nested:

Example

This article uses the example of a database with three tables, WIFE , SACK and CAT . Each wife can have zero or more sacks, and each sack can contain zero or more cats, as a generalisation of this nursery rhyme. The following code was used to set up the database:

create table wife (
  name varchar2(30) primary key,
  age integer
);
create table sack (
  id integer primary key,
  volume binary_double,
  owner varchar2(30) references wife (name)

);

create table cat (
  name varchar2(30) primary key,
  breed varchar2(30),
  sack integer references sack (id)
);

insert into wife values ('Jane',21);
insert into sack values (1,2.3,'Jane');
insert into cat values ('Tiger','tabby',1);
insert into cat values ('Fluffy','Persian',1);
insert into wife values ('Joan',42);
insert into sack values (2,2.3,'Joan');
insert into cat values ('Smokey','tiger',2);
insert into sack values (3,2.3,'Joan');
insert into cat values ('Mittens','Siamese',3);

Operations

Currently NestedView supports query, definition, insert, delete and clear operations. Update is not yet supported, due to the difficulty in deciding how the interface should work.

Query

The table returned by a query has all of the columns that are in that table in the database. A query with depth 0 simply returns all rows from the given table, with no additional information. If the depth parameter is greater than 0, and there are foreign key constraints concerning the table, then there will also be other columns. The columns will be of type table, and each row will contain a table with the results of querying a database table for the records with a key matching that row.

The columns will be named after the table their contents are from. If there is more than one foreign key relationship between the same two tables, TStore will return an error.

Previously, such columns were named according to the table their contents were from, and the columns used in the relevant foreign key constraint, as follows:

TABLENAME_PARENTCOLUMN->CHILDCOLUMN

where TABLENAME is the name of the table which the contents of the column come from, PARENTCOLUMN is the name of a column in the same table that this column is in, and CHILDCOLUMN is the name of a column in the TABLENAME table. The diagrams on this page still use this old naming scheme.

A query with depth 1 on the WIFE table would give the table shown on the right.

The NestedView follows foreign key relationships in both directions, so a query with depth 1 on the SACK table would return sub-tables from the WIFE table as well as from the CAT table, as shown in the second image.

This means that for any query of depth greater than 1, the same results will appear in multiple levels in the hierarchy, for example in the pictured query on the WIFE table with depth 2. To save space, the VOLUME column on the SACK table is not shown.

Note that since each sack is owned by only one wife, each table in the WIFE_OWNER->NAME column has only one row. The sub-table contains all columns of the database table, even those which have their values already determined because they match columns in the parent table. This should make it easier if you need to use a single table from the result on its own. However this duplicated information increases the size of the table which needs to be transferred over the network, so you think carefully before increasing the depth of your query.

Definition and Insert

While in theory a table definition message should return an empty table with column definitions but no rows, for a NestedView it is sometimes necessary for a table to contain a single row, in order to define the structure of any sub-tables. This row will contain an empty table in some columns, and NULL values in the others. If you want to leave a table empty, you will need to delete this row. When you insert new rows into the table, you should copy the definition of any sub-tables into the new rows, and fill in the other columns.

As mentioned above, a query result can have multiple copies of the same data. To avoid inconsistencies when inserting, a table definition has a simpler structure from a query result. Foreign key constraints are only followed in one direction, the direction in which it is not essential to have anything in the child tables. Also, the columns of a sub-table which are already determined by the values in the parent table are not included in the definition. A definition of the WIFE table with depth 2 would return the table in the last picture. Note that there is no OWNER column in the SACK table, because the owner will be the same as the NAME in that row of the WIFE table. There is also no WIFE_OWNER->NAME column, because the owner is already determined by the enclosing row. Similarly there is no SACK column in the CAT , because the SACK column will be filled in with the ID from the row containing the table.

Code example

After getting a table definition, fill in the values as normal. When you come across a column of type 'table', copy the table definition for that column from the first row of the enclosing definition table, and fill in that table. When you have finished adding the values you need, delete this first row so that you don't insert a column of NULL s into the database. This example, from the TStoreTest application, fills a table with random values.

void TStoreTest::insertRandomRowsIntoTable(xdata::Table &tableDefinition) {
	//if there is a row in the definition, it is the row used to give definitions for any columns of type 'table'.
	//we will need to look at it to find out the definition of the tables to put in those columns, 
	//and then delete it after we have finished adding all the rows.
	bool hasSubtables=(tableDefinition.getRowCount()==1);
	//fill the table with a random number of random rows
	for (unsigned int innerRowCount=rand()%4+1;innerRowCount>0;innerRowCount--) {
		insertRandomRow(table);
	}
	if (hasSubtables) tableDefinition.erase(tableDefinition.begin()); //delete the definition row if there is one
}

void TStoreTest::insertRandomRow(xdata::Table &table) {
	std::vector<std::string> columns=table.getColumns();
	vector<std::string>::iterator columnIterator;
	unsigned int rowIndex=table.getRowCount();
	for(columnIterator=columns.begin(); columnIterator!=columns.end(); columnIterator++) {
		string columnType=table.getColumnType(*columnIterator);
		xdata::Serializable *xdataValue=NULL;

		//...
		//set xdataValue to a random value if it is a scalar type
		//(see complete code in TStoreTest)

		else if (columnType=="table") {
			xdata::Table *sampleTable=static_cast<xdata::Table *>(table.getValueAt(0,*columnIterator));
			xdata::Table *newTable=new xdata::Table(*sampleTable);
			insertRandomRowsIntoTable(*newTable);
			xdataValue=newTable;
		}
		if (xdataValue) {
			table.setValueAt(rowIndex,*columnIterator,*xdataValue);
			delete xdataValue;
		}
	}
}

Delete and Clear

Currently a NestedView will only delete or clear from a single table. To control what happens to the related records in child tables, use the ON DELETE clause when you specify the foreign key constraints.

Parameters

A single NestedView can provide access to any table in a database. To identify which one to access, every query, definition, insert, delete or clear message must include the view parameter called table .

You can also provide a depth parameter to say how deep the hierarchy of tables and child tables should go. By default this is 0, which means there will be no child tables, just an xdata::Table corresponding to a single database table. It is also possible to configure default depth parameters for certain tables.

Other parameters, to be used in the where clause for the main table, can be defined in the configuration.

Configuration

No configuration apart from the standard view configuration is necessary. A NestedView can access all the tables in the schema which the user has access to. However, it is possible to provide a default depth and a where clause for each table. Configuration specific to NestedView must be in the urn:tstore-view-Nested namespace.

Default depth

It is possible to provide default values for the depth parameter in the configuration. This is done using a tag giving a table name and depth, as in the following example:

<tstore:configuration xmlns:tstore="urn:xdaq-tstore:1.0" xmlns:nested="urn:tstore-view-Nested">
  <tstore:view id="urn:tstore-view-Nested:MyNestedView">
    <tstore:connection dbname="db" username="scott"/>
	<nested:table name="wife" depth="2"/>
  </tstore:view>
</tstore:configuration>

With this configuration, if you access WIFE through urn:tstore-view-Nested:MyNestedView and you don't specify a depth parameter, you will get a table with depth 2 (assuming there are tables linked to WIFE by foreign keys, as there are in the example.) You can still access other tables, but if you don't specify a depth parameter, you will get a table with depth 0.

Where clause

By default, all rows of the outer table are returned, and all related rows of other tables. This can waste a lot of bandwidth if you are only interested in some of the rows. From the next release of TStore, you will be able to specify a parameterised where clause for the table in a CDATA section, similar to that used in an SQLView. The where clause only affects the table it is defined for, and only when it is the table parameter of a query message. It never affects child tables in a query. The rows fetched from child tables are already restricted to match the enclosing row according to the foreign key constraint.

You can use the tag to define parameters, which will be passed as part of the SOAP query message. Within tags, you can give the default value in a CDATA section. Whitespace will not be trimmed from the default value. If no value is sent for a parameter, and there is no default defined, then an empty string will be used. In the SQL statement, you can refer to the parameters by preceding the parameter name with a $ sign.

By default, a $parameter in a query is simply replaced in the SQL text by the given parameter value. So it can be the name of a table or column, or anything. However, if the parameter is simply a value to match in a where clause, for example, then for the sake of efficiency (as recommended by Oracle Support at CERN), it should be a bind variable. The TStore application is not able to tell whether this is the case or not, so you have to specify a bind="yes" attribute in the tag. In the following example:

<nested:table name="wife" depth="2">
	<nested:parameter bind="yes" name="wifename">
		<![CDATA[Jane]]>
      </nested:parameter>
      <![CDATA[where name=$wifename]]>
</nested:table>

if you query the WIFE table without any parameters, you will just get the row with the name Jane, and (because the query has depth 2) all of Jane's sacks and cats. If you set the nested:wifename parameter to 'Joan' in your query message, you will get the row for Joan, and all of her sacks and cats. This does not affect queries on other tables, so if you run a query of depth 1 or more on SACK , you will still get both Joan's and Jane's sacks, and the information on Joan and Jane in the appropriate rows of the WIFE_OWNER->NAME column.