double-opt-in / src / Repository / FollowUpRepository.php

FollowUpRepository.php in Double Opt-In for Contact Form 7 – Secure, GDPR-Compliant Email Verification 5.6.0, at src/Repository/FollowUpRepository.php

341 lines 9.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Follow-up repository (wpdb).
4	*
5	* @package Forge12\DoubleOptIn\Repository
6	* @since 5.6.0
7	*/
8
9	declare( strict_types=1 );
10
11	namespace Forge12\DoubleOptIn\Repository;
12
13	use Forge12\DoubleOptIn\FollowUp\FollowUpAction;
14	use Forge12\DoubleOptIn\FollowUp\FollowUpAttempt;
15	use Forge12\DoubleOptIn\FollowUp\FollowUpRecord;
16	use Forge12\DoubleOptIn\FollowUp\FollowUpResult;
17	use Forge12\DoubleOptIn\FollowUp\FollowUpStatus;
18
19	if ( ! defined( 'ABSPATH' ) ) {
20	exit;
21	}
22
23	/**
24	* Class FollowUpRepository
25	*/
26	class FollowUpRepository implements FollowUpRepositoryInterface {
27
28	/** @var \wpdb */
29	private $wpdb;
30
31	/** @var string */
32	private $table;
33
34	/** @var string */
35	private $optInTable;
36
37	public function __construct( \wpdb $wpdb ) {
38	$this->wpdb = $wpdb;
39	$this->table = $wpdb->prefix . FollowUpSchema::TABLE_NAME;
40	$this->optInTable = $wpdb->prefix . 'f12_cf7_doubleoptin';
41	}
42
43	/**
44	* {@inheritdoc}
45	*/
46	public function getTableName(): string {
47	return $this->table;
48	}
49
50	/**
51	* {@inheritdoc}
52	*/
53	public function plan( int $optInId, string $integration, array $actions, array $skipReasons, string $fingerprint, string $now ): int {
54	$inserted = 0;
55
56	foreach ( $actions as $action ) {
57	if ( ! $action instanceof FollowUpAction ) {
58	continue;
59	}
60
61	$skip = $skipReasons[ $action->getId() ] ?? '';
62	$status = $skip !== '' ? FollowUpStatus::SKIPPED : FollowUpStatus::PENDING;
63
64	// wpdb::prepare() renders null as '' — invalid for a datetime
65	// column in strict mode — so NULL is written as a literal.
66	$finished = $skip !== '' ? $this->wpdb->prepare( '%s', $now ) : 'NULL';
67
68	// INSERT IGNORE: the unique key (optin_id, action_id) turns a
69	// second plan for the same opt-in — a parallel click, a retry
70	// of the confirming request — into a no-op.
71	$result = $this->wpdb->query(
72	$this->wpdb->prepare(
73	"INSERT IGNORE INTO {$this->table}
74	(optin_id, integration, action_id, action_kind, action_label, config_fingerprint,
75	status, attempts, error_code, finished_at, created_at, updated_at)
76	VALUES (%d, %s, %s, %s, %s, %s, %s, 0, %s, {$finished}, %s, %s)",
77	$optInId,
78	$integration,
79	$action->getId(),
80	$action->getKind(),
81	$action->getLabel(),
82	$fingerprint,
83	$status,
84	$skip,
85	$now,
86	$now
87	)
88	);
89
90	if ( is_int( $result ) && $result > 0 ) {
91	$inserted += $result;
92	}
93	}
94
95	return $inserted;
96	}
97
98	/**
99	* {@inheritdoc}
100	*/
101	public function findByOptIn( int $optInId ): array {
102	$rows = $this->wpdb->get_results(
103	$this->wpdb->prepare( "SELECT * FROM {$this->table} WHERE optin_id = %d ORDER BY id ASC", $optInId ),
104	ARRAY_A
105	);
106
107	$records = array();
108	foreach ( (array) $rows as $row ) {
109	if ( is_array( $row ) ) {
110	$records[] = FollowUpRecord::fromRow( $row );
111	}
112	}
113	return $records;
114	}
115
116	/**
117	* {@inheritdoc}
118	*/
119	public function claim( int $rowId, array $fromStatuses, string $attemptId, string $trigger, string $now, string $leaseUntil ): bool {
120	$fromStatuses = array_values( array_intersect( $fromStatuses, FollowUpStatus::all() ) );
121	if ( empty( $fromStatuses ) ) {
122	return false;
123	}
124
125	$placeholders = implode( ', ', array_fill( 0, count( $fromStatuses ), '%s' ) );
126	// A manual retry starts a fresh automatic-retry budget: the admin
127	// fixed the cause, so the backoff schedule applies again from the
128	// start. `attempts` keeps counting every attempt for the record.
129	$budget = $trigger === FollowUpAttempt::TRIGGER_MANUAL ? '1' : 'budget_attempts + 1';
130
131	$params = array_merge(
132	array( FollowUpStatus::RUNNING, $attemptId, $trigger, $now, $leaseUntil, $now, $rowId ),
133	$fromStatuses
134	);
135
136	$affected = $this->wpdb->query(
137	$this->wpdb->prepare(
138	"UPDATE {$this->table}
139	SET status = %s, attempt_id = %s, attempt_trigger = %s, attempts = attempts + 1,
140	budget_attempts = {$budget},
141	started_at = %s, finished_at = NULL, lease_until = %s, next_attempt_at = NULL,
142	error_code = '', retryable = 0, http_status = 0, response_kind = '', evidence = '',
143	updated_at = %s
144	WHERE id = %d AND status IN ({$placeholders})",
145	$params
146	)
147	);
148
149	return $affected === 1;
150	}
151
152	/**
153	* {@inheritdoc}
154	*/
155	public function complete( int $rowId, string $attemptId, FollowUpResult $result, string $now, string $nextAttemptAt ): bool {
156	$next = $nextAttemptAt !== '' ? $this->wpdb->prepare( '%s', $nextAttemptAt ) : 'NULL';
157
158	$affected = $this->wpdb->query(
159	$this->wpdb->prepare(
160	"UPDATE {$this->table}
161	SET status = %s, error_code = %s, retryable = %d, http_status = %d, response_kind = %s,
162	entry_ref = CASE WHEN %s = '' THEN entry_ref ELSE %s END,
163	evidence = %s, finished_at = %s, next_attempt_at = {$next}, lease_until = NULL,
164	ticket_hash = NULL, ticket_expires = NULL, updated_at = %s
165	WHERE id = %d AND attempt_id = %s AND status = %s",
166	$result->getStatus(),
167	$result->getErrorCode(),
168	$result->isRetryable() ? 1 : 0,
169	$result->getHttpStatus(),
170	$result->getResponseKind(),
171	$result->getEntryRef(),
172	$result->getEntryRef(),
173	$result->getEvidence(),
174	$now,
175	$now,
176	$rowId,
177	$attemptId,
178	FollowUpStatus::RUNNING
179	)
180	);
181
182	return $affected === 1;
183	}
184
185	/**
186	* {@inheritdoc}
187	*/
188	public function setTicket( int $optInId, string $attemptId, string $ticketHash, string $expiresAt ): void {
189	$this->wpdb->query(
190	$this->wpdb->prepare(
191	"UPDATE {$this->table} SET ticket_hash = %s, ticket_expires = %s
192	WHERE optin_id = %d AND attempt_id = %s AND status = %s",
193	$ticketHash,
194	$expiresAt,
195	$optInId,
196	$attemptId,
197	FollowUpStatus::RUNNING
198	)
199	);
200	}
201
202	/**
203	* {@inheritdoc}
204	*/
205	public function consumeTicket( int $optInId, string $ticketHash, string $now ): ?array {
206	if ( $ticketHash === '' ) {
207	return null;
208	}
209
210	$actionIds = $this->wpdb->get_col(
211	$this->wpdb->prepare(
212	"SELECT action_id FROM {$this->table}
213	WHERE optin_id = %d AND ticket_hash = %s AND ticket_expires >= %s AND status = %s",
214	$optInId,
215	$ticketHash,
216	$now,
217	FollowUpStatus::RUNNING
218	)
219	);
220
221	if ( empty( $actionIds ) ) {
222	return null;
223	}
224
225	// The conditional UPDATE is the arbiter: of two requests presenting
226	// the same ticket, only one sees affected rows > 0.
227	$affected = $this->wpdb->query(
228	$this->wpdb->prepare(
229	"UPDATE {$this->table} SET ticket_hash = NULL, ticket_expires = NULL
230	WHERE optin_id = %d AND ticket_hash = %s AND ticket_expires >= %s AND status = %s",
231	$optInId,
232	$ticketHash,
233	$now,
234	FollowUpStatus::RUNNING
235	)
236	);
237
238	if ( ! is_int( $affected ) \|\| $affected < 1 ) {
239	return null;
240	}
241
242	return array_map( 'strval', $actionIds );
243	}
244
245	/**
246	* {@inheritdoc}
247	*/
248	public function expireLeases( string $now ): int {
249	$affected = $this->wpdb->query(
250	$this->wpdb->prepare(
251	"UPDATE {$this->table}
252	SET status = %s, error_code = %s, retryable = 0, finished_at = %s, lease_until = NULL,
253	ticket_hash = NULL, ticket_expires = NULL, updated_at = %s
254	WHERE status = %s AND lease_until IS NOT NULL AND lease_until < %s",
255	FollowUpStatus::UNKNOWN,
256	'lease_expired',
257	$now,
258	$now,
259	FollowUpStatus::RUNNING,
260	$now
261	)
262	);
263
264	return is_int( $affected ) ? $affected : 0;
265	}
266
267	/**
268	* {@inheritdoc}
269	*/
270	public function findDueOptInIds( string $now, string $pendingBefore, int $limit ): array {
271	$ids = $this->wpdb->get_col(
272	$this->wpdb->prepare(
273	"SELECT DISTINCT f.optin_id FROM {$this->table} f
274	INNER JOIN {$this->optInTable} o ON o.id = f.optin_id
275	WHERE o.doubleoptin = 1
276	AND ( ( f.status = %s AND f.next_attempt_at IS NOT NULL AND f.next_attempt_at <= %s )
277	OR ( f.status = %s AND f.created_at < %s ) )
278	ORDER BY f.optin_id ASC
279	LIMIT %d",
280	FollowUpStatus::FAILED_RETRYABLE,
281	$now,
282	FollowUpStatus::PENDING,
283	$pendingBefore,
284	max( 1, $limit )
285	)
286	);
287
288	return array_map( 'intval', (array) $ids );
289	}
290
291	/**
292	* {@inheritdoc}
293	*/
294	public function skipOpen( int $optInId, string $reason, string $now ): int {
295	$affected = $this->wpdb->query(
296	$this->wpdb->prepare(
297	"UPDATE {$this->table}
298	SET status = %s, error_code = %s, retryable = 0, next_attempt_at = NULL, finished_at = %s, updated_at = %s
299	WHERE optin_id = %d AND status IN (%s, %s)",
300	FollowUpStatus::SKIPPED,
301	$reason,
302	$now,
303	$now,
304	$optInId,
305	FollowUpStatus::PENDING,
306	FollowUpStatus::FAILED_RETRYABLE
307	)
308	);
309
310	return is_int( $affected ) ? $affected : 0;
311	}
312
313	/**
314	* {@inheritdoc}
315	*/
316	public function deleteOrphans( int $limit ): int {
317	$affected = $this->wpdb->query(
318	$this->wpdb->prepare(
319	// The derived table lets MySQL delete from a table it also
320	// reads in the sub-query, and carries the LIMIT that a
321	// multi-table DELETE does not accept.
322	"DELETE FROM {$this->table} WHERE id IN (
323	SELECT id FROM ( SELECT f.id FROM {$this->table} f
324	LEFT JOIN {$this->optInTable} o ON o.id = f.optin_id
325	WHERE o.id IS NULL LIMIT %d ) AS orphan_ids )",
326	max( 1, $limit )
327	)
328	);
329
330	return is_int( $affected ) ? $affected : 0;
331	}
332
333	/**
334	* {@inheritdoc}
335	*/
336	public function deleteByOptIn( int $optInId ): int {
337	$affected = $this->wpdb->delete( $this->table, array( 'optin_id' => $optInId ), array( '%d' ) );
338	return is_int( $affected ) ? $affected : 0;
339	}
340	}
341