prefix . 'vasp_cat_hashes'; } function vasp_wc_catalog_queue_table(): string { global $wpdb; return $wpdb->prefix . 'vasp_cat_queue'; } function vasp_wc_catalog_storage_error(string $vasp_reason, array $vasp_details = []): WP_Error { $vasp_nonfatal_image_issue = vasp_is_catalog_image_source_error($vasp_reason); return vasp_create_error(VASP_ERROR_WC_CATALOG_SCHEDULE_FAILED, [ 'user_message' => __('WordPress could not save the catalog synchronization state.', 'valuespace'), 'reason' => $vasp_reason, ], $vasp_details, !$vasp_nonfatal_image_issue); } function vasp_wc_catalog_storage_ready(): bool { return (int) get_option(VASP_OPTION_WC_CATALOG_STORAGE_SCHEMA, 0) >= VASP_WC_CATALOG_STORAGE_SCHEMA_VERSION; } /** Install the current baseline/queue schema from a clean local state. */ function vasp_wc_catalog_install_storage() { global $wpdb; if (vasp_wc_catalog_storage_ready()) { return true; } $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // Prepare charset/collation identifiers as well as table names. $vasp_charset = $wpdb->charset; $vasp_collation = $wpdb->collate; // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Only fixed optional charset/collation placeholder clauses; every identifier is bound with %i. dbDelta($wpdb->prepare("CREATE TABLE %i ( product_id bigint(20) unsigned NOT NULL, hash varchar(64) NOT NULL DEFAULT '', image_hash varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (product_id) )" . ($vasp_charset ? ' DEFAULT CHARACTER SET %i' : '') . ($vasp_collation ? ' COLLATE %i' : '') . ' ENGINE=InnoDB;', array_merge([$vasp_table], $vasp_charset ? [$vasp_charset] : [], $vasp_collation ? [$vasp_collation] : []) )); // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Only fixed optional charset/collation placeholder clauses; every identifier is bound with %i. dbDelta($wpdb->prepare("CREATE TABLE %i ( product_id bigint(20) unsigned NOT NULL, action varchar(8) NOT NULL DEFAULT '', action_at datetime(6) NOT NULL, image_action varchar(8) NOT NULL DEFAULT '', image_action_at datetime(6) NOT NULL, PRIMARY KEY (product_id), KEY pending_products (action,action_at), KEY pending_images (image_action,image_action_at) )" . ($vasp_charset ? ' DEFAULT CHARACTER SET %i' : '') . ($vasp_collation ? ' COLLATE %i' : '') . ' ENGINE=InnoDB;', array_merge([$vasp_queue], $vasp_charset ? [$vasp_charset] : [], $vasp_collation ? [$vasp_collation] : []) )); // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared $vasp_columns = $wpdb->get_col($wpdb->prepare("SHOW COLUMNS FROM %i", $vasp_table)); $vasp_queue_columns = $wpdb->get_col($wpdb->prepare("SHOW COLUMNS FROM %i", $vasp_queue)); if ($wpdb->last_error !== '' || count(array_diff(['product_id', 'hash', 'image_hash'], (array) $vasp_columns)) > 0 || count(array_diff([ 'product_id', 'action', 'action_at', 'image_action', 'image_action_at', ], (array) $vasp_queue_columns)) > 0) { return vasp_wc_catalog_storage_error('catalog_table_unavailable'); } if (vasp_wc_catalog_discover_products() === false) { return vasp_wc_catalog_storage_error('catalog_membership_discovery_failed'); } if (!update_option( VASP_OPTION_WC_CATALOG_STORAGE_SCHEMA, VASP_WC_CATALOG_STORAGE_SCHEMA_VERSION, false ) && !vasp_wc_catalog_storage_ready()) { return vasp_wc_catalog_storage_error('catalog_schema_marker_failed'); } foreach (VASP_WC_CATALOG_RETIRED_OPTIONS as $vasp_option) { delete_option($vasp_option); } // No network in activation; the next allowed job uses // the same producer and sender as a normal explicit synchronization. if (vasp_wc_catalog_push_enabled()) { update_option(VASP_OPTION_WC_CATALOG_STEP, 'store', false); update_option(VASP_OPTION_WC_CATALOG_STATUS, 'pending', false); } return true; } /** Advance action time atomically so an older acknowledgement cannot clear newer work. */ function vasp_wc_catalog_request_product( int $vasp_id, string $vasp_action = 'update', bool $vasp_image = false, bool $vasp_force = false ): bool { global $wpdb; if ($vasp_id <= 0) { return false; } $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_action = $vasp_action === 'delete' && !$vasp_image ? 'delete' : 'update'; $vasp_inserted = $wpdb->query($wpdb->prepare( "INSERT IGNORE INTO %i (product_id) VALUES (%d)", $vasp_table, $vasp_id )); if ($vasp_inserted === false) { return false; } $vasp_queue_action = $vasp_force ? 'replay' : $vasp_action; if ($vasp_image) { return $wpdb->query($wpdb->prepare( "INSERT INTO %i (product_id,action,action_at,image_action,image_action_at) VALUES (%d,'',UTC_TIMESTAMP(6),%s,UTC_TIMESTAMP(6)) ON DUPLICATE KEY UPDATE image_action=IF(image_action='replay' AND VALUES(image_action)='update', 'replay',VALUES(image_action)), image_action_at=GREATEST(UTC_TIMESTAMP(6),image_action_at + INTERVAL 1 MICROSECOND)", $vasp_queue, $vasp_id, $vasp_queue_action )) !== false; } $vasp_initial_image_action = $vasp_inserted > 0 && $vasp_action !== 'delete' ? 'update' : ''; return $wpdb->query($wpdb->prepare( "INSERT INTO %i (product_id,action,action_at,image_action,image_action_at) VALUES (%d,%s,UTC_TIMESTAMP(6),%s,UTC_TIMESTAMP(6)) ON DUPLICATE KEY UPDATE action=IF(action='replay' AND VALUES(action)='update','replay',VALUES(action)), action_at=GREATEST(UTC_TIMESTAMP(6),action_at + INTERVAL 1 MICROSECOND)", $vasp_queue, $vasp_id, $vasp_queue_action, $vasp_initial_image_action )) !== false; } /** Source membership also repairs missed deletion hooks and can queue one full hash inspection. */ function vasp_wc_catalog_discover_products( &$vasp_published_ids = null, bool $vasp_queue_products = false ): bool { global $wpdb; $vasp_table = vasp_wc_catalog_hash_table(); $vasp_posts = $wpdb->posts; $vasp_primary_ids = []; $vasp_last_id = 0; while (true) { $vasp_page = $wpdb->get_col($wpdb->prepare( "SELECT ID FROM %i WHERE ID>%d AND post_type IN ('product','product_variation') ORDER BY ID LIMIT %d", $vasp_posts, $vasp_last_id, VASP_WC_CATALOG_SELECTION_PAGE_SIZE )); if ($wpdb->last_error !== '' || !is_array($vasp_page)) { return false; } if (!$vasp_page) { break; } $vasp_last_id = (int) end($vasp_page); foreach ($vasp_page as $vasp_id) { $vasp_id = (int) $vasp_id; if (vasp_wc_catalog_product_id_is_published($vasp_id)) { $vasp_primary_ids[$vasp_id] = $vasp_id; } } if (count($vasp_page) < VASP_WC_CATALOG_SELECTION_PAGE_SIZE) { break; } } $vasp_primary_ids = array_values($vasp_primary_ids); foreach (array_chunk($vasp_primary_ids, 500) as $vasp_chunk) { $vasp_placeholders = implode(',', array_fill(0, count($vasp_chunk), '%d')); if ($wpdb->query($wpdb->prepare("INSERT IGNORE INTO %i (product_id) SELECT p.ID FROM %i p LEFT JOIN %i parent ON parent.ID=p.post_parent WHERE p.ID IN ($vasp_placeholders) AND p.post_status='publish' AND (p.post_type='product' OR (p.post_type='product_variation' AND parent.post_status='publish' AND parent.post_type='product'))", array_merge([$vasp_table, $vasp_posts, $vasp_posts], $vasp_chunk) )) === false) { return false; } } $vasp_queue = vasp_wc_catalog_queue_table(); foreach (array_chunk($vasp_primary_ids, 500) as $vasp_chunk) { $vasp_placeholders = implode(',', array_fill(0, count($vasp_chunk), '%d')); if ($wpdb->query($wpdb->prepare("INSERT INTO %i (product_id,action,action_at,image_action,image_action_at) SELECT b.product_id,IF(%d OR b.hash='','update',''),UTC_TIMESTAMP(6), IF(b.image_hash='','update',''),UTC_TIMESTAMP(6) FROM %i b JOIN %i p ON p.ID=b.product_id LEFT JOIN %i parent ON parent.ID=p.post_parent WHERE p.ID IN ($vasp_placeholders) AND (%d OR b.hash='' OR b.image_hash='') AND p.post_status='publish' AND (p.post_type='product' OR (p.post_type='product_variation' AND parent.post_status='publish' AND parent.post_type='product')) ON DUPLICATE KEY UPDATE action_at=IF(VALUES(action)<>'' AND action='', GREATEST(UTC_TIMESTAMP(6),action_at + INTERVAL 1 MICROSECOND),action_at), action=IF(VALUES(action)='',action,IF(action='replay','replay',IF(action='','update',action))), image_action_at=IF(VALUES(image_action)<>'' AND image_action='', GREATEST(UTC_TIMESTAMP(6),image_action_at + INTERVAL 1 MICROSECOND),image_action_at), image_action=IF(VALUES(image_action)='',image_action, IF(image_action='replay','replay',IF(image_action='','update',image_action)))", array_merge([$vasp_queue, (int) $vasp_queue_products, $vasp_table, $vasp_posts, $vasp_posts], $vasp_chunk, [(int) $vasp_queue_products]) )) === false) { return false; } } $vasp_known = $wpdb->get_results($wpdb->prepare("SELECT b.product_id,b.hash,COALESCE(q.action,'') action FROM %i b LEFT JOIN %i q ON q.product_id=b.product_id", $vasp_table, $vasp_queue), ARRAY_A); if ($wpdb->last_error !== '' || !is_array($vasp_known)) { return false; } $vasp_primary_lookup = array_fill_keys($vasp_primary_ids, true); foreach ($vasp_known as $vasp_row) { $vasp_id = (int) $vasp_row['product_id']; if (!isset($vasp_primary_lookup[$vasp_id]) && ((string) $vasp_row['hash'] !== '' || in_array((string) $vasp_row['action'], ['update', 'replay'], true)) && !vasp_wc_catalog_request_product($vasp_id, 'delete')) { return false; } } $vasp_published_ids = $vasp_primary_lookup; return true; } function vasp_wc_catalog_pending_rows(bool $vasp_image = false, int $vasp_limit = 100) { global $wpdb; $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_action_column = $vasp_image ? 'image_action' : 'action'; $vasp_action_at_column = $vasp_image ? 'image_action_at' : 'action_at'; $vasp_rows = $wpdb->get_results($wpdb->prepare( "SELECT b.*,q.action,q.action_at,q.image_action,q.image_action_at FROM %i q JOIN %i b ON b.product_id=q.product_id WHERE q.%i<>'' ORDER BY q.%i,q.product_id LIMIT %d", $vasp_queue, $vasp_table, $vasp_action_column, $vasp_action_at_column, $vasp_limit ), ARRAY_A); return $wpdb->last_error === '' ? (array) $vasp_rows : vasp_wc_catalog_storage_error('pending_read_failed'); } /** Make acknowledged products absent from the complete server inventory bypass hash equality. */ function vasp_wc_catalog_replay_missing_products(array $vasp_ids): bool { global $wpdb; if (!$vasp_ids) { return true; } $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); foreach (array_chunk(array_values(array_unique(array_map('intval', $vasp_ids))), 500) as $vasp_chunk) { $vasp_placeholders = implode(',', array_fill(0, count($vasp_chunk), '%d')); if ($wpdb->query($wpdb->prepare( "UPDATE %i q JOIN %i b ON b.product_id=q.product_id SET q.action='replay', q.action_at=GREATEST(UTC_TIMESTAMP(6),q.action_at + INTERVAL 1 MICROSECOND) WHERE q.product_id IN ($vasp_placeholders) AND b.hash<>''", array_merge([$vasp_queue, $vasp_table], $vasp_chunk) )) === false) { return false; } } return true; } /** Read the complete pending scope for stable delivery totals. */ function vasp_wc_catalog_all_pending_rows(bool $vasp_image = false) { global $wpdb; $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_action_column = $vasp_image ? 'image_action' : 'action'; $vasp_action_at_column = $vasp_image ? 'image_action_at' : 'action_at'; $vasp_rows = $wpdb->get_results($wpdb->prepare( "SELECT b.*,q.action,q.action_at,q.image_action,q.image_action_at FROM %i q JOIN %i b ON b.product_id=q.product_id WHERE q.%i<>'' ORDER BY q.%i,q.product_id", $vasp_queue, $vasp_table, $vasp_action_column, $vasp_action_at_column ), ARRAY_A); return $wpdb->last_error === '' ? (array) $vasp_rows : vasp_wc_catalog_storage_error('pending_read_failed'); } function vasp_wc_catalog_pending_count(bool $vasp_image = false): int { global $wpdb; if (!vasp_wc_catalog_storage_ready()) { return 0; } $vasp_table = vasp_wc_catalog_queue_table(); $vasp_action_column = $vasp_image ? 'image_action' : 'action'; return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i WHERE %i<>''", $vasp_table, $vasp_action_column)); } /** Remove a drained queue row without racing a newer product or image request. */ function vasp_wc_catalog_prune_queue(int $vasp_id): bool { global $wpdb; $vasp_queue = vasp_wc_catalog_queue_table(); return $wpdb->query($wpdb->prepare( "DELETE FROM %i WHERE product_id=%d AND action='' AND image_action=''", $vasp_queue, $vasp_id )) !== false; } /** Clear image work whose current bytes still match its acknowledged baseline. */ function vasp_wc_catalog_settle_unchanged_image(array $vasp_row): bool { global $wpdb; $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i SET image_action='' WHERE product_id=%d AND image_action='update' AND image_action_at=%s", $vasp_queue, (int) $vasp_row['product_id'], $vasp_row['image_action_at'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } /** Settle one terminal image attempt, including an explicit server replay. */ function vasp_wc_catalog_settle_image_attempt(array $vasp_row): bool { global $wpdb; $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i SET image_action='' WHERE product_id=%d AND image_action_at=%s", $vasp_queue, (int) $vasp_row['product_id'], $vasp_row['image_action_at'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } /** Clear a local update whose current payload still matches its acknowledged baseline. */ function vasp_wc_catalog_settle_unchanged_product(array $vasp_row): bool { global $wpdb; $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i SET action='' WHERE product_id=%d AND action='update' AND action_at=%s", $vasp_queue, (int) $vasp_row['product_id'], $vasp_row['action_at'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } /** Settle one terminal product attempt without changing its acknowledged baseline. */ function vasp_wc_catalog_settle_product_attempt(array $vasp_row): bool { global $wpdb; $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i SET action='' WHERE product_id=%d AND action_at=%s", $vasp_queue, (int) $vasp_row['product_id'], $vasp_row['action_at'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } /** Persist the received hash and clear only the work this sender actually read. */ function vasp_wc_catalog_receive_product(array $vasp_row, string $vasp_hash, bool $vasp_image = false): bool { global $wpdb; $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_prefix = $vasp_image ? 'image_' : ''; if (!$vasp_image && $vasp_hash === '') { $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i b JOIN %i q ON q.product_id=b.product_id SET b.hash=CASE WHEN q.action_at=%s THEN '' ELSE b.hash END, q.action=CASE WHEN q.action_at=%s THEN '' ELSE q.action END, b.image_hash=CASE WHEN q.image_action_at=%s THEN '' ELSE b.image_hash END, q.image_action=CASE WHEN q.image_action_at=%s THEN '' ELSE q.image_action END WHERE b.product_id=%d", $vasp_table, $vasp_queue, $vasp_row['action_at'], $vasp_row['action_at'], $vasp_row['image_action_at'], $vasp_row['image_action_at'], (int) $vasp_row['product_id'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } $vasp_result = $wpdb->query($wpdb->prepare( "UPDATE %i b JOIN %i q ON q.product_id=b.product_id SET b.%i=CASE WHEN q.%i=%s THEN %s ELSE b.%i END, q.%i=CASE WHEN q.%i=%s THEN '' ELSE q.%i END WHERE b.product_id=%d", $vasp_table, $vasp_queue, $vasp_prefix . 'hash', $vasp_prefix . 'action_at', $vasp_row[$vasp_prefix . 'action_at'], $vasp_hash, $vasp_prefix . 'hash', $vasp_prefix . 'action', $vasp_prefix . 'action_at', $vasp_row[$vasp_prefix . 'action_at'], $vasp_prefix . 'action', (int) $vasp_row['product_id'] )); return $vasp_result !== false && vasp_wc_catalog_prune_queue((int) $vasp_row['product_id']); } /** Explicit reset/uninstall removes both local receipt and queue state. */ function vasp_wc_catalog_uninstall_storage(): void { global $wpdb; $vasp_table = vasp_wc_catalog_hash_table(); $vasp_queue = vasp_wc_catalog_queue_table(); $vasp_retired_table = $wpdb->prefix . 'vasp_catalog_sync'; $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $vasp_queue)); $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $vasp_table)); $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $vasp_retired_table)); }