数据库清理工作流 database-cleanup-workflow

使用以下SQL查询恢复要删除的列表：

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

每个列表都有多个指向其他表的链接。 使用以下查询批量删除所有这些链接：

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

其中$(relatedTable)是与​ NmsGroup ​相关的表，$(l)是列表标识符。

当列表是“列表”类型列表时，将使用以下查询删除关联的表：

DROP TABLE grp$(l)

使用以下查询删除操作恢复的每个​ Select ​类型列表：

DELETE FROM NmsGroup WHERE iGroupId=$(l)

其中$(l)是列表标识符

Database cleanup ​工作流会选择其值为​ Yes ​或​ Recycled ​的​ deleteStatus ​字段的所有投放，这些投放的删除日期早于部署向导的​ Deleted deliveries (NmsCleanup_RecycledDeliveryPurgeDelay)字段中定义的时间段。 有关详细信息，请参阅部署向导。 此时间段根据当前服务器日期计算。

对于每个中间源服务器，该任务会选择要删除的投放列表。

Database cleanup ​工作流会删除投放日志、附件、镜像页面信息和所有其他相关数据。

永久删除投放之前，工作流会清除下表中的链接信息：

• 在投放排除表(NmsDlvExclusion)中，使用了以下查询：

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

  其中​ $(l) ​是投放的标识符。

• 在优惠券表(NmsCouponValue)中，使用了以下查询（包含批量删除）：

  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)

  其中$(l)是投放的标识符。

• 在投放日志表(NmsBroadlogXxx)中，批量删除是以20,000条记录的批次执行的。

• 在优惠建议表(NmsPropositionXxx)中，批量删除是以20,000条记录的批处理方式执行的。

• 在跟踪日志表(NmsTrackinglogXxx)中，批量删除是以20,000条记录的批次执行的。

• 在投放片段表(NmsDeliveryPart)中，批量删除是以500,000条记录的批次执行的。 此表包含有关要投放的其余消息的个性化信息。

• 在镜像页面数据片段表(NmsMirrorPageInfo)中，对已过期的投放部分以及已完成或已取消的投放部分成批执行20,000条记录。 此表包含有关用于生成镜像页面的所有消息的个性化信息。

• 在镜像页面搜索表(NmsMirrorPageSearch)中，批量删除是以20,000条记录的批量方式执行的。 此表是一个搜索索引，用于访问存储在​ NmsMirrorPageInfo ​表中的个性化信息。

• 在批处理日志表(XtkJobLog)中，成批删除是以20,000条记录的批处理方式执行的。 此表包含要删除的投放的日志。

• 在投放URL跟踪表(NmsTrackingUrl)中，使用了以下查询：

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

  其中$(l)是投放的标识符。

  此表包含在要删除的投放中找到的URL以启用其跟踪。

该投放已从投放表(NmsDelivery)中删除：

DELETE FROM NmsDelivery WHERE iDeliveryId = $(l)

其中$(l)是投放的标识符。

为此，工作流会检查每个投放是否处于不活动状态（根据其状态）。 如果投放处于活动状态，则会在删除之前将其停止。 通过执行以下查询来执行检查：

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

其中​ $(l) ​是投放的标识符。

如果状态的值为​ Start pending、In progress、Recovery pending、Recovery in progress、Pause requested、Pause in progress ​或​ Paused（值为51、55、61、62、71、72、75），则投放将停止并且任务会清除链接的信息。

Database cleanup ​工作流创建已过期的投放列表。 此列表包括状态为​ Finished ​以外的所有过期投放，以及最近停止的投放，其未处理的消息超过10,000条。 使用以下查询：

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 ))

其中delivery mode 1与​ Mass delivery ​模式匹配，state 51与​ Start pending ​状态匹配，state 85与​ Stopped ​状态匹配，并且投放服务器上批量更新的投放日志的最大数量等于10,000。

然后，该工作流会包含使用中间源的最近过期的投放列表。 将排除尚未通过中间源服务器恢复投放日志的投放。

使用以下查询：

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))

以下查询用于检测外部帐户是否仍处于活动状态，以按日期筛选投放：

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

在过期投放列表中，状态为​ Pending ​的投放日志切换到​ Delivery cancelled，此列表中的所有投放切换到​ Finished。

使用以下查询：

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

其中$(curdate)是数据库服务器的当前日期，$(bl)是投放日志消息的标识符，$(dl)是投放标识符，delivery status 6与​ Pending ​状态匹配，delivery status 7与​ Delivery cancelled ​状态匹配。

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

其中delivery state 95与​ Finished ​状态匹配，$(dl)是投放的标识符。

