Show Menu
TOPICS×

Database mapping

The SQL mapping of our example schema gives the following XML document:
<schema mappingType="sql" name="recipient" namespace="cus" xtkschema="xtk:schema">
  <enumeration basetype="byte" name="gender">    
    <value label="Not specified" name="unknown" value="0"/>    
    <value label="Male" name="male" value="1"/>    
    <value label="Female" name="female" value="2"/> 
  </enumeration>  

  <element name="recipient" sqltable="CusRecipient">    
    <attribute desc="Recipient e-mail address" label="Email" length="80" name="email" sqlname="sEmail" type="string"/>    
    <attribute default="GetDate()" label="Date of creation" name="created" sqlname="tsCreated" type="datetime"/>    
    <attribute enum="gender" label="Gender" name="gender" sqlname="iGender" type="byte"/>    
    <element label="Location" name="location">      
      <attribute label="City" length="50" name="city" sqlname="sCity" type="string" userEnum="city"/>    
    </element>  
  </element>
</schema>

Description

The root element of the schema is no longer <srcschema> , but <schema> .
This takes us to another type of document, which is generated automatically from the source schema, simply referred to as the schema. This schema will be used by the Adobe Campaign application.
The SQL names are determined automatically based on element name and type.
The SQL naming rules are as follows:
  • table: concatenation of the schema namespace and name
    In our example, the name of the table is entered via the main element of the schema in the sqltable attribute:
    <element name="recipient" sqltable="CusRecipient">
    
    
  • field: name of the element preceded by a prefix defined according to type ('i' for integer, 'd' for double, 's' for string, 'ts' for dates, etc.)
    The field name is entered via the sqlname attribute for each typed <attribute> and <element> :
    <attribute desc="E-mail address of recipient" label="Email" length="80" name="email" sqlname="sEmail" type="string"/> 
    
    
SQL names can be overloaded from the source schema. To do this, populate the "sqltable" or "sqlname" attributes on the element concerned.
The SQL script to create the table generated from the extended schema is as follows:
CREATE TABLE CusRecipient(
  iGender NUMERIC(3) NOT NULL Default 0,   
  sCity VARCHAR(50),   
  sEmail VARCHAR(80),
  tsCreated TIMESTAMP Default NULL);

The SQL field constraints are as follows:
  • no null values in numeric and date fields,
  • numeric fields are initialized to 0.

XML fields

By default, any typed <attribute> and <element> element is mapped onto an SQL field of the data schema table. You can, however, reference this field in XML instead of SQL, which means that the data is stored in a memo field ("mData") of the table containing the values of all XML fields. The storage of these data is an XML document that observes the schema structure.
To populate a field in XML, you must add the xml attribute with the value "true" to the element concerned.
Example : here are two examples of XML field use.
  • Multi-line comment field:
    <element name="comment" xml="true" type="memo" label="Comment"/>
    
    
  • Description of data in HTML format:
    <element name="description" xml="true" type="html" label="Description"/>
    
    
    The "html" type lets you store the HTML content in a CDATA tag and display a special HTML edit check in the Adobe Campaign client interface.
The use of XML fields lets you add fields without needing to modify the physical structure of the database. Another advantage is that you use less resources (size allocated to SQL fields, limit on the number of fields per table, etc.).
The main disadvantage is that it is impossible to index or filter an XML field.

Indexed fields

Indexes let you optimize the performance of the SQL queries used in the application.
An index is declared from the main element of the data schema.
<dbindex name="name_of_index" unique="true/false">
  <keyfield xpath="xpath_of_field1"/>
  <keyfield xpath="xpath_of_field2"/>
  ...
</key>

Indexes obey the following rules:
  • An index can reference one or more fields in the table.
  • An index can be unique (to avoid duplicates) in all of the fields if the unique attribute contains the value "true".
  • The SQL name of the index is determined from the SQL name of the table and the name of the index.
As a standard, indexes are the first elements declared from the main element of the schema.
Indexes are created automatically during table mapping (standard or FDA).
Example :
  • Adding an index to the e-mail address and city:
    <srcSchema name="recipient" namespace="cus">
      <element name="recipient">
        <dbindex name="email">
          <keyfield xpath="@email"/> 
          <keyfield xpath="location/@city"/> 
        </dbindex>
    
        <attribute name="email" type="string" length="80" label="Email" desc="E-mail address of recipient"/>
        <element name="location" label="Location">
          <attribute name="city" type="string" length="50" label="City" userEnum="city"/>
        </element>
      </element>
    </srcSchema>
    
    
  • Adding a unique index to the "id" name field:
    <srcSchema name="recipient" namespace="cus">
      <element name="recipient">
        <dbindex name="id" unique="true">
          <keyfield xpath="@id"/> 
        </dbindex>
    
        <dbindex name="email">
          <keyfield xpath="@email"/> 
        </dbindex>
    
        <attribute name="id" type="long" label="Identifier"/>
        <attribute name="email" type="string" length="80" label="Email" desc="E-mail address of recipient"/>
      </element>
    </srcSchema>
    
    

Management of keys

A table must have at least one key for identifying a record in the 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>

Keys obey the following rules:
  • A key can reference one or more fields in the table.
  • A key is known as 'primary' (or 'priority') when it is the first in the schema to be populated or if it contains the internal attribute with the value "true".
  • A unique index is declared implicitly for each key definition. The creation of an index on the key can be prevented by adding the noDbIndex attribute with the value "true".
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 e-mail address and city:
    <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="E-mail 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:
    <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="E-mail 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:
    <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="E-mail address of recipient"/>
      </element>
    </srcSchema>
    
    
    The schema generated:
    <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="E-mail address of recipient" label="Email" length="80" name="email" sqlname="sEmail" type="string"/>  
      </element>
    </schema>
    
    

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.
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 to a 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 appliation 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.
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.