Database cleanup workflow database-cleanup-workflow

Lists to be deleted are recovered using the following SQL query:

SELECT iGroupId, sLabel, iType FROM NmsGroup WHERE iDeleteStatus <> 0 OR tsExpirationDate <= GetDate()

Each list has several links to other tables. All of these links are deleted in bulk using the following query:

DELETE FROM $(relatedTable) WHERE iGroupId=$(l) IN (SELECT iGroupId FROM $(relatedTable) WHERE iGroupId=$(l) LIMIT 5000)

where $(relatedTable) is a table related to NmsGroup and $(l) is the list identifier.

When the list is a ‘List’ type list, the associated table is deleted using the following query:

DROP TABLE grp$(l)

Every Select type list recovered by the operation is deleted using the following query:

DELETE FROM NmsGroup WHERE iGroupId=$(l)

where $(l) is the list identifier

The Database cleanup workflow selects all deliveries for which the deleteStatus field has the value Yes or Recycled and whose delete date is earlier than the period defined in the Deleted deliveries (NmsCleanup_RecycledDeliveryPurgeDelay) field of the deployment wizard. For more on this, refer to Deployment wizard. This period is calculated in relation to the current server date.

For each mid-sourcing server, the task selects the list of deliveries to be deleted.

The Database cleanup workflow deletes delivery logs, attachments, mirror page information and all other related data.

Before deleting the delivery for good, the workflow purges linked information from the following tables:

• In the delivery exclusion table (NmsDlvExclusion), the following query is used:

  code language-sql
  DELETE FROM NmsDlvExclusion WHERE iDeliveryId=$(l)

  where $(l) is the identifier of the delivery.

• In the coupon table (NmsCouponValue), the following query is used (with mass-deletions):

  code language-sql
  DELETE FROM NmsCouponValue WHERE iMessageId IN (SELECT iMessageId FROM NmsCouponValue WHERE EXISTS (SELECT B.iBroadLogId FROM $(BroadLogTableName) B WHERE B.iDeliveryId = $(l) AND B.iBroadLogId = iMessageId ) LIMIT 5000)

  where $(l) is the identifier of the delivery.

• In the delivery log tables (NmsBroadlogXxx), mass-deletions are executed in batches of 20,000 records.

• In the offer proposition tables (NmsPropositionXxx), mass-deletions are executed in batches of 20,000 records.

• In the tracking log tables (NmsTrackinglogXxx), mass-deletions are executed in batches of 20,000 records.

• In the delivery fragment table (NmsDeliveryPart), mass-deletions are executed in batches of 500,000 records. This table contains personalization information on the remaining messages to be delivered.

• In the mirror page data fragment table (NmsMirrorPageInfo), mass-deletions are executed in batches of 20,000 records for expired delivery parts and for finished or cancelled ones. This table contains personalization information on all messages used for generating mirror pages.

• In the mirror page search table (NmsMirrorPageSearch), mass-deletions are executed in batches of 20,000 records. This table is a search index which provides access to personalization information stored in the NmsMirrorPageInfo table.

• In the batch process log table (XtkJobLog), mass-deletions are executed in batches of 20,000 records. This table contains the log of deliveries to be deleted.

• In the delivery URL tracking table (NmsTrackingUrl), the following query is used:

  code language-sql
  DELETE FROM NmsTrackingUrl WHERE iDeliveryId=$(l)

  where $(l) is the identifier of the delivery.

  This table contains the URLs found in the deliveries to be deleted to enable their tracking.

The delivery is deleted from the delivery table (NmsDelivery):

DELETE FROM NmsDelivery WHERE iDeliveryId = $(l)

where $(l) is the identifier of the delivery.

To do this, the workflow checks that each delivery is inactive (based on its status). If a delivery is active, it will be stopped before it is deleted. The check is carried out by executing the following query:

SELECT iState FROM NmsDelivery WHERE iDeliveryId = $(l) AND iState <> 100;

where $(l) is the identifier of the delivery.

