Unica Campaign system tables: List of changes by version

This topic identifies system database schema changes by version.

reserves the right, at any time, to change the system table schemas. If you choose to develop custom integrations using the Unica Campaign system tables, these components must be reviewed and possibly modified to work with future releases of the Unica Campaign system tables.

There is no guarantee of backwards compatibility or automated migration for custom-developed or third-party extensions using the Unica Campaign system tables. Unless otherwise stated, does not support any use of the Unica Campaign system tables outside of standard application use that is conducted through the Unica Campaign application or standard tools shipped as part of the product.

Note: The column data types that are indicated below are specific to certain database types and are not applicable to all database types.

System database schema changes for v12.1.2

  • System database schema changes for v12.1.2
    • CREATE TABLE UA_TableMappingXRef (

    TableMappingXRef_ID bigint NOT NULL,

    FlowchartID bigint NOT NULL,

    Datasource nvarchar(255) NOT NULL,

    Audience nvarchar(255) NOT NULL,

    Type nvarchar(64) NOT NULL,

    UserTablename nvarchar(255) NOT NULL,

    IDs nvarchar(1024) NOT NULL,

    CONSTRAINT cTableMappingXRef_PK

    PRIMARY KEY (TableMappingXRef_ID)

    );

    • CREATE TABLE UA_Audiences (

    ID bigint NOT NULL,

    Name nvarchar(64) NOT NULL,

    Fields nvarchar(1024) NOT NULL,

    CHTableName nvarchar(64) NULL,

    DCHTableName nvarchar(64) NULL,

    RHTableName nvarchar(64) NULL,

    SEGTableName nvarchar(64) NULL,

    CONSTRAINT cAudiences_PK PRIMARY KEY(ID)

    );

    • CREATE TABLE UA_STO_METRICS (

    CustomerID bigint NOT NULL,

    ContactEmail varchar(890) NULL,

    CustomerStatus int NULL,

    PreferredSendTime varchar(8) NOT NULL,

    STOOpenCount int NOT NULL,

    STOGenDate datetime NOT NULL,

    TimeZone varchar(64) NULL,

    CONSTRAINT tStoMetrics_PK

    PRIMARY KEY (CustomerID ASC)

    );

Note: These tables are available from v12.1.2

System database schema changes for v12.1.0.3

  • The following changes were made in the table:

CREATE TABLE UA_JourneyEsInfo (

CampaignID bigint NOT NULL,

FlowchartID bigint NOT NULL,

EntrySourceID bigint NOT NULL,

CONSTRAINT cUA_JourneyEsInfo_PK

PRIMARY KEY (CampaignID, FlowchartID, EntrySourceID)

);

Note: This table is available from fix pack 3.

System database schema changes for v12.1.0.2

  • The following changes were made in the table:

CREATE TABLE ua_attribute_default_value (

AttributeID bigint NOT NULL,

StringValue varchar(1024) NULL,

NumberValue float NULL,

DatetimeValue timestamp NULL,

ClobValue CLOB,

CONSTRAINT cUA_ATTRIBUTE_DEFAULT_VALUE_FK1

FOREIGN KEY (AttributeID)

REFERENCES UA_AttributeDef(AttributeID)

);

CREATE TABLE UA_AttributeDependent (

OfferTemplID bigint NOT NULL,

AttributeID bigint NOT NULL,

DependentAttrID bigint NOT NULL,

CONSTRAINT cAttributeDependent_PK

PRIMARY KEY (OfferTemplID, AttributeID, DependentAttrID)

);

ALTER TABLE UA_AttributeDef ADD column LKUP_TABLE varchar(256) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_ID_COLUMN varchar(256) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_DISP_COLUMN varchar(256) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_SORT_COLUMN varchar(256) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_ID_COL_DBTYPE varchar(50) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_SORT_COL_DBTYPE varchar(50) NULL;

ALTER TABLE UA_AttributeDef ADD column LKUP_SORT_ASCENDING varchar(5) NULL;

CALL sysproc.admin_cmd('REORG TABLE UA_AttributeDef');

ALTER TABLE UA_OfferTemplAttr ADD COLUMN ClobValue CLOB;

CALL sysproc.admin_cmd('REORG TABLE UA_OfferTemplAttr');

ALTER TABLE UA_OfferAttribute ADD COLUMN ClobValue CLOB;

CALL sysproc.admin_cmd('REORG TABLE UA_OfferAttribute');

Note: This table is available from fix pack 2.

System database schema changes for v12.1.0

  • The following changes were made in the table:

CREATE INDEX cOfferHistory_IX1 ON UA_OfferHistory

(OfferID ,CRC);

CREATE INDEX enumAttValues_IX1 ON UA_EnumAttrValues

(AttributeID);

CREATE INDEX cOfferTemplateAttr_IX1 ON UA_OfferTemplAttr

(OfferTemplID);

CREATE INDEX cOfferAttr_IX1 ON UA_OfferAttribute

(AttributeID);

CREATE INDEX cOffListMemb_IX2 ON UA_OfferListMember

(NestedOfferListID);

CREATE INDEX cOffListMemb_IX3 ON UA_OfferListMember

(OfferID);

CREATE INDEX cOfferSuprs_IX1 ON UA_OfferSuppression

(OfferID);

CREATE INDEX cOfferToProd_IX1 ON UA_OfferToProduct

(OfferID);

CREATE INDEX cUsrRespType_IX1 ON UA_UsrResponseType

(Name);

CREATE INDEX cOffer_IX2 ON UA_Offer

(FolderID);

CREATE INDEX cOfferList_IX1 ON UA_OfferList

(FolderID);

ALTER TABLE UA_RespTypeMapping RENAME COLUMN DeliverRespTypeID TO DeliverRespTypeID;

ALTER TABLE UA_DtlContactHist ADD OfferListID bigint;

ALTER TABLE UA_DtlContactHist ADD ABTestBranchID bigint;

System database schema changes for v11.1.0

  • The following changes were made to exiting table:


    ALTER TABLE UA_EngageEtlRecordStatus
    ADD FailureCount int,
    TrackingCode varchar(256),
    CampaignAudienceId varchar(256),
    ExternalSystemReferenceId varchar(64);

    CREATE INDEX cEngageEtlRecordStatus_IX1 ON UA_EngageEtlRecordStatus(Recordid, Status, FailureCount);

    CREATE INDEX cEngageEtlRecordStatus_IX2 ON UA_EngageETLRecordStatus ( Status, EventType );

  • The following new tables are added:

    CREATE TABLE UA_History(
    RecordID       bigint NOT NULL,
    ObjectType     varchar(32) NOT NULL,
    CampaignID     bigint NOT NULL,
    SessionID      bigint NOT NULL,
    sourceType     bigint NOT NULL,
    sourceTypeId   bigint NOT NULL,
    sourceTypeName varchar(255) NOT NULL,
    DatetimeValue  timestamp NOT NULL,
    eventType      bigint NOT NULL,
    UpdateBy       varchar(256) NOT NULL,
    SaveComment    varchar(1024) NULL,
    CONSTRAINT cUA_History_PK PRIMARY KEY (RecordID)
    );

    CREATE INDEX iHistory_CampaignID ON UA_History(CampaignID);

    CREATE INDEX iHistory_SessionID ON UA_History(SessionID);

    CREATE TABLE UA_TempTreatment (
    TreatmentId bigint NOT NULL,
    Count bigint NOT NULL
    );

System database schema changes for v11.0.0.0

  • The following changes were made.ALTER TABLE UA_InteractedSMS ADD COLUMN MessageBody varchar(256) NULL;
  • The following new table was added:

    CREATE TABLE UA_EmailOptOut (
        RecordID        bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
        Provider        varchar(64) NULL,
        EndpointSource    varchar(64) NULL,
        Channel            varchar(64) NULL,
        X1ID            varchar(64) NULL,
        ContactId        varchar(64) NULL,
        Email            varchar(64) NULL,
        EventCode        varchar(64) NOT NULL,
        EventTimeStamp    timestamp NOT NULL,
        EventNameSpace    varchar(64) NULL,
        EventVersion    varchar(64) NULL,
        EventName        varchar(64) NULL,
        Description        varchar(128) NULL,
        OptOutDetails   varchar(1024) NOT NULL,
        MessageId        bigint NULL,
        MailingTemplateId bigint NULL,
        ReportId        varchar(64) NULL,    
        SubjectLine     varchar(256) NULL,
        MessageName     varchar(1024) NULL,
        DocType            varchar(64) NULL,
        EventId         varchar(64) NULL,
        TrackingCode       varchar(1024) NULL,
        ExternalSystemName varchar(64) NULL,
        ExternalSystemReferenceID varchar(64) NULL,
        CampaignAudienceID varchar(256) NULL,
        CONSTRAINT tUA_EmailOptOut_PK PRIMARY KEY (RecordID)
    );

System database schema changes for v10.0.0.2

  • No new tables were added.
  • The following changes were made:

    - ALTER TABLE UA_SimpNot_appOpened ADD COLUMN ActionTaken varchar(64);
    - ALTER TABLE UA_SimpNot_appOpened ADD COLUMN ActionValue varchar(1024);
    - ALTER TABLE UA_SimpNot_URLClicked ADD COLUMN ActionTaken varchar(64);
    - ALTER TABLE UA_SimpNot_URLClicked ADD COLUMN ActionValue varchar(1024);
    - CREATE INDEX emailSend_IX3 ON UA_EmailSend
      (
        ExternalSystemReferenceID,    
        CampaignAudienceID
      );
    - ALTER TABLE UA_EmailClick ALTER COLUMN ClickUrl SET DATA
        TYPE varchar(1024);
    - ALTER TABLE UA_EmailClick ALTER COLUMN UrlDescription SET DATA
        TYPE varchar(1024);

System database schema changes for v10.0.0.1

The following new tables were added.


CREATE TABLE UA_EmailSend (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
ContactId varchar(64) NULL,
Email varchar(64) NULL,
EventCode varchar(64) NOT NULL,
EventTimeStamp timestamp NOT NULL,
EventNameSpace varchar(64) NULL,
EventVersion varchar(64) NULL,
EventName varchar(64) NULL,
Description varchar(128) NULL,
MessageId bigint NULL,
MailingTemplateId bigint NULL,
ReportId varchar(64) NULL,
SubjectLine varchar(256) NULL,
MessageName varchar(256) NULL,
DocType      varchar(64) NULL,
SendType     varchar(64) NULL,
EventId     varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
    CONSTRAINT tUA_EmailSend_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_EmailOpen (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
ContactId varchar(64) NULL,
Email varchar(64) NULL,
EventCode varchar(64) NOT NULL,
EventTimeStamp timestamp NOT NULL,
EventNameSpace varchar(64) NULL,
EventVersion varchar(64) NULL,
EventName varchar(64) NULL,
Description varchar(128) NULL,
MessageId bigint NULL,
MailingTemplateId bigint NULL,
ReportId varchar(64) NULL,
SubjectLine varchar(256) NULL,
MessageName varchar(256) NULL,
DocType      varchar(64) NULL,
EventId     varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
    CONSTRAINT tUA_EmailOpen_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_EmailClick (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
ContactId varchar(64) NULL,
Email varchar(64) NULL,
EventCode varchar(64) NOT NULL,
EventTimeStamp timestamp NOT NULL,
EventNameSpace varchar(64) NULL,
EventVersion varchar(64) NULL,
EventName varchar(64) NULL,
Description varchar(128) NULL,
MessageId bigint NULL,
MailingTemplateId bigint NULL,
ReportId varchar(64) NULL,
SubjectLine varchar(256) NULL,
MessageName varchar(256) NULL,
DocType      varchar(64) NULL,
ClickUrl    varchar(128) NULL,
UrlDescription varchar(128) NULL,
EventId     varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
    CONSTRAINT tUA_EmailClick_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_EmailBounce (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
ContactId varchar(64) NULL,
Email varchar(64) NULL,
EventCode varchar(64) NOT NULL,
EventTimeStamp timestamp NOT NULL,
EventNameSpace varchar(64) NULL,
EventVersion varchar(64) NULL,
EventName varchar(64) NULL,
Description varchar(128) NULL,
MessageId bigint NULL,
MailingTemplateId bigint NULL,
ReportId varchar(64) NULL,
SubjectLine varchar(256) NULL,
MessageName varchar(256) NULL,
DocType      varchar(64) NULL,
BounceType     varchar(64) NULL,
EventId     varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
    CONSTRAINT tUA_EmailBounce_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_SentSMS (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
ContactId varchar(64) NULL,
MobileNumber varchar(64) NULL,
EventName varchar(256) NULL,
DeliveryStatus varchar(64) NULL,
ProgramType varchar(64) NULL,
LocationCountry varchar(64) NULL,
Code varchar(64) NULL,
MessageType varchar(64) NULL,
MailingTemplateId varchar(256) NULL,
ExternalSystemReferenceId varchar(256) NULL,
Source varchar(256) NULL,
ProgramId varchar(64) NULL,
CampaignName varchar(256) NULL,
ProgramName varchar(256) NULL,
MessageBody varchar(256) NULL,
EventId varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
CONSTRAINT tUA_SentSMS_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_InteractedSMS (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
ContactId varchar(64) NULL,
MobileNumber varchar(64) NULL,
EventName varchar(256) NULL,
CampaignName varchar(256) NULL,
ProgramName varchar(256) NULL,
ProgramType varchar(64) NULL,
SetConsent varchar(64) NULL,
Source varchar(256) NULL,
EventId varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
CONSTRAINT tUA_InteractedSMS_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_Installed (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_Installed_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_Uninstalled (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_Uninstalled_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_SessionStarted (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_SessionStarted_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_SessionEnded (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_SessionEnded_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_UIPushEnabled (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_UIPushEnabled_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_App_UIPushDisabled (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
CONSTRAINT tUA_App_UIPushDisabled_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_SimpNot_appOpened (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
CONSTRAINT tUA_SimpNot_appOpened_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_SimpNot_URLClicked (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
UserId varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
CONSTRAINT tUA_SimpNot_URLClicked_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_Mob_Push_Send (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Provider varchar(64) NULL,
EndpointSource varchar(64) NULL,
Channel varchar(64) NULL,
X1ID varchar(64) NULL,
EventCode varchar(64) NULL,
EventTimestamp timestamp NOT NULL,
EventNamespace varchar(64) NULL,
EventVersion varchar(64) NULL,
EventName varchar(256) NULL,
ContactId varchar(64) NULL,
ReportId varchar(64) NULL,
RichContentId varchar(64) NULL,
MobileUserId varchar(64) NULL,
PushType varchar(64) NULL,
AppKey varchar(64) NULL,
ChannelId varchar(64) NULL,
Url varchar(64) NULL,
Attribution varchar(64) NULL,
TrackingCode   varchar(1024) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
CampaignAudienceID varchar(256) NULL,
CONSTRAINT tUA_App_UIPushSend_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_Gen_Event_Record (
RecordID bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
Channel varchar(64) NULL,
EventCode varchar(64) NULL,
EventName varchar(256) NULL,
ExternalSystemName varchar(64) NULL,
ExternalSystemReferenceID varchar(64) NULL,
EventJSON CLOB(5M) NULL,
CONSTRAINT tUA_Gen_Event_Record_PK PRIMARY KEY (RecordID)
);

CREATE TABLE UA_CampaignEngageResponseMap (
CampaignEventType bigint NOT NULL,
EngageEventType bigint NOT NULL,
ApplicationType int NULL
);

CREATE TABLE UA_EngageEtlRecordStatus (
        RecordID             bigint NOT NULL,
        EventType            int NOT NULL,
Status               int NOT NULL,
        Description          varchar(256),
        CONSTRAINT cEngEtlRecStat_PK
               PRIMARY KEY (RecordID, EventType)
);

CREATE TABLE UA_EngageEtlTracker (
EventType int NOT NULL,
LastProcessedRecordId bigint NOT NULL
);

System database schema changes for v10.0

One new table was added:

UA_APIKEY (
    id varchar(256) NOT NULL,
    apikey varchar(256) NOT NULL,
    createDate timestamp
)

This table supports the Unica Campaign internal API security implementation. Do not modify this table or table data.

System database schema changes for v9.1.2

There were no schema changes in version 9.1.2.

System database schema changes for v9.1.1

  • No tables were modified.
  • One new table was added:

    UA_AnnotationInfo (
    FlowchartID bigint NOT NULL,
    AnchorID bigint NOT NULL,
    AnchorType int NOT NULL,
    CreationDate datetime NULL,
    CreatedBy int NULL,
    LastUpdated datetime NULL,
    LastUpdatedBy int NULL,
    X int NULL,
    Y int NULL,
    IsVisible int NULL,
    Content nvarchar(1024) NULL,
    CONSTRAINT cAnnotationInfo_PK
    PRIMARY KEY (FlowchartID ASC, AnchorID ASC, AnchorType ASC)
    )

System database schema changes for v9.1

  • No tables were modified.
  • A new table, UA_RespTypeMapping, was added to support Unica Deliver-Unica Campaign integration:
    CampaignRespTypeID DeliverRespTypeID ApplicationType
    9 1 3
    10 14 3
    11 18 3
  • The UA_RespTypeMapping table is used as a join between the Unica Campaign UA_UsrResponseType table and the Unica Deliver table UACE_ResponseType.
  • Link Click (9,1,3), Landing Page (10,14,3) and SMS Reply Message (11,18,3) are available. Currently, only Link Click is used. Columns for Landing Page and SMS Reply message are not populated by the ETL process at this time. Over time, more response types can be added which could then be used by the ETL process and be added in the performance report.

System database schema changes for v9.0

  • No tables were modified.
  • Two new tables were added:

    UA_OfferSuppression (
            OfferID              bigint NOT NULL,
            SuppressionType      int NOT NULL,
            ResponseTypeCode     varchar(64) NULL,
            EventCount           int NULL,
            AttributeID          bigint NULL,
            StringValue          nvarchar(1024) NULL,
            NumberValue          float NULL,
            DatetimeValue        datetime NULL,
            SuppressionDuration  int NULL,
            CONSTRAINT cOfferSuppression_PK
            PRIMARY KEY (OfferID ASC, SuppressionType ASC)
    )

    UA_RespTypeMapping (
    CampaignRespTypeID bigint NOT NULL,
    DeliverRespTypeID bigint NOT NULL,
    ApplicationType int NULL
    )

System database schema changes for v8.6

There were no schema changes in version 8.6.

System database schema changes for v8.5

  • No new tables were added.
  • The following changes were made:
    • UA_TmpTablePool: add InstanceID bigint NULL
    • UPDATE UA_TmpTablePool: set InstanceID = 0 where InstanceID IS NULL
    • ALTER Table UA_OfferList: add creatorFlag int NULL, creatorObjectId bigint NULL
    • UPDATE UA_OfferList: set creatorFlag = 0 , creatorObjectId = 0
    • ALTER Table UA_Folder: add creatorFlag int NULL, creatorObjectId bigint NULL
    • UPDATE UA_Folder: set creatorFlag = 0, creatorObjectId = 0
    • ALTER TABLE UA_ATTRIBUTEDEF: add isMandatory int NULL
    • ALTER TABLE UA_ATTRIBUTEDEF: add sortOrder int NULL
    • UPDATE UA_ATTRIBUTEDEF: set isMandatory = 0, sortOrder = 0
    • ALTER TABLE UA_ENUMATTRVALUES: add isDefault int NULL
    • UPDATE UA_ENUMATTRVALUES: set isDefault = 0
    • Insert into UA_AttributeDef (AttributeID, Name, DisplayName, Description, ObjectType, AttributeType, EnumType, Length, SystemDefined, isMandatory, sortOrder) VALUES (15, ' CreativeURL ', 'Creative URL', 'The creative or digital asset URL associated with this offer', 4, 1, 0, 512, 1, 0, 0)

System database schema changes for v8.2

  • No new tables were added.
  • The following changes were made:
    • ALTER Table UA_Offer add creatorFlag int NULL, creatorObjectId bigint NULL
    • UPDATE UA_Offer set creatorFlag = 0 , creatorObjectId = 0
    • ALTER Table UA_OfferTemplate add creatorFlag int NULL, creatorObjectId bigint NULL
    • UPDATE UA_OfferTemplate set creatorFlag = 0, creatorObjectId = 0

System database schema changes for v8.1

  • The following table was added:

    UA_TmpTablePool (
           ID                   bigint NOT NULL,
           DataSrcName          nvarchar(255) NOT NULL,
           TableSchema          nvarchar(1024) NOT NULL,
           TableID              bigint NULL,
           UserID               bigint NULL,
           FlowchartID          bigint NULL,
           Status               tinyint NULL,
           CONSTRAINT cTmpTablePool2_PK
                  PRIMARY KEY (ID ASC)
    )

  • The following change was made:

    UPDATE UA_TARGETCELLS SET approved = 1 WHERE approved IS NULL

System database schema changes for v8.0

  • No new tables were added.
  • The following change was made:

    CREATE UNIQUE INDEX cAttributeDef_IX1 ON UA_AttributeDef
    (
            Name ASC
    )