Migration_20221028_105818.php — MailPoet – Newsletters, Email Marketing, and Automation 5.36.0

mailpoet / lib / Migrations / Db / Migration_20221028_105818.php

mailpoet / lib / Migrations / Db Last commit date

Migration_20221028_105818.php

1093 lines

1	<?php declare(strict_types = 1);
2
3	namespace MailPoet\Migrations\Db;
4
5	if (!defined('ABSPATH')) exit;
6
7
8	use MailPoet\Config\Env;
9	use MailPoet\Entities\DynamicSegmentFilterData;
10	use MailPoet\Entities\FormEntity;
11	use MailPoet\Entities\NewsletterEntity;
12	use MailPoet\Entities\NewsletterLinkEntity;
13	use MailPoet\Entities\NewsletterTemplateEntity;
14	use MailPoet\Entities\ScheduledTaskEntity;
15	use MailPoet\Entities\SendingQueueEntity;
16	use MailPoet\Entities\SettingEntity;
17	use MailPoet\Entities\SubscriberEntity;
18	use MailPoet\Migrator\DbMigration;
19	use MailPoet\Segments\DynamicSegments\Filters\EmailAction;
20	use MailPoet\Segments\DynamicSegments\Filters\UserRole;
21	use MailPoet\Segments\DynamicSegments\Filters\WooCommerceCategory;
22	use MailPoet\Segments\DynamicSegments\Filters\WooCommerceProduct;
23	use MailPoet\Segments\DynamicSegments\Filters\WooCommerceSubscription;
24	use MailPoet\Util\Helpers;
25
26	/**
27	* Moved from MailPoet\Config\Migrator.
28	*
29	* The "created_at" column must be NULL in some tables to avoid "there can be only one
30	* TIMESTAMP column with CURRENT_TIMESTAMP" error on MySQL version < 5.6.5 that occurs
31	* even when other timestamp is simply "NOT NULL".
32	*/
33	class Migration_20221028_105818 extends DbMigration {
34	/** @var string */
35	private $prefix;
36
37	/** @var string */
38	private $charsetCollate;
39
40	/** @var string[] */
41	private $models = [
42	'segments',
43	'settings',
44	'custom_fields',
45	'scheduled_tasks',
46	'stats_notifications',
47	'scheduled_task_subscribers',
48	'sending_queues',
49	'subscribers',
50	'subscriber_segment',
51	'subscriber_custom_field',
52	'subscriber_ips',
53	'newsletters',
54	'newsletter_templates',
55	'newsletter_option_fields',
56	'newsletter_option',
57	'newsletter_segment',
58	'newsletter_links',
59	'newsletter_posts',
60	'forms',
61	'statistics_newsletters',
62	'statistics_clicks',
63	'statistics_bounces',
64	'statistics_opens',
65	'statistics_unsubscribes',
66	'statistics_forms',
67	'statistics_woocommerce_purchases',
68	'log',
69	'user_flags',
70	'feature_flags',
71	'dynamic_segment_filters',
72	'user_agents',
73	'tags',
74	'subscriber_tag',
75	];
76
77	public function run(): void {
78	global $wpdb;
79	$this->prefix = Env::$dbPrefix;
80	$this->charsetCollate = $wpdb->get_charset_collate();
81
82	// Ensure dbDelta function
83	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
84	$output = [];
85	foreach ($this->models as $model) {
86	$modelMethod = Helpers::underscoreToCamelCase($model);
87	$output = array_merge(dbDelta($this->$modelMethod()), $output);
88	}
89	$this->updateNullInUnsubscribeStats();
90	$this->fixScheduledTasksSubscribersTimestampColumns();
91	$this->removeDeprecatedStatisticsIndexes();
92	$this->migrateSerializedFilterDataToNewColumns();
93	$this->migratePurchasedProductDynamicFilters();
94	$this->migrateWooSubscriptionsDynamicFilters();
95	$this->migratePurchasedInCategoryDynamicFilters();
96	$this->migrateEmailActionsFilters();
97
98	// POPULATOR
99	$this->updateMetaFields();
100	$this->updateLastSubscribedAt();
101	$this->updateSentUnsubscribeLinksToInstantUnsubscribeLinks();
102	$this->pauseTasksForPausedNewsletters();
103	$this->moveGoogleAnalyticsFromPremium();
104	$this->moveNewsletterTemplatesThumbnailData();
105	$this->fixNotificationHistoryRecordsStuckAtSending();
106	}
107
108	private function getDbVersion(string $fallback): string {
109	$settingsTable = $this->getTableName(SettingEntity::class);
110	$dbVersion = $this->connection->fetchOne("SELECT value FROM $settingsTable WHERE name = 'db_version'");
111	return is_string($dbVersion) ? $dbVersion : $fallback;
112	}
113
114	private function segments() {
115	$attributes = [
116	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
117	'name varchar(90) NOT NULL,',
118	'type varchar(90) NOT NULL DEFAULT \'default\',',
119	'description varchar(250) NOT NULL DEFAULT \'\',',
120	'public_description text NULL,',
121	'created_at timestamp NULL,', // must be NULL, see comment at the top
122	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
123	'deleted_at timestamp NULL,',
124	'average_engagement_score FLOAT unsigned NULL,',
125	'average_engagement_score_updated_at timestamp NULL,',
126	'PRIMARY KEY (id),',
127	'UNIQUE KEY name (name),',
128	'KEY average_engagement_score_updated_at (average_engagement_score_updated_at)',
129	];
130	return $this->sqlify(__FUNCTION__, $attributes);
131	}
132
133	private function settings() {
134	$attributes = [
135	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
136	'name varchar(50) NOT NULL,',
137	'value longtext,',
138	'created_at timestamp NULL,', // must be NULL, see comment at the top
139	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
140	'PRIMARY KEY (id),',
141	'UNIQUE KEY name (name)',
142	];
143	return $this->sqlify(__FUNCTION__, $attributes);
144	}
145
146	private function customFields() {
147	$attributes = [
148	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
149	'name varchar(90) NOT NULL,',
150	'type varchar(90) NOT NULL,',
151	'params longtext NOT NULL,',
152	'created_at timestamp NULL,', // must be NULL, see comment at the top
153	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
154	'PRIMARY KEY (id),',
155	'UNIQUE KEY name (name)',
156	];
157	return $this->sqlify(__FUNCTION__, $attributes);
158	}
159
160	private function scheduledTasks() {
161	$attributes = [
162	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
163	'type varchar(90) NULL DEFAULT NULL,',
164	'status varchar(12) NULL DEFAULT NULL,',
165	'priority mediumint(9) NOT NULL DEFAULT 0,',
166	'scheduled_at timestamp NULL,',
167	'processed_at timestamp NULL,',
168	'created_at timestamp NULL,', // must be NULL, see comment at the top
169	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
170	'deleted_at timestamp NULL,',
171	'in_progress int(1),',
172	'reschedule_count int(11) NOT NULL DEFAULT 0,',
173	'meta longtext,',
174	'PRIMARY KEY (id),',
175	'KEY type (type),',
176	'KEY status (status)',
177	];
178	return $this->sqlify(__FUNCTION__, $attributes);
179	}
180
181	private function statsNotifications() {
182	$attributes = [
183	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
184	'newsletter_id int(11) unsigned NOT NULL,',
185	'task_id int(11) unsigned NOT NULL,',
186	'created_at timestamp NULL,', // must be NULL, see comment at the top
187	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
188	'PRIMARY KEY (id),',
189	'UNIQUE KEY newsletter_id_task_id (newsletter_id, task_id),',
190	'KEY task_id (task_id)',
191	];
192	return $this->sqlify(__FUNCTION__, $attributes);
193	}
194
195	private function scheduledTaskSubscribers() {
196	$attributes = [
197	'task_id int(11) unsigned NOT NULL,',
198	'subscriber_id int(11) unsigned NOT NULL,',
199	'processed int(1) NOT NULL,',
200	'failed smallint(1) NOT NULL DEFAULT 0,',
201	'error text NULL,',
202	'created_at timestamp NULL,', // must be NULL, see comment at the top
203	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
204	'PRIMARY KEY (task_id, subscriber_id),',
205	'KEY subscriber_id (subscriber_id)',
206	];
207	return $this->sqlify(__FUNCTION__, $attributes);
208	}
209
210	private function sendingQueues() {
211	$attributes = [
212	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
213	'task_id int(11) unsigned NOT NULL,',
214	'newsletter_id int(11) unsigned NULL,',
215	'newsletter_rendered_body longtext,',
216	'newsletter_rendered_subject varchar(250) NULL DEFAULT NULL,',
217	'subscribers longtext,',
218	'count_total int(11) unsigned NOT NULL DEFAULT 0,',
219	'count_processed int(11) unsigned NOT NULL DEFAULT 0,',
220	'count_to_process int(11) unsigned NOT NULL DEFAULT 0,',
221	'meta longtext,',
222	'created_at timestamp NULL,', // must be NULL, see comment at the top
223	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
224	'deleted_at timestamp NULL,',
225	'PRIMARY KEY (id),',
226	'KEY task_id (task_id),',
227	'KEY newsletter_id (newsletter_id)',
228	];
229	return $this->sqlify(__FUNCTION__, $attributes);
230	}
231
232	private function subscribers() {
233	$attributes = [
234	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
235	'wp_user_id bigint(20) NULL,',
236	'is_woocommerce_user int(1) NOT NULL DEFAULT 0,',
237	'first_name varchar(255) NOT NULL DEFAULT \'\',',
238	'last_name varchar(255) NOT NULL DEFAULT \'\',',
239	'email varchar(150) NOT NULL,',
240	'status varchar(12) NOT NULL DEFAULT \'' . SubscriberEntity::STATUS_UNCONFIRMED . '\',',
241	'subscribed_ip varchar(45) NULL,',
242	'confirmed_ip varchar(45) NULL,',
243	'confirmed_at timestamp NULL,',
244	'last_subscribed_at timestamp NULL,',
245	'created_at timestamp NULL,', // must be NULL, see comment at the top
246	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
247	'deleted_at timestamp NULL,',
248	'unconfirmed_data longtext,',
249	"source enum('form','imported','administrator','api','wordpress_user','woocommerce_user','woocommerce_checkout','unknown') DEFAULT 'unknown',",
250	'count_confirmations int(11) unsigned NOT NULL DEFAULT 0,',
251	'unsubscribe_token char(15) NULL,',
252	'link_token char(32) NULL,',
253	'engagement_score FLOAT unsigned NULL,',
254	'engagement_score_updated_at timestamp NULL,',
255	'last_engagement_at timestamp NULL,',
256	'woocommerce_synced_at timestamp NULL,',
257	'email_count int(11) unsigned NOT NULL DEFAULT 0, ',
258	'PRIMARY KEY (id),',
259	'UNIQUE KEY email (email),',
260	'UNIQUE KEY unsubscribe_token (unsubscribe_token),',
261	'KEY wp_user_id (wp_user_id),',
262	'KEY updated_at (updated_at),',
263	'KEY status_deleted_at (status,deleted_at),',
264	'KEY last_subscribed_at (last_subscribed_at),',
265	'KEY engagement_score_updated_at (engagement_score_updated_at),',
266	'KEY link_token (link_token)',
267	];
268	return $this->sqlify(__FUNCTION__, $attributes);
269	}
270
271	private function subscriberSegment() {
272	$attributes = [
273	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
274	'subscriber_id int(11) unsigned NOT NULL,',
275	'segment_id int(11) unsigned NOT NULL,',
276	'status varchar(12) NOT NULL DEFAULT \'' . SubscriberEntity::STATUS_SUBSCRIBED . '\',',
277	'created_at timestamp NULL,', // must be NULL, see comment at the top
278	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
279	'PRIMARY KEY (id),',
280	'UNIQUE KEY subscriber_segment (subscriber_id,segment_id),',
281	'KEY segment_id (segment_id)',
282	];
283	return $this->sqlify(__FUNCTION__, $attributes);
284	}
285
286	private function subscriberCustomField() {
287	$attributes = [
288	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
289	'subscriber_id int(11) unsigned NOT NULL,',
290	'custom_field_id int(11) unsigned NOT NULL,',
291	'value text NOT NULL,',
292	'created_at timestamp NULL,', // must be NULL, see comment at the top
293	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
294	'PRIMARY KEY (id),',
295	'UNIQUE KEY subscriber_id_custom_field_id (subscriber_id,custom_field_id)',
296	];
297	return $this->sqlify(__FUNCTION__, $attributes);
298	}
299
300	private function subscriberIps() {
301	$attributes = [
302	'ip varchar(45) NOT NULL,',
303	'created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,',
304	'PRIMARY KEY (created_at, ip),',
305	'KEY ip (ip)',
306	];
307	return $this->sqlify(__FUNCTION__, $attributes);
308	}
309
310	private function newsletters() {
311	$attributes = [
312	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
313	'hash varchar(150) NULL DEFAULT NULL,',
314	'parent_id int(11) unsigned NULL,',
315	'subject varchar(250) NOT NULL DEFAULT \'\',',
316	'type varchar(20) NOT NULL DEFAULT \'standard\',',
317	'sender_address varchar(150) NOT NULL DEFAULT \'\',',
318	'sender_name varchar(150) NOT NULL DEFAULT \'\',',
319	'status varchar(20) NOT NULL DEFAULT \'' . NewsletterEntity::STATUS_DRAFT . '\',',
320	'reply_to_address varchar(150) NOT NULL DEFAULT \'\',',
321	'reply_to_name varchar(150) NOT NULL DEFAULT \'\',',
322	'preheader varchar(250) NOT NULL DEFAULT \'\',',
323	'body longtext,',
324	'sent_at timestamp NULL,',
325	'created_at timestamp NULL,', // must be NULL, see comment at the top
326	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
327	'deleted_at timestamp NULL,',
328	'unsubscribe_token char(15) NULL,',
329	'ga_campaign varchar(250) NOT NULL DEFAULT \'\',',
330	'PRIMARY KEY (id),',
331	'UNIQUE KEY unsubscribe_token (unsubscribe_token),',
332	'KEY type_status (type,status)',
333	];
334	return $this->sqlify(__FUNCTION__, $attributes);
335	}
336
337	private function newsletterTemplates() {
338	$attributes = [
339	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
340	'newsletter_id int(11) NULL DEFAULT 0,',
341	'name varchar(250) NOT NULL,',
342	'categories varchar(250) NOT NULL DEFAULT \'[]\',',
343	'description varchar(255) NOT NULL DEFAULT \'\',',
344	'body longtext,',
345	'thumbnail longtext,',
346	'thumbnail_data longtext,',
347	'readonly tinyint(1) DEFAULT 0,',
348	'created_at timestamp NULL,', // must be NULL, see comment at the top
349	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
350	'PRIMARY KEY (id)',
351	];
352	return $this->sqlify(__FUNCTION__, $attributes);
353	}
354
355	private function newsletterOptionFields() {
356	$attributes = [
357	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
358	'name varchar(90) NOT NULL,',
359	'newsletter_type varchar(90) NOT NULL,',
360	'created_at timestamp NULL,', // must be NULL, see comment at the top
361	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
362	'PRIMARY KEY (id),',
363	'UNIQUE KEY name_newsletter_type (newsletter_type,name)',
364	];
365	return $this->sqlify(__FUNCTION__, $attributes);
366	}
367
368	private function newsletterOption() {
369	$attributes = [
370	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
371	'newsletter_id int(11) unsigned NOT NULL,',
372	'option_field_id int(11) unsigned NOT NULL,',
373	'value longtext,',
374	'created_at timestamp NULL,', // must be NULL, see comment at the top
375	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
376	'PRIMARY KEY (id),',
377	'UNIQUE KEY newsletter_id_option_field_id (newsletter_id,option_field_id)',
378	];
379	return $this->sqlify(__FUNCTION__, $attributes);
380	}
381
382	private function newsletterSegment() {
383	$attributes = [
384	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
385	'newsletter_id int(11) unsigned NOT NULL,',
386	'segment_id int(11) unsigned NOT NULL,',
387	'created_at timestamp NULL,', // must be NULL, see comment at the top
388	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
389	'PRIMARY KEY (id),',
390	'UNIQUE KEY newsletter_segment (newsletter_id,segment_id)',
391	];
392	return $this->sqlify(__FUNCTION__, $attributes);
393	}
394
395	private function newsletterLinks() {
396	$attributes = [
397	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
398	'newsletter_id int(11) unsigned NOT NULL,',
399	'queue_id int(11) unsigned NOT NULL,',
400	'url varchar(2083) NOT NULL,',
401	'hash varchar(20) NOT NULL,',
402	'created_at timestamp NULL,', // must be NULL, see comment at the top
403	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
404	'PRIMARY KEY (id),',
405	'KEY newsletter_id (newsletter_id),',
406	'KEY queue_id (queue_id),',
407	'KEY url (url(100))',
408	];
409	return $this->sqlify(__FUNCTION__, $attributes);
410	}
411
412	private function newsletterPosts() {
413	$attributes = [
414	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
415	'newsletter_id int(11) unsigned NOT NULL,',
416	'post_id int(11) unsigned NOT NULL,',
417	'created_at timestamp NULL,', // must be NULL, see comment at the top
418	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
419	'PRIMARY KEY (id),',
420	'KEY newsletter_id (newsletter_id)',
421	];
422	return $this->sqlify(__FUNCTION__, $attributes);
423	}
424
425	private function forms() {
426	$attributes = [
427	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
428	'name varchar(90) NOT NULL,', // should be null but db_delta can't handle this change
429	'status varchar(20) NOT NULL DEFAULT \'' . FormEntity::STATUS_ENABLED . '\',',
430	'body longtext,',
431	'settings longtext,',
432	'styles longtext,',
433	'created_at timestamp NULL,', // must be NULL, see comment at the top
434	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
435	'deleted_at timestamp NULL,',
436	'PRIMARY KEY (id)',
437	];
438	return $this->sqlify(__FUNCTION__, $attributes);
439	}
440
441	private function statisticsNewsletters() {
442	$attributes = [
443	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
444	'newsletter_id int(11) unsigned NOT NULL,',
445	'subscriber_id int(11) unsigned NOT NULL,',
446	'queue_id int(11) unsigned NOT NULL,',
447	'sent_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
448	'PRIMARY KEY (id),',
449	'KEY newsletter_id (newsletter_id),',
450	'KEY subscriber_id (subscriber_id)',
451	];
452	return $this->sqlify(__FUNCTION__, $attributes);
453	}
454
455	private function statisticsBounces() {
456	$attributes = [
457	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
458	'newsletter_id int(11) unsigned NOT NULL,',
459	'subscriber_id int(11) unsigned NOT NULL,',
460	'queue_id int(11) unsigned NOT NULL,',
461	'created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,',
462	'PRIMARY KEY (id)',
463	];
464	return $this->sqlify(__FUNCTION__, $attributes);
465	}
466
467	private function statisticsClicks() {
468	$attributes = [
469	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
470	'newsletter_id int(11) unsigned NOT NULL,',
471	'subscriber_id int(11) unsigned NOT NULL,',
472	'queue_id int(11) unsigned NOT NULL,',
473	'link_id int(11) unsigned NOT NULL,',
474	'user_agent_id int(11) unsigned NULL,',
475	'user_agent_type tinyint(1) NOT NULL DEFAULT 0,',
476	'count int(11) unsigned NOT NULL,',
477	'created_at timestamp NULL,', // must be NULL, see comment at the top
478	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
479	'PRIMARY KEY (id),',
480	'KEY newsletter_id_subscriber_id_user_agent_type (newsletter_id, subscriber_id, user_agent_type),',
481	'KEY queue_id (queue_id),',
482	'KEY subscriber_id (subscriber_id)',
483	];
484	return $this->sqlify(__FUNCTION__, $attributes);
485	}
486
487	private function statisticsOpens() {
488	$attributes = [
489	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
490	'newsletter_id int(11) unsigned NOT NULL,',
491	'subscriber_id int(11) unsigned NOT NULL,',
492	'queue_id int(11) unsigned NOT NULL,',
493	'user_agent_id int(11) unsigned NULL,',
494	'user_agent_type tinyint(1) NOT NULL DEFAULT 0,',
495	'created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,',
496	'PRIMARY KEY (id),',
497	'KEY newsletter_id_subscriber_id_user_agent_type (newsletter_id, subscriber_id, user_agent_type),',
498	'KEY queue_id (queue_id),',
499	'KEY subscriber_id (subscriber_id),',
500	'KEY created_at (created_at),',
501	'KEY subscriber_id_created_at (subscriber_id, created_at)',
502	];
503	return $this->sqlify(__FUNCTION__, $attributes);
504	}
505
506	private function statisticsUnsubscribes() {
507	$attributes = [
508	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
509	'newsletter_id int(11) unsigned NULL,',
510	'subscriber_id int(11) unsigned NOT NULL,',
511	'queue_id int(11) unsigned NULL,',
512	'created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,',
513	"source varchar(255) DEFAULT 'unknown',",
514	'meta varchar(255) NULL,',
515	'PRIMARY KEY (id),',
516	'KEY newsletter_id_subscriber_id (newsletter_id, subscriber_id),',
517	'KEY queue_id (queue_id),',
518	'KEY subscriber_id (subscriber_id)',
519	];
520	return $this->sqlify(__FUNCTION__, $attributes);
521	}
522
523	private function statisticsForms() {
524	$attributes = [
525	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
526	'form_id int(11) unsigned NOT NULL,',
527	'subscriber_id int(11) unsigned NOT NULL,',
528	'created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,',
529	'PRIMARY KEY (id),',
530	'UNIQUE KEY form_subscriber (form_id,subscriber_id)',
531	];
532	return $this->sqlify(__FUNCTION__, $attributes);
533	}
534
535	private function statisticsWoocommercePurchases() {
536	$attributes = [
537	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
538	'newsletter_id int(11) unsigned NOT NULL,',
539	'subscriber_id int(11) unsigned NOT NULL,',
540	'queue_id int(11) unsigned NOT NULL,',
541	'click_id int(11) unsigned NOT NULL,',
542	'order_id bigint(20) unsigned NOT NULL,',
543	'order_currency char(3) NOT NULL,',
544	'order_price_total float NOT NULL COMMENT \'With shipping and taxes in order_currency\',',
545	'created_at timestamp NULL,', // must be NULL, see comment at the top
546	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
547	'PRIMARY KEY (id),',
548	'KEY newsletter_id (newsletter_id),',
549	'KEY queue_id (queue_id),',
550	'KEY subscriber_id (subscriber_id),',
551	'UNIQUE KEY click_id_order_id (click_id, order_id)',
552	];
553	return $this->sqlify(__FUNCTION__, $attributes);
554	}
555
556	private function log() {
557	$attributes = [
558	'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,',
559	'name varchar(255),',
560	'level int(11),',
561	'message longtext,',
562	'created_at timestamp DEFAULT CURRENT_TIMESTAMP,',
563	'PRIMARY KEY (id)',
564	];
565	return $this->sqlify(__FUNCTION__, $attributes);
566	}
567
568	private function userFlags() {
569	$attributes = [
570	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
571	'user_id bigint(20) NOT NULL,',
572	'name varchar(50) NOT NULL,',
573	'value varchar(255),',
574	'created_at timestamp NULL,', // must be NULL, see comment at the top
575	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
576	'PRIMARY KEY (id),',
577	'UNIQUE KEY user_id_name (user_id, name)',
578	];
579	return $this->sqlify(__FUNCTION__, $attributes);
580	}
581
582	private function featureFlags() {
583	$attributes = [
584	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
585	'name varchar(100) NOT NULL,',
586	'value tinyint(1),',
587	'created_at timestamp NULL,', // must be NULL, see comment at the top
588	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
589	'PRIMARY KEY (id),',
590	'UNIQUE KEY name (name)',
591	];
592	return $this->sqlify(__FUNCTION__, $attributes);
593	}
594
595	private function dynamicSegmentFilters() {
596	$attributes = [
597	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
598	'segment_id int(11) unsigned NOT NULL,',
599	'created_at timestamp NULL,',
600	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
601	'filter_data longblob,',
602	'filter_type varchar(255) NULL,',
603	'action varchar(255) NULL,',
604	'PRIMARY KEY (id),',
605	'KEY segment_id (segment_id)',
606	];
607	return $this->sqlify(__FUNCTION__, $attributes);
608	}
609
610	private function userAgents() {
611	$attributes = [
612	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
613	'hash varchar(32) UNIQUE NOT NULL, ',
614	'user_agent text NOT NULL, ',
615	'created_at timestamp NULL,',
616	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
617	'PRIMARY KEY (id)',
618	];
619	return $this->sqlify(__FUNCTION__, $attributes);
620	}
621
622	private function tags(): string {
623	$attributes = [
624	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
625	'name varchar(191) NOT NULL,',
626	'description text NOT NULL DEFAULT \'\',',
627	'created_at timestamp NULL,', // must be NULL, see comment at the top
628	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
629	'PRIMARY KEY (id),',
630	'UNIQUE KEY name (name)',
631	];
632	return $this->sqlify(__FUNCTION__, $attributes);
633	}
634
635	private function subscriberTag(): string {
636	$attributes = [
637	'id int(11) unsigned NOT NULL AUTO_INCREMENT,',
638	'subscriber_id int(11) unsigned NOT NULL,',
639	'tag_id int(11) unsigned NOT NULL,',
640	'created_at timestamp NULL,', // must be NULL, see comment at the top
641	'updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,',
642	'PRIMARY KEY (id),',
643	'UNIQUE KEY subscriber_tag (subscriber_id, tag_id),',
644	'KEY tag_id (tag_id)',
645	];
646	return $this->sqlify(__FUNCTION__, $attributes);
647	}
648
649	private function sqlify($model, $attributes) {
650	$table = $this->prefix . Helpers::camelCaseToUnderscore($model);
651
652	$sql = [];
653	$sql[] = "CREATE TABLE " . $table . " (";
654	$sql = array_merge($sql, $attributes);
655	$sql[] = ") " . $this->charsetCollate . ";";
656
657	return implode("\n", $sql);
658	}
659
660	private function updateNullInUnsubscribeStats() {
661	global $wpdb;
662	// perform once for versions below or equal to 3.47.6
663	if (version_compare($this->getDbVersion('3.47.6'), '3.47.6', '>')) {
664	return false;
665	}
666	$wpdb->query($wpdb->prepare("
667	ALTER TABLE %i
668	CHANGE `newsletter_id` `newsletter_id` int(11) unsigned NULL,
669	CHANGE `queue_id` `queue_id` int(11) unsigned NULL;
670	", "{$this->prefix}statistics_unsubscribes"));
671	return true;
672	}
673
674	/**
675	* This method adds updated_at column to scheduled_task_subscribers for users with old MySQL..
676	* Updated_at was added after created_at column and created_at used to have default CURRENT_TIMESTAMP.
677	* Since MySQL versions below 5.6.5 allow only one column with CURRENT_TIMESTAMP as default per table
678	* and db_delta doesn't remove default values we need to perform this change manually..
679	* @return bool
680	*/
681	private function fixScheduledTasksSubscribersTimestampColumns() {
682	// skip the migration if the DB version is higher than 3.63.0 or is not set (a new install)
683	if (version_compare($this->getDbVersion('3.63.1'), '3.63.0', '>')) {
684	return false;
685	}
686
687	global $wpdb;
688	$scheduledTasksSubscribersTable = "{$this->prefix}scheduled_task_subscribers";
689	// Remove default CURRENT_TIMESTAMP from created_at
690	$wpdb->query($wpdb->prepare("
691	ALTER TABLE %i
692	CHANGE `created_at` `created_at` timestamp NULL;
693	", $scheduledTasksSubscribersTable));
694
695	// Add updated_at column in case it doesn't exist
696
697	$updatedAtColumnExists = $this->columnExists($scheduledTasksSubscribersTable, 'updated_at');
698	if (empty($updatedAtColumnExists)) {
699	$wpdb->query($wpdb->prepare("
700	ALTER TABLE %i
701	ADD `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
702	", $scheduledTasksSubscribersTable));
703	}
704	return true;
705	}
706
707	private function removeDeprecatedStatisticsIndexes(): bool {
708	global $wpdb;
709	// skip the migration if the DB version is higher than 3.67.1 or is not set (a new install)
710	if (version_compare($this->getDbVersion('3.67.1'), '3.67.1', '>')) {
711	return false;
712	}
713
714	$statisticsTables = [
715	esc_sql("{$this->prefix}statistics_clicks"),
716	esc_sql("{$this->prefix}statistics_opens"),
717	];
718	foreach ($statisticsTables as $statisticsTable) {
719	$oldStatisticsIndexExists = $this->indexExists($statisticsTable, 'newsletter_id_subscriber_id');
720	if (!empty($oldStatisticsIndexExists)) {
721	$wpdb->query($wpdb->prepare("
722	ALTER TABLE %i
723	DROP INDEX `newsletter_id_subscriber_id`
724	", $statisticsTable));
725	}
726	}
727
728	return true;
729	}
730
731	private function migrateSerializedFilterDataToNewColumns(): bool {
732	global $wpdb;
733	// skip the migration if the DB version is higher than 3.73.1 or is not set (a new install)
734	if (version_compare($this->getDbVersion('3.73.1'), '3.73.0', '>')) {
735	return false;
736	}
737
738	$dynamicSegmentFiltersTable = esc_sql("{$this->prefix}dynamic_segment_filters");
739	$dynamicSegmentFilters = $wpdb->get_results($wpdb->prepare("
740	SELECT id, filter_data, filter_type, `action`
741	FROM %i
742	", $dynamicSegmentFiltersTable), ARRAY_A);
743	foreach ($dynamicSegmentFilters as $dynamicSegmentFilter) {
744	if ($dynamicSegmentFilter['filter_type'] && $dynamicSegmentFilter['action']) {
745	continue;
746	}
747	/** @var array $filterData */
748	$filterData = unserialize($dynamicSegmentFilter['filter_data']);
749	// bc compatibility fix, the filter with the segmentType userRole didn't have filled action
750	if ($filterData['segmentType'] === DynamicSegmentFilterData::TYPE_USER_ROLE && empty($filterData['action'])) {
751	$filterData['action'] = UserRole::TYPE;
752	}
753	$wpdb->update($dynamicSegmentFiltersTable, [
754	'action' => $filterData['action'] ?? null,
755	'filter_type' => $filterData['segmentType'] ?? null,
756	], ['id' => $dynamicSegmentFilter['id']]);
757	}
758
759	return true;
760	}
761
762	private function migratePurchasedProductDynamicFilters(): bool {
763	global $wpdb;
764	// skip the migration if the DB version is higher than 3.74.3 or is not set (a new install)
765	if (version_compare($this->getDbVersion('3.74.3'), '3.74.2', '>')) {
766	return false;
767	}
768
769	$dynamicSegmentFiltersTable = esc_sql("{$this->prefix}dynamic_segment_filters");
770	$filterType = DynamicSegmentFilterData::TYPE_WOOCOMMERCE;
771	$action = WooCommerceProduct::ACTION_PRODUCT;
772
773	$dynamicSegmentFilters = $wpdb->get_results($wpdb->prepare("
774	SELECT `id`, `filter_data`, `filter_type`, `action`
775	FROM %i
776	WHERE `filter_type` = %s
777	AND `action` = %s
778	", $dynamicSegmentFiltersTable, $filterType, $action), ARRAY_A);
779
780	foreach ($dynamicSegmentFilters as $dynamicSegmentFilter) {
781	/** @var array $filterData */
782	$filterData = unserialize($dynamicSegmentFilter['filter_data']);
783	if (!isset($filterData['product_ids'])) {
784	$filterData['product_ids'] = [];
785	}
786
787	if (isset($filterData['product_id']) && !in_array($filterData['product_id'], $filterData['product_ids'])) {
788	$filterData['product_ids'][] = $filterData['product_id'];
789	unset($filterData['product_id']);
790	}
791
792	if (!isset($filterData['operator'])) {
793	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_ANY;
794	}
795
796	$wpdb->update($dynamicSegmentFiltersTable, [
797	'filter_data' => serialize($filterData),
798	], ['id' => $dynamicSegmentFilter['id']]);
799	}
800
801	return true;
802	}
803
804	private function migratePurchasedInCategoryDynamicFilters(): bool {
805	global $wpdb;
806	// skip the migration if the DB version is higher than 3.75.1 or is not set (a new install)
807	if (version_compare($this->getDbVersion('3.76.0'), '3.75.1', '>')) {
808	return false;
809	}
810
811	$dynamicSegmentFiltersTable = "{$this->prefix}dynamic_segment_filters";
812	$filterType = DynamicSegmentFilterData::TYPE_WOOCOMMERCE;
813	$action = WooCommerceCategory::ACTION_CATEGORY;
814
815	$dynamicSegmentFilters = $wpdb->get_results($wpdb->prepare("
816	SELECT `id`, `filter_data`, `filter_type`, `action`
817	FROM %i
818	WHERE `filter_type` = %s
819	AND `action` = %s
820	", $dynamicSegmentFiltersTable, $filterType, $action), ARRAY_A);
821
822	foreach ($dynamicSegmentFilters as $dynamicSegmentFilter) {
823	/** @var array $filterData */
824	$filterData = unserialize($dynamicSegmentFilter['filter_data']);
825	if (!isset($filterData['category_ids'])) {
826	$filterData['category_ids'] = [];
827	}
828
829	if (isset($filterData['category_id']) && !in_array($filterData['category_id'], $filterData['category_ids'])) {
830	$filterData['category_ids'][] = $filterData['category_id'];
831	unset($filterData['category_id']);
832	}
833
834	if (!isset($filterData['operator'])) {
835	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_ANY;
836	}
837
838	$wpdb->update($dynamicSegmentFiltersTable, [
839	'filter_data' => serialize($filterData),
840	], ['id' => $dynamicSegmentFilter['id']]);
841	}
842
843	return true;
844	}
845
846	private function migrateWooSubscriptionsDynamicFilters(): bool {
847	global $wpdb;
848	// skip the migration if the DB version is higher than 3.75.1 or is not set (a new installation)
849	if (version_compare($this->getDbVersion('3.76.0'), '3.75.1', '>')) {
850	return false;
851	}
852
853	$dynamicSegmentFiltersTable = "{$this->prefix}dynamic_segment_filters";
854	$filterType = DynamicSegmentFilterData::TYPE_WOOCOMMERCE_SUBSCRIPTION;
855	$action = WooCommerceSubscription::ACTION_HAS_ACTIVE;
856
857	$dynamicSegmentFilters = $wpdb->get_results($wpdb->prepare("
858	SELECT `id`, `filter_data`, `filter_type`, `action`
859	FROM %i
860	WHERE `filter_type` = %s
861	AND `action` = %s
862	", $dynamicSegmentFiltersTable, $filterType, $action), ARRAY_A);
863
864	foreach ($dynamicSegmentFilters as $dynamicSegmentFilter) {
865	/** @var array $filterData */
866	$filterData = unserialize($dynamicSegmentFilter['filter_data']);
867	if (!isset($filterData['product_ids'])) {
868	$filterData['product_ids'] = [];
869	}
870
871	if (isset($filterData['product_id']) && !in_array($filterData['product_id'], $filterData['product_ids'])) {
872	$filterData['product_ids'][] = $filterData['product_id'];
873	unset($filterData['product_id']);
874	}
875
876	if (!isset($filterData['operator'])) {
877	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_ANY;
878	}
879
880	$wpdb->update($dynamicSegmentFiltersTable, [
881	'filter_data' => serialize($filterData),
882	], ['id' => $dynamicSegmentFilter['id']]);
883	}
884	return true;
885	}
886
887	private function migrateEmailActionsFilters(): bool {
888	global $wpdb;
889	// skip the migration if the DB version is higher than 3.77.1 or is not set (a new installation)
890	if (version_compare($this->getDbVersion('3.77.2'), '3.77.1', '>')) {
891	return false;
892	}
893
894	$dynamicSegmentFiltersTable = "{$this->prefix}dynamic_segment_filters";
895	$filterType = DynamicSegmentFilterData::TYPE_EMAIL;
896
897	$dynamicSegmentFilters = $wpdb->get_results($wpdb->prepare("
898	SELECT `id`, `filter_data`, `filter_type`, `action`
899	FROM %i
900	WHERE `filter_type` = %s
901	", $dynamicSegmentFiltersTable, $filterType), ARRAY_A);
902
903	foreach ($dynamicSegmentFilters as $dynamicSegmentFilter) {
904	if (!is_array($dynamicSegmentFilter)) {
905	continue;
906	}
907	$serialized = is_string($dynamicSegmentFilter['filter_data']) ? $dynamicSegmentFilter['filter_data'] : '';
908	$filterData = $serialized !== '' ? unserialize($serialized) : false;
909	if (!is_array($filterData)) {
910	continue;
911	}
912	$action = $dynamicSegmentFilter['action'];
913
914	// Not clicked filter is no longer used and was replaced by clicked with none of operator
915	if ($action === EmailAction::ACTION_NOT_CLICKED) {
916	$action = EmailAction::ACTION_CLICKED;
917	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_NONE;
918	}
919
920	// Clicked link filter is refactored to work with multiple link ids
921	if ($action === EmailAction::ACTION_CLICKED) {
922	if (!isset($filterData['link_ids']) \|\| !is_array($filterData['link_ids'])) {
923	$filterData['link_ids'] = [];
924	}
925
926	if (isset($filterData['link_id']) && is_numeric($filterData['link_id']) && !in_array($filterData['link_id'], $filterData['link_ids'])) {
927	$filterData['link_ids'][] = (int)$filterData['link_id'];
928	unset($filterData['link_id']);
929	}
930	}
931
932	// Not opened filter is no longer used and was replaced by opened with none of operand
933	if ($action === EmailAction::ACTION_NOT_OPENED) {
934	$action = EmailAction::ACTION_OPENED;
935	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_NONE;
936	}
937
938	// Opened and Machine opened filters are refactored to work with multiple newsletters
939	if (($action === EmailAction::ACTION_OPENED) \|\| ($action === EmailAction::ACTION_MACHINE_OPENED)) {
940	if (!isset($filterData['newsletters']) \|\| !is_array($filterData['newsletters'])) {
941	$filterData['newsletters'] = [];
942	}
943
944	if (isset($filterData['newsletter_id']) && is_numeric($filterData['newsletter_id']) && !in_array($filterData['newsletter_id'], $filterData['newsletters'])) {
945	$filterData['newsletters'][] = (int)$filterData['newsletter_id'];
946	unset($filterData['newsletter_id']);
947	}
948	}
949
950	// Ensure default operator
951	if (!isset($filterData['operator'])) {
952	$filterData['operator'] = DynamicSegmentFilterData::OPERATOR_ANY;
953	}
954
955	$wpdb->update($dynamicSegmentFiltersTable, [
956	'filter_data' => serialize($filterData),
957	'action' => $action,
958	], ['id' => $dynamicSegmentFilter['id']]);
959	}
960	return true;
961	}
962
963	private function updateMetaFields() {
964	global $wpdb;
965	// perform once for versions below or equal to 3.26.0
966	if (version_compare($this->getDbVersion('3.26.1'), '3.26.0', '>')) {
967	return false;
968	}
969	$scheduledTaskTable = $this->getTableName(ScheduledTaskEntity::class);
970	$sendingQueueTable = $this->getTableName(SendingQueueEntity::class);
971	$tables = [$scheduledTaskTable, $sendingQueueTable];
972	foreach ($tables as $table) {
973	$wpdb->query("UPDATE `" . esc_sql($table) . "` SET meta = NULL WHERE meta = 'null'");
974	}
975	return true;
976	}
977
978	private function updateSentUnsubscribeLinksToInstantUnsubscribeLinks() {
979	if (version_compare($this->getDbVersion('3.46.14'), '3.46.13', '>')) {
980	return;
981	}
982	global $wpdb;
983
984	$wpdb->query($wpdb->prepare(
985	"UPDATE %i SET `url` = %s WHERE `url` = %s",
986	$this->getTableName(NewsletterLinkEntity::class),
987	NewsletterLinkEntity::INSTANT_UNSUBSCRIBE_LINK_SHORT_CODE,
988	NewsletterLinkEntity::UNSUBSCRIBE_LINK_SHORT_CODE
989	));
990	}
991
992	private function pauseTasksForPausedNewsletters() {
993	if (version_compare($this->getDbVersion('3.60.5'), '3.60.4', '>')) {
994	return;
995	}
996
997	$scheduledTaskTable = $this->getTableName(ScheduledTaskEntity::class);
998	$sendingQueueTable = $this->getTableName(SendingQueueEntity::class);
999	$newsletterTable = $this->getTableName(NewsletterEntity::class);
1000
1001	$query = "
1002	UPDATE $scheduledTaskTable as t
1003	JOIN $sendingQueueTable as q ON t.id = q.task_id
1004	JOIN $newsletterTable as n ON n.id = q.newsletter_id
1005	SET t.status = :tStatusPaused
1006	WHERE
1007	t.status = :tStatusScheduled
1008	AND n.status = :nStatusDraft
1009	";
1010	$this->connection->executeStatement(
1011	$query,
1012	[
1013	'tStatusPaused' => ScheduledTaskEntity::STATUS_PAUSED,
1014	'tStatusScheduled' => ScheduledTaskEntity::STATUS_SCHEDULED,
1015	'nStatusDraft' => NewsletterEntity::STATUS_DRAFT,
1016	]
1017	);
1018	}
1019
1020	private function moveGoogleAnalyticsFromPremium() {
1021	global $wpdb;
1022	if (version_compare($this->getDbVersion('3.38.2'), '3.38.1', '>')) {
1023	return;
1024	}
1025
1026	$premiumTableName = $wpdb->prefix . 'mailpoet_premium_newsletter_extra_data';
1027	$premiumTableExists = $this->tableExists($premiumTableName);
1028	if ($premiumTableExists) {
1029	$wpdb->query($wpdb->prepare("
1030	UPDATE
1031	%i as n
1032	JOIN %i as ped ON n.id=ped.newsletter_id
1033	SET n.ga_campaign = ped.ga_campaign
1034	", $this->getTableName(NewsletterEntity::class), $premiumTableName));
1035	}
1036	return true;
1037	}
1038
1039	private function updateLastSubscribedAt() {
1040	global $wpdb;
1041	// perform once for versions below or equal to 3.42.0
1042	if (version_compare($this->getDbVersion('3.42.1'), '3.42.0', '>')) {
1043	return false;
1044	}
1045
1046	$wpdb->query($wpdb->prepare(
1047	"UPDATE %i SET last_subscribed_at = GREATEST(COALESCE(confirmed_at, 0), COALESCE(created_at, 0)) WHERE status != %s AND last_subscribed_at IS NULL",
1048	$this->getTableName(SubscriberEntity::class),
1049	SubscriberEntity::STATUS_UNCONFIRMED
1050	));
1051	return true;
1052	}
1053
1054	private function moveNewsletterTemplatesThumbnailData() {
1055	if (version_compare($this->getDbVersion('3.73.3'), '3.73.2', '>')) {
1056	return;
1057	}
1058	$newsletterTemplatesTable = $this->getTableName(NewsletterTemplateEntity::class);
1059	$this->connection->executeQuery("
1060	UPDATE " . $newsletterTemplatesTable . "
1061	SET thumbnail_data = thumbnail, thumbnail = NULL
1062	WHERE thumbnail LIKE 'data:image%';");
1063	}
1064
1065	private function fixNotificationHistoryRecordsStuckAtSending() {
1066	// perform once for versions below or equal to 3.99.0
1067	if (version_compare($this->getDbVersion('3.99.1'), '3.99.0', '>')) {
1068	return false;
1069	}
1070
1071	$newsletters = $this->getTableName(NewsletterEntity::class);
1072	$queues = $this->getTableName(SendingQueueEntity::class);
1073	$tasks = $this->getTableName(ScheduledTaskEntity::class);
1074
1075	$this->connection->executeStatement("
1076	UPDATE {$newsletters} n
1077	JOIN {$queues} q ON n.id = q.newsletter_id
1078	JOIN {$tasks} t ON q.task_id = t.id
1079	SET n.status = :sentStatus
1080	WHERE n.type = :type
1081	AND n.status = :sendingStatus
1082	AND t.status = :taskStatus
1083	", [
1084	'type' => NewsletterEntity::TYPE_NOTIFICATION_HISTORY,
1085	'sendingStatus' => NewsletterEntity::STATUS_SENDING,
1086	'sentStatus' => NewsletterEntity::STATUS_SENT,
1087	'taskStatus' => ScheduledTaskEntity::STATUS_COMPLETED,
1088	]);
1089
1090	return true;
1091	}
1092	}
1093