If the value of the status is Start pending , In progress , Recovery pending , Recovery in progress , Pause requested , Pause in progress , or Paused (values 51, 55, 61, 62, 71, 72, 75), the delivery is stopped and the task purges the linked information.

The Database cleanup workflow creates the list of deliveries which have expired. This list includes all expired deliveries with a status other than Finished , as well as recently stopped deliveries with over 10,000 non-processed messages. The following query is used:

SELECT iDeliveryId, iState FROM NmsDelivery WHERE iDeleteStatus=0 AND iIsModel=0 AND iDeliveryMode=1 AND ( (iState >= 51 AND iState < 85 AND tsValidity IS NOT NULL AND tsValidity < $(currentDate) ) OR (iState = 85 AND DateMinusDays(15) < tsLastModified AND iToDeliver - iProcessed >= 10000 ))

where delivery mode 1 matches the Mass delivery mode, state 51 matches the Start pending state, state 85 matches the Stopped state, and the highest number of delivery logs mass-updated on the delivery server equals 10,000.

The workflow then includes the list of recently expired deliveries which use mid-sourcing. Deliveries for which no delivery logs have yet been recovered via the mid-sourcing server are excluded.

The following query is used:

SELECT iDeliveryId, tsValidity, iMidRemoteId, mData FROM NmsDelivery WHERE (iDeliveryMode = 4 AND (iState = 85 OR iState = 95) AND tsValidity IS NOT NULL AND (tsValidity < SubDays(GetDate() , 15) OR tsValidity < $(DateOfLastLogPullUp)) AND tsLastModified > SubDays(GetDate() , 15))

The following query is used to detect whether or not the external account is still active, for filtering deliveries by date:

SELECT iExtAccountId FROM NmsExtAccount WHERE iActive<>0 AND sName=$(providerName)

In the list of expired deliveries, delivery logs whose status is Pending , switch to Delivery cancelled , and all deliveries in this list switch to Finished .

The following queries are used:

UPDATE $(BroadLogTableName) SET tsLastModified=$(curdate), iStatus=7, iMsgId=$(bl) WHERE iDeliveryId=$(dl) AND iStatus=6

where $(curdate)is the current date of the database server, $(bl) is the identifier of the delivery logs message, $(dl) is the delivery identifier, delivery status 6 matches the Pending status and delivery status 7 matches the Delivery cancelled status.

UPDATE NmsDelivery SET iState = 95, tsLastModified = $(curdate), tsBroadEnd = tsValidity WHERE iDeliveryId = $(dl)

where delivery state 95 matches the Finished status, and $(dl) is the identifier of the delivery.

All fragments (deliveryParts) of obsolete deliveries are deleted and all obsolete fragments of notification deliveries in progress are deleted. Mass-deletion is used for both these tasks.

The following queries are used:

DELETE FROM NmsDeliveryPart WHERE iDeliveryPartId IN (SELECT iDeliveryPartId FROM NmsDeliveryPart WHERE iDeliveryId IN (SELECT iDeliveryId FROM NmsDelivery WHERE iState=95 OR iState=85) LIMIT 5000)

DELETE FROM NmsDeliveryPart WHERE iDeliveryPartId IN (SELECT iDeliveryPartId FROM NmsDeliveryPart WHERE tsValidity < $(curDate) LIMIT 500000)

where delivery state 95 matches the Finished status, delivery state 85 matches the Stopped status, and $(curDate) is the current server date.

First of all, the list of deliveries to be purged is recovered using the following query:

SELECT iDeliveryId, iNeedMirrorPage FROM NmsDelivery WHERE iWebResPurged = 0 AND tsWebValidity IS NOT NULL AND tsWebValidity < $(curdate)

where $(curDate) is the current server date.

The NmsMirrorPageInfo table is then purged, if necessary using the identifier of the previously recovered delivery. Mass-deletion is used to generate the following queries:

DELETE FROM NmsMirrorPageInfo WHERE iMirrorPageInfoId IN (SELECT iMirrorPageInfoId FROM NmsMirrorPageInfo WHERE iDeliveryId = $(dl)) LIMIT 5000

