Database Changes

MSSQL script to be executed for Email Unsubscribed events:
Note: Below database changes should be executed only for MSSQL database and not for all databases.

Please execute the below SQL scripts on Journey system tables, this is required for Email Unsubscribe details to be populated.

DROP TABLE IF EXISTS EmailUnsubscribedList;

CREATE TABLE EmailUnsubscribedList(

id BIGINT NOT NULL IDENTITY,

emailId NVARCHAR(200) NOT NULL,

status NVARCHAR(200) DEFAULT 0 NOT NULL,

channelAgent NVARCHAR(50),

eventID BIGINT NOT NULL,

audienceResponseId BIGINT,

audienceResponseExtendedId BIGINT,

createdBy NVARCHAR(200) DEFAULT 'SYSTEM' NOT NULL,

version BIGINT,

createdDate DATETIME2,

createdDateEpoch BIGINT NOT NULL,

modifiedDateTimeEpoch BIGINT,

FOREIGN KEY (eventID) REFERENCES AudienceResponseEventMaster(id),

FOREIGN KEY (audienceResponseId) REFERENCES AudienceResponse(id),

CONSTRAINT unique_emailId UNIQUE (emailId),

PRIMARY KEY (id)

);

DROP TABLE IF EXISTS AudienceResponseExtended;

CREATE TABLE AudienceResponseExtended(

id BIGINT NOT NULL IDENTITY,

audienceResponseId BIGINT NOT NULL,

associatedAttributes NVARCHAR(MAX),

isProcessed BIT DEFAULT 0 NOT NULL,

createdDate DATETIME2,

createdBy NVARCHAR(200),

version BIGINT,

responseTimeEpoch BIGINT NOT NULL,

createdDateEpoch BIGINT,

FOREIGN KEY (audienceResponseId) REFERENCES AudienceResponse(id),

CONSTRAINT ensure_attribute_json CHECK (ISJSON(associatedAttributes) > 0),

PRIMARY KEY (id)

);