Workflow de nettoyage de la base de données database-cleanup-workflow

Les listes à supprimer sont récupérées à l'aide de la requête SQL suivante :

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

Chaque liste possède plusieurs liens vers d'autres tables. Tous ces liens sont supprimés en masse à l'aide de la requête suivante :

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

où $(relatedTable) est un tableau associé à NmsGroup et $(l) est l’identifiant de la liste.

Lorsque la liste est de type 'Liste', la table associée est supprimée à l'aide de la requête suivante :

DROP TABLE grp$(l)

Chaque liste récupérée par l'opération de type Select est supprimée à l'aide de la requête suivante :

DELETE FROM NmsGroup WHERE iGroupId=$(l)

où $(l) est l’identifiant de la liste

Le workflow Nettoyage de la base sélectionne toutes les diffusions pour lesquelles le champ deleteStatus a la valeur Oui ou Recyclé et dont la date de suppression est antérieure à la période définie dans le champ Diffusions supprimées (NmsCleanup_RecycledDeliveryPurgeDelay) de l’assistant de déploiement. Pour plus d’informations, consultez Assistant de déploiement. Cette période est calculée par rapport à la date actuelle du serveur.

La tâche sélectionne ensuite, pour chaque serveur de mid-sourcing, la liste des diffusions à supprimer.

Le workflow Nettoyage de la base supprime les logs de diffusion, les pièces jointes, les informations de pages miroir et toute autre donnée associée.

Avant la suppression définitive de la diffusion, le workflow purge les informations associées dans les tables suivantes :

• Dans la table des exclusions de diffusion (NmsDlvExclusion), la requête suivante est utilisée :

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

  où $(l) est l’identifiant de la diffusion.

• Dans la table des coupons (NmsCouponValue), la requête suivante est utilisée (avec des suppressions en masse) :

  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)

  où $(l) est l’identifiant de la diffusion.

• Dans les tables de logs de diffusion (NmsBroadlogXxx), les suppressions en masse sont exécutées par groupes de 20 000 enregistrements.

• Dans les tables de propositions d’offres (NmsPropositionXxx), les suppressions en masse sont exécutées par groupes de 20 000 enregistrements.

• Dans les tables de logs de tracking (NmsTrackinglogXxx), les suppressions en masse sont exécutées par groupes de 20 000 enregistrements.

• Dans la table de fragments de diffusion (NmsDeliveryPart), les suppressions en masse sont exécutées par groupes de 500 000 enregistrements. Cette table contient des informations de personnalisation relatives aux messages restants à diffuser.

• Dans la table des fragments de données des pages miroir (NmsMirrorPageInfo), les suppressions en masse sont exécutées par groupes de 20 000 enregistrements pour les fragments de diffusion arrivés à expiration, mais aussi terminés ou annulés. Cette table contient des informations de personnalisation relatives à tous les messages utilisés pour générer les pages miroir.

• Dans la table de recherche des pages miroir (NmsMirrorPageSearch), les suppressions en masse sont exécutées par groupes de 20 000 enregistrements. Cette table est un index de recherche qui donne accès aux informations de personnalisation stockées dans la table NmsMirrorPageInfo.

• Dans la table de log de traitement par lot (XtkJobLog), les suppressions en masse sont exécutées par lots de 20 000 enregistrements. Cette table contient le log des diffusions à supprimer.

• Dans le tableau de tracking des URL d’une diffusion (NmsTrackingUrl), la requête suivante est utilisée :

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

  où $(l) est l’identifiant de la diffusion.

  Cette table contient les URL présentes dans les diffusions à supprimer afin de permettre leur tracking.

La diffusion est ensuite supprimée de la table des diffusions (NmsDelivery) :

DELETE FROM NmsDelivery WHERE iDeliveryId = $(l)

où $(l) est l’identifiant de la diffusion.

Pour cela, le workflow vérifie que chaque diffusion est inactive (en se basant sur son état). Si une diffusion est active, elle sera interrompue avant d'être supprimée. La vérification est effectuée en exécutant la requête suivante :

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

