Avoiding duplicated data

For example:

  <ReportOptions

    xmlns="http://www.teradp.com/schemas/GN4/1/ReportOptions.xsd"

    NavOptions="CreationAndModified"

    TableName="ReportFolderTable">

    . . .

    <Conditions xmlns="http://www.teradp.com/schemas/GN4/1/SearchConditions.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      ObjectTypeName="folder">

      <SearchNode xsi:type="ConditionSearchNode" Op="Greater">

        <Left xsi:type="AttributeSearchNode" Path="folder.id" />

        <Right xsi:type="VariableSearchNode" Name="MaxId" Type="Integer" Optional="false" />

      </SearchNode>

      <OrderList IsAscending="true">

        <Item xsi:type="AttributeRef" AttributeTypeName="id" ObjectTypeName="folder"/>

      </OrderList>

    </Conditions>

    <Columns>

      <Column Name="Id" Type="Integer" SearchVariable="MaxId" Key="Unique" />

      <Column Name="Name" Type="String" Length="80" />

      <Column Name="Description" Type="String" Length="500" />

    </Columns>

  </ReportOptions>

If  you defined the SearchVariable, then GN4 searches the maximum value of that column into the table and stores the maximum value into the specified variable.

In the above example, the maximum value of the Id column is stored in the MaxId variable - it contains the greatest id of the rows already stored in the ReportFolderTable.

The query condition on folder.id in the configured SearchConditions looks only for the GN4 folders with id greater than MaxId.

This trick is used for tables containing list of objects (like ReportFolderTable, ReportTitleTable), so to insert only the new items (that is, items with new greater ids) each time the report is generated.

See http://forum.teradp.com/topic.asp?TOPIC_ID=702&FORUM_ID=8#3178 for more information about this.

For example:

  <ReportOptions

    xmlns:ropt="http://www.teradp.com/schemas/GN4/1/ReportOptions.xsd"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    NavOptions="None"

    TableName="ReportArticleFootprintTable">

. . .

    <Columns>

      <Column Name="ArticleId" Type="Integer" Key="Unique" />

      <Column Name="ActionName" Type="String" Length="80" Key="Unique" />

      <Column Name="UserId" Type="Integer" Key="Unique" />

      <Column Name="Time" Type="DateTime" Key="Unique" />

      <Column Name="InfoId" Type="Integer" />

      < Column Name="InfoName" Type="String" Length="80" />

    </Columns>

  </ReportOptions>

You can specify more than one 'unique' column.

GN4 creates a 'unique constraint' referencing all the columns that have the Key="Unique" attribute. Using this 'unique constraint' GN4 can detect, for example, if a row with a specific id already exists into the database: if the row does not exist, then it is inserted into the table; vice versa, if it exists, then it is updated.

See http://forum.teradp.com/topic.asp?TOPIC_ID=702&FORUM_ID=8#3330 for more information about this.

When writing to a report database, it is desirable to be able to add only new data, instead to have to re-extract everything every time.

This is possible filtering the extracted data, adding to the SearchConditions configuration a condition about an index value (for example, it can be the object creation date, or the object id).

This 'index value' is configured as variable parameter (a VariableSearchNode of the SearchConditions).

<Conditions xmlns="http://www.teradp.com/schemas/GN4/1/SearchConditions.xsd" ObjectTypeName="archiveObject">

  <SearchNode xsi:type="ConditionSearchNode" Op="And">

    <Left xsi:type="ConditionSearchNode" Op="Equal">

      <Left xsi:type="AttributeSearchNode" Path="archiveObject.folderRef.path" />

      <Right xsi:type="VariableSearchNode" Name="folderPath" Type="String" />

    </Left>

    <Right xsi:type="ConditionSearchNode" Op="Greater">

      <Left xsi:type="AttributeSearchNode" Path="archiveObject.creationDate" />

      <Right xsi:type="VariableSearchNode" Name="MaxDate" Type="DateTime" />

    </Right>

  </SearchNode>

</Conditions>

In the above example, only the archiveObjects with a creationDate value greater than the MaxDate parameter are returned by the search.

The MaxDate parameter is the maximum value of an already existing column into the report table, and it is specified by the SearchVariable attribute of the column definition.

 <Columns>

  <Column Name="Id" Type="Integer" />

  <Column Name="Type" Type="String" Length="80" />

  <Column Name="Size" Type="Long" />

  <Column Name="CreationDate" Type="DateTime" SearchVariable="MaxDate"/>

  <Column Name="Width" Type="Double" />

</Columns>

The process is:

1.GN4 reads the maximum value of the 'CreationDate' column from the report database ('select max(CreationDate) from...').

2.The 'MaxDate' variable parameter of the SearchConditions is substituted with the read max 'CreationDate' value.

3.The search in the GN4 database returns only the objects with a CreationDate greater than 'MaxDate', that is only the objects newer than the ones already stored into the report database.

In this way it is possible to consider only the more recent GN4 data when generating the report.