Table nesting example

The nesting example below is taken from the RealTime menu item and reused for the Farms menu item.

1. Open the %DNADataRoot%\ADE\config\ADE\TreeLayout\Schema\custom\<menu item name>\DNATreeLayoutSchema.Alter.xml file.

   In this example, we are using the Farms menu item.

2. Refer to the %DNAInstallRoot%\ADE\config\ADE\TreeLayout\Schema\baseline\RealTime\DNATreeLayoutSchema.xml file for a completed example.

To highlight the hierarchy structure and the most important attributes required for nesting tables, the example below includes only certain attributes for each table. The ViewType element defines the menu items displayed by the tables. You can define multiple ViewType elements in the same DNATreeLayoutSchema.xml file or DNATreeLayoutSchema.Alter.xml file. The following example explains how to build nesting tables using the area, group, and remote tables.

<?xml version="1.0" encoding="UTF-8"?>

<DNATreeLayoutSchema xmlns="http://www.telvent.com/DNATreeLayoutSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.telvent.com/DNATreeLayoutSchema

..\..\..\DNATreeLayoutSchema.xsd">

<Views ViewType="Farms">

<Tables Type="Table"

Label="Area"

TableName="Area"

PrimaryKeyField="name"

. . .

FromClause="FROM &quot;area&quot;"

WhereClause="">

<Tables Type="Table"

Label="Group"

TableName="group"

PrimaryKeyField="name"

ColumnsLinkingToParentNodes="areaId,,"

. . .

FromClause="FROM &quot;group&quot;, &quot;groupAreaJoin&quot;"

WhereClause="WHERE &quot;groupAreaJoin&quot;.areaId='{0}' AND &quot;groupAreaJoin&quot;.grpId=&quot;group&quot;.name">

<Tables Type="Table"

Label="Remote"

TableName="remote"

PrimaryKeyField="name"

ColumnsLinkingToParentNodes="group,"

. . .

FromClause="FROM &quot;remote&quot;"

WhereClause="WHERE &quot;group&quot;='{0}'">

<Tables Type="Table"

Label="Remote-Connection"

TableName="remconnjoin"

PrimaryKeyField="cno"

OtherKeyField="rno"

ColumnsLinkingToParentNodes="rno,"

. . .

FromClause="FROM &quot;remconnjoin&quot;"

WhereClause="WHERE &quot;rno&quot;='{0}'">

</Tables>

<Tables Type="Table"

Label="Analog"

TableName="analog"

PrimaryKeyField="name"

ColumnsLinkingToParentNodes="rtu,group,"

. . .

FromClause="FROM &quot;analog&quot;"

WhereClause="WHERE &quot;rtu&quot;='{0}' AND &quot;group&quot;='{1}'">

</Tables>

<Tables Type="Table"

Label="Connection"

TableName="connection"

PrimaryKeyField="name"

ColumnsLinkingToParentNodes="rname,"

. . .

FromClause="FROM &quot;connection&quot;, &quot;remconnjoin&quot;"

WhereClause="WHERE &quot;remconnjoin&quot;.rname='{0}' AND &quot;remconnjoin&quot;.cname=&quot;connection&quot;.name" >

</Tables>

</Tables>

</Tables>

</Tables>

<Tables Type="Table"

Label="Farms"

TableName="farms"

PrimaryKeyField="name"

. . .

FromClause="FROM &quot;farms&quot;"

WhereClause="">

</Tables>

</Views>

<Views ViewType="Realtime">

<Tables Type="Table"

Label="Farms"

TableName="farms"

PrimaryKeyField="name"

. . .

FromClause="FROM &quot;farms&quot;"

WhereClause="">

</Tables>

</Views>

</DNATreeLayoutSchema>

• The Area table is one of the tables defined in the Farms view. The Area table is the first level of a hierarchy. The FromClause defines the data as coming from the RealTime database (RTDB) area table. The last line in the Tables element is WhereClause="">, which states that this is the end of the Tables element. In this example, the Tables element ends with >, which indicates that tables will be nested under the Area table in the Advanced Database Editor (ADE). Expanding the Area table displays all the areas defined in the system.

• The second level in this hierarchy is the Group table. When you expand one of the areas under the Area table in the ADE window, ADE displays all the groups associated with the area. The Tables element defined for the Group table is linked to the previous level using the ColumnsLinkingToParentNodes attribute. The relationship between the RTDB group and area tables is defined in the GroupAreaJoin table. To determine the groups defined for an area, the system queries the GroupAreaJoin table using the area opened in ADE. The value for the opened area is obtained using '{0}', which indicates that the value comes from the parent table. If the value comes from one level above the parent table, '{1}' is used, as seen in the analog table defined for the remote table.

  • The FromClause queries both group and GroupAreaJoin tables to retrieve the name column from the group table. The name column is defined in the PrimaryKeyField.

  • The WhereClause defines the relationship between the group and GroupAreaJoin tables. A query is used to define the relationship between the group and GroupAreaJoin tables. The name column in the group table is the same as the grpId column in the GroupAreaJoin table, WHERE "groupAreaJoin".areaId='{0}' AND "groupAreaJoin".grpId="group".name.

  • The ColumnsLinkingToParentNodes attribute indicates that the areaId column in the GroupAreaJoin table is used to restrict the returning value from the GroupAreaJoin table.

• The third level in the hierarchy for this example is the Remote table. Area is not a column defined in the RTDB remote table, but group is a value defined in the remote table. To restrict the remote record name returned from the query, the system uses the Group opened in the previous level.

  • PrimaryKeyField identities the column name of the return data, which is the name column in the remote table.

  • FromClause defines remote as the table to query for data.

  • The WhereClause defines the constraints to return remotes with the Group defined in the parent level. The '{0}' is used to represent the Group value from the previous level. The group was defined in the parent or previous level and the '{0}' is used to represent the value from the previous level.

  • ColumnsLinkingToParentNodes indicates that the group column is linking the remote table with the parent level.

• The fourth level in the hierarchy for this example contains Analog, Connection, and Remote-Connection tables. The Analog table is used as an example. There are many analog points defined with the same Group, but they may belong to different remotes or be manual points. Points belonging to a remote may have different groups defined. To retrieve analog points belonging to the remote for a particular group, the remote and group column in the analog table must be used to restrict the return data.

  • PrimaryKeyField defines name as the column to return the value.

  • FromClause indicates that the name of the table is analog.

  • The WhereClause requires two values to uniquely identify and return the correct values. The two identifiers come from level 2 and 3. '{0}' is used to retrieve for the previous level, while '{1}' is used for the second level. As a result, the query is FROM "remote" WHERE "rtu"='{0}' AND "group"='{1}'. This query uses the expanded value from the RTDB group and remote records.

  • ColumnsLinkingToParentNodes indicates that the constraints are the rtu and group columns from the remote table.