où $(l) est l’identifiant de la diffusion.

Si l'état a pour valeur Démarrage en attente, En cours, Reprise en attente, Reprise en cours, Pause demandée, Pause en cours, ou En pause (valeurs 51, 55, 61, 62, 71, 72, 75), la diffusion est alors stoppée et la tâche procède à la purge des informations associées.

Le workflow Nettoyage de la base crée la liste des diffusions ayant expiré. Cette liste inclut toutes les diffusions ayant expiré dont l'état est différent de Terminé, ainsi que les diffusions récemment arrêtées avec plus de 10000 messages non traités. La requête suivante est utilisée :

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

où delivery mode 1 correspond au mode Envoi en masse, state 51 correspond à l’état Démarrage en attente, state 85 correspond à l’état Arrêté, et le nombre maximum de logs de diffusion mis à jour en masse sur le serveur de diffusion est de 10 000.

Le workflow inclut ensuite la liste des diffusions qui ont récemment expiré et qui utilisent le mid-sourcing. Les diffusions pour lesquelles les logs de diffusion n'ont pas encore été récupérés depuis serveur de mid-sourcing ne sont pas incluses.

La requête suivante est utilisée :

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

La requête suivante est utilisée pour détecter si le compte externe est toujours actif, afin de pouvoir filtrer les diffusions selon la date :

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

Dans la liste de diffusions ayant expiré, les logs de diffusion dont le statut est En attente passent au statut Envoi annulé, et toutes les diffusions de cette liste passent au statut Terminé .

Les requêtes utilisées sont les suivantes :

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

où $(curdate) est la date courante du serveur de la base de données, $(bl) est l’identifiant du message des logs de diffusion, $(dl) est l’identifiant de la diffusion, delivery status 6 correspond au statut En attente et delivery status 7 correspond au statut Envoie annulé.

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

où delivery state 95 correspond au statut Terminé et $(dl) est l’identifiant de la diffusion.

Tous les fragments (deliveryParts) des diffusions obsolètes sont supprimés et tous les fragments obsolètes des diffusions de notification toujours en cours sont supprimés. Une suppression en masse est utilisée pour ces deux tâches.

Les requêtes utilisées sont les suivantes :

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)

où delivery state 95 correspond au statut Terminé, delivery state 85 correspond au statut Arrêté et $(curDate) est la date courante du serveur.

Tout d'abord, la liste des diffusions à purger est récupérée à l'aide de la requête suivante :

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

où $(curDate) est la date actuelle du serveur.

Le tableau NmsMirrorPageInfo est ensuite purgé, si nécessaire, à l’aide de l’identifiant de la diffusion récupéré précédemment. Une suppression en masse est utilisée pour générer les requêtes suivantes :

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

où $(dl) est l’identifiant de la diffusion.

Un log est ensuite ajouté au journal de la diffusion.

Les diffusions purgées sont ensuite identifiées afin de ne pas avoir à les retraiter par la suite. La requête suivante est exécutée :

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

où $(strIn) est la liste des identifiants de diffusion.

La liste des tables dont le nom commence par wkDlv_ est récupérée en premier lieu avec la requête suivante (postgresql) :

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

Les tables utilisées par des workflows en cours sont ensuite exclues. Pour cela, la liste des diffusions en cours est récupérée à l'aide de la requête suivante :

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

où 0 est la valeur correspondant au statut de diffusion En édition, 85 correspond au statut Arrêté et 100 correspond au statut Supprimé.

Les tableaux qui ne sont plus utilisés sont supprimés à l’aide de la requête suivante :

DROP TABLE wkDlv_15487_1;

Une suppression en masse est exécutée sur la table XtkReject avec la requête suivante :

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

où $(curDate) est la date actuelle du serveur à laquelle est soustraite la période définie pour l’option NmsCleanup_RejectsPurgeDelay (voir Assistant de déploiement) et $(l) est le nombre maximum d’enregistrements à supprimer en masse.

Tous les rejets orphelins sont alors supprimés à l’aide de la requête suivante :

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

