資料庫清除工作流程 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 ​工作流程會選取​ deleteStatus ​欄位值為​ Yes ​或​ Recycled ​且刪除日期早於部署精靈之​ 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)是傳遞識別碼的清單。

名稱以​ wkDlv_ ​開頭的資料表清單，會先透過下列查詢(postgresql)復原：

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