DataInconsistencyController.php
1 day ago
DataInconsistencyRepository.php
1 day ago
index.php
2 years ago
DataInconsistencyRepository.php
331 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Util\DataInconsistency; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Cron\Workers\SendingQueue\SendingQueue; |
| 9 | use MailPoet\Entities\CustomFieldEntity; |
| 10 | use MailPoet\Entities\NewsletterEntity; |
| 11 | use MailPoet\Entities\NewsletterLinkEntity; |
| 12 | use MailPoet\Entities\NewsletterPostEntity; |
| 13 | use MailPoet\Entities\ScheduledTaskEntity; |
| 14 | use MailPoet\Entities\ScheduledTaskSubscriberEntity; |
| 15 | use MailPoet\Entities\SegmentEntity; |
| 16 | use MailPoet\Entities\SendingQueueEntity; |
| 17 | use MailPoet\Entities\SubscriberCustomFieldEntity; |
| 18 | use MailPoet\Entities\SubscriberEntity; |
| 19 | use MailPoet\Entities\SubscriberSegmentEntity; |
| 20 | use MailPoet\Entities\SubscriberTagEntity; |
| 21 | use MailPoet\Entities\TagEntity; |
| 22 | use MailPoetVendor\Doctrine\DBAL\ArrayParameterType; |
| 23 | use MailPoetVendor\Doctrine\DBAL\ParameterType; |
| 24 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 25 | use MailPoetVendor\Doctrine\ORM\Query; |
| 26 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 27 | |
| 28 | class DataInconsistencyRepository { |
| 29 | const DELETE_ROWS_LIMIT = 10000; |
| 30 | |
| 31 | private EntityManager $entityManager; |
| 32 | |
| 33 | public function __construct( |
| 34 | EntityManager $entityManager |
| 35 | ) { |
| 36 | $this->entityManager = $entityManager; |
| 37 | } |
| 38 | |
| 39 | public function getOrphanedSendingTasksCount(): int { |
| 40 | $builder = $this->entityManager->createQueryBuilder() |
| 41 | ->select('count(st.id)'); |
| 42 | return (int)$this->buildOrphanedSendingTasksQuery($builder)->getSingleScalarResult(); |
| 43 | } |
| 44 | |
| 45 | public function getOrphanedScheduledTasksSubscribersCount(): int { |
| 46 | $this->createOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 47 | $count = $this->getOrphanedScheduledTasksSubscribersCountFromTemporaryTables(); |
| 48 | $this->dropOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 49 | return $count; |
| 50 | } |
| 51 | |
| 52 | private function getOrphanedScheduledTasksSubscribersCountFromTemporaryTables(): int { |
| 53 | $connection = $this->entityManager->getConnection(); |
| 54 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 55 | /** @var string $count */ |
| 56 | $count = $connection->executeQuery(" |
| 57 | SELECT COUNT(*) FROM $stsTable sts WHERE sts.task_id IN (SELECT task_id FROM orphaned_task_ids) |
| 58 | ")->fetchOne(); |
| 59 | return intval($count); |
| 60 | } |
| 61 | |
| 62 | /** |
| 63 | * The mirror image of getOrphanedSendingTasksCount(): sending queues whose scheduled task |
| 64 | * is gone. Such a queue is the only remaining record of how many emails it sent, so there |
| 65 | * is no safe cleanup for it — it is reported so support can recognise a damaged sending |
| 66 | * history behind statistics that no longer add up. |
| 67 | */ |
| 68 | public function getSendingQueuesWithoutTaskCount(): int { |
| 69 | $sqTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 70 | $stTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName(); |
| 71 | /** @var string $count */ |
| 72 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 73 | SELECT count(*) FROM $sqTable sq |
| 74 | LEFT JOIN $stTable st ON st.`id` = sq.`task_id` |
| 75 | WHERE st.`id` IS NULL |
| 76 | ")->fetchOne(); |
| 77 | return intval($count); |
| 78 | } |
| 79 | |
| 80 | public function getSendingQueuesWithoutNewsletterCount(): int { |
| 81 | $sqTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 82 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 83 | /** @var string $count */ |
| 84 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 85 | SELECT count(*) FROM $sqTable sq |
| 86 | LEFT JOIN $newsletterTable n ON n.`id` = sq.`newsletter_id` |
| 87 | WHERE n.`id` IS NULL |
| 88 | ")->fetchOne(); |
| 89 | return intval($count); |
| 90 | } |
| 91 | |
| 92 | public function getOrphanedSubscriptionsCount(): int { |
| 93 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 94 | $segmentTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName(); |
| 95 | $subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName(); |
| 96 | /** @var string $count */ |
| 97 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 98 | SELECT count(distinct ss.`id`) FROM $subscriberSegmentTable ss |
| 99 | LEFT JOIN $segmentTable seg ON seg.`id` = ss.`segment_id` |
| 100 | LEFT JOIN $subscriberTable sub ON sub.`id` = ss.`subscriber_id` |
| 101 | WHERE seg.`id` IS NULL OR sub.`id` IS NULL |
| 102 | ")->fetchOne(); |
| 103 | return intval($count); |
| 104 | } |
| 105 | |
| 106 | public function getOrphanedSubscriberCustomFieldsCount(): int { |
| 107 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 108 | $customFieldTable = $this->entityManager->getClassMetadata(CustomFieldEntity::class)->getTableName(); |
| 109 | $subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName(); |
| 110 | /** @var string $count */ |
| 111 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 112 | SELECT count(distinct scf.`id`) FROM $subscriberCustomFieldTable scf |
| 113 | LEFT JOIN $customFieldTable cf ON cf.`id` = scf.`custom_field_id` |
| 114 | LEFT JOIN $subscriberTable sub ON sub.`id` = scf.`subscriber_id` |
| 115 | WHERE cf.`id` IS NULL OR sub.`id` IS NULL |
| 116 | ")->fetchOne(); |
| 117 | return intval($count); |
| 118 | } |
| 119 | |
| 120 | public function getOrphanedSubscriberTagsCount(): int { |
| 121 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 122 | $tagTable = $this->entityManager->getClassMetadata(TagEntity::class)->getTableName(); |
| 123 | $subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName(); |
| 124 | /** @var string $count */ |
| 125 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 126 | SELECT count(distinct st.`id`) FROM $subscriberTagTable st |
| 127 | LEFT JOIN $tagTable t ON t.`id` = st.`tag_id` |
| 128 | LEFT JOIN $subscriberTable sub ON sub.`id` = st.`subscriber_id` |
| 129 | WHERE t.`id` IS NULL OR sub.`id` IS NULL |
| 130 | ")->fetchOne(); |
| 131 | return intval($count); |
| 132 | } |
| 133 | |
| 134 | public function getOrphanedNewsletterLinksCount(): int { |
| 135 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 136 | $sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 137 | $newsletterLinkTable = $this->entityManager->getClassMetadata(NewsletterLinkEntity::class)->getTableName(); |
| 138 | /** @var string $count */ |
| 139 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 140 | SELECT count(distinct nl.`id`) FROM $newsletterLinkTable nl |
| 141 | LEFT JOIN $newsletterTable n ON n.`id` = nl.`newsletter_id` |
| 142 | LEFT JOIN $sendingQueueTable sq ON sq.`id` = nl.`queue_id` |
| 143 | WHERE n.`id` IS NULL OR sq.`id` IS NULL |
| 144 | ")->fetchOne(); |
| 145 | return intval($count); |
| 146 | } |
| 147 | |
| 148 | public function getOrphanedNewsletterPostsCount(): int { |
| 149 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 150 | $newsletterPostTable = $this->entityManager->getClassMetadata(NewsletterPostEntity::class)->getTableName(); |
| 151 | /** @var string $count */ |
| 152 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 153 | SELECT count(distinct np.`id`) FROM $newsletterPostTable np |
| 154 | LEFT JOIN $newsletterTable n ON n.`id` = np.`newsletter_id` |
| 155 | WHERE n.`id` IS NULL |
| 156 | ")->fetchOne(); |
| 157 | return intval($count); |
| 158 | } |
| 159 | |
| 160 | public function cleanupOrphanedSendingTasks(): int { |
| 161 | /** @var array<int, array{id: string}> $ids */ |
| 162 | $ids = $this->buildOrphanedSendingTasksQuery( |
| 163 | $this->entityManager->createQueryBuilder() |
| 164 | ->select('st.id') |
| 165 | )->getResult(); |
| 166 | |
| 167 | if (!$ids) { |
| 168 | return 0; |
| 169 | } |
| 170 | $ids = array_column($ids, 'id'); |
| 171 | // delete the orphaned tasks |
| 172 | $qb = $this->entityManager->createQueryBuilder(); |
| 173 | $countDeletedTasks = $qb->delete(ScheduledTaskEntity::class, 'st') |
| 174 | ->where($qb->expr()->in('st.id', ':ids')) |
| 175 | ->setParameter('ids', $ids) |
| 176 | ->getQuery() |
| 177 | ->execute(); |
| 178 | |
| 179 | // delete the scheduled tasks subscribers |
| 180 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 181 | $this->entityManager->getConnection()->executeStatement( |
| 182 | "DELETE sts_top FROM $stsTable sts_top |
| 183 | JOIN ( |
| 184 | SELECT sts.`task_id`, sts.`subscriber_id` FROM $stsTable sts |
| 185 | WHERE `task_id` IN (:ids) |
| 186 | LIMIT :limit |
| 187 | ) as to_delete ON sts_top.`task_id` = to_delete.`task_id` AND sts_top.`subscriber_id` = to_delete.`subscriber_id`", |
| 188 | ['limit' => self::DELETE_ROWS_LIMIT, 'ids' => $ids], |
| 189 | ['limit' => ParameterType::INTEGER, 'ids' => ArrayParameterType::INTEGER] |
| 190 | ); |
| 191 | |
| 192 | |
| 193 | $qb = $this->entityManager->createQueryBuilder(); |
| 194 | $qb->delete(ScheduledTaskSubscriberEntity::class, 'sts') |
| 195 | ->where($qb->expr()->in('sts.task', ':ids')) |
| 196 | ->setParameter('ids', $ids) |
| 197 | ->getQuery() |
| 198 | ->execute(); |
| 199 | |
| 200 | return $countDeletedTasks; |
| 201 | } |
| 202 | |
| 203 | public function cleanupOrphanedScheduledTaskSubscribers(): int { |
| 204 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 205 | $deletedCount = 0; |
| 206 | |
| 207 | $this->createOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 208 | do { |
| 209 | $deletedCount += (int)$this->entityManager->getConnection()->executeStatement( |
| 210 | " |
| 211 | DELETE sts_top FROM $stsTable sts_top |
| 212 | JOIN ( |
| 213 | SELECT task_id, subscriber_id |
| 214 | FROM $stsTable |
| 215 | WHERE task_id IN (SELECT task_id FROM orphaned_task_ids) |
| 216 | LIMIT :limit |
| 217 | ) AS to_delete ON sts_top.task_id = to_delete.task_id AND sts_top.subscriber_id = to_delete.subscriber_id |
| 218 | ", |
| 219 | ['limit' => self::DELETE_ROWS_LIMIT], |
| 220 | ['limit' => ParameterType::INTEGER] |
| 221 | ); |
| 222 | } while ($this->getOrphanedScheduledTasksSubscribersCountFromTemporaryTables() > 0); |
| 223 | $this->dropOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 224 | return $deletedCount; |
| 225 | } |
| 226 | |
| 227 | public function cleanupSendingQueuesWithoutNewsletter(): int { |
| 228 | $sqTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 229 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 230 | $deletedQueuesCount = (int)$this->entityManager->getConnection()->executeStatement(" |
| 231 | DELETE sq FROM $sqTable sq |
| 232 | LEFT JOIN $newsletterTable n ON n.`id` = sq.`newsletter_id` |
| 233 | WHERE n.`id` IS NULL |
| 234 | "); |
| 235 | |
| 236 | $this->cleanupOrphanedSendingTasks(); |
| 237 | return $deletedQueuesCount; |
| 238 | } |
| 239 | |
| 240 | public function cleanupOrphanedSubscriptions(): int { |
| 241 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 242 | $segmentTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName(); |
| 243 | $subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName(); |
| 244 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 245 | DELETE ss FROM $subscriberSegmentTable ss |
| 246 | LEFT JOIN $segmentTable seg ON seg.`id` = ss.`segment_id` |
| 247 | LEFT JOIN $subscriberTable sub ON sub.`id` = ss.`subscriber_id` |
| 248 | WHERE seg.`id` IS NULL OR sub.`id` IS NULL |
| 249 | "); |
| 250 | } |
| 251 | |
| 252 | public function cleanupOrphanedSubscriberCustomFields(): int { |
| 253 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 254 | $customFieldTable = $this->entityManager->getClassMetadata(CustomFieldEntity::class)->getTableName(); |
| 255 | $subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName(); |
| 256 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 257 | DELETE scf FROM $subscriberCustomFieldTable scf |
| 258 | LEFT JOIN $customFieldTable cf ON cf.`id` = scf.`custom_field_id` |
| 259 | LEFT JOIN $subscriberTable sub ON sub.`id` = scf.`subscriber_id` |
| 260 | WHERE cf.`id` IS NULL OR sub.`id` IS NULL |
| 261 | "); |
| 262 | } |
| 263 | |
| 264 | public function cleanupOrphanedSubscriberTags(): int { |
| 265 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 266 | $tagTable = $this->entityManager->getClassMetadata(TagEntity::class)->getTableName(); |
| 267 | $subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName(); |
| 268 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 269 | DELETE st FROM $subscriberTagTable st |
| 270 | LEFT JOIN $tagTable t ON t.`id` = st.`tag_id` |
| 271 | LEFT JOIN $subscriberTable sub ON sub.`id` = st.`subscriber_id` |
| 272 | WHERE t.`id` IS NULL OR sub.`id` IS NULL |
| 273 | "); |
| 274 | } |
| 275 | |
| 276 | public function cleanupOrphanedNewsletterLinks(): int { |
| 277 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 278 | $sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 279 | $newsletterLinkTable = $this->entityManager->getClassMetadata(NewsletterLinkEntity::class)->getTableName(); |
| 280 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 281 | DELETE nl FROM $newsletterLinkTable nl |
| 282 | LEFT JOIN $newsletterTable n ON n.`id` = nl.`newsletter_id` |
| 283 | LEFT JOIN $sendingQueueTable sq ON sq.`id` = nl.`queue_id` |
| 284 | WHERE n.`id` IS NULL OR sq.`id` IS NULL |
| 285 | "); |
| 286 | } |
| 287 | |
| 288 | public function cleanupOrphanedNewsletterPosts(): int { |
| 289 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 290 | $newsletterPostTable = $this->entityManager->getClassMetadata(NewsletterPostEntity::class)->getTableName(); |
| 291 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 292 | DELETE np FROM $newsletterPostTable np |
| 293 | LEFT JOIN $newsletterTable n ON n.`id` = np.`newsletter_id` |
| 294 | WHERE n.`id` IS NULL |
| 295 | "); |
| 296 | } |
| 297 | |
| 298 | private function buildOrphanedSendingTasksQuery(QueryBuilder $queryBuilder): Query { |
| 299 | return $queryBuilder |
| 300 | ->from(ScheduledTaskEntity::class, 'st') |
| 301 | ->leftJoin('st.sendingQueue', 'sq') |
| 302 | ->where('sq.id IS NULL') |
| 303 | ->andWhere('st.type = :type') |
| 304 | ->setParameter('type', SendingQueue::TASK_TYPE) |
| 305 | ->getQuery(); |
| 306 | } |
| 307 | |
| 308 | private function createOrphanedScheduledTaskSubscribersTemporaryTables(): void { |
| 309 | $connection = $this->entityManager->getConnection(); |
| 310 | $stTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName(); |
| 311 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 312 | |
| 313 | // 1. Get the DISTINCT task IDs so that the subsequent JOIN is more efficient. |
| 314 | $connection->executeStatement(" |
| 315 | CREATE TEMPORARY TABLE IF NOT EXISTS task_ids |
| 316 | SELECT DISTINCT task_id FROM $stsTable |
| 317 | "); |
| 318 | |
| 319 | // 2. Get the orphaned task IDs. |
| 320 | $connection->executeStatement(" |
| 321 | CREATE TEMPORARY TABLE IF NOT EXISTS orphaned_task_ids |
| 322 | SELECT task_id FROM task_ids LEFT JOIN $stTable st ON st.id = task_ids.task_id WHERE st.id IS NULL |
| 323 | "); |
| 324 | } |
| 325 | |
| 326 | private function dropOrphanedScheduledTaskSubscribersTemporaryTables(): void { |
| 327 | $this->entityManager->getConnection()->executeStatement("DROP TEMPORARY TABLE IF EXISTS task_ids"); |
| 328 | $this->entityManager->getConnection()->executeStatement("DROP TEMPORARY TABLE IF EXISTS orphaned_task_ids"); |
| 329 | } |
| 330 | } |
| 331 |