Pour récupérer la liste des workflows à supprimer, la requête suivante est utilisée :

SELECT iWorkflowId, iHistory FROM XtkWorkflow WHERE iWorkflowId<>0

Cette requête génère la liste des workflows qui seront utilisés pour supprimer tous les logs associés, les tâches terminées et les évènements terminés, à l'aide des requêtes suivantes :

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

où $(lworkflow) est l’identifiant du workflow et $(lhistory) est l’identifiant de l’historique.

Toutes les tables inutilisées sont supprimées. À cette fin, toutes les tables sont collectées grâce à un masque de type wkf% à l’aide de la requête suivante (postgresql) :

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

Puis, toutes les tables utilisées par une instance de workflow en attente sont exclues. La liste des workflows actifs est récupérée à l’aide de la requête suivante :

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

Chaque identifiant de workflow est ensuite récupéré afin de trouver le nom des tables utilisées par des workflows en cours. Ces noms sont exclus de la liste de tables récupérée précédemment.

Les tables d'historique des activités de type "requête incrémentale" sont exclues, à l'aide des requêtes suivantes :

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

où $(strcondition) est la liste des tableaux correspondant au masque wkfhisto%.

Les tableaux restants sont supprimés à l’aide de la requête suivante :

DROP TABLE wkf15487_12;

Tout d'abord, la liste des tables de logs de tracking est récupérée à l'aide de la requête suivante :

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

Une suppression en masse est utilisée pour purger toutes les tables dans la liste des tables récupérées précédemment. La requête utilisée est la suivante :

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

où $(tsDate) est la date actuelle du serveur à laquelle est soustraite la période définie pour l’option NmsCleanup_TrackingLogPurgeDelay.

Le tableau des statistiques de tracking est purgé à l’aide d’une suppression en masse. La requête suivante est utilisée :

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

où $(tsDate) est la date actuelle du serveur à laquelle est soustraite la période définie pour l’option NmsCleanup_TrackingStatPurgeDelay.

Pour cela, la liste des schémas de logs de diffusion est récupérée à l'aide de la requête suivante :

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

Dans le cas de l'utilisation du mid-sourcing, la table NmsBroadLogMid n'est pas référencée dans les mappings de diffusion. Le schéma nms:broadLogMid est alors ajouté à la liste récupérée par la requête précédente.

Le workflow Nettoyage de la base procède ensuite à la purge des enregistrements obsolètes dans les tables récupérées précédemment. La requête suivante est utilisée :

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

où $(tableName) est le nom de chaque tableau dans la liste de schémas, et $(option) est la date définie pour l’option NmsCleanup_BroadLogPurgeDelay (voir Assistant de déploiement).

Le workflow vérifie enfin si le tableau NmsProviderMsgId existe. Si c’est le cas, tous ses enregistrements obsolètes sont supprimés à l’aide de la requête suivante :

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

où $(option) correspond à la date définie pour l’option NmsCleanup_BroadLogPurgeDelay (voir Assistant de déploiement).

La requête suivante est exécutée afin de ne conserver que les erreurs situées au-delà d'un certain seuil (égal à 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

Le message coalescingErrors apparaît.

Une nouvelle connexion est créée pour supprimer toutes les erreurs survenues entre les dates de début et de fin. La requête suivante est utilisée :

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

Chaque erreur est enregistrée dans la table NmsEmailErrorStat à l'aide de la requête suivante :

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

où chaque variable correspond à une valeur récupérée par la requête précédente.

La variable start est mise à jour avec les valeurs de la date du traitement précédent afin de terminer la boucle.

Pour récupérer la liste des simulations à nettoyer, la requête suivante est utilisée :

SELECT iSimulationId FROM NmsSimulation WHERE iSimulationId<>0

Le nom des tables à supprimer est composé du préfixe wkSimu_ suivi de l'identifiant de la simulation (par exemple : wkSimu_456831_aggr). La requête suivante est utilisée :

DROP TABLE wkSimu_456831_aggr