Show Menu
TOPICS×

Examples of schemas edition

Extending a table

To extend the nms:recipient schema recipient table, apply the following procedure:
  1. Create the extension schema ( cus:extension ) using the following data:
    <srcSchema mappingType="sql" name="extension" namespace="cus" xtkschema="xtk:srcSchema" extendedSchema="nms:recipient">  
      <enumeration basetype="string" default="area1" name="area">    
        <value label="Zone 1" name="area1"/>    
        <value label="Zone 2" name="area2"/>  
      </enumeration>  
    
      <element name="extension">    
        <dbindex name="area">      
          <keyfield xpath="location/@area"/>    
        </dbindex>      
    
        <attribute label="Loyalty code" name="fidelity" type="long"/>    
        <element name="location">      
          <attribute name="area" label="Purchasing zone" type="string" length="50" enum="area"/>    
        </element>  </element>  
    </srcSchema>
    
    
    In this example, an indexed field ( fidelity ) is added, and the location element (which already existed in the nms:recipient schema) is supplemented with an enumerated field ( area ).
    Remember to add the extendedSchema attribute to reference the extension schema.
  2. Check that the extended schema is the nms:recipient schema and that the additional data is present:
    <schema dependingSchemas="cus:extension" mappingType="sql" name="recipient" namespace="nms" xtkschema="xtk:schema">
      ...
      <enumeration basetype="string" default="area1" name="area">    
        <value label="Zone 1" name="area1"/>    
        <value label="Zone 2" name="area2"/>  
      </enumeration>
      ...
      <element autopk="true" name="recipient" sqltable="NmsRecipient"> 
        <dbindex name="area">      
          <keyfield xpath="location/@area"/>    
        </dbindex>
    
        ...
        <attribute belongsTo="cus:extension" label="Loyalty code" name="fidelity" sqlname="iFidelity" type="long"/>
        <element name="location">
          ...
          <attribute enum="area" label="Purchasing zone" length="50" name="area" sqlname="sArea" type="string"/>
        </element>
        ...
      </element>
    </schema>
    
    
    The SQL script generated from the database update wizard is as follows:
    ALTER TABLE NmsRecipient ADD iFidelity INTEGER;
    UPDATE NmsRecipient SET iFidelity = 0;
    ALTER TABLE NmsRecipient ALTER COLUMN iFidelity SET NOT NULL;ALTER TABLE NmsRecipient ALTER COLUMN iFidelity SET Default 0;
    ALTER TABLE NmsRecipient ADD sArea VARCHAR(50); 
    CREATE INDEX NmsRecipient_area ON NmsRecipient(sArea);
    
    

Linked collection table

This section describes how to create an order table linked to the recipient table with cardinality 1-N.
Order table source schema:
<srcSchema label="Order" name="order" namespace="cus" xtkschema="xtk:srcSchema">  
  <element autopk="true" name="order">    
    <compute-string expr="@number" + '(' + ToString(@date) + ')'/>    
    
    <attribute label="Number" length="128" name="number" type="string"/>    
    <attribute desc="Order date" label="Date" name="date" type="datetime" default="GetDate()"/>    
    <attribute desc="order total" label="Total" name="total" type="double"/>    
    <element label="Recipient" name="recipient" revDesc="Orders associated with this recipient" revIntegrity="own" revLabel="Orders" target="nms:recipient" type="link"/>  
  </element>
</srcSchema>

