Fluxo de trabalho de limpeza do banco de dados database-cleanup-workflow

As listas a serem excluídas são recuperadas usando a seguinte consulta SQL:

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

Cada lista tem vários links para outras tabelas. Todos esses links são excluídos em massa usando a seguinte query:

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

onde $(relatedTable) é uma tabela relacionada a NmsGroup e $(l) é o identificador da lista.

Quando a lista é do tipo "Lista", a tabela associada é excluída usando a seguinte query:

DROP TABLE grp$(l)

A cada lista de tipos Select recuperada pela operação é excluída usando a seguinte consulta:

DELETE FROM NmsGroup WHERE iGroupId=$(l)

onde $(l) é o identificador da lista

O fluxo de trabalho Database cleanup seleciona todas as entregas para as quais o campo deleteStatus tem o valor Yes ou Recycled e cuja data de exclusão é anterior ao período definido no campo Deleted deliveries (NmsCleanup_RecycledDeliveryPurgeDelay) do assistente de implantação. Para obter mais informações, consulte assistente de implantação. Este período é calculado em relação à data atual do servidor.

Para cada servidor mid-sourcing, a tarefa seleciona a lista de deliveries a serem excluídos.

O fluxo de trabalho Database cleanup exclui logs de entrega, anexos, informações de mirror page e todos os outros dados relacionados.

Antes de excluir o delivery definitivamente, o workflow limpa as informações vinculadas das seguintes tabelas:

• Na tabela de exclusão de entrega (NmsDlvExclusion), a seguinte consulta é usada:

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

  onde $(l) é o identificador da entrega.

• Na tabela de cupons (NmsCouponValue), a seguinte consulta é usada (com exclusões em massa):

  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)

  onde $(l) é o identificador da entrega.

• Nas tabelas de log de entrega (NmsBroadlogXxx), exclusões em massa são executadas em lotes de 20.000 registros.

• Nas tabelas de apresentação de ofertas (NmsPropositionXxx), exclusões em massa são executadas em lotes de 20.000 registros.

• Nas tabelas de log de rastreamento (NmsTrackinglogXxx), exclusões em massa são executadas em lotes de 20.000 registros.

• Na tabela de fragmentos de entrega (NmsDeliveryPart), exclusões em massa são executadas em lotes de 500.000 registros. Esta tabela contém informações de personalização sobre as mensagens restantes a serem entregues.

• Na tabela de fragmento de dados da mirror page (NmsMirrorPageInfo), as exclusões em massa são executadas em lotes de 20.000 registros para partes de entrega expiradas e para partes concluídas ou canceladas. Esta tabela contém informações de personalização de todas as mensagens usadas para gerar mirror pages.

• Na tabela de pesquisa da mirror page (NmsMirrorPageSearch), exclusões em massa são executadas em lotes de 20.000 registros. Esta tabela é um índice de pesquisa que fornece acesso às informações de personalização armazenadas na tabela NmsMirrorPageInfo.

• Na tabela de log do processo em lote (XtkJobLog), exclusões em massa são executadas em lotes de 20.000 registros. Esta tabela contém o log de deliveries a serem excluídos.

• Na tabela de rastreamento de URL de entrega (NmsTrackingUrl), a seguinte consulta é usada:

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

  onde $(l) é o identificador da entrega.

  Esta tabela contém os URLs encontrados nos deliveries a serem excluídos para ativar o rastreamento.

A entrega foi excluída da tabela de entrega (NmsDelivery):

DELETE FROM NmsDelivery WHERE iDeliveryId = $(l)

onde $(l) é o identificador da entrega.

Para fazer isso, o workflow verifica se cada delivery está inativo (com base em seu status ). Se um delivery estiver ativo, ele será interrompido antes de ser excluído. A verificação é realizada executando a seguinte query:

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

onde $(l) é o identificador da entrega.

Se o valor do status for Start pending , In progress , Recovery pending , Recovery in progress , Pause requested , Pause in progress ou Paused (valores 51, 55, 61, 62, 71, 72, 75), a entrega será interrompida e a tarefa limpará as informações vinculadas.

O fluxo de trabalho Database cleanup cria a lista de entregas que expiraram. Esta lista inclui todas as entregas expiradas com status diferente de Finished, bem como entregas interrompidas recentemente com mais de 10.000 mensagens não processadas. A seguinte query é usada:

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

onde delivery mode 1 corresponde ao modo Mass delivery, state 51 corresponde ao estado Start pending, state 85 corresponde ao estado Stopped e o número mais alto de logs de entrega atualizados em massa no servidor de entrega é igual a 10.000.

O workflow inclui a lista de deliveries expirados recentemente que usam mid-sourcing. Os deliveries para os quais nenhum log de delivery ainda foi recuperado por meio do servidor mid-sourcing são excluídos.

A seguinte query é usada:

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

A consulta a seguir é usada para detectar se a conta externa ainda está ativa ou não, para filtrar deliveries por data:

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

Na lista de entregas expiradas, os logs de entrega cujo status é Pending , alternam para Delivery cancelled e todas as entregas nessa lista alternam para Finished .

As seguintes queries são usadas:

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

onde $(curdate) é a data atual do servidor de banco de dados, $(bl) é o identificador da mensagem dos logs de entrega, $(dl) é o identificador de entrega, delivery status 6 corresponde ao status Pending e delivery status 7 corresponde ao status Delivery cancelled.

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

onde delivery state 95 corresponde ao status Finished e $(dl) é o identificador da entrega.

