DataInconsistencyController.php
2 weeks ago
DataInconsistencyRepository.php
2 weeks ago
index.php
1 year ago
DataInconsistencyRepository.php
313 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 | public function getSendingQueuesWithoutNewsletterCount(): int { |
| 63 | $sqTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 64 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 65 | /** @var string $count */ |
| 66 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 67 | SELECT count(*) FROM $sqTable sq |
| 68 | LEFT JOIN $newsletterTable n ON n.`id` = sq.`newsletter_id` |
| 69 | WHERE n.`id` IS NULL |
| 70 | ")->fetchOne(); |
| 71 | return intval($count); |
| 72 | } |
| 73 | |
| 74 | public function getOrphanedSubscriptionsCount(): int { |
| 75 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 76 | $segmentTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName(); |
| 77 | $subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName(); |
| 78 | /** @var string $count */ |
| 79 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 80 | SELECT count(distinct ss.`id`) FROM $subscriberSegmentTable ss |
| 81 | LEFT JOIN $segmentTable seg ON seg.`id` = ss.`segment_id` |
| 82 | LEFT JOIN $subscriberTable sub ON sub.`id` = ss.`subscriber_id` |
| 83 | WHERE seg.`id` IS NULL OR sub.`id` IS NULL |
| 84 | ")->fetchOne(); |
| 85 | return intval($count); |
| 86 | } |
| 87 | |
| 88 | public function getOrphanedSubscriberCustomFieldsCount(): int { |
| 89 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 90 | $customFieldTable = $this->entityManager->getClassMetadata(CustomFieldEntity::class)->getTableName(); |
| 91 | $subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName(); |
| 92 | /** @var string $count */ |
| 93 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 94 | SELECT count(distinct scf.`id`) FROM $subscriberCustomFieldTable scf |
| 95 | LEFT JOIN $customFieldTable cf ON cf.`id` = scf.`custom_field_id` |
| 96 | LEFT JOIN $subscriberTable sub ON sub.`id` = scf.`subscriber_id` |
| 97 | WHERE cf.`id` IS NULL OR sub.`id` IS NULL |
| 98 | ")->fetchOne(); |
| 99 | return intval($count); |
| 100 | } |
| 101 | |
| 102 | public function getOrphanedSubscriberTagsCount(): int { |
| 103 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 104 | $tagTable = $this->entityManager->getClassMetadata(TagEntity::class)->getTableName(); |
| 105 | $subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName(); |
| 106 | /** @var string $count */ |
| 107 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 108 | SELECT count(distinct st.`id`) FROM $subscriberTagTable st |
| 109 | LEFT JOIN $tagTable t ON t.`id` = st.`tag_id` |
| 110 | LEFT JOIN $subscriberTable sub ON sub.`id` = st.`subscriber_id` |
| 111 | WHERE t.`id` IS NULL OR sub.`id` IS NULL |
| 112 | ")->fetchOne(); |
| 113 | return intval($count); |
| 114 | } |
| 115 | |
| 116 | public function getOrphanedNewsletterLinksCount(): int { |
| 117 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 118 | $sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 119 | $newsletterLinkTable = $this->entityManager->getClassMetadata(NewsletterLinkEntity::class)->getTableName(); |
| 120 | /** @var string $count */ |
| 121 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 122 | SELECT count(distinct nl.`id`) FROM $newsletterLinkTable nl |
| 123 | LEFT JOIN $newsletterTable n ON n.`id` = nl.`newsletter_id` |
| 124 | LEFT JOIN $sendingQueueTable sq ON sq.`id` = nl.`queue_id` |
| 125 | WHERE n.`id` IS NULL OR sq.`id` IS NULL |
| 126 | ")->fetchOne(); |
| 127 | return intval($count); |
| 128 | } |
| 129 | |
| 130 | public function getOrphanedNewsletterPostsCount(): int { |
| 131 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 132 | $newsletterPostTable = $this->entityManager->getClassMetadata(NewsletterPostEntity::class)->getTableName(); |
| 133 | /** @var string $count */ |
| 134 | $count = $this->entityManager->getConnection()->executeQuery(" |
| 135 | SELECT count(distinct np.`id`) FROM $newsletterPostTable np |
| 136 | LEFT JOIN $newsletterTable n ON n.`id` = np.`newsletter_id` |
| 137 | WHERE n.`id` IS NULL |
| 138 | ")->fetchOne(); |
| 139 | return intval($count); |
| 140 | } |
| 141 | |
| 142 | public function cleanupOrphanedSendingTasks(): int { |
| 143 | /** @var array<int, array{id: string}> $ids */ |
| 144 | $ids = $this->buildOrphanedSendingTasksQuery( |
| 145 | $this->entityManager->createQueryBuilder() |
| 146 | ->select('st.id') |
| 147 | )->getResult(); |
| 148 | |
| 149 | if (!$ids) { |
| 150 | return 0; |
| 151 | } |
| 152 | $ids = array_column($ids, 'id'); |
| 153 | // delete the orphaned tasks |
| 154 | $qb = $this->entityManager->createQueryBuilder(); |
| 155 | $countDeletedTasks = $qb->delete(ScheduledTaskEntity::class, 'st') |
| 156 | ->where($qb->expr()->in('st.id', ':ids')) |
| 157 | ->setParameter('ids', $ids) |
| 158 | ->getQuery() |
| 159 | ->execute(); |
| 160 | |
| 161 | // delete the scheduled tasks subscribers |
| 162 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 163 | $this->entityManager->getConnection()->executeStatement( |
| 164 | "DELETE sts_top FROM $stsTable sts_top |
| 165 | JOIN ( |
| 166 | SELECT sts.`task_id`, sts.`subscriber_id` FROM $stsTable sts |
| 167 | WHERE `task_id` IN (:ids) |
| 168 | LIMIT :limit |
| 169 | ) as to_delete ON sts_top.`task_id` = to_delete.`task_id` AND sts_top.`subscriber_id` = to_delete.`subscriber_id`", |
| 170 | ['limit' => self::DELETE_ROWS_LIMIT, 'ids' => $ids], |
| 171 | ['limit' => ParameterType::INTEGER, 'ids' => ArrayParameterType::INTEGER] |
| 172 | ); |
| 173 | |
| 174 | |
| 175 | $qb = $this->entityManager->createQueryBuilder(); |
| 176 | $qb->delete(ScheduledTaskSubscriberEntity::class, 'sts') |
| 177 | ->where($qb->expr()->in('sts.task', ':ids')) |
| 178 | ->setParameter('ids', $ids) |
| 179 | ->getQuery() |
| 180 | ->execute(); |
| 181 | |
| 182 | return $countDeletedTasks; |
| 183 | } |
| 184 | |
| 185 | public function cleanupOrphanedScheduledTaskSubscribers(): int { |
| 186 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 187 | $deletedCount = 0; |
| 188 | |
| 189 | $this->createOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 190 | do { |
| 191 | $deletedCount += (int)$this->entityManager->getConnection()->executeStatement( |
| 192 | " |
| 193 | DELETE sts_top FROM $stsTable sts_top |
| 194 | JOIN ( |
| 195 | SELECT task_id, subscriber_id |
| 196 | FROM $stsTable |
| 197 | WHERE task_id IN (SELECT task_id FROM orphaned_task_ids) |
| 198 | LIMIT :limit |
| 199 | ) AS to_delete ON sts_top.task_id = to_delete.task_id AND sts_top.subscriber_id = to_delete.subscriber_id |
| 200 | ", |
| 201 | ['limit' => self::DELETE_ROWS_LIMIT], |
| 202 | ['limit' => ParameterType::INTEGER] |
| 203 | ); |
| 204 | } while ($this->getOrphanedScheduledTasksSubscribersCountFromTemporaryTables() > 0); |
| 205 | $this->dropOrphanedScheduledTaskSubscribersTemporaryTables(); |
| 206 | return $deletedCount; |
| 207 | } |
| 208 | |
| 209 | public function cleanupSendingQueuesWithoutNewsletter(): int { |
| 210 | $sqTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 211 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 212 | $deletedQueuesCount = (int)$this->entityManager->getConnection()->executeStatement(" |
| 213 | DELETE sq FROM $sqTable sq |
| 214 | LEFT JOIN $newsletterTable n ON n.`id` = sq.`newsletter_id` |
| 215 | WHERE n.`id` IS NULL |
| 216 | "); |
| 217 | |
| 218 | $this->cleanupOrphanedSendingTasks(); |
| 219 | return $deletedQueuesCount; |
| 220 | } |
| 221 | |
| 222 | public function cleanupOrphanedSubscriptions(): int { |
| 223 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 224 | $segmentTable = $this->entityManager->getClassMetadata(SegmentEntity::class)->getTableName(); |
| 225 | $subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName(); |
| 226 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 227 | DELETE ss FROM $subscriberSegmentTable ss |
| 228 | LEFT JOIN $segmentTable seg ON seg.`id` = ss.`segment_id` |
| 229 | LEFT JOIN $subscriberTable sub ON sub.`id` = ss.`subscriber_id` |
| 230 | WHERE seg.`id` IS NULL OR sub.`id` IS NULL |
| 231 | "); |
| 232 | } |
| 233 | |
| 234 | public function cleanupOrphanedSubscriberCustomFields(): int { |
| 235 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 236 | $customFieldTable = $this->entityManager->getClassMetadata(CustomFieldEntity::class)->getTableName(); |
| 237 | $subscriberCustomFieldTable = $this->entityManager->getClassMetadata(SubscriberCustomFieldEntity::class)->getTableName(); |
| 238 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 239 | DELETE scf FROM $subscriberCustomFieldTable scf |
| 240 | LEFT JOIN $customFieldTable cf ON cf.`id` = scf.`custom_field_id` |
| 241 | LEFT JOIN $subscriberTable sub ON sub.`id` = scf.`subscriber_id` |
| 242 | WHERE cf.`id` IS NULL OR sub.`id` IS NULL |
| 243 | "); |
| 244 | } |
| 245 | |
| 246 | public function cleanupOrphanedSubscriberTags(): int { |
| 247 | $subscriberTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 248 | $tagTable = $this->entityManager->getClassMetadata(TagEntity::class)->getTableName(); |
| 249 | $subscriberTagTable = $this->entityManager->getClassMetadata(SubscriberTagEntity::class)->getTableName(); |
| 250 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 251 | DELETE st FROM $subscriberTagTable st |
| 252 | LEFT JOIN $tagTable t ON t.`id` = st.`tag_id` |
| 253 | LEFT JOIN $subscriberTable sub ON sub.`id` = st.`subscriber_id` |
| 254 | WHERE t.`id` IS NULL OR sub.`id` IS NULL |
| 255 | "); |
| 256 | } |
| 257 | |
| 258 | public function cleanupOrphanedNewsletterLinks(): int { |
| 259 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 260 | $sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 261 | $newsletterLinkTable = $this->entityManager->getClassMetadata(NewsletterLinkEntity::class)->getTableName(); |
| 262 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 263 | DELETE nl FROM $newsletterLinkTable nl |
| 264 | LEFT JOIN $newsletterTable n ON n.`id` = nl.`newsletter_id` |
| 265 | LEFT JOIN $sendingQueueTable sq ON sq.`id` = nl.`queue_id` |
| 266 | WHERE n.`id` IS NULL OR sq.`id` IS NULL |
| 267 | "); |
| 268 | } |
| 269 | |
| 270 | public function cleanupOrphanedNewsletterPosts(): int { |
| 271 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 272 | $newsletterPostTable = $this->entityManager->getClassMetadata(NewsletterPostEntity::class)->getTableName(); |
| 273 | return (int)$this->entityManager->getConnection()->executeStatement(" |
| 274 | DELETE np FROM $newsletterPostTable np |
| 275 | LEFT JOIN $newsletterTable n ON n.`id` = np.`newsletter_id` |
| 276 | WHERE n.`id` IS NULL |
| 277 | "); |
| 278 | } |
| 279 | |
| 280 | private function buildOrphanedSendingTasksQuery(QueryBuilder $queryBuilder): Query { |
| 281 | return $queryBuilder |
| 282 | ->from(ScheduledTaskEntity::class, 'st') |
| 283 | ->leftJoin('st.sendingQueue', 'sq') |
| 284 | ->where('sq.id IS NULL') |
| 285 | ->andWhere('st.type = :type') |
| 286 | ->setParameter('type', SendingQueue::TASK_TYPE) |
| 287 | ->getQuery(); |
| 288 | } |
| 289 | |
| 290 | private function createOrphanedScheduledTaskSubscribersTemporaryTables(): void { |
| 291 | $connection = $this->entityManager->getConnection(); |
| 292 | $stTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName(); |
| 293 | $stsTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 294 | |
| 295 | // 1. Get the DISTINCT task IDs so that the subsequent JOIN is more efficient. |
| 296 | $connection->executeStatement(" |
| 297 | CREATE TEMPORARY TABLE IF NOT EXISTS task_ids |
| 298 | SELECT DISTINCT task_id FROM $stsTable |
| 299 | "); |
| 300 | |
| 301 | // 2. Get the orphaned task IDs. |
| 302 | $connection->executeStatement(" |
| 303 | CREATE TEMPORARY TABLE IF NOT EXISTS orphaned_task_ids |
| 304 | SELECT task_id FROM task_ids LEFT JOIN $stTable st ON st.id = task_ids.task_id WHERE st.id IS NULL |
| 305 | "); |
| 306 | } |
| 307 | |
| 308 | private function dropOrphanedScheduledTaskSubscribersTemporaryTables(): void { |
| 309 | $this->entityManager->getConnection()->executeStatement("DROP TEMPORARY TABLE IF EXISTS task_ids"); |
| 310 | $this->entityManager->getConnection()->executeStatement("DROP TEMPORARY TABLE IF EXISTS orphaned_task_ids"); |
| 311 | } |
| 312 | } |
| 313 |