The table type is autopk in order to create an auto-generated primary key to be used by the join of the link to the recipient table.
Schema generated:
<schema label="Order" mappingType="sql" name="order" namespace="cus" xtkschema="xtk:schema">  
  <element autopk="true" label="Order" name="order" sqltable="CusOrder">    
    <compute-string expr="ToString(@date) + ' - ' + @number"/>    

    <dbindex name="id" unique="true">      
      <keyfield xpath="@id"/>    
    </dbindex>    

    <key internal="true" name="id">      
      <keyfield xpath="@id"/>    
    </key>    

    <dbindex name="recipientId">      
      <keyfield xpath="@recipient-id"/>    
    </dbindex>    

    <attribute desc="Internal primary key" label="Primary key" name="id" sqlname="iOrderId" type="long"/>    
    <attribute label="Number" length="128" name="number" sqlname="sNumber" type="string"/>    
    <attribute desc="Order date" label="Date" name="date" sqlname="tsDate" type="datetime"/>    
    <attribute desc="order total" label="Total" name="total" sqlname="Total" type="double"/>    
    <element label="Recipient" name="recipient" revLink="order" target="nms:recipient" type="link">      
      <join xpath-dst="@id" xpath-src="@recipient-id"/>    
    </element>    
    <attribute advanced="true" label="Foreign key of 'Recipient' link ('id' field)" name="recipient-id" sqlname="iRecipientId" type="long"/>  
   </element>
</schema>

The table creation SQL script is as follows:
CREATE TABLE CusOrder(dTotal DOUBLE PRECISION NOT NULL Default 0, iOrderId INTEGER NOT NULL Default 0, iRecipientId INTEGER NOT NULL Default 0, sNumber VARCHAR(128), tsDate TIMESTAMP Default NULL);
CREATE UNIQUE INDEX CusOrder_id ON CusOrder(iOrderId);
CREATE INDEX CusOrder_recipientId ON CusOrder(iRecipientId);  
INSERT INTO CusOrder (iOrderId) VALUES (0); 

The SQL command INSERT INTO at the end of the script lets you insert an identifier record set to 0 in order to simulate outer joins.

Extension table

An extension table lets you extend the content of an existing table in a linked table of cardinality 1-1.
The purpose of an extension table is to avoid limitations on the number of fields supported in a table, or to optimize the space occupied by the data, which is consumed on demand.
Creating the extension table schema ( cus:feature ):
<srcSchema mappingType="sql" name="feature" namespace="cus" xtkschema="xtk:srcSchema">  
  <element autopk="true" name="feature">    
    <attribute label="Children" name="children" type="byte"/>    
    <attribute label="Single" name="single" type="boolean"/>    
    <attribute label="Spouse first name" length="100" name="spouseFirstName" type="string"/>  
  </element>
</srcSchema>

Creating an extension schema on the recipient table to add the link of cardinality 1-1:
<srcSchema extendedSchema="nms:recipient" label="Recipient" mappingType="sql" name="recipient" namespace="cus" xtkschema="xtk:srcSchema">  
  <element name="recipient">    
    <element desc="Features" integrity="own" label="Features" name="feature" revCardinality="single" revLink="recipient" target="cus:feature" type="link"/> 
  </element>
</srcSchema>

The definition of the link between the recipient table and the extension table must be populated from the schema containing the foreign key.
The SQL script for creating the extension table is as follows:
CREATE TABLE CusFeature(  iChildren NUMERIC(3) NOT NULL Default 0, iFeatureId INTEGER NOT NULL Default 0, iSingle NUMERIC(3) NOT NULL Default 0, sSpouseFirstName VARCHAR(100));
CREATE UNIQUE INDEX CusFeature_id ON CusFeature(iFeatureId);  
INSERT INTO CusFeature (iFeatureId) VALUES (0); 

The recipient table SQL update script is as follows:
ALTER TABLE NmsRecipient ADD iFeatureId INTEGER;
UPDATE NmsRecipient SET iFeatureId = 0;
ALTER TABLE NmsRecipient ALTER COLUMN iFeatureId SET NOT NULL;
ALTER TABLE NmsRecipient ALTER COLUMN iFeatureId SET Default 0;
CREATE INDEX NmsRecipient_featureId ON NmsRecipient(iFeatureId);

Overflow table

An overflow table is an extension table (cardinality 1-1), but the declaration of the link to the table to be extended is populated in the schema of the overflow table.
The overflow table contains the foreign key to the table to be extended. The table to be extended is therefore not modified. The relation between the two tables is the value of the primary key of the table to be extended.
Creating the overflow table schema ( cus:overflow ):
<srcSchema label="Overflow" name="overflow" namespace="cus" xtkschema="xtk:srcSchema">  
  <element name="overflow">    
    <key internal="true" name="id">      
      <keyfield xlink="recipient"/>    
    </key>    

    <attribute label="Children" name="children" type="byte"/>    
    <attribute label="Single" name="single" type="boolean"/>    
    <attribute label="Spouse first name" length="100" name="spouseFirstName" type="string"/>  
    <element label="Customer" name="recipient" revCardinality="single" revIntegrity="own" revExternalJoin="true" target="nms:recipient" type="link"/>  
  </element>  
</srcSchema>

The primary key of the overflow table is the link to the table to be extended ("nms:recipient" schema in our example).
The table creation SQL script is as follows:
CREATE TABLE CusOverflow(iChildren NUMERIC(3) NOT NULL Default 0, iRecipientId INTEGER NOT NULL Default 0, iSingle NUMERIC(3) NOT NULL Default 0, sSpouseFirstName VARCHAR(100));
CREATE UNIQUE INDEX CusOverflow2_id ON CusOverflow2(iRecipientId);  

Relationship table

A relationship table lets you link two tables with cardinality N-N. This table contains only the foreign keys of the tables to be linked.
Example of a relationship table between groups ( nms:group ) and recipients ( nms:recipient ).
Source schema of the relationship table:
<srcSchema name="rcpGrpRel" namespace="cus">
  <element name="rcpGrpRel">
    <key internal="true" name="id">      
      <keyfield xlink="rcpGroup"/>      
      <keyfield xlink="recipient"/>    
    </key>

    <element integrity="neutral" label="Recipient" name="recipient" revDesc="Groups to which this recipient belongs" revIntegrity="own" revLabel="Groups" target="nms:recipient" type="link"/>    
    <element integrity="neutral" label="Group" name="rcpGroup" revDesc="Recipients in the group" revIntegrity="own" revLabel="Recipients" revLink="rcpGrpRel" target="nms:group" type="link"/>
  </element>
</srcSchema>

The schema generated is as follows:
<schema mappingType="sql" name="rcpGrpRel" namespace="cus" xtkschema="xtk:schema">  
  <element name="rcpGrpRel" sqltable="CusRcpGrpRel">    
    <compute-string expr="ToString([@rcpGroup-id]) + ',' + ToString([@recipient-id])"/>    
    <dbindex name="id" unique="true">      
      <keyfield xpath="@rcpGroup-id"/>      
      <keyfield xpath="@recipient-id"/>    
    </dbindex>    

    <key internal="true" name="id">      
      <keyfield xpath="@rcpGroup-id"/>      
      <keyfield xpath="@recipient-id"/>    
    </key>    

    <dbindex name="rcpGroupId">      
      <keyfield xpath="@rcpGroup-id"/>    
    </dbindex>    
    <dbindex name="recipientId">      
      <keyfield xpath="@recipient-id"/>    
    </dbindex>    

    <element integrity="neutral" label="Recipient" name="recipient" revLink="rcpGrpRel" target="nms:recipient" type="link">      
      <join xpath-dst="@id" xpath-src="@recipient-id"/>    
    </element>    
    <attribute advanced="true" label="Foreign key of 'Recipient' link ('id' field)" name="recipient-id" sqlname="iRecipientId" type="long"/>    

    <element integrity="neutral" label="Group" name="rcpGroup" revLink="rcpGrpRel" target="nms:group" type="link">      
      <join xpath-dst="@id" xpath-src="@rcpGroup-id"/>    
    </element>    
    <attribute advanced="true" label="Foreign key of 'Group' link ('id' field)" name="rcpGroup-id" sqlname="iRcpGroupId" type="long"/>  
  </element>
</schema>

The table creation SQL script is as follows:
CREATE TABLE CusRcpGrpRel( iRcpGroupId INTEGER NOT NULL Default 0, iRecipientId INTEGER NOT NULL Default 0);
CREATE UNIQUE INDEX CusRcpGrpRel_id ON CusRcpGrpRel(iRcpGroupId, iRecipientId);
CREATE INDEX CusRcpGrpRel_recipientId ON CusRcpGrpRel(iRecipientId);