DELETE FROM NmsMirrorPageSearch WHERE iMessageId IN (SELECT iMessageId FROM NmsMirrorPageSearch WHERE iDeliveryId = $(dl)) LIMIT 5000

where $(dl) is the identifier of the delivery.

An entry is then added to the delivery log.

Purged deliveries are then identified, to avoid having to reprocess them later. The following query is executed:

UPDATE NmsDelivery SET iWebResPurged = 1 WHERE iDeliveryId IN ($(strIn))

where $(strIn) is the list of delivery identifiers.

The list of tables with names beginning with wkDlv_ is recovered first with the following query (postgresql):

SELECT relname FROM pg_class WHERE relname LIKE Lower('wkDlv_%') ESCAPE E'\\' AND relkind IN ('r','v') AND pg_get_userbyid(relowner)<>'postgres'

The tables used by workflows in progress are then excluded. To do this, the list of deliveries in progress is recovered using the following query:

SELECT iDeliveryId FROM NmsDelivery WHERE iDeliveryId<>0 AND iDeleteStatus=0 AND iState NOT IN (0,85,100);

where 0 is the value which matches the Being edited delivery status, 85 matches the Stopped status and 100 matches the Deleted status.

Tables that are no longer used will be deleted using the following query:

DROP TABLE wkDlv_15487_1;

Mass-deletion is carried out on the XtkReject table with the following query:

DELETE FROM XtkReject WHERE iRejectId IN (SELECT iRejectId FROM XtkReject WHERE tsLog < $(curDate)) LIMIT $(l)

where $(curDate) is the current server date from which we subtract the period defined for the NmsCleanup_RejectsPurgeDelay option (refer to Deployment wizard) and $(l) is the maximum number of records to be mass deleted.

All orphan rejects are then deleted using the following query:

DELETE FROM XtkReject WHERE iJobId NOT IN (SELECT iJobId FROM XtkJob)

To recover the list of workflows to be deleted, the following query is used:

SELECT iWorkflowId, iHistory FROM XtkWorkflow WHERE iWorkflowId<>0

This query generates the list of workflows which will be used to delete all linked logs, finished tasks and finished events, using the following queries:

DELETE FROM XtkWorkflowLog WHERE iWorkflowId=$(lworkflow) AND tsLog < DateMinusDays($(lhistory))

DELETE FROM XtkWorkflowTask WHERE iWorkflowId=$(lworkflow) AND iStatus<>0 AND tsCompletion < DateMinusDays($(lhistory))

DELETE FROM XtkWorkflowEvent WHERE iWorkflowId=$(l) AND iStatus>2 AND tsProcessing < DateMinusDays($(lHistory))

where $(lworkflow) is the identifier of the workflow and $(lhistory) is the identifier of the history.

All unused tables are deleted. For this purpose, all tables are collected thanks to a wkf% type mask using the following query (postgresql):

SELECT relname FROM pg_class WHERE relname LIKE Lower('wkf%') ESCAPE E'\\' AND relkind IN ('r','v') AND pg_get_userbyid(relowner)<>'postgres'

Then all tables used by a pending workflow instance are excluded. The list of active workflows is recovered using the following query:

SELECT iWorkflowId FROM XtkWorkflow WHERE iWorkflowId<>0 AND iState<>20

Each workflow identifier is then recovered to find the name of the tables used by workflows in progress. These names are excluded from the list of previously recovered tables.

“incremental query” type activity history tables are excluded using the following queries:

SELECT relname FROM pg_class WHERE relname LIKE Lower('wkfhisto%') ESCAPE E'\\' AND relkind IN ('r','v') AND pg_get_userbyid(relowner)<>'postgres'

SELECT iWorkflowId FROM XtkWorkflow WHERE iWorkflowId IN ($(strCondition))

where $(strcondition) is the list of tables which match the wkfhisto% mask.

The remaining tables are deleted using the following query:

DROP TABLE wkf15487_12;

First, the list of tracking log tables is recovered using the following query:

SELECT distinct(sTrackingLogSchema) FROM NmsDeliveryMapping WHERE sTrackingLogSchema IS NOT NULL;

