v7
仅适用于 Campaign Classic v7

模式版本示例 examples-of-schemas-edition

扩展表 extending-a-table

要扩展 nms:recipient 模式收件人表,请按照以下步骤进行操作:

  1. 创建扩展架构(cus:extension),并使用以下数据:

    code language-none
    <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>
    

    在此示例中,索引字段(保真),并且 位置 元素(已存在于 nms:recipient schema)后附加一个枚举字段(区域)。

    note important
    IMPORTANT
    请记得添加 extendedschema 属性,以引用扩展架构。
  2. 检查扩展架构是否 nms:recipient 架构中是否存在其他数据:

    code language-none
    <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>
    

    通过数据库更新向导生成的SQL脚本如下所示:

    code language-none
    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

本节介绍如何创建链接到基数为1-N的收件人表的订单表。

订单表源架构:

<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>

表类型为 autopk 以创建一个自动生成的主键,供链接到收件人表的链接使用。

已生成架构:

<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>

表创建SQL脚本如下所示:

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);
NOTE
SQL命令INSERT INTO在脚本末尾用于插入设置为0的标识符记录,以模拟外部联接。

扩展表 extension-table

扩展表允许您扩展链接基数1-1表中的现有表的内容。

扩展表的目的是避免对表中支持的字段数的限制,或者优化数据占用的空间(按需使用)。

创建扩展表架构(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>

在收件人表上创建扩展模式以添加基数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>
NOTE
收件人表和扩展表之间链接的定义必须从包含外键的模式中填充。

用于创建扩展表的SQL脚本如下所示:

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);

收件人表SQL更新脚本如下所示:

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

溢出表是扩展表(基数1-1),但指向要扩展的表的链接的声明填充在溢出表的模式中。

溢出表包含要扩展的表的外键。 因此,不会修改要扩展的表。 两个表之间的关系是要扩展的表的主键值。

创建溢出表模式(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>
NOTE
溢出表的主键是指向要扩展的表的链接(在本例中为“nms:recipient”模式)。

表创建SQL脚本如下所示:

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

关系表允许您链接两个具有基数N-N的表。此表只包含要链接的表的外键。

组间关系表的示例(nms:group)和收件人(nms:recipient)。

关系表的源架构:

<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>

生成的架构如下:

<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>

表创建SQL脚本如下所示:

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);

此用例演示如何使用现有引用表作为内置Adobe Campaign枚举机制(enum、userEnum或dbEnum)的替代方法。

您还可以在架构中使用现有引用表作为枚举。 这可以通过在表和参考表之间创建链接以及添加属性来实现 displayAsField="true".

在本例中,参考表包含银行名称和标识符的列表:

<srcSchema entitySchema="xtk:srcSchema" img="cus:bank16x16.png" label="Bank" mappingType="sql" name="bank" namespace="cus"
xtkschema="xtk:srcSchema">
    <element img="cus:bank16x16.png" label="Banks" name="bank">
        <compute-string expr="@name"/>
        <key name="id">
            <keyfield xpath="@id"/>
        </key>
        <attribute label="Bank Id" name="id" type="short"/>
        <attribute label="Name" length="64" name="name" type="string"/>
     </element>
</srcSchema>

在使用此参考表的任何表中,定义链接并添加 displayAsField="true" 属性。

<element displayAsField="true" label="Bank" name="bank" target="cus:bank" type="link" noDbIndex="true"/>

用户界面不会显示链接,但会显示字段。 当用户选取该字段时,可以从参照表中选取值或使用自动完成功能。

  • 为了使其自动完成,必须在参考表中定义计算字符串。

  • 添加 noDbIndex="true" 属性,以防止Adobe Campaign对链接源表中存储的值创建索引。

相关主题

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