Todos os fragmentos (deliveryParts) de entregas obsoletas são excluídos e todos os fragmentos obsoletos de entregas de notificação em andamento são excluídos. A exclusão em massa é usada para essas tarefas.

As seguintes queries são usadas:

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)

onde delivery state 95 corresponde ao status Finished, delivery state 85 corresponde ao status Stopped e $(curDate) é a data atual do servidor.

Primeiro, a lista de deliveries a serem removidos é recuperada usando a seguinte query:

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

onde $(curDate) é a data atual do servidor.

A tabela NmsMirrorPageInfo é então removida, se necessário, usando o identificador da entrega recuperada anteriormente. A exclusão em massa é usada para gerar as seguintes consultas:

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

onde $(dl) é o identificador da entrega.

Uma entrada é adicionada ao log de delivery.

Os deliveries removidos são identificados para evitar a necessidade de reprocessá-los posteriormente. A seguinte consulta é executada:

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

onde $(strIn) é a lista de identificadores de entrega.

A lista de tabelas com nomes começando com wkDlv_ é recuperada primeiro com a seguinte consulta (postgresql):

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

As tabelas usadas por workflows em andamento são excluídas. Para fazer isso, a lista de deliveries em andamento é recuperada usando a seguinte query:

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

onde 0 é o valor que corresponde ao status de entrega Being edited, 85 corresponde ao status Stopped e 100 corresponde ao status Deleted.

As tabelas que não são mais usadas serão excluídas usando a seguinte query:

DROP TABLE wkDlv_15487_1;

A exclusão em massa é realizada na tabela XtkReject com a seguinte consulta:

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

onde $(curDate) é a data atual do servidor da qual subtraímos o período definido para a opção NmsCleanup_RejectsPurgeDelay (consulte assistente de implantação) e $(l) é o número máximo de registros a serem excluídos em massa.

Todas as rejeições órfãs são excluídas usando a seguinte query:

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

Para recuperar a lista de workflows a serem excluídos, a seguinte query é usada:

SELECT iWorkflowId, iHistory FROM XtkWorkflow WHERE iWorkflowId<>0

Essa consulta gera a lista de workflows que será usada para excluir todos os logs vinculados, tarefas concluídas e eventos concluídos, usando as seguintes consultas:

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

onde $(lworkflow) é o identificador do fluxo de trabalho e $(lhistory) é o identificador do histórico.

Todas as tabelas não utilizadas são excluídas. Para esta finalidade, todas as tabelas são coletadas graças a uma máscara do tipo wkf% usando a seguinte 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'

Em seguida, todas as tabelas usadas por uma instância de workflow pendente são excluídas. A lista de workflows ativos é recuperada usando a seguinte query:

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

Cada identificador de workflow é então recuperado para encontrar o nome das tabelas usadas pelos workflows em andamento. Esses nomes são excluídos da lista de tabelas recuperadas anteriormente.

as tabelas de histórico de atividades do tipo "consulta incremental" são excluídas usando as seguintes consultas:

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

onde $(strcondition) é a lista de tabelas que correspondem à máscara wkfhisto%.

As tabelas restantes são excluídas usando a seguinte query:

DROP TABLE wkf15487_12;

Primeiro, a lista de tabelas de log de rastreamento é recuperada usando a seguinte query:

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

A exclusão em massa é usada para limpar todas as tabelas na lista de tabelas recuperadas anteriormente. A seguinte query é usada:

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

onde $(tsDate) é a data atual do servidor da qual subtraímos o período definido para a opção NmsCleanup_TrackingLogPurgeDelay.

A tabela de estatísticas de rastreamento é removida usando a exclusão em massa. A seguinte query é usada:

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

onde $(tsDate) é a data atual do servidor da qual subtraímos o período definido para a opção NmsCleanup_TrackingStatPurgeDelay.

Para essa finalidade, a lista de schemas de log de delivery é recuperada usando a seguinte query:

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

Ao usar mid-sourcing, a tabela NmsBroadLogMid não é referenciada nos mapeamentos de entrega. O esquema nms:broadLogMid foi adicionado à lista recuperada pela consulta anterior.

O fluxo de trabalho Limpeza do banco de dados limpa dados obsoletos de tabelas recuperadas anteriormente. A seguinte query é usada:

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

onde $(tableName) é o nome de cada tabela na lista de esquemas e $(option) é a data definida para a opção NmsCleanup_BroadLogPurgeDelay (consulte o assistente de implantação).

Finalmente, o fluxo de trabalho verifica se a tabela NmsProviderMsgId existe. Em caso afirmativo, todos os dados obsoletos são excluídos usando a seguinte query:

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

onde $(option) corresponde à data definida para a opção NmsCleanup_BroadLogPurgeDelay (consulte assistente de implantação).

A seguinte consulta é executada para manter somente os erros além de um determinado limite (que é igual a 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

A mensagem coalescingErrors é exibida.

Uma nova conexão é criada para excluir todos os erros que ocorreram entre as datas de início e término. A seguinte query é usada:

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

Cada erro é salvo na tabela NmsEmailErrorStat usando esta consulta:

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

onde cada variável corresponde a um valor recuperado pela consulta anterior.

A variável start foi atualizada com os valores do processo anterior para concluir o loop.

Para recuperar a lista de simulações que exigem limpeza, a seguinte consulta é usada:

SELECT iSimulationId FROM NmsSimulation WHERE iSimulationId<>0

O nome das tabelas a serem excluídas é composto do prefixo wkSimu_ seguido pelo identificador da simulação (por exemplo: wkSimu_456831_aggr):

DROP TABLE wkSimu_456831_aggr