Mass-deletion is used to purge all tables in the list of previously recovered tables. The following query is used:

DELETE FROM NmsTrackingLogRcp WHERE iTrackingLogId IN (SELECT iTrackingLogId FROM NmsTrackingLogRcp WHERE tsLog < $(tsDate) LIMIT 5000)

where $(tsDate) is the current server date from which we subtract the period defined for the NmsCleanup_TrackingLogPurgeDelay option.

The tracking statistics table is purged using mass-deletion. The following query is used:

DELETE FROM NmsTrackingStats WHERE iTrackingStatsId IN (SELECT iTrackingStatsId FROM NmsTrackingStats WHERE tsStart < $(tsDate) LIMIT 5000)

where $(tsDate) is the current server date from which we subtract the period defined for the NmsCleanup_TrackingStatPurgeDelay option.

For this purpose, the list of delivery log schemas is recovered using the following query:

SELECT distinct(sBroadLogSchema) FROM NmsDeliveryMapping WHERE sBroadLogSchema IS NOT NULL UNION SELECT distinct(sBroadLogExclSchema) FROM NmsDeliveryMapping WHERE sBroadLogExclSchema IS NOT NULL

When using mid-sourcing, the NmsBroadLogMid table isn’t referenced in delivery mappings. The nms:broadLogMid schema is added to the list recovered by the previous query.

The Database cleanup workflow then purges obsolete data from previously recovered tables. The following query is used:

DELETE FROM $(tableName) WHERE iBroadLogId IN (SELECT iBroadLogId FROM $(tableName) WHERE tsLastModified < $(option) LIMIT 5000)

where $(tableName) is the name of each table in the list of schemas, and $(option) is the date defined for the NmsCleanup_BroadLogPurgeDelay option (refer to Deployment wizard).

Finally, the workflow checks whether the NmsProviderMsgId table exists. If so, all obsolete data is deleted using the following query:

DELETE FROM NmsProviderMsgId WHERE iBroadLogId IN (SELECT iBroadLogId FROM NmsProviderMsgId WHERE tsCreated < $(option) LIMIT 5000)

where $(option) matches the date defined for the NmsCleanup_BroadLogPurgeDelay option (refer to Deployment wizard).

The following query is executed in order to keep only errors beyond a certain threshold (which equals 20):

SELECT iMXIP, iPublicId, SUM(iTotalConnections), SUM(iTotalErrors), SUM(iMessageErrors), SUM(iAbortedConnections), SUM(iFailedConnections), SUM(iRefusedConnections), SUM(iTimeoutConnections) FROM NmsEmailErrorStat WHERE tsDate>=$(start ) AND tsDate<$(end ) GROUP BY iMXIP, iPublicId HAVING SUM(iTotalErrors) >= 20

The coalescingErrors message is displayed.

A new connection is created to delete all errors which occurred between the start and end dates. The following query is used:

DELETE FROM NmsEmailErrorStat WHERE tsDate>=$(start) AND tsDate<$(end)

Each error is saved in the NmsEmailErrorStat table using the following query:

INSERT INTO NmsEmailErrorStat(iMXIP, iPublicId, tsDate, iTotalConnections, iTotalErrors, iTimeoutConnections, iRefusedConnections, iAbortedConnections, iFailedConnections, iMessageErrors) VALUES($(lmxip ), $(lpublicId ), $(tsstart ), $(lconnections ), $(lconnectionErrors ),$(ltimeoutConnections ), $(lrefusedConnections ), $(labortedConnections ), $(lfailedConnections ), $(lmessageErrors))

where each variable matches a value recovered by the previous query.

The start variable is updated with the values of the previous process to finish the loop.

To recover the list of simulations that require cleanup, the following query is used:

SELECT iSimulationId FROM NmsSimulation WHERE iSimulationId<>0

The name of the tables to delete is made up of the wkSimu_ prefix followed by the identifier of the simulation (for instance: wkSimu_456831_aggr):

DROP TABLE wkSimu_456831_aggr