擴展 nms:收件人 方案收件人表,應用以下過程:
建立擴展架構(cus:擴展)使用以下資料:
<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:收件人 使用枚舉欄位(schema)進行補充面積)。
記住添加 擴展架構 屬性,以引用擴展架構。
檢查擴展架構是否 nms:收件人 模式,並且存在附加資料:
<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指令碼如下所示:
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);
本節介紹如何建立連結到基數為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>
表類型為 奧托普 以建立自動生成的主鍵,該主鍵用於連結與收件人表的連接。
已生成架構:
<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);
SQL命令在指令碼末尾處的INSERT INTO允許您插入標識符記錄集為0以模擬外連接。
擴展表允許您擴展連結基數1-1表中現有表的內容。
擴展表的目的是避免對表中支援的欄位數的限制,或優化資料所佔用的空間,而資料是按需佔用的。
建立擴展表架構(cus:功能):
<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>
必須從包含外鍵的架構中填充收件人表和擴展表之間連結的定義。
用於建立擴展表的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);
溢出表是擴展表(基數1-1),但到要擴展的表的連結聲明將填充在溢出表的架構中。
溢出表包含要擴展的表的外鍵。 因此,不會修改要擴展的表。 兩個表之間的關係是要擴展的表的主鍵的值。
建立溢出表架構(cus:溢出):
<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>
溢出表的主鍵是指向要擴展的表的連結(本例中為「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);
使用關係表可以連結兩個基數為N-N的表。此表僅包含要連結的表的外鍵。
組之間關係表的示例(nms:group)和收件人(nms:收件人)。
關係表的源架構:
<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"/>
用戶介面將不顯示連結,而顯示欄位。 當用戶選取該欄位時,他們可以從參照表中選擇值或使用自動完成功能。
要使其自動完成,必須在引用表中定義compute-string。
添加 noDbIndex="true" 屬性,以防止Adobe Campaign在連結的源表中儲存的值上建立索引。