已删除过时投放的所有片段(deliveryParts)，并删除正在进行的通知投放的所有过时片段。 批量删除用于这两个任务。

使用以下查询：

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)

其中delivery state 95与​ Finished ​状态匹配，delivery state 85与​ Stopped ​状态匹配，$(curDate)是当前服务器日期。

首先，使用以下查询恢复要清除的投放列表：

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

其中$(curDate)是当前服务器日期。

然后，如有必要，使用先前恢复的投放的标识符清除​ NmsMirrorPageInfo ​表。 批量删除用于生成以下查询：

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

其中$(dl)是投放的标识符。

然后，将条目添加到投放日志中。

然后识别清除的投放，以避免以后必须重新处理它们。 执行以下查询：

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

其中$(strIn)是投放标识符的列表。

首先使用以下查询(postgresql)恢复名称以​ wkDlv_ ​开头的表的列表：

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

随后将排除正在进行的工作流使用的表。 为此，使用以下查询可恢复正在进行的投放列表：

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

其中，0是与​ Being edited ​投放状态匹配的值，85与​ Stopped ​状态匹配，100与​ Deleted ​状态匹配。

将使用以下查询删除不再使用的表：

DROP TABLE wkDlv_15487_1;

使用以下查询对​ XtkReject ​表执行批量删除：

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

其中$(curDate)是当前服务器日期，我们从其中减去为​ NmsCleanup_RejectsPurgeDelay ​选项（请参阅部署向导）定义的时间段，$(l)是批量删除的最大记录数。

然后，使用以下查询删除所有孤立拒绝：

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

要恢复要删除的工作流列表，请使用以下查询：

SELECT iWorkflowId, iHistory FROM XtkWorkflow WHERE iWorkflowId<>0

此查询生成工作流列表，工作流列表将用于使用以下查询删除所有链接的日志、已完成的任务和已完成的事件：

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))

其中$(lworkflow)是工作流的标识符，$(lhistory)是历史记录的标识符。

删除所有未使用的表。 为此，使用下列查询(postgresql) wkf% ​类型掩码收集所有表：

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

然后，将排除挂起工作流实例使用的所有表。 使用以下查询可恢复活动工作流的列表：

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

然后，恢复每个工作流标识符以查找正在进行的工作流使用的表的名称。 这些名称将从以前恢复的表列表中排除。

使用以下查询排除“增量查询”类型的活动历史记录表：

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))

其中$(strcondition)是与​ wkfhisto% ​掩码匹配的表的列表。

剩余的表将使用以下查询删除：

DROP TABLE wkf15487_12;

首先，使用以下查询恢复跟踪日志表的列表：

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

批量删除用于清除先前恢复的表列表中的所有表。 使用以下查询：

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

其中$(tsDate)是当前服务器日期，我们从其中减去为​ NmsCleanup_TrackingLogPurgeDelay ​选项定义的时段。

使用批量删除清除跟踪统计信息表。 使用以下查询：

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

其中$(tsDate)是当前服务器日期，我们从其中减去为​ NmsCleanup_TrackingStatPurgeDelay ​选项定义的时段。

为此，使用以下查询可恢复投放日志架构列表：

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

使用中间源时，投放映射中未引用​ NmsBroadLogMid ​表。 nms：broadLogMid ​架构已添加到由上一个查询恢复的列表。

然后，数据库清理 ​工作流会从以前恢复的表清除过时的数据。 使用以下查询：

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

其中$(tableName)是架构列表中每个表的名称，$(option)是为​ NmsCleanup_BroadLogPurgeDelay ​选项定义的日期（请参阅部署向导）。

最后，工作流检查​ NmsProviderMsgId ​表是否存在。 如果是，则使用以下查询删除所有过时数据：

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

其中$(option)与为​ NmsCleanup_BroadLogPurgeDelay ​选项定义的日期匹配（请参阅部署向导）。

执行以下查询以仅保留超过特定阈值（等于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

显示​ coalescingErrors ​消息。

将创建一个新连接，以删除在开始日期和结束日期之间发生的所有错误。 使用以下查询：

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

每个错误都使用以下查询保存在​ NmsEmailErrorStat ​表中：

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

其中每个变量与上一个查询恢复的值匹配。

start ​变量已使用上一个进程的值更新，以完成循环。

要恢复需要清理的模拟列表，请使用以下查询：

SELECT iSimulationId FROM NmsSimulation WHERE iSimulationId<>0

要删除的表的名称由​ wkSimu_ ​前缀以及模拟的标识符组成(例如： wkSimu_456831_aggr)：

DROP TABLE wkSimu_456831_aggr