Workflow de nettoyage de la base de données

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 :

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

  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 :

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