← All changes
|
jetpack_vendor/automattic/jetpack-sync/src/sync-queue/class-queue-storage-table.php
+674
-0
16.2-beta
→
16.3
View file →
| @@ -1,0 +1,674 @@ | ||
| 1 | +<?php | |
| 2 | +/** | |
| 3 | + * The class responsible for storing Queue events in a custom Sync events table. | |
| 4 | + * | |
| 5 | + * Used by class Queue. | |
| 6 | + * | |
| 7 | + * @see \Automattic\Jetpack\Sync\Queue | |
| 8 | + * | |
| 9 | + * @package automattic/jetpack-sync | |
| 10 | + */ | |
| 11 | + | |
| 12 | +namespace Automattic\Jetpack\Sync\Queue; | |
| 13 | + | |
| 14 | +use Automattic\Jetpack\Sync\Sender; | |
| 15 | +use Automattic\Jetpack\Sync\Settings; | |
| 16 | + | |
| 17 | +/** | |
| 18 | + * Custom Sync events table storage backend for the Queue. | |
| 19 | + */ | |
| 20 | +class Queue_Storage_Table { | |
| 21 | + | |
| 22 | + /** | |
| 23 | + * The name of the transient to use to disable custom queue table in we get a table doesn't exist error. | |
| 24 | + */ | |
| 25 | + const CUSTOM_QUEUE_TABLE_DISABLE_WPDB_ERROR_NOT_EXIST_FLAG = 'jetpack_sync_custom_queue_table_disable_wpdb_error_not_exist'; | |
| 26 | + /** | |
| 27 | + * The custom Sync events table name, without a prefix. | |
| 28 | + * A prefix will be added when the class is instantiated, | |
| 29 | + * as we fetch the prefix from `$wpdb` as is configured in | |
| 30 | + * the WordPress config file. | |
| 31 | + * | |
| 32 | + * @var string | |
| 33 | + */ | |
| 34 | + public $table_name_no_prefix = 'jetpack_sync_queue'; | |
| 35 | + | |
| 36 | + /** | |
| 37 | + * The table name with the DB prefix. | |
| 38 | + * | |
| 39 | + * @var string | |
| 40 | + */ | |
| 41 | + public $table_name = ''; | |
| 42 | + | |
| 43 | + /** | |
| 44 | + * What queue is this instance responsible for. | |
| 45 | + * | |
| 46 | + * @var string | |
| 47 | + */ | |
| 48 | + public $queue_id = ''; | |
| 49 | + | |
| 50 | + /** | |
| 51 | + * Class constructor. | |
| 52 | + * | |
| 53 | + * @param string $queue_id The queue name this instance will be responsible for. | |
| 54 | + * | |
| 55 | + * @throws \Exception If queue name was not provided. | |
| 56 | + */ | |
| 57 | + public function __construct( $queue_id ) { | |
| 58 | + global $wpdb; | |
| 59 | + | |
| 60 | + if ( empty( $queue_id ) ) { | |
| 61 | + // TODO what should we return here or throw an exception? | |
| 62 | + throw new \Exception( 'Invalid queue_id provided' ); | |
| 63 | + } | |
| 64 | + | |
| 65 | + // TODO validate the value maybe? | |
| 66 | + $this->queue_id = $queue_id; | |
| 67 | + | |
| 68 | + // Initialize the `table_name` property with the correct prefix for easier usage in the class. | |
| 69 | + $this->table_name = $wpdb->prefix . $this->table_name_no_prefix; | |
| 70 | + } | |
| 71 | + | |
| 72 | + /** | |
| 73 | + * Creates the new table and updates the options to work with | |
| 74 | + * the new table if it was created successfully. | |
| 75 | + * | |
| 76 | + * @return void | |
| 77 | + */ | |
| 78 | + protected function create_table() { | |
| 79 | + global $wpdb; | |
| 80 | + | |
| 81 | + require_once ABSPATH . '/wp-admin/includes/upgrade.php'; | |
| 82 | + | |
| 83 | + $charset_collate = $wpdb->get_charset_collate(); | |
| 84 | + | |
| 85 | + $table_definition = "CREATE TABLE {$this->table_name} ( | |
| 86 | + `ID` bigint(20) NOT NULL AUTO_INCREMENT, | |
| 87 | + `queue_id` varchar(50) NOT NULL, | |
| 88 | + `event_id` varchar(100) NOT NULL, | |
| 89 | + `event_payload` longtext NOT NULL, | |
| 90 | + `timestamp` timestamp NOT NULL DEFAULT current_timestamp(), | |
| 91 | + PRIMARY KEY (`ID`), | |
| 92 | + KEY `event_id` (`event_id`), | |
| 93 | + KEY `queue_id` (`queue_id`), | |
| 94 | + KEY `queue_id_event_id` (queue_id, event_id), | |
| 95 | + KEY `timestamp` (`timestamp`) | |
| 96 | + ) $charset_collate;"; | |
| 97 | + | |
| 98 | + /** | |
| 99 | + * The function dbDelta will only return the differences. If the table exists, the result will be empty, | |
| 100 | + * so let's run a check afterward to see if the table exists and is healthy. | |
| 101 | + */ | |
| 102 | + \dbDelta( $table_definition ); | |
| 103 | + } | |
| 104 | + | |
| 105 | + /** | |
| 106 | + * Check if the Custom table actually exists. | |
| 107 | + * | |
| 108 | + * @return bool | |
| 109 | + */ | |
| 110 | + protected function custom_table_exists() { | |
| 111 | + global $wpdb; | |
| 112 | + | |
| 113 | + // Check if the table exists | |
| 114 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 115 | + $result = $wpdb->get_row( | |
| 116 | + $wpdb->prepare( 'SHOW TABLES LIKE %s', $this->table_name ), | |
| 117 | + ARRAY_N | |
| 118 | + ); | |
| 119 | + | |
| 120 | + if ( empty( $result ) || count( $result ) !== 1 || $result[0] !== $this->table_name ) { | |
| 121 | + return false; | |
| 122 | + } | |
| 123 | + | |
| 124 | + return true; | |
| 125 | + } | |
| 126 | + /** | |
| 127 | + * Check if the table is healthy, and we can read and write from/to it. | |
| 128 | + * | |
| 129 | + * @return true|\WP_Error If the custom table is available, and we can read and write from/to it. | |
| 130 | + */ | |
| 131 | + protected function is_custom_table_healthy() { | |
| 132 | + global $wpdb; | |
| 133 | + | |
| 134 | + if ( ! $this->custom_table_exists() ) { | |
| 135 | + return new \WP_Error( 'custom_table_not_exist', 'Jetpack Sync Custom table: Table does not exist' ); | |
| 136 | + } | |
| 137 | + | |
| 138 | + // Try to read from the table | |
| 139 | + | |
| 140 | + // Ignore the interpolated table name | |
| 141 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 142 | + $query = $wpdb->query( "SELECT count(`ID`) FROM {$this->table_name}" ); | |
| 143 | + | |
| 144 | + if ( $query === false ) { | |
| 145 | + // The query failed to select anything from the table, so there must be an issue reading from it. | |
| 146 | + return new \WP_Error( 'custom_table_unable_to_read', 'Jetpack Sync Custom table: Unable to read from table' ); | |
| 147 | + } | |
| 148 | + | |
| 149 | + if ( $wpdb->last_error ) { | |
| 150 | + // There was an error reading, that's not necessarily failing the query. | |
| 151 | + // TODO check if we need this error check. | |
| 152 | + // TODO add more information about the erorr in the return value. | |
| 153 | + return new \WP_Error( 'custom_table_unable_to_read_sql_error', 'Jetpack Sync Custom table: Unable to read from table - SQL error' ); | |
| 154 | + } | |
| 155 | + | |
| 156 | + // Check if we can write in the table | |
| 157 | + if ( ! $this->insert_item( 'test', 'test' ) ) { | |
| 158 | + return new \WP_Error( 'custom_table_unable_to_writeread', 'Jetpack Sync Custom table: Unable to write into table' ); | |
| 159 | + } | |
| 160 | + | |
| 161 | + // See if we can read the item back | |
| 162 | + $items = $this->fetch_items_by_ids( array( 'test' ) ); | |
| 163 | + if ( empty( $items ) || ! is_object( $items[0] ) || $items[0]->value !== 'test' ) { | |
| 164 | + return new \WP_Error( 'custom_table_unable_to_writeread', 'Jetpack Sync Custom table: Unable to read item after writing' ); | |
| 165 | + } | |
| 166 | + | |
| 167 | + // Try to insert an item, read it back and then delete it. | |
| 168 | + $this->delete_items_by_ids( array( 'test' ) ); | |
| 169 | + | |
| 170 | + // Try to fetch the item back. It should not exist. | |
| 171 | + $items = $this->fetch_items_by_ids( array( 'test' ) ); | |
| 172 | + if ( ! empty( $items ) ) { | |
| 173 | + return new \WP_Error( 'custom_table_unable_to_writeread', 'Jetpack Sync Custom table: Unable to delete from table' ); | |
| 174 | + } | |
| 175 | + | |
| 176 | + return true; | |
| 177 | + } | |
| 178 | + | |
| 179 | + /** | |
| 180 | + * Drop the custom table as part of cleanup. | |
| 181 | + * | |
| 182 | + * @return bool If the table is cleared. | |
| 183 | + */ | |
| 184 | + public function drop_table() { | |
| 185 | + global $wpdb; | |
| 186 | + | |
| 187 | + if ( $this->custom_table_exists() ) { | |
| 188 | + // Ignoring the linting warning, as there's still no placeholder replacement for DB field name. | |
| 189 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange | |
| 190 | + return (bool) $wpdb->query( "DROP TABLE {$this->table_name}" ); | |
| 191 | + } | |
| 192 | + } | |
| 193 | + | |
| 194 | + /** | |
| 195 | + * Queue API implementation | |
| 196 | + */ | |
| 197 | + | |
| 198 | + /** | |
| 199 | + * Insert an item in the queue. | |
| 200 | + * | |
| 201 | + * @param string $item_id The item ID. | |
| 202 | + * @param string $item Serialized item data. | |
| 203 | + * | |
| 204 | + * @return bool If the item was added. | |
| 205 | + */ | |
| 206 | + public function insert_item( $item_id, $item ) { | |
| 207 | + global $wpdb; | |
| 208 | + | |
| 209 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 210 | + $rows_added = $wpdb->query( | |
| 211 | + $wpdb->prepare( | |
| 212 | + /** | |
| 213 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 214 | + * in this case this is `$this->table_name` | |
| 215 | + */ | |
| 216 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 217 | + "INSERT INTO {$this->table_name} (queue_id, event_id, event_payload) VALUES (%s, %s,%s)", | |
| 218 | + $this->queue_id, | |
| 219 | + $item_id, | |
| 220 | + $item | |
| 221 | + ) | |
| 222 | + ); | |
| 223 | + | |
| 224 | + return ( 0 !== $rows_added ); | |
| 225 | + } | |
| 226 | + | |
| 227 | + /** | |
| 228 | + * Fetch items from the queue. | |
| 229 | + * | |
| 230 | + * @param int|null $item_count How many items to fetch from the queue. | |
| 231 | + * Null for no limit. | |
| 232 | + * @param string $order Sort direction for the items. Accepts 'ASC' or 'DESC'. | |
| 233 | + * Any other value will be treated as 'ASC'. | |
| 234 | + * | |
| 235 | + * @return array|object|null Array of result objects on success, or null on failure. | |
| 236 | + */ | |
| 237 | + public function fetch_items( $item_count, $order = 'ASC' ) { | |
| 238 | + global $wpdb; | |
| 239 | + | |
| 240 | + $order = ( 'DESC' === $order ) ? 'DESC' : 'ASC'; | |
| 241 | + $sql_order = "ORDER BY event_id {$order}"; | |
| 242 | + | |
| 243 | + $sql = " | |
| 244 | + SELECT | |
| 245 | + event_id AS id, | |
| 246 | + event_payload AS value | |
| 247 | + FROM {$this->table_name} | |
| 248 | + WHERE queue_id LIKE %s | |
| 249 | + {$sql_order} | |
| 250 | + "; | |
| 251 | + | |
| 252 | + $params = array( $this->queue_id ); | |
| 253 | + | |
| 254 | + if ( $item_count ) { | |
| 255 | + $sql .= ' LIMIT %d'; | |
| 256 | + $params[] = $item_count; | |
| 257 | + } | |
| 258 | + | |
| 259 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 260 | + $items = $wpdb->get_results( | |
| 261 | + $wpdb->prepare( $sql, $params ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared | |
| 262 | + ); | |
| 263 | + | |
| 264 | + return $items; | |
| 265 | + } | |
| 266 | + | |
| 267 | + /** | |
| 268 | + * Fetches items with specific IDs from the Queue. | |
| 269 | + * | |
| 270 | + * @param array $items_ids Items IDs to fetch from the queue. | |
| 271 | + * | |
| 272 | + * @return object[]|null | |
| 273 | + */ | |
| 274 | + public function fetch_items_by_ids( $items_ids ) { | |
| 275 | + global $wpdb; | |
| 276 | + | |
| 277 | + // return early if $items_ids is empty or not an array. | |
| 278 | + if ( empty( $items_ids ) || ! is_array( $items_ids ) ) { | |
| 279 | + return array(); | |
| 280 | + } | |
| 281 | + | |
| 282 | + $ids_placeholders = implode( ', ', array_fill( 0, count( $items_ids ), '%s' ) ); | |
| 283 | + $query_with_placeholders = "SELECT event_id AS id, event_payload AS value | |
| 284 | + FROM {$this->table_name} | |
| 285 | + WHERE queue_id = %s AND event_id IN ( $ids_placeholders )"; | |
| 286 | + | |
| 287 | + $replacement_values = array_merge( array( $this->queue_id ), $items_ids ); | |
| 288 | + | |
| 289 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 290 | + $items = $wpdb->get_results( | |
| 291 | + $wpdb->prepare( | |
| 292 | + $query_with_placeholders, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared | |
| 293 | + $replacement_values | |
| 294 | + ), | |
| 295 | + OBJECT | |
| 296 | + ); | |
| 297 | + | |
| 298 | + return $items; | |
| 299 | + } | |
| 300 | + | |
| 301 | + /** | |
| 302 | + * Check how many items are in the queue. | |
| 303 | + * | |
| 304 | + * @return int | |
| 305 | + */ | |
| 306 | + public function get_item_count() { | |
| 307 | + global $wpdb; | |
| 308 | + | |
| 309 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 310 | + $items_count = (int) $wpdb->get_var( | |
| 311 | + $wpdb->prepare( | |
| 312 | + /** | |
| 313 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 314 | + * in this case this is `$this->table_name` | |
| 315 | + */ | |
| 316 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 317 | + "SELECT COUNT(*) FROM {$this->table_name} WHERE queue_id = %s", | |
| 318 | + $this->queue_id | |
| 319 | + ) | |
| 320 | + ); | |
| 321 | + // If the table does not exist, disable the custom queue table and send an error. | |
| 322 | + if ( ! empty( $wpdb->last_error ) | |
| 323 | + && str_contains( $wpdb->last_error, $this->table_name_no_prefix . "' doesn't exist" ) | |
| 324 | + && ! get_transient( self::CUSTOM_QUEUE_TABLE_DISABLE_WPDB_ERROR_NOT_EXIST_FLAG ) | |
| 325 | + ) { | |
| 326 | + set_transient( self::CUSTOM_QUEUE_TABLE_DISABLE_WPDB_ERROR_NOT_EXIST_FLAG, true, 6 * HOUR_IN_SECONDS ); | |
| 327 | + Settings::update_settings( array( 'custom_queue_table_enabled' => 0 ) ); | |
| 328 | + $data = array( | |
| 329 | + 'timestamp' => microtime( true ), | |
| 330 | + 'error' => $wpdb->last_error, | |
| 331 | + ); | |
| 332 | + $sender = Sender::get_instance(); | |
| 333 | + $sender->send_action( 'jetpack_sync_storage_error_custom_table_not_exist', $data ); | |
| 334 | + } | |
| 335 | + | |
| 336 | + return $items_count; | |
| 337 | + } | |
| 338 | + | |
| 339 | + /** | |
| 340 | + * Clear out the queue. | |
| 341 | + * | |
| 342 | + * @return bool|int|\mysqli_result|resource|null | |
| 343 | + */ | |
| 344 | + public function clear_queue() { | |
| 345 | + global $wpdb; | |
| 346 | + | |
| 347 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 348 | + return $wpdb->query( | |
| 349 | + $wpdb->prepare( | |
| 350 | + /** | |
| 351 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 352 | + * in this case this is `$this->table_name` | |
| 353 | + */ | |
| 354 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 355 | + "DELETE FROM {$this->table_name} WHERE queue_id = %s", | |
| 356 | + $this->queue_id | |
| 357 | + ) | |
| 358 | + ); | |
| 359 | + } | |
| 360 | + | |
| 361 | + /** | |
| 362 | + * Return the lag amount for the queue. | |
| 363 | + * | |
| 364 | + * @param float|int|null $now A timestamp to use as starting point when calculating the lag. | |
| 365 | + * | |
| 366 | + * @return float|int The lag amount. | |
| 367 | + */ | |
| 368 | + public function get_lag( $now = null ) { | |
| 369 | + global $wpdb; | |
| 370 | + | |
| 371 | + // TODO replace with peek and a flag to fetch only the name. | |
| 372 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 373 | + $first_item_name = $wpdb->get_var( | |
| 374 | + $wpdb->prepare( | |
| 375 | + /** | |
| 376 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 377 | + * in this case this is `$this->table_name` | |
| 378 | + */ | |
| 379 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 380 | + "SELECT event_id FROM {$this->table_name} WHERE queue_id = %s ORDER BY event_id ASC LIMIT 1", | |
| 381 | + $this->queue_id | |
| 382 | + ) | |
| 383 | + ); | |
| 384 | + | |
| 385 | + if ( ! $first_item_name ) { | |
| 386 | + return 0; | |
| 387 | + } | |
| 388 | + | |
| 389 | + if ( null === $now ) { | |
| 390 | + $now = microtime( true ); | |
| 391 | + } | |
| 392 | + | |
| 393 | + // Break apart the item name to get the timestamp. | |
| 394 | + $matches = null; | |
| 395 | + if ( preg_match( '/^jpsq_' . $this->queue_id . '-(\d+\.\d+)-/', $first_item_name, $matches ) ) { | |
| 396 | + return $now - (float) $matches[1]; | |
| 397 | + } else { | |
| 398 | + return 0; | |
| 399 | + } | |
| 400 | + } | |
| 401 | + | |
| 402 | + /** | |
| 403 | + * Add multiple items to the queue at once. | |
| 404 | + * | |
| 405 | + * @param array $items Array of items to add. | |
| 406 | + * @param string $id_prefix Prefix to use for all the items. | |
| 407 | + * | |
| 408 | + * @return bool|int|\mysqli_result|resource|null | |
| 409 | + */ | |
| 410 | + public function add_all( $items, $id_prefix ) { | |
| 411 | + global $wpdb; | |
| 412 | + | |
| 413 | + $query = "INSERT INTO {$this->table_name} (queue_id, event_id, event_payload ) VALUES "; | |
| 414 | + | |
| 415 | + $rows = array(); | |
| 416 | + $count_items = count( $items ); | |
| 417 | + for ( $i = 0; $i < $count_items; ++$i ) { | |
| 418 | + // skip empty items. | |
| 419 | + if ( empty( $items[ $i ] ) ) { | |
| 420 | + continue; | |
| 421 | + } | |
| 422 | + try { | |
| 423 | + $event_id = esc_sql( $id_prefix . '-' . $i ); | |
| 424 | + $event_payload = esc_sql( serialize( $items[ $i ] ) ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.serialize_serialize | |
| 425 | + $rows[] = "('{$this->queue_id}', '$event_id','$event_payload')"; | |
| 426 | + } catch ( \Exception $e ) { | |
| 427 | + // Item cannot be serialized so skip. | |
| 428 | + continue; | |
| 429 | + } | |
| 430 | + } | |
| 431 | + | |
| 432 | + $rows_added = $wpdb->query( $query . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 433 | + | |
| 434 | + return $rows_added; | |
| 435 | + } | |
| 436 | + | |
| 437 | + /** | |
| 438 | + * Return $max_count items from the queue, including their value string length. | |
| 439 | + * | |
| 440 | + * @param int $max_count How many items to fetch from the queue. | |
| 441 | + * | |
| 442 | + * @return object[]|null | |
| 443 | + */ | |
| 444 | + public function get_items_ids_with_size( $max_count ) { | |
| 445 | + global $wpdb; | |
| 446 | + | |
| 447 | + // TODO optimize the fetch to happen by queue name not by the IDs as it can be issue cross-queues. | |
| 448 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 449 | + return $wpdb->get_results( | |
| 450 | + $wpdb->prepare( | |
| 451 | + /** | |
| 452 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 453 | + * in this case this is `$this->table_name` | |
| 454 | + */ | |
| 455 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 456 | + "SELECT event_id AS id, LENGTH( event_payload ) AS value_size FROM {$this->table_name} WHERE queue_id = %s ORDER BY event_id ASC LIMIT %d", | |
| 457 | + $this->queue_id, | |
| 458 | + $max_count | |
| 459 | + ), | |
| 460 | + OBJECT | |
| 461 | + ); | |
| 462 | + } | |
| 463 | + | |
| 464 | + /** | |
| 465 | + * Delete items with specific IDs from the queue. | |
| 466 | + * | |
| 467 | + * @param array $ids IDs of the items to remove from the queue. | |
| 468 | + * | |
| 469 | + * @return bool|int|\mysqli_result|resource|null | |
| 470 | + */ | |
| 471 | + public function delete_items_by_ids( $ids ) { | |
| 472 | + global $wpdb; | |
| 473 | + $ids_placeholders = implode( ', ', array_fill( 0, count( $ids ), '%s' ) ); | |
| 474 | + | |
| 475 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 476 | + return $wpdb->query( | |
| 477 | + $wpdb->prepare( | |
| 478 | + /** | |
| 479 | + * Ignoring the linting warning, as there's still no placeholder replacement for DB field name, | |
| 480 | + * in this case this is `$this->table_name` | |
| 481 | + */ | |
| 482 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 483 | + "DELETE FROM {$this->table_name} WHERE queue_id = %s AND event_id IN ( $ids_placeholders )", | |
| 484 | + array_merge( array( $this->queue_id ), $ids ) | |
| 485 | + ) | |
| 486 | + ); | |
| 487 | + } | |
| 488 | + | |
| 489 | + /** | |
| 490 | + * Table initialization | |
| 491 | + */ | |
| 492 | + public static function initialize_custom_sync_table() { | |
| 493 | + /** | |
| 494 | + * Initialize an instance of the class with a test name, so we can use table prefix and then test if the table is healthy. | |
| 495 | + */ | |
| 496 | + $custom_table_instance = new Queue_Storage_Table( 'test_queue' ); | |
| 497 | + | |
| 498 | + // Check if the table exists | |
| 499 | + if ( ! $custom_table_instance->custom_table_exists() ) { | |
| 500 | + $custom_table_instance->create_table(); | |
| 501 | + } | |
| 502 | + | |
| 503 | + return $custom_table_instance->is_custom_table_healthy(); | |
| 504 | + } | |
| 505 | + | |
| 506 | + /** | |
| 507 | + * Migrates the existing Sync events from the options table to the Custom table | |
| 508 | + * | |
| 509 | + * @return bool | |
| 510 | + */ | |
| 511 | + public static function migrate_from_options_table_to_custom_table() { | |
| 512 | + global $wpdb; | |
| 513 | + | |
| 514 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 515 | + $count_result = $wpdb->get_row( | |
| 516 | + " | |
| 517 | + SELECT | |
| 518 | + COUNT(*) as item_count | |
| 519 | + FROM | |
| 520 | + {$wpdb->options} | |
| 521 | + WHERE | |
| 522 | + option_name LIKE 'jpsq_%' | |
| 523 | + " | |
| 524 | + ); | |
| 525 | + | |
| 526 | + $item_count = $count_result->item_count; | |
| 527 | + | |
| 528 | + $limit = 100; | |
| 529 | + $offset = 0; | |
| 530 | + | |
| 531 | + do { | |
| 532 | + // get all the records from the options table | |
| 533 | + $query = " | |
| 534 | + SELECT | |
| 535 | + option_name as event_id, | |
| 536 | + option_value as event_payload | |
| 537 | + FROM | |
| 538 | + {$wpdb->options} | |
| 539 | + WHERE | |
| 540 | + option_name LIKE 'jpsq_%' | |
| 541 | + ORDER BY | |
| 542 | + option_name ASC | |
| 543 | + LIMIT $offset, $limit | |
| 544 | + "; | |
| 545 | + | |
| 546 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared | |
| 547 | + $rows = $wpdb->get_results( $query ); | |
| 548 | + | |
| 549 | + $insert_rows = array(); | |
| 550 | + | |
| 551 | + foreach ( $rows as $event ) { | |
| 552 | + $event_id = $event->event_id; | |
| 553 | + | |
| 554 | + // Parse the event | |
| 555 | + if ( | |
| 556 | + preg_match( | |
| 557 | + '!jpsq_(?P<queue_id>[^-]+)-(?P<timestamp>[^-]+)-.+!', | |
| 558 | + $event_id, | |
| 559 | + $events_matches | |
| 560 | + ) | |
| 561 | + ) { | |
| 562 | + $queue_id = $events_matches['queue_id']; | |
| 563 | + $timestamp = $events_matches['timestamp']; | |
| 564 | + | |
| 565 | + $insert_rows[] = $wpdb->prepare( | |
| 566 | + '(%s, %s, %s, %s)', | |
| 567 | + array( | |
| 568 | + $queue_id, | |
| 569 | + $event_id, | |
| 570 | + $event->event_payload, | |
| 571 | + (int) $timestamp, | |
| 572 | + ) | |
| 573 | + ); | |
| 574 | + } | |
| 575 | + } | |
| 576 | + | |
| 577 | + // Instantiate table storage, so we can get the table name. Queue ID is just a placeholder here. | |
| 578 | + $queue_table_storage = new Queue_Storage_Table( 'test_queue' ); | |
| 579 | + | |
| 580 | + if ( ! empty( $insert_rows ) ) { | |
| 581 | + $insert_query = 'INSERT INTO ' . $queue_table_storage->table_name . ' (queue_id, event_id, event_payload, timestamp) VALUES '; | |
| 582 | + | |
| 583 | + $insert_query .= implode( ',', $insert_rows ); | |
| 584 | + | |
| 585 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared | |
| 586 | + $wpdb->query( $insert_query ); | |
| 587 | + } | |
| 588 | + | |
| 589 | + $offset += $limit; | |
| 590 | + } while ( $offset < $item_count ); | |
| 591 | + | |
| 592 | + // Clear out the options queue | |
| 593 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared | |
| 594 | + $wpdb->query( | |
| 595 | + $wpdb->prepare( | |
| 596 | + "DELETE FROM $wpdb->options WHERE option_name LIKE %s", | |
| 597 | + 'jpsq_%-%' | |
| 598 | + ) | |
| 599 | + ); | |
| 600 | + | |
| 601 | + return true; | |
| 602 | + } | |
| 603 | + | |
| 604 | + /** | |
| 605 | + * Migrates the existing Sync events from the Custom table to the Options table | |
| 606 | + * | |
| 607 | + * @return void | |
| 608 | + */ | |
| 609 | + public static function migrate_from_custom_table_to_options_table() { | |
| 610 | + global $wpdb; | |
| 611 | + | |
| 612 | + // Instantiate table storage, so we can get the table name. Queue ID is just a placeholder here. | |
| 613 | + $queue_table_storage = new Queue_Storage_Table( 'test_queue' ); | |
| 614 | + $custom_table_name = $queue_table_storage->table_name; | |
| 615 | + | |
| 616 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 617 | + $count_result = $wpdb->get_row( "SELECT COUNT(*) as item_count FROM {$custom_table_name}" ); | |
| 618 | + | |
| 619 | + if ( $wpdb->last_error ) { | |
| 620 | + return; | |
| 621 | + } | |
| 622 | + | |
| 623 | + $item_count = $count_result->item_count; | |
| 624 | + | |
| 625 | + $limit = 100; | |
| 626 | + $offset = 0; | |
| 627 | + | |
| 628 | + do { | |
| 629 | + // get all the records from the options table | |
| 630 | + $query = " | |
| 631 | + SELECT | |
| 632 | + event_id, | |
| 633 | + event_payload | |
| 634 | + FROM | |
| 635 | + {$custom_table_name} | |
| 636 | + ORDER BY | |
| 637 | + event_id ASC | |
| 638 | + LIMIT $offset, $limit | |
| 639 | + "; | |
| 640 | + | |
| 641 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared | |
| 642 | + $rows = $wpdb->get_results( $query ); | |
| 643 | + | |
| 644 | + $insert_rows = array(); | |
| 645 | + | |
| 646 | + foreach ( $rows as $event ) { | |
| 647 | + $insert_rows[] = $wpdb->prepare( | |
| 648 | + '(%s, %s, "no")', | |
| 649 | + array( | |
| 650 | + $event->event_id, | |
| 651 | + $event->event_payload, | |
| 652 | + ) | |
| 653 | + ); | |
| 654 | + } | |
| 655 | + | |
| 656 | + if ( ! empty( $insert_rows ) ) { | |
| 657 | + $insert_query = "INSERT INTO {$wpdb->options} (option_name, option_value, autoload) VALUES "; | |
| 658 | + | |
| 659 | + $insert_query .= implode( ',', $insert_rows ); | |
| 660 | + | |
| 661 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared | |
| 662 | + $wpdb->query( $insert_query ); | |
| 663 | + } | |
| 664 | + | |
| 665 | + $offset += $limit; | |
| 666 | + } while ( $offset < $item_count ); | |
| 667 | + | |
| 668 | + // Clear the custom table | |
| 669 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 670 | + $wpdb->query( "DELETE FROM {$custom_table_name}" ); | |
| 671 | + | |
| 672 | + // TODO should we drop the table here instead? | |
| 673 | + } | |
| 674 | +} | |