jetpack
/
jetpack_vendor
/
automattic
/
jetpack-sync
/
src
/
sync-queue
/
class-queue-storage-table.php
class-queue-storage-table.php
675 lines
| 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 | } |
| 675 |