blocks / includes / signls / src / CounterStore.php

CounterStore.php in Blocks – Reusable Content, Shortcodes & Site Variables trunk, at includes/signls/src/CounterStore.php

273 lines 13.0 KB

Raw Download Zip

1	<?php
2	/**
3	* Shared aggregate counter storage.
4	*
5	* @package signls-sdk
6	* @license GPL-3.0-or-later
7	*/
8
9	namespace Signls\Sdk\V1;
10
11	defined( 'ABSPATH' ) \|\| exit;
12
13	final class CounterStore {
14
15	const SCHEMA_VERSION = 2;
16
17	private const FAILURE_CLASSES = array(
18	'transport_dns',
19	'transport_tls',
20	'transport_connect',
21	'transport_timeout',
22	'http_4xx',
23	'http_429',
24	'http_5xx',
25	'auth',
26	'configuration',
27	'validation',
28	'remote_rejected',
29	'unknown',
30	);
31
32	public static function ensure_schema(): bool {
33	global $wpdb;
34	if ( ! is_object( $wpdb ) \|\| ! method_exists( $wpdb, 'query' ) \|\| ! method_exists( $wpdb, 'prepare' ) \|\| ! method_exists( $wpdb, 'get_var' ) ) {
35	return false;
36	}
37	$stored_version = get_option( 'signls_sdk_counter_schema_version', 0 );
38	$stored_version = is_scalar( $stored_version ) ? (int) $stored_version : 0;
39	if ( self::SCHEMA_VERSION === $stored_version ) {
40	return true;
41	}
42
43	$lock_name = 'signls_sdk_counter_schema_v2';
44	$locked = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s,5)', $lock_name ) );
45	if ( 1 !== $locked ) {
46	return false;
47	}
48
49	$table = self::table();
50	$daily_table = self::daily_table();
51	$reason_table = self::reason_table();
52	$lifetime_sql = "CREATE TABLE IF NOT EXISTS {$table} (
53	product_hash char(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
54	integration_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
55	operation_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
56	counter_slug varchar(48) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
57	counter_value bigint unsigned NOT NULL DEFAULT 0,
58	first_recorded_at bigint unsigned NOT NULL DEFAULT 0,
59	last_success_at bigint unsigned NOT NULL DEFAULT 0,
60	last_failure_at bigint unsigned NOT NULL DEFAULT 0,
61	updated_at bigint unsigned NOT NULL DEFAULT 0,
62	PRIMARY KEY (product_hash,integration_slug,operation_slug,counter_slug),
63	KEY updated_at (updated_at)
64	) ENGINE=InnoDB";
65	$daily_sql = "CREATE TABLE IF NOT EXISTS {$daily_table} (
66	product_hash char(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
67	integration_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
68	operation_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
69	signal_date date NOT NULL,
70	attempts bigint unsigned NOT NULL DEFAULT 0,
71	successes bigint unsigned NOT NULL DEFAULT 0,
72	failures bigint unsigned NOT NULL DEFAULT 0,
73	PRIMARY KEY (product_hash,integration_slug,operation_slug,signal_date),
74	KEY signal_date (signal_date)
75	) ENGINE=InnoDB";
76	$reason_sql = "CREATE TABLE IF NOT EXISTS {$reason_table} (
77	product_hash char(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
78	integration_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
79	operation_slug varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
80	reason_code varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
81	counter_value bigint unsigned NOT NULL DEFAULT 0,
82	sample varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
83	last_seen_at bigint unsigned NOT NULL DEFAULT 0,
84	PRIMARY KEY (product_hash,integration_slug,operation_slug,reason_code),
85	KEY last_seen_at (last_seen_at)
86	) ENGINE=InnoDB";
87
88	$created = false !== $wpdb->query( $lifetime_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned DDL.
89	$created = $created && false !== $wpdb->query( $daily_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned DDL.
90	$created = $created && false !== $wpdb->query( $reason_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned DDL.
91	foreach ( array( 'first_recorded_at', 'last_success_at', 'last_failure_at' ) as $column ) {
92	if ( $created && ! self::column_exists( $table, $column ) ) {
93	$alter_sql = $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN %i bigint unsigned NOT NULL DEFAULT 0 AFTER counter_value', $table, $column );
94	$alter_sql = self::finalize_identifier_placeholders( $alter_sql, array( $table, $column ) );
95	$created = '' !== $alter_sql && false !== $wpdb->query( $alter_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned identifiers selected from a closed list.
96	}
97	}
98	if ( $created ) {
99	if ( false === get_option( 'signls_sdk_counter_schema_version', false ) ) {
100	add_option( 'signls_sdk_counter_schema_version', self::SCHEMA_VERSION, '', false );
101	} else {
102	update_option( 'signls_sdk_counter_schema_version', self::SCHEMA_VERSION, false );
103	}
104	}
105	$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
106	return $created;
107	}
108
109	public static function record_outcome( string $product, string $integration, string $operation, bool $success, string $failure_class = 'unknown', string $reason_code = '', string $reason_sample = '' ): bool {
110	global $wpdb;
111	if ( ! self::ensure_schema() ) {
112	return false;
113	}
114
115	$integration = Sanitizer::slug( $integration, 32 );
116	$operation = Sanitizer::slug( $operation, 32 );
117	$failure = Sanitizer::slug( $failure_class, 32, 'unknown' );
118	if ( '' === $integration \|\| '' === $operation \|\| ! in_array( $failure, self::FAILURE_CLASSES, true ) ) {
119	return false;
120	}
121
122	$counters = array( 'attempts', $success ? 'successes' : 'failures', $success ? 'outcome_success' : 'failure_' . $failure );
123	$values = array();
124	$args = array();
125	$hash = self::product_hash( $product );
126	$now = time();
127	foreach ( $counters as $counter ) {
128	$values[] = '(%s,%s,%s,%s,1,%d,%d,%d,%d)';
129	array_push( $args, $hash, $integration, $operation, $counter, $now, $success ? $now : 0, $success ? 0 : $now, $now );
130	}
131
132	$lifetime_sql = 'INSERT INTO ' . self::table() . ' (product_hash,integration_slug,operation_slug,counter_slug,counter_value,first_recorded_at,last_success_at,last_failure_at,updated_at) VALUES ' . implode( ',', $values ) . ' ON DUPLICATE KEY UPDATE counter_value=counter_value+1,first_recorded_at=IF(first_recorded_at=0,VALUES(first_recorded_at),LEAST(first_recorded_at,VALUES(first_recorded_at))),last_success_at=GREATEST(last_success_at,VALUES(last_success_at)),last_failure_at=GREATEST(last_failure_at,VALUES(last_failure_at)),updated_at=VALUES(updated_at)';
133	$daily_table = self::daily_table();
134	$daily_sql = $wpdb->prepare(
135	'INSERT INTO %i (product_hash,integration_slug,operation_slug,signal_date,attempts,successes,failures) VALUES (%s,%s,%s,%s,1,%d,%d) ON DUPLICATE KEY UPDATE attempts=attempts+1,successes=successes+VALUES(successes),failures=failures+VALUES(failures)',
136	$daily_table,
137	$hash,
138	$integration,
139	$operation,
140	gmdate( 'Y-m-d', $now ),
141	$success ? 1 : 0,
142	$success ? 0 : 1
143	);
144	$daily_sql = self::finalize_identifier_placeholders( $daily_sql, array( $daily_table ) );
145	$reason_code = Sanitizer::slug( $reason_code, 32 );
146	$reason_sample = Sanitizer::text( $reason_sample, 50 );
147
148	if ( false === $wpdb->query( 'START TRANSACTION' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned atomic counter transaction.
149	return false;
150	}
151	$ok = false !== $wpdb->query( $wpdb->prepare( $lifetime_sql, $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic placeholders only; identifiers are SDK-owned.
152	$ok = $ok && false !== $wpdb->query( $daily_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
153	if ( $ok && ! $success && '' !== $reason_code ) {
154	$reason_table = self::reason_table();
155	$reason_sql = $wpdb->prepare(
156	'INSERT INTO %i (product_hash,integration_slug,operation_slug,reason_code,counter_value,sample,last_seen_at) VALUES (%s,%s,%s,%s,1,%s,%d) ON DUPLICATE KEY UPDATE counter_value=counter_value+1,sample=IF(VALUES(sample)<>\'\',VALUES(sample),sample),last_seen_at=VALUES(last_seen_at)',
157	$reason_table,
158	$hash,
159	$integration,
160	$operation,
161	$reason_code,
162	$reason_sample,
163	$now
164	);
165	$reason_sql = self::finalize_identifier_placeholders( $reason_sql, array( $reason_table ) );
166	$ok = false !== $wpdb->query( $reason_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
167	}
168	if ( ! $ok ) {
169	$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned atomic counter transaction.
170	return false;
171	}
172	if ( false === $wpdb->query( 'COMMIT' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned atomic counter transaction.
173	$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned atomic counter transaction.
174	return false;
175	}
176	return true;
177	}
178
179	public static function read_daily( string $product, int $days ): array {
180	global $wpdb;
181	if ( ! self::ensure_schema() ) {
182	return array();
183	}
184	$days = max( 1, min( 365, $days ) );
185	$from = gmdate( 'Y-m-d', time() - ( $days - 1 ) * DAY_IN_SECONDS );
186	$sql = $wpdb->prepare( 'SELECT integration_slug,operation_slug,signal_date,attempts,successes,failures FROM ' . self::daily_table() . ' WHERE product_hash=%s AND signal_date>=%s ORDER BY signal_date ASC,integration_slug ASC,operation_slug ASC', self::product_hash( $product ), $from ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned table and prepared values.
187	$rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
188	return is_array( $rows ) ? $rows : array();
189	}
190
191	public static function read_reasons( string $product ): array {
192	global $wpdb;
193	if ( ! self::ensure_schema() ) {
194	return array();
195	}
196	$sql = $wpdb->prepare( 'SELECT integration_slug,operation_slug,reason_code,counter_value,sample,last_seen_at FROM ' . self::reason_table() . ' WHERE product_hash=%s ORDER BY counter_value DESC,reason_code ASC', self::product_hash( $product ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Fixed SDK-owned table and prepared value.
197	$rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
198	return is_array( $rows ) ? $rows : array();
199	}
200
201	public static function read_product( string $product ): array {
202	global $wpdb;
203	if ( ! self::ensure_schema() ) {
204	return array();
205	}
206	// phpcs:disable Generic.Formatting.MultipleStatementAlignment -- The prepared-query annotation would otherwise distort local assignment indentation.
207	$hash = self::product_hash( $product );
208	$sql = $wpdb->prepare( 'SELECT integration_slug,operation_slug,counter_slug,counter_value,first_recorded_at,last_success_at,last_failure_at,updated_at FROM ' . self::table() . ' WHERE product_hash=%s', $hash ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The SDK owns the fixed table name; the value remains prepared.
209	$rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
210	// phpcs:enable Generic.Formatting.MultipleStatementAlignment
211	return is_array( $rows ) ? $rows : array();
212	}
213
214	public static function delete_product( string $product ): bool {
215	global $wpdb;
216	$hash = self::product_hash( $product );
217	if ( false === $wpdb->query( 'START TRANSACTION' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned product cleanup transaction.
218	return false;
219	}
220	foreach ( array( self::reason_table(), self::daily_table(), self::table() ) as $table ) {
221	if ( false === $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $table . ' WHERE product_hash=%s', $hash ) ) ) { // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Closed SDK-owned table list and prepared value.
222	$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned product cleanup transaction.
223	return false;
224	}
225	}
226	return false !== $wpdb->query( 'COMMIT' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- SDK-owned product cleanup transaction.
227	}
228
229	public static function table(): string {
230	global $wpdb;
231	return $wpdb->prefix . 'signls_signal_counters';
232	}
233
234	public static function daily_table(): string {
235	global $wpdb;
236	return $wpdb->prefix . 'signls_signal_daily_counters';
237	}
238
239	public static function reason_table(): string {
240	global $wpdb;
241	return $wpdb->prefix . 'signls_signal_reason_counters';
242	}
243
244	private static function product_hash( string $product ): string {
245	return substr( hash( 'sha256', $product ), 0, 12 );
246	}
247
248	private static function finalize_identifier_placeholders( string $sql, array $identifiers ): string {
249	foreach ( $identifiers as $identifier ) {
250	if ( false === strpos( $sql, '%i' ) ) {
251	break;
252	}
253	if ( ! is_string( $identifier ) \|\| 1 !== preg_match( '/^[A-Za-z0-9_]+$/', $identifier ) ) {
254	return '';
255	}
256	$sql = (string) preg_replace( '/%i/', '`' . $identifier . '`', $sql, 1 );
257	}
258	return false === strpos( $sql, '%i' ) ? $sql : '';
259	}
260
261	private static function column_exists( string $table, string $column ): bool {
262	global $wpdb;
263	$count = $wpdb->get_var(
264	$wpdb->prepare(
265	'SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s AND COLUMN_NAME=%s',
266	$table,
267	$column
268	)
269	);
270	return (int) $count > 0;
271	}
272	}
273