DocumentationCampaignCampaign Classic v7 Documentation

Key management in schemas management-of-keys

Last update: Wed Sep 18 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Developer

Each table associated with a data schema must have at least one key for identifying a record in a table.

A key is declared from the main element of the data schema.

<key name="name_of_key">
  <keyfield xpath="xpath_of_field1"/>
  <keyfield xpath="xpath_of_field2"/>
  ...
</key>

A key is known as a ‘primary key’ when it is the first in the schema to be populated, or if it contains the internal attribute set to “true”.

The following rules apply to keys:

  • A key can reference one or more fields in the table
  • A unique index is declared implicitly for each key definition. The creation of an index on the key can be prevented by setting the noDbIndex attribute to “true”.
NOTE

  • As a standard, keys are the elements declared from the main element of the schema after indexes have been defined.

  • Keys are created during table mapping (standard or FDA), Adobe Campaign finds unique indexes.

Example:

  • Adding a key to the email address and city:

    code language-sql 
    <srcSchema name="recipient" namespace="cus">
  <element name="recipient">
    <key name="email">
      <keyfield xpath="@email"/>
      <keyfield xpath="location/@city"/>
    </key>

    <attribute name="email" type="string" length="80" label="Email" desc="Email address of recipient"/>
    <element name="location" label="Location">
      <attribute name="city" type="string" length="50" label="City" userEnum="city"/>
    </element>
  </element>
</srcSchema>

    The schema generated:

    code language-sql 
    <schema mappingType="sql" name="recipient" namespace="cus" xtkschema="xtk:schema">
  <element name="recipient" sqltable="CusRecipient">
   <dbindex name="email" unique="true">
     <keyfield xpath="@email"/>
     <keyfield xpath="location/@city"/>
   </dbindex>

   <key name="email">
    <keyfield xpath="@email"/>
    <keyfield xpath="location/@city"/>
   </key>

   <attribute desc="Email address of recipient" label="Email" length="80" name="email" sqlname="sEmail" type="string"/>
   <element label="Location" name="location">
     <attribute label="City" length="50" name="city" sqlname="sCity" type="string" userEnum="city"/>
   </element>
  </element>
</schema>

  • Adding a primary or internal key on the “id” name field:

    code language-sql 
    <srcSchema name="recipient" namespace="cus">
  <element name="recipient">
    <key name="id" internal="true">
      <keyfield xpath="@id"/>
    </key>

    <key name="email" noDbIndex="true">
      <keyfield xpath="@email"/>
    </key>

    <attribute name="id" type="long" label="Identifier"/>
    <attribute name="email" type="string" length="80" label="Email" desc="Email address of recipient"/>
  </element>
</srcSchema>

    The schema generated:

    code language-sql 
    <schema mappingType="sql" name="recipient" namespace="cus" xtkschema="xtk:schema">
  <element name="recipient" sqltable="CusRecipient">
    <key name="email">
      <keyfield xpath="@email"/>
    </key>

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

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

    <attribute label="Identifier" name="id" sqlname="iRecipientId" type="long"/>
    <attribute desc="Email address of recipient" label="Email" length="80" name="email" sqlname="sEmail" type="string"/>
  </element>
</schema>

Auto-incremental key auto-incremental-key

The primary key of most Adobe Campaign tables is a 32-bit long integer auto-generated by the database engine. The calculation of the key value depends on a sequence (by default, the XtkNewId SQL function) generating a number that is unique in the entire database. The content of the key is automatically entered on insertion of the record.

The advantage of an incremental key is that it provides a non-modifiable technical key for the joins between tables. In addition, this key does not occupy much memory because it uses a double-byte integer.

You can specify in the source schema the name of the sequence to be used with the pkSequence attribute. If this attribute is not given in the source schema, the XtkNewId default sequence will be used. The application uses dedicated sequences for the nms:broadLog and nms:trackingLog schemas (NmsBroadLogId and NmsTrackingLogId respectively) because these are the tables that contain the most records.

From ACC 18.10, XtkNewId is no more the default value for the sequence in the out-of-the-box schemas. You are now able to build schema or to extend existing schema with a dedicated sequence.

IMPORTANT
When creating a new schema or during a schema extension, you need to keep the same primary key sequence value (@pkSequence) for the whole schema.

A sequence referenced in an Adobe Campaign schema (NmsTrackingLogId for example) must be associated with an SQL function that returns the number of IDs in the parameters, separated by commas. This function must be called GetNew XXX Ids, where XXX is the name of the sequence (GetNewNmsTrackingLogIds for example). View the postgres-nms.sql, mssql-nms.sql or oracle-nms.sql files provided with the application in the datakit/nms/eng/sql/ directory to recover the example of a ‘NmsTrackingLogId’ sequence creation for each database engine.

To declare a unique key, populate the autopk attribute (with value “true”) on the main element of the data schema.

Example:

Declaring an incremental key in the source schema:

<srcSchema name="recipient" namespace="cus">
  <element name="recipient" autopk="true">
  ...
  </element>
</srcSchema>

The schema generated:

<schema mappingType="sql" name="recipient" namespace="cus" xtkschema="xtk:schema">
  <element name="recipient" autopk="true" pkSequence="XtkNewId" sqltable="CusRecipient">
    <dbindex name="id" unique="true">
      <keyfield xpath="@id"/>
    </dbindex>

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

    <attribute desc="Internal primary key" label="Primary key" name="id" sqlname="iRecipientId" type="long"/>
  </element>
</schema>

In addition to the definition of the key and its index, a numeric field called “id” has been added to the extended schema in order to contain the auto-generated primary key.

IMPORTANT
A record with a primary key set to 0 is automatically inserted on creation of the table. This record is used to avoid outer joins, which are not effective on volume tables. By default, all foreign keys are initialized with value 0 so that a result can always be returned on the join when the data item is not populated.

Learn more

Browse the following links to learn more:

recommendation-more-help
601d79c3-e613-4db3-889a-ae959cd9e3e1