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.
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 IBM eMessage-Unica Campaign integration:
CampaignRespTypeID EMessageRespTypeID 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 IBM eMessage 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,
EMessageRespTypeID 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
)