| 1 |
<?php |
| 2 |
/** |
| 3 |
* WCPOS sync store component. |
| 4 |
* |
| 5 |
* @package WCPOS\WooCommercePOS\Sync |
| 6 |
*/ |
| 7 |
|
| 8 |
namespace WCPOS\WooCommercePOS\Sync; |
| 9 |
|
| 10 |
use WCPOS\WooCommercePOS\Services\Barcode_Field; |
| 11 |
|
| 12 |
// phpcs:disable Squiz.Commenting, Generic.Commenting -- Ported lab documentation is preserved verbatim. |
| 13 |
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Queries use internal table names and generated SQL fragments. |
| 14 |
|
| 15 |
/** |
| 16 |
* The READ half of the integrity-digest store — the sync engine's query object. |
| 17 |
* |
| 18 |
* {@see Integrity_Digest} owns the WRITE half (hook-time upserts, deletes and the |
| 19 |
* rebuild); this class owns every question the read surface asks of the digest |
| 20 |
* store, and the canonical SQL both halves share. Callers name a COLLECTION, a |
| 21 |
* bucket RANGE and (optionally) FILTERS — never a table, a column or a JOIN. |
| 22 |
* |
| 23 |
* Three questions, one per REST consumer: |
| 24 |
* |
| 25 |
* - {@see bucket_aggregates} — stored-vs-current BIT_XOR per id-range bucket (the scan). |
| 26 |
* - {@see bucket_drift} — the per-id mismatches inside ONE bucket (the drill-down). |
| 27 |
* - {@see bucket_listing} — the authoritative live {id, digest, object_type} for one bucket. |
| 28 |
* |
| 29 |
* Plus the two scoping questions the same id-space owns: |
| 30 |
* {@see published_product_ids} (the readable-catalog filter, one home for a rule |
| 31 |
* that used to be re-spelled in every consumer) and {@see needs_product_rebuild}. |
| 32 |
* |
| 33 |
* Servable scoping — publish state and POS visibility — lives HERE rather than in |
| 34 |
* the controllers, so "what the POS may see" is answered identically wherever the |
| 35 |
* digest store is read. Visibility comes from {@see Pos_Visibility}, unchanged. |
| 36 |
*/ |
| 37 |
final class Digest_Index { |
| 38 |
|
| 39 |
/** |
| 40 |
* Legacy baseline of postmeta keys covered by the product/variation digest. Must include every key the |
| 41 |
* sql-bypass fixture mutates (_price and _regular_price today — see |
| 42 |
* class-fixtures-controller.php sql_bypass()) plus the keys a |
| 43 |
* hook-bypassing import/inventory tool plausibly touches. |
| 44 |
* |
| 45 |
* Changing the MEMBERSHIP of this set moves two things, with two different |
| 46 |
* levers (ADR 0036): the STORED digests rebuild automatically — this set |
| 47 |
* feeds {@see digest_formula_fingerprint}, whose move schedules the guarded |
| 48 |
* product-digest rebuild — and the digests CLIENTS hold go stale, which is |
| 49 |
* what bumping Config_Fingerprint::PAYLOAD_CONTRACT_VERSION for BOTH owners |
| 50 |
* (products AND variations — the set is shared) in the same commit repairs. |
| 51 |
* Reordering without a membership change is a no-op everywhere (SQL sorts). |
| 52 |
* The fingerprint tests pin the semantic set. |
| 53 |
*/ |
| 54 |
public const DIGESTED_META_KEYS = array( '_global_unique_id', '_price', '_regular_price', '_sale_price', '_sku', '_stock', '_stock_status' ); |
| 55 |
|
| 56 |
/** |
| 57 |
* Option recording the key set the STORED digests were computed from. |
| 58 |
* Never autoloaded — read only on the scan path. See {@see digest_formula_fingerprint}. |
| 59 |
*/ |
| 60 |
public const FORMULA_FP_OPTION = 'wcpos_integrity_digest_formula_fp'; |
| 61 |
|
| 62 |
/** |
| 63 |
* Customer usermeta folded into the digest (ADR 0015, Leg-3 phase 7). Kept small + stable — |
| 64 |
* identity/existence fields the POS keys on, NOT every usermeta row (a churny meta bloats the digest |
| 65 |
* with irrelevant drift). The customer's core columns (email, display name, registered) come from |
| 66 |
* wp_users directly; these four are the identifying usermeta. The site-prefixed `capabilities` |
| 67 |
* meta joins them at runtime in {@see customer_digest_select_sql} (the prefix is per-site, so it |
| 68 |
* cannot live in a const): roles are part of the served record under #1379, and a hookless |
| 69 |
* capabilities write (direct update_user_meta/SQL/import) must drift the digest so the |
| 70 |
* integrity scan can repair the stale role — no role/profile hook fires for those writes. |
| 71 |
* |
| 72 |
* Changing the MEMBERSHIP of this set (ADR 0036): bump |
| 73 |
* Config_Fingerprint::PAYLOAD_CONTRACT_VERSION for `customers` in the same |
| 74 |
* commit so clients re-pull — and know that the STORED customer digests have |
| 75 |
* NO automatic rebuild trigger today: {@see digest_formula_fingerprint} folds |
| 76 |
* the product keys only, so the scan reports store-wide false customer drift |
| 77 |
* until a manual rebuild. Extending the rebuild trigger to this set is |
| 78 |
* #1756 phase 2/3 work; until it lands, a change here also needs a |
| 79 |
* deliberate rebuild plan. The fingerprint tests pin the semantic set. |
| 80 |
*/ |
| 81 |
public const CUSTOMER_DIGESTED_META_KEYS = array( 'first_name', 'last_name', 'billing_email', 'billing_phone' ); |
| 82 |
|
| 83 |
/** |
| 84 |
* Order postmeta folded into the digest under the CPT (legacy) storage path (ADR 0015, Leg-3 phase 7). |
| 85 |
* Under HPOS these live as wc_orders COLUMNS (total_amount, customer_id) so no meta join is needed; |
| 86 |
* this allowlist only applies to the wp_posts fallback. Kept minimal — existence/identity signal. |
| 87 |
* |
| 88 |
* Changing the MEMBERSHIP of this set (ADR 0036): bump |
| 89 |
* Config_Fingerprint::PAYLOAD_CONTRACT_VERSION for `orders` in the same |
| 90 |
* commit. Like the customer set, it has NO automatic stored-digest rebuild |
| 91 |
* trigger today ({@see digest_formula_fingerprint} folds product keys only) — |
| 92 |
* #1756 phase 2/3 owns that. The fingerprint tests pin the semantic set. |
| 93 |
*/ |
| 94 |
public const ORDER_DIGESTED_META_KEYS = array( '_order_total', '_customer_user' ); |
| 95 |
|
| 96 |
/** |
| 97 |
* The digest object_types sharing the wp_posts (product-space) id range. |
| 98 |
*/ |
| 99 |
public const OBJECT_TYPES_SQL = "('product','variation')"; |
| 100 |
|
| 101 |
private const PRODUCT_POST_TYPES_SQL = "('product','product_variation')"; |
| 102 |
private const EXCLUDED_POST_STATUSES_SQL = "('trash','auto-draft')"; |
| 103 |
|
| 104 |
/** |
| 105 |
* Request-level memo for {@see digested_meta_keys}, KEYED BY BLOG ID. The hook write path |
| 106 |
* recomputes the digest on every product save, and the barcode key costs an option read to |
| 107 |
* resolve, so the set is resolved once per blog per request. Deliberately NOT invalidated |
| 108 |
* when the setting changes mid-request: every digest written during one request must use |
| 109 |
* ONE key set, or the stored side of a single request disagrees with itself. |
| 110 |
* |
| 111 |
* The blog-id key is load-bearing on multisite (the plugin network-activates). `barcode_field` |
| 112 |
* is a per-site option and `Abstract_Section::read()` re-reads it uncached, so the SETTING |
| 113 |
* follows switch_to_blog() correctly — a process-wide memo would not, and a batch that walks |
| 114 |
* sites would then digest site B's products under site A's barcode key: the stored side would |
| 115 |
* silently cover the wrong meta key, which is the exact staleness this class exists to catch. |
| 116 |
* |
| 117 |
* @var array<int, string[]> |
| 118 |
*/ |
| 119 |
private static array $memoized_digested_meta_keys = array(); |
| 120 |
|
| 121 |
/** |
| 122 |
* The postmeta keys the product/variation digest ACTUALLY covers: the legacy baseline plus |
| 123 |
* the site's configured WCPOS barcode key (mono#1234). |
| 124 |
* |
| 125 |
* The barcode field is merchant-configurable to any meta key, and only `_sku` / |
| 126 |
* `_global_unique_id` are baseline keys. A custom carrier key was therefore undigested: |
| 127 |
* a hookless write to it (importer, direct SQL, inventory tool) produced no journal row |
| 128 |
* and no digest mismatch, so a till's barcodes went stale INDEFINITELY — the one field |
| 129 |
* cashiers key on. Folding the configured key in closes that at tier-2 latency. |
| 130 |
* |
| 131 |
* Sorted so the set has ONE spelling, which is what makes |
| 132 |
* {@see digest_formula_fingerprint} stable across requests. |
| 133 |
* |
| 134 |
* @return string[] |
| 135 |
*/ |
| 136 |
public static function digested_meta_keys(): array { |
| 137 |
$blog_id = get_current_blog_id(); |
| 138 |
if ( ! isset( self::$memoized_digested_meta_keys[ $blog_id ] ) ) { |
| 139 |
$keys = array_values( array_unique( array_merge( self::DIGESTED_META_KEYS, array( Barcode_Field::meta_key() ) ) ) ); |
| 140 |
sort( $keys ); |
| 141 |
self::$memoized_digested_meta_keys[ $blog_id ] = $keys; |
| 142 |
} |
| 143 |
|
| 144 |
return self::$memoized_digested_meta_keys[ $blog_id ]; |
| 145 |
} |
| 146 |
|
| 147 |
/** |
| 148 |
* Fingerprint of the key set the digest formula currently uses. |
| 149 |
* |
| 150 |
* Stored and live digests are only comparable when BOTH were computed from the same key |
| 151 |
* set. Change the set without rebuilding and every stored digest is an old-formula value: |
| 152 |
* every bucket mismatches at once and the merchant sees a store-wide false "N records need |
| 153 |
* attention" for data that is already correct. So the set that produced the stored digests |
| 154 |
* is recorded (by {@see \WCPOS\WooCommercePOS\Sync\Integrity_Digest::rebuild}) and |
| 155 |
* compared on scan. |
| 156 |
*/ |
| 157 |
public static function digest_formula_fingerprint(): string { |
| 158 |
return md5( implode( ',', self::digested_meta_keys() ) ); |
| 159 |
} |
| 160 |
|
| 161 |
/** |
| 162 |
* Fingerprint of the BARE baseline — the formula every install used before mono#1234. |
| 163 |
* |
| 164 |
* The upgrade path must not flood. Installs upgrading into this code have no recorded |
| 165 |
* fingerprint, and assuming the worst would schedule a rebuild on every store. A default |
| 166 |
* store (barcode = `_sku` or `_global_unique_id`, both already baseline keys) has an |
| 167 |
* unchanged set, so seeding the missing option with THIS value rebuilds only the stores |
| 168 |
* whose formula genuinely moved: the custom-carrier ones. |
| 169 |
*/ |
| 170 |
public static function legacy_formula_fingerprint(): string { |
| 171 |
$keys = self::DIGESTED_META_KEYS; |
| 172 |
sort( $keys ); |
| 173 |
|
| 174 |
return md5( implode( ',', $keys ) ); |
| 175 |
} |
| 176 |
|
| 177 |
/** |
| 178 |
* The POS servable-set contract (ADR 0014 WP-M5). Injectable for tests; the |
| 179 |
* default instance reads the live visibility option. |
| 180 |
*/ |
| 181 |
private Pos_Visibility $visibility; |
| 182 |
|
| 183 |
public function __construct( ?Pos_Visibility $visibility = null ) { |
| 184 |
$this->visibility = $visibility ?? new Pos_Visibility(); |
| 185 |
} |
| 186 |
|
| 187 |
public function table_name(): string { |
| 188 |
global $wpdb; |
| 189 |
|
| 190 |
return $wpdb->prefix . Health::STORED_DIGEST_TABLE; |
| 191 |
} |
| 192 |
|
| 193 |
/** |
| 194 |
* Raise the session `group_concat_max_len` before ANY query built on {@see row_digest_select_sql}. |
| 195 |
* That expression GROUP_CONCATs the digested meta; MySQL's default (1024 bytes) SILENTLY TRUNCATES |
| 196 |
* for a row with many/large meta. And because the consumers (hook upsert, rebuild, scan, |
| 197 |
* drill-down, bucket listing) MUST produce byte-identical digests, a truncation on one path but not |
| 198 |
* another is a PERMANENT false-drift bug — the bucket never converges. So raise it identically |
| 199 |
* everywhere the expression runs. |
| 200 |
*/ |
| 201 |
public function raise_group_concat_max_len(): void { |
| 202 |
global $wpdb; |
| 203 |
$wpdb->query( 'SET SESSION group_concat_max_len = 1048576' ); |
| 204 |
} |
| 205 |
|
| 206 |
/** |
| 207 |
* Canonical per-row digest SELECT, shared verbatim by the hook upsert, |
| 208 |
* the scan's current-side aggregate, the drill-down and the rebuild — |
| 209 |
* the stored-vs-current comparison is only sound when every consumer |
| 210 |
* computes the digest with the byte-identical expression. |
| 211 |
* |
| 212 |
* 64-bit digest — CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|', ...)),1,16),16,10) AS UNSIGNED) — over the wp_posts content columns plus |
| 213 |
* the {@see digested_meta_keys} rows (key=value pairs ordered by meta_key then |
| 214 |
* meta_id, so duplicate meta rows digest deterministically). Every |
| 215 |
* nullable operand is COALESCE'd because CONCAT_WS silently SKIPS NULL |
| 216 |
* arguments — ('a', NULL, 'b') would collide with ('a', 'b', '') — |
| 217 |
* while COALESCE keeps every position present and deterministic. |
| 218 |
* |
| 219 |
* $where_sql may reference alias p and contain placeholders; callers |
| 220 |
* run the final statement through $wpdb->prepare. |
| 221 |
* |
| 222 |
* @internal Engine-internal: the digest expression, not a read-surface contract. |
| 223 |
*/ |
| 224 |
public function row_digest_select_sql( string $where_sql = '' ): string { |
| 225 |
global $wpdb; |
| 226 |
// esc_sql because one of these keys is merchant-settable free text: the |
| 227 |
// `barcode_field` setting's REST validator accepts any string, so an |
| 228 |
// apostrophe would otherwise terminate the IN list — breaking EVERY digest |
| 229 |
// query (hook upsert and scan alike) and taking the integrity backstop down |
| 230 |
// with it. The baseline keys are literals; the barcode key is not. |
| 231 |
$meta_keys_sql = "('" . implode( "','", array_map( 'esc_sql', self::digested_meta_keys() ) ) . "')"; |
| 232 |
|
| 233 |
return 'SELECT p.ID AS id,' |
| 234 |
. " CASE WHEN p.post_type = 'product_variation' THEN 'variation' ELSE 'product' END AS object_type," |
| 235 |
. " CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|'," |
| 236 |
. ' p.ID,' |
| 237 |
. " COALESCE(p.post_title,'')," |
| 238 |
. " COALESCE(p.post_excerpt,'')," |
| 239 |
. " COALESCE(p.post_content,'')," |
| 240 |
. " COALESCE(p.post_status,'')," |
| 241 |
. ' COALESCE(p.post_parent,0),' |
| 242 |
. ' COALESCE(p.menu_order,0),' |
| 243 |
. " COALESCE(p.post_modified_gmt,'')," |
| 244 |
. " COALESCE(GROUP_CONCAT(CONCAT(pm.meta_key,'=',COALESCE(pm.meta_value,'')) ORDER BY pm.meta_key ASC, pm.meta_id ASC SEPARATOR '|'),'')" |
| 245 |
// 64-bit digest (ADR 0014 M1): the top 16 hex of MD5 → an integer that still folds under |
| 246 |
// BIT_XOR and fits the BIGINT UNSIGNED column, dropping the CRC32 collision floor from |
| 247 |
// 2^-32 to 2^-64 (a stable per-bucket false "in sync" is unacceptable in a convergence |
| 248 |
// backstop, and CRC32 is linear so structured bulk edits correlate collisions). |
| 249 |
. ')),1,16),16,10) AS UNSIGNED) AS crc' |
| 250 |
. " FROM {$wpdb->posts} p" |
| 251 |
. " LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key IN {$meta_keys_sql}" |
| 252 |
. ' WHERE ' . $this->live_product_predicate_sql( 'p' ) |
| 253 |
. ( '' === $where_sql ? '' : ' AND ' . $where_sql ) |
| 254 |
. ' GROUP BY p.ID'; |
| 255 |
} |
| 256 |
|
| 257 |
/** |
| 258 |
* Canonical per-CUSTOMER digest SELECT (ADR 0015, Leg-3 phase 7) — the wp_users analogue of |
| 259 |
* {@see row_digest_select_sql}. Same 64-bit MD5-derived formula (BIT_XOR-foldable), over a customer's |
| 260 |
* identity columns + the allowlisted usermeta. ALL wp_users rows are POS customers under #1379 |
| 261 |
* (1.9 parity). `$where_sql` narrows to a single user for the hook upsert (`u.ID = %d`); |
| 262 |
* empty selects every user. |
| 263 |
* |
| 264 |
* @internal Engine-internal: the digest expression, not a read-surface contract. |
| 265 |
*/ |
| 266 |
public function customer_digest_select_sql( string $where_sql = '' ): string { |
| 267 |
global $wpdb; |
| 268 |
$meta_keys_sql = "('" . implode( "','", array_merge( self::CUSTOMER_DIGESTED_META_KEYS, array( $wpdb->prefix . 'capabilities' ) ) ) . "')"; |
| 269 |
|
| 270 |
return 'SELECT u.ID AS id,' |
| 271 |
. " 'customer' AS object_type," |
| 272 |
. " CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|'," |
| 273 |
. ' u.ID,' |
| 274 |
. " COALESCE(u.user_email,'')," |
| 275 |
. " COALESCE(u.display_name,'')," |
| 276 |
. " COALESCE(u.user_registered,'')," |
| 277 |
. " COALESCE(GROUP_CONCAT(CONCAT(um.meta_key,'=',COALESCE(um.meta_value,'')) ORDER BY um.meta_key ASC, um.umeta_id ASC SEPARATOR '|'),'')" |
| 278 |
// 64-bit digest (ADR 0014 M1): top 16 hex of MD5 → BIGINT UNSIGNED, folds under BIT_XOR. |
| 279 |
. ')),1,16),16,10) AS UNSIGNED) AS crc' |
| 280 |
. " FROM {$wpdb->users} u" |
| 281 |
. " LEFT JOIN {$wpdb->usermeta} um ON um.user_id = u.ID AND um.meta_key IN {$meta_keys_sql}" |
| 282 |
. ( '' === $where_sql ? '' : ' WHERE ' . $where_sql ) |
| 283 |
. ' GROUP BY u.ID'; |
| 284 |
} |
| 285 |
|
| 286 |
/** |
| 287 |
* Canonical per-ORDER digest SELECT (ADR 0015, Leg-3 phase 7) — HPOS/CPT-aware. Under HPOS orders live |
| 288 |
* in WooCommerce's own {prefix}wc_orders table (status/total/customer are COLUMNS); under legacy CPT |
| 289 |
* they're wp_posts + postmeta. Per install exactly ONE path runs (an install is HPOS or CPT, never both), |
| 290 |
* so stored-vs-current always uses the same path — self-consistent. Same 64-bit formula. |
| 291 |
* |
| 292 |
* `$id_condition` narrows to a single order / a bucket range using the neutral `{id}` placeholder, |
| 293 |
* substituted with the path's real id column (o.id HPOS, p.ID CPT) so callers stay path-agnostic. |
| 294 |
* |
| 295 |
* LIVE-VERIFY: the HPOS column set is shape-asserted here (fake wpdb); verify against a real HPOS store. |
| 296 |
* |
| 297 |
* @internal Engine-internal: the digest expression, not a read-surface contract. |
| 298 |
*/ |
| 299 |
public function order_digest_select_sql( string $id_condition = '' ): string { |
| 300 |
global $wpdb; |
| 301 |
$hpos = $this->orders_are_hpos(); |
| 302 |
$id_col = $hpos ? 'o.id' : 'p.ID'; |
| 303 |
$condition = '' === $id_condition ? '' : ' AND ' . str_replace( '{id}', $id_col, $id_condition ); |
| 304 |
|
| 305 |
if ( $hpos ) { |
| 306 |
$orders_table = $wpdb->prefix . 'wc_orders'; |
| 307 |
return 'SELECT o.id AS id,' |
| 308 |
. " 'order' AS object_type," |
| 309 |
. " CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|'," |
| 310 |
. ' o.id,' |
| 311 |
. " COALESCE(o.status,'')," |
| 312 |
. " COALESCE(o.type,'')," |
| 313 |
. " COALESCE(o.total_amount,'')," |
| 314 |
. ' COALESCE(o.customer_id,0),' |
| 315 |
. " COALESCE(o.date_updated_gmt,''))),1,16),16,10) AS UNSIGNED) AS crc" |
| 316 |
. " FROM {$orders_table} o" |
| 317 |
. ' WHERE ' . $this->live_order_predicate_sql( 'o' ) |
| 318 |
. $condition; |
| 319 |
} |
| 320 |
|
| 321 |
$meta_keys_sql = "('" . implode( "','", self::ORDER_DIGESTED_META_KEYS ) . "')"; |
| 322 |
return 'SELECT p.ID AS id,' |
| 323 |
. " 'order' AS object_type," |
| 324 |
. " CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|'," |
| 325 |
. ' p.ID,' |
| 326 |
. " COALESCE(p.post_status,'')," |
| 327 |
. " COALESCE(p.post_modified_gmt,'')," |
| 328 |
. " COALESCE(GROUP_CONCAT(CONCAT(pm.meta_key,'=',COALESCE(pm.meta_value,'')) ORDER BY pm.meta_key ASC, pm.meta_id ASC SEPARATOR '|'),''))),1,16),16,10) AS UNSIGNED) AS crc" |
| 329 |
. " FROM {$wpdb->posts} p" |
| 330 |
. " LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key IN {$meta_keys_sql}" |
| 331 |
. ' WHERE ' . $this->live_order_predicate_sql( 'p' ) |
| 332 |
. $condition |
| 333 |
. ' GROUP BY p.ID'; |
| 334 |
} |
| 335 |
|
| 336 |
/** |
| 337 |
* Bulk-read the STORED 64-bit digests for ONE collection's id-space -> `[id => digest string]`. |
| 338 |
* |
| 339 |
* ONE reader for every id-space. The registry row names the object types the |
| 340 |
* collection stores (products carries product+variation; customers and orders |
| 341 |
* carry their own), so no id-space can bleed into another's reconcile and a |
| 342 |
* fourth id-space needs no fourth body. A collection with no digest group reads |
| 343 |
* nothing rather than falling through to the products digests. |
| 344 |
* |
| 345 |
* The digest is BIGINT UNSIGNED (above PHP_INT_MAX), so it is returned as a |
| 346 |
* STRING (ADR 0014 M1). Ids with no stored digest yet (never hooked/rebuilt) are |
| 347 |
* simply absent from the result. |
| 348 |
* |
| 349 |
* @param string $collection Canonical plural collection name. |
| 350 |
* @param int[] $ids Requested ids in the collection's own id-space. |
| 351 |
* |
| 352 |
* @return array<int, string> |
| 353 |
*/ |
| 354 |
public function read_digests( string $collection, array $ids ): array { |
| 355 |
global $wpdb; |
| 356 |
$object_types = self::digest_object_types( $collection ); |
| 357 |
if ( array() === $object_types ) { |
| 358 |
return array(); |
| 359 |
} |
| 360 |
$ids = array_values( |
| 361 |
array_unique( |
| 362 |
array_filter( |
| 363 |
array_map( 'intval', $ids ), |
| 364 |
static function ( $id ) { |
| 365 |
return $id > 0; |
| 366 |
} |
| 367 |
) |
| 368 |
) |
| 369 |
); |
| 370 |
if ( array() === $ids ) { |
| 371 |
return array(); |
| 372 |
} |
| 373 |
$placeholders = implode( ',', array_fill( 0, \count( $ids ), '%d' ) ); |
| 374 |
$rows = $wpdb->get_results( |
| 375 |
$wpdb->prepare( |
| 376 |
'SELECT object_id, digest FROM ' . $this->table_name() |
| 377 |
. ' WHERE ' . self::object_type_predicate_sql( $object_types ) |
| 378 |
. ' AND object_id IN (' . $placeholders . ')', |
| 379 |
...$ids |
| 380 |
), |
| 381 |
ARRAY_A |
| 382 |
); |
| 383 |
$out = array(); |
| 384 |
foreach ( (array) $rows as $row ) { |
| 385 |
$out[ (int) $row['object_id'] ] = (string) $row['digest']; |
| 386 |
} |
| 387 |
|
| 388 |
return $out; |
| 389 |
} |
| 390 |
|
| 391 |
/** |
| 392 |
* Narrow a collection's ids to what this store will still serve — the digest |
| 393 |
* endpoint's authoritative-absence answer. |
| 394 |
* |
| 395 |
* FAIL-OPEN, deliberately: on ANY SQL error every requested id comes back |
| 396 |
* servable. The caller turns an UNservable id into `deleted: true` and the till |
| 397 |
* acts on that by dropping its local record — for orders, order data — so a |
| 398 |
* database hiccup must never be able to manufacture a deletion. The same rule |
| 399 |
* covers a collection this store cannot answer for at all. |
| 400 |
* |
| 401 |
* @param string $collection Canonical plural collection name. |
| 402 |
* @param int[] $ids Requested ids in request order. |
| 403 |
* |
| 404 |
* @return int[] Servable ids in request order. |
| 405 |
*/ |
| 406 |
public function servable( string $collection, array $ids ): array { |
| 407 |
global $wpdb; |
| 408 |
$ids = array_values( array_map( 'intval', $ids ) ); |
| 409 |
if ( array() === $ids ) { |
| 410 |
return array(); |
| 411 |
} |
| 412 |
$row = Collections::row( $collection ); |
| 413 |
$live_rows = isset( $row['digest']['live_rows'] ) ? (string) $row['digest']['live_rows'] : ''; |
| 414 |
// Products are the one collection whose servability is NARROWER than a live |
| 415 |
// row: POS visibility and the readable-catalog scope both apply, so the |
| 416 |
// richer reader owns them. Every other id-space is exactly its live row. |
| 417 |
$products = 'products' === $collection; |
| 418 |
if ( ! $products && ( '' === $live_rows || ! method_exists( $this, $live_rows ) ) ) { |
| 419 |
return $ids; |
| 420 |
} |
| 421 |
$wpdb->last_error = ''; |
| 422 |
if ( $products ) { |
| 423 |
$servable_ids = $this->servable_product_ids( $ids, true ); |
| 424 |
} else { |
| 425 |
$predicate = (string) \call_user_func( array( $this, $live_rows ), 'requested.id' ); |
| 426 |
$requested = implode( ' UNION ALL ', array_fill( 0, \count( $ids ), 'SELECT %d AS id' ) ); |
| 427 |
$servable_ids = $wpdb->get_col( |
| 428 |
$wpdb->prepare( |
| 429 |
'SELECT requested.id FROM (' . $requested . ') requested WHERE ' . $predicate, |
| 430 |
...$ids |
| 431 |
) |
| 432 |
); |
| 433 |
} |
| 434 |
/** @var string $last_error */ |
| 435 |
$last_error = $wpdb->last_error; |
| 436 |
|
| 437 |
return '' !== $last_error |
| 438 |
? $ids |
| 439 |
: array_values( array_intersect( $ids, array_map( 'intval', (array) $servable_ids ) ) ); |
| 440 |
} |
| 441 |
|
| 442 |
/** |
| 443 |
* The registry's digest object types for a collection; empty when the collection |
| 444 |
* owns no digest id-space (fail closed — never a fall-through to products). |
| 445 |
* |
| 446 |
* @return string[] |
| 447 |
*/ |
| 448 |
private static function digest_object_types( string $collection ): array { |
| 449 |
$row = Collections::row( $collection ); |
| 450 |
|
| 451 |
return isset( $row['digest']['object_types'] ) ? (array) $row['digest']['object_types'] : array(); |
| 452 |
} |
| 453 |
|
| 454 |
/** |
| 455 |
* `object_type = 'x'` for a single-type id-space, `object_type IN (...)` for a |
| 456 |
* shared one. Values are registry constants, never request data. |
| 457 |
* |
| 458 |
* @param string[] $object_types Stored object types. |
| 459 |
*/ |
| 460 |
private static function object_type_predicate_sql( array $object_types ): string { |
| 461 |
$quoted = array(); |
| 462 |
foreach ( $object_types as $object_type ) { |
| 463 |
$quoted[] = "'" . $object_type . "'"; |
| 464 |
} |
| 465 |
|
| 466 |
return 1 === \count( $quoted ) |
| 467 |
? 'object_type = ' . $quoted[0] |
| 468 |
: 'object_type IN (' . implode( ',', $quoted ) . ')'; |
| 469 |
} |
| 470 |
|
| 471 |
/** |
| 472 |
* Live-row predicate reused by the drill-down's deleted branch and the rebuild's orphan prune. |
| 473 |
* |
| 474 |
* @internal Engine-internal SQL fragment. |
| 475 |
*/ |
| 476 |
public function live_row_exists_sql( string $id_expr ): string { |
| 477 |
global $wpdb; |
| 478 |
|
| 479 |
return "EXISTS (SELECT 1 FROM {$wpdb->posts} lp WHERE lp.ID = {$id_expr}" |
| 480 |
. ' AND ' . $this->live_product_predicate_sql( 'lp' ) . ')'; |
| 481 |
} |
| 482 |
|
| 483 |
/** |
| 484 |
* Customer analogue of {@see live_row_exists_sql}: the id still names any WordPress user (ADR 0015). |
| 485 |
* |
| 486 |
* @internal Engine-internal SQL fragment. |
| 487 |
*/ |
| 488 |
public function customer_live_row_exists_sql( string $id_expr ): string { |
| 489 |
global $wpdb; |
| 490 |
|
| 491 |
return "EXISTS (SELECT 1 FROM {$wpdb->users} lu WHERE lu.ID = {$id_expr})"; |
| 492 |
} |
| 493 |
|
| 494 |
/** |
| 495 |
* Order analogue of {@see live_row_exists_sql} — HPOS/CPT-aware (ADR 0015, Leg-3 phase 7). |
| 496 |
* |
| 497 |
* @internal Engine-internal SQL fragment. |
| 498 |
*/ |
| 499 |
public function order_live_row_exists_sql( string $id_expr ): string { |
| 500 |
global $wpdb; |
| 501 |
if ( $this->orders_are_hpos() ) { |
| 502 |
$orders_table = $wpdb->prefix . 'wc_orders'; |
| 503 |
return "EXISTS (SELECT 1 FROM {$orders_table} lo WHERE lo.id = {$id_expr}" |
| 504 |
. ' AND ' . $this->live_order_predicate_sql( 'lo' ) . ')'; |
| 505 |
} |
| 506 |
return "EXISTS (SELECT 1 FROM {$wpdb->posts} lp WHERE lp.ID = {$id_expr}" |
| 507 |
. ' AND ' . $this->live_order_predicate_sql( 'lp' ) . ')'; |
| 508 |
} |
| 509 |
|
| 510 |
/** |
| 511 |
* Stored-vs-current bucket aggregate over one id window (the integrity scan). |
| 512 |
* |
| 513 |
* Bucket aggregate: BIT_XOR over per-row 64-bit digests, deliberately instead of |
| 514 |
* MD5(GROUP_CONCAT(... ORDER BY id)). XOR is commutative and associative, so the |
| 515 |
* aggregate needs no ORDER BY and carries no group_concat_max_len truncation |
| 516 |
* hazard; its state is a constant-size integer regardless of bucket population. |
| 517 |
* Record counts travel alongside so add/delete imbalances that could cancel in |
| 518 |
* XOR still flag. |
| 519 |
* |
| 520 |
* Digests are unsigned 64-bit (ADR 0014 M1) — above PHP_INT_MAX, so an (int) cast |
| 521 |
* would SATURATE two distinct high-bit values to the same number and report a |
| 522 |
* drifted bucket as `match`, hiding drift. They stay strings end to end. |
| 523 |
* |
| 524 |
* `max_id` is the larger of both sides' max id, so orphaned stored digests past |
| 525 |
* the last live post still get scanned before the walk is called complete. |
| 526 |
* |
| 527 |
* @param array $range { Bucket window. @type int $bucket_size, @type int $start, @type int $end } |
| 528 |
* @return array{buckets: array<int, array<string, mixed>>, max_id: int} |
| 529 |
*/ |
| 530 |
public function bucket_aggregates( array $range, string $collection = 'products', array $filters = array() ): array { |
| 531 |
global $wpdb; |
| 532 |
$bucket_size = max( 1, (int) ( $range['bucket_size'] ?? 1 ) ); |
| 533 |
$window_start = max( 0, (int) ( $range['start'] ?? 0 ) ); |
| 534 |
$window_end = max( 0, (int) ( $range['end'] ?? 0 ) ); |
| 535 |
$publish = 'products' === $collection && 'publish' === ( $filters['status'] ?? '' ); |
| 536 |
$object_types = self::OBJECT_TYPES_SQL; |
| 537 |
$current_sql = $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' ); |
| 538 |
if ( 'customers' === $collection ) { |
| 539 |
$object_types = "('customer')"; |
| 540 |
$current_sql = $this->customer_digest_select_sql( 'u.ID >= %d AND u.ID < %d' ); |
| 541 |
} elseif ( 'orders' === $collection ) { |
| 542 |
$object_types = "('order')"; |
| 543 |
$current_sql = $this->order_digest_select_sql( '{id} >= %d AND {id} < %d' ); |
| 544 |
} |
| 545 |
$current_scope = $publish ? $this->product_servable_predicate_sql( 't.id', true ) : array( |
| 546 |
'sql' => '', |
| 547 |
'args' => array(), |
| 548 |
); |
| 549 |
$stored_scope = $publish ? $this->product_servable_predicate_sql( 'd.object_id', true ) : array( |
| 550 |
'sql' => '', |
| 551 |
'args' => array(), |
| 552 |
); |
| 553 |
$current_join = $publish ? $this->servable_product_join_sql( 't.id' ) : ''; |
| 554 |
$stored_join = $publish ? $this->servable_product_join_sql( 'd.object_id' ) : ''; |
| 555 |
|
| 556 |
// Current side: one SQL pass — per-row canonical digests aggregated |
| 557 |
// per bucket inside the DB engine. Raw rows are digested for |
| 558 |
// DETECTION only; hydration goes through filtered REST (ADR 0003). |
| 559 |
// Raise group_concat_max_len so the current-side digest matches the |
| 560 |
// stored-side (written by the hook) byte-for-byte (ADR 0014 / no truncation drift). |
| 561 |
$this->raise_group_concat_max_len(); |
| 562 |
$current_rows = $wpdb->get_results( |
| 563 |
$wpdb->prepare( |
| 564 |
'SELECT FLOOR(t.id / %d) AS bucket, COUNT(*) AS record_count, BIT_XOR(t.crc) AS digest' |
| 565 |
. ' FROM (' . $current_sql . ') t' . $current_join . ( '' === $current_scope['sql'] ? '' : ' WHERE ' . $current_scope['sql'] ) |
| 566 |
. ' GROUP BY bucket ORDER BY bucket', |
| 567 |
$bucket_size, |
| 568 |
$window_start, |
| 569 |
$window_end, |
| 570 |
...$current_scope['args'] |
| 571 |
), |
| 572 |
ARRAY_A |
| 573 |
); |
| 574 |
|
| 575 |
// Stored side: one SQL pass over the hook-maintained digest table. |
| 576 |
$stored_rows = $wpdb->get_results( |
| 577 |
$wpdb->prepare( |
| 578 |
'SELECT FLOOR(d.object_id / %d) AS bucket, COUNT(*) AS record_count, BIT_XOR(d.digest) AS digest' |
| 579 |
. ' FROM ' . $this->table_name() . ' d' . $stored_join |
| 580 |
. ' WHERE d.object_type IN ' . $object_types |
| 581 |
. ' AND d.object_id >= %d AND d.object_id < %d' . ( '' === $stored_scope['sql'] ? '' : ' AND ' . $stored_scope['sql'] ) |
| 582 |
. ' GROUP BY bucket ORDER BY bucket', |
| 583 |
$bucket_size, |
| 584 |
$window_start, |
| 585 |
$window_end, |
| 586 |
...$stored_scope['args'] |
| 587 |
), |
| 588 |
ARRAY_A |
| 589 |
); |
| 590 |
|
| 591 |
$sides = array(); |
| 592 |
foreach ( \is_array( $stored_rows ) ? $stored_rows : array() as $row ) { |
| 593 |
$sides[ (int) $row['bucket'] ]['stored'] = $row; |
| 594 |
} |
| 595 |
foreach ( \is_array( $current_rows ) ? $current_rows : array() as $row ) { |
| 596 |
$sides[ (int) $row['bucket'] ]['current'] = $row; |
| 597 |
} |
| 598 |
ksort( $sides ); |
| 599 |
|
| 600 |
$buckets = array(); |
| 601 |
foreach ( $sides as $bucket => $side ) { |
| 602 |
$stored_count = isset( $side['stored'] ) ? (int) $side['stored']['record_count'] : 0; |
| 603 |
$current_count = isset( $side['current'] ) ? (int) $side['current']['record_count'] : 0; |
| 604 |
$stored_digest = isset( $side['stored'] ) ? (string) $side['stored']['digest'] : ''; |
| 605 |
$current_digest = isset( $side['current'] ) ? (string) $side['current']['digest'] : ''; |
| 606 |
$buckets[] = array( |
| 607 |
'bucket' => $bucket, |
| 608 |
'range' => array( |
| 609 |
'start' => $bucket * $bucket_size, |
| 610 |
'end' => ( $bucket + 1 ) * $bucket_size, |
| 611 |
), |
| 612 |
'stored_count' => $stored_count, |
| 613 |
'current_count' => $current_count, |
| 614 |
'stored_digest' => $stored_digest, |
| 615 |
'current_digest' => $current_digest, |
| 616 |
'match' => $stored_count === $current_count && $stored_digest === $current_digest, |
| 617 |
); |
| 618 |
} |
| 619 |
|
| 620 |
// Completion id: the larger of the last LIVE id under the collection's own |
| 621 |
// servable predicate and the last STORED id. The live side is MAX(id) |
| 622 |
// straight off the base table — wrapping the un-windowed per-row digest |
| 623 |
// SELECT as a derived table just to take its max digested the whole |
| 624 |
// collection on every scan page (0.4–3 s on real stores; #1805, ADR 0038). |
| 625 |
$live_max = $this->live_max_id_sql( $collection, $publish ); |
| 626 |
$max_query = 'SELECT GREATEST(COALESCE((' . $live_max['sql'] . '), 0),' |
| 627 |
. ' COALESCE((SELECT MAX(d.object_id) FROM ' . $this->table_name() . ' d' |
| 628 |
. ' WHERE d.object_type IN ' . $object_types . '), 0))'; |
| 629 |
$max_id = (int) $wpdb->get_var( empty( $live_max['args'] ) ? $max_query : $wpdb->prepare( $max_query, ...$live_max['args'] ) ); |
| 630 |
|
| 631 |
return array( |
| 632 |
'buckets' => $buckets, |
| 633 |
'max_id' => $max_id, |
| 634 |
); |
| 635 |
} |
| 636 |
|
| 637 |
/** |
| 638 |
* Per-id stored-vs-current comparison inside ONE bucket (the scan drill-down). |
| 639 |
* |
| 640 |
* Three mismatch shapes: changed (both sides present, digests differ), |
| 641 |
* missing_stored (live row never digested — created without hooks or |
| 642 |
* pre-backfill), deleted (stored digest whose row is gone — hook-bypassing |
| 643 |
* delete). Digests stay strings (ADR 0014 M1) and are null where the side is |
| 644 |
* absent. |
| 645 |
* |
| 646 |
* @param array $range { Bucket window. @type int $start, @type int $end } |
| 647 |
* |
| 648 |
* @return array<int, array<string, mixed>> |
| 649 |
*/ |
| 650 |
public function bucket_drift( array $range ): array { |
| 651 |
global $wpdb; |
| 652 |
$range_start = max( 0, (int) ( $range['start'] ?? 0 ) ); |
| 653 |
$range_end = max( 0, (int) ( $range['end'] ?? 0 ) ); |
| 654 |
$table = $this->table_name(); |
| 655 |
|
| 656 |
// Same-formula invariant: the current side must digest identically to the stored side. |
| 657 |
$this->raise_group_concat_max_len(); |
| 658 |
$rows = $wpdb->get_results( |
| 659 |
$wpdb->prepare( |
| 660 |
'SELECT cur.id AS id,' |
| 661 |
. " CASE WHEN d.digest IS NULL THEN 'missing_stored' ELSE 'changed' END AS status," |
| 662 |
. ' d.digest AS stored_digest, cur.crc AS current_digest, cur.object_type AS object_type' |
| 663 |
. ' FROM (' . $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' ) . ') cur' |
| 664 |
. " LEFT JOIN {$table} d ON d.object_id = cur.id AND d.object_type = cur.object_type" |
| 665 |
. ' WHERE d.digest IS NULL OR d.digest <> cur.crc' |
| 666 |
. ' UNION ALL' |
| 667 |
. " SELECT d.object_id AS id, 'deleted' AS status, d.digest AS stored_digest, NULL AS current_digest, d.object_type AS object_type" |
| 668 |
. " FROM {$table} d" |
| 669 |
. ' WHERE d.object_type IN ' . self::OBJECT_TYPES_SQL |
| 670 |
. ' AND d.object_id >= %d AND d.object_id < %d' |
| 671 |
. ' AND NOT ' . $this->live_row_exists_sql( 'd.object_id' ) |
| 672 |
. ' ORDER BY id ASC', |
| 673 |
$range_start, |
| 674 |
$range_end, |
| 675 |
$range_start, |
| 676 |
$range_end |
| 677 |
), |
| 678 |
ARRAY_A |
| 679 |
); |
| 680 |
|
| 681 |
return array_map( |
| 682 |
static function ( array $row ): array { |
| 683 |
return array( |
| 684 |
'id' => (int) $row['id'], |
| 685 |
'status' => (string) $row['status'], |
| 686 |
'object_type' => (string) ( $row['object_type'] ?? '' ), |
| 687 |
// Unsigned 64-bit (ADR 0014 M1): keep as strings — a (int) cast (and JS Number) can't |
| 688 |
// hold values above PHP_INT_MAX / 2^53 without precision loss. |
| 689 |
'stored_digest' => null === $row['stored_digest'] ? null : (string) $row['stored_digest'], |
| 690 |
'current_digest' => null === $row['current_digest'] ? null : (string) $row['current_digest'], |
| 691 |
); |
| 692 |
}, |
| 693 |
\is_array( $rows ) ? $rows : array() |
| 694 |
); |
| 695 |
} |
| 696 |
|
| 697 |
/** |
| 698 |
* The authoritative current {id, digest, object_type} for every live SERVABLE record whose id falls |
| 699 |
* in the given range of the collection's own id-space (products/variations over wp_posts, customers |
| 700 |
* over wp_users, orders over HPOS or CPT). Digests come from the SAME 64-bit formula the client's |
| 701 |
* manifest stores, so the two compare apples-to-apples. |
| 702 |
* |
| 703 |
* Products carry the servable scoping the pull filter applies, so the reconcile prunes anything the |
| 704 |
* POS may no longer see: the optional `status => publish` readable-catalog filter, and ALWAYS the |
| 705 |
* POS-hidden (`online_only`) ids. READ-SIDE ONLY — a visibility toggle changes no product row (no |
| 706 |
* hook fires), so stored per-record digests are never touched; omitting the ids from this read is |
| 707 |
* enough because the client folds THIS list. Products and variations share the wp_posts id-space, so |
| 708 |
* their two hidden lists union safely on cur.id. |
| 709 |
* |
| 710 |
* @param string $collection Digest id-space owner: products | customers | orders. |
| 711 |
* @param array $range { @type int $start, @type int $end } |
| 712 |
* @param array $filters { @type string $status 'publish' scopes products to the readable catalog. } |
| 713 |
* |
| 714 |
* @return array<int, array{id: int, digest: string, object_type: string}> |
| 715 |
*/ |
| 716 |
public function bucket_listing( string $collection, array $range, array $filters = array() ): array { |
| 717 |
global $wpdb; |
| 718 |
$range_start = max( 0, (int) ( $range['start'] ?? 0 ) ); |
| 719 |
$range_end = max( 0, (int) ( $range['end'] ?? 0 ) ); |
| 720 |
|
| 721 |
$servable_join = ''; |
| 722 |
$servable_filter = ''; |
| 723 |
$servable_args = array(); |
| 724 |
if ( 'customers' === $collection ) { |
| 725 |
$inner_sql = $this->customer_digest_select_sql( 'u.ID >= %d AND u.ID < %d' ); |
| 726 |
} elseif ( 'orders' === $collection ) { |
| 727 |
// Orders bucket over their own id-space (HPOS o.id / CPT p.ID) via the {id} placeholder. |
| 728 |
$inner_sql = $this->order_digest_select_sql( '{id} >= %d AND {id} < %d' ); |
| 729 |
} else { |
| 730 |
$inner_sql = $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' ); |
| 731 |
$servable_join = " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = cur.id"; |
| 732 |
if ( 'publish' === ( $filters['status'] ?? '' ) ) { |
| 733 |
$servable_join .= " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent" |
| 734 |
. " AND catalog_post.post_type = 'product_variation'"; |
| 735 |
} |
| 736 |
$servable = $this->product_servable_predicate_sql( 'cur.id', 'publish' === ( $filters['status'] ?? '' ) ); |
| 737 |
$servable_filter = '' === $servable['sql'] ? '' : ' WHERE ' . $servable['sql']; |
| 738 |
$servable_args = $servable['args']; |
| 739 |
} |
| 740 |
|
| 741 |
// Same-formula invariant + GROUP_CONCAT stability, exactly as the scan's current side. |
| 742 |
$this->raise_group_concat_max_len(); |
| 743 |
$rows = $wpdb->get_results( |
| 744 |
$wpdb->prepare( |
| 745 |
'SELECT cur.id AS id, cur.crc AS digest, cur.object_type AS object_type FROM (' |
| 746 |
. $inner_sql |
| 747 |
. ') cur' . $servable_join . $servable_filter . ' ORDER BY cur.id ASC', |
| 748 |
$range_start, |
| 749 |
$range_end, |
| 750 |
...$servable_args |
| 751 |
), |
| 752 |
ARRAY_A |
| 753 |
); |
| 754 |
|
| 755 |
return array_map( |
| 756 |
static function ( array $row ): array { |
| 757 |
return array( |
| 758 |
'id' => (int) $row['id'], |
| 759 |
// Unsigned 64-bit (ADR 0014 M1): keep as a string — (int)/JS Number lose precision above 2^53. |
| 760 |
'digest' => (string) $row['digest'], |
| 761 |
'object_type' => (string) $row['object_type'], |
| 762 |
); |
| 763 |
}, |
| 764 |
\is_array( $rows ) ? $rows : array() |
| 765 |
); |
| 766 |
} |
| 767 |
|
| 768 |
/** |
| 769 |
* Narrow product-space ids to the readable catalog: published products, and variations whose parent |
| 770 |
* product is published. The SAME rule {@see bucket_listing} applies with `status => publish`, so the |
| 771 |
* prime-pass digest read and the reconcile listing can never disagree about what "publish" means. |
| 772 |
* |
| 773 |
* @param int[] $ids Requested product-space ids. |
| 774 |
* |
| 775 |
* @return int[] The subset that is readable, in the caller's order. |
| 776 |
*/ |
| 777 |
public function published_product_ids( array $ids ): array { |
| 778 |
global $wpdb; |
| 779 |
$ids = array_values( array_map( 'intval', $ids ) ); |
| 780 |
if ( array() === $ids ) { |
| 781 |
return array(); |
| 782 |
} |
| 783 |
$placeholders = implode( ',', array_fill( 0, \count( $ids ), '%d' ) ); |
| 784 |
$published_ids = $wpdb->get_col( |
| 785 |
$wpdb->prepare( |
| 786 |
"SELECT p.ID FROM {$wpdb->posts} p" |
| 787 |
. " LEFT JOIN {$wpdb->posts} parent ON parent.ID = p.post_parent AND p.post_type = 'product_variation'" |
| 788 |
. ' WHERE p.ID IN (' . $placeholders . ')' |
| 789 |
. ' AND ' . $this->published_product_predicate_sql( 'p', 'parent' ), |
| 790 |
...$ids |
| 791 |
) |
| 792 |
); |
| 793 |
|
| 794 |
return array_values( array_intersect( $ids, array_map( 'intval', (array) $published_ids ) ) ); |
| 795 |
} |
| 796 |
|
| 797 |
/** Narrow product-space ids to the integrity scan's canonical servable membership. */ |
| 798 |
public function servable_product_ids( array $ids, bool $publish = false ): array { |
| 799 |
global $wpdb; |
| 800 |
$ids = array_values( array_map( 'intval', $ids ) ); |
| 801 |
if ( array() === $ids ) { |
| 802 |
return array(); |
| 803 |
} |
| 804 |
$placeholders = implode( ',', array_fill( 0, \count( $ids ), '%d' ) ); |
| 805 |
$servable = $this->product_servable_predicate_sql( 'catalog_post.ID', $publish ); |
| 806 |
$query_args = array_merge( $ids, $servable['args'] ); |
| 807 |
$servable_ids = $wpdb->get_col( |
| 808 |
$wpdb->prepare( |
| 809 |
"SELECT catalog_post.ID FROM {$wpdb->posts} catalog_post" |
| 810 |
. ( $publish ? " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'" : '' ) |
| 811 |
. ' WHERE catalog_post.ID IN (' . $placeholders . ') AND ' . $servable['sql'], |
| 812 |
...$query_args |
| 813 |
) |
| 814 |
); |
| 815 |
|
| 816 |
return array_values( array_intersect( $ids, array_map( 'intval', (array) $servable_ids ) ) ); |
| 817 |
} |
| 818 |
|
| 819 |
/** |
| 820 |
* True when the product space holds live rows but carries NO stored digests at all — the |
| 821 |
* "stored side was never backfilled (or was wiped)" signal the scan answers with a guarded rebuild |
| 822 |
* instead of reporting the whole catalog as drift. |
| 823 |
*/ |
| 824 |
public function needs_product_rebuild(): bool { |
| 825 |
global $wpdb; |
| 826 |
|
| 827 |
return (bool) $wpdb->get_var( |
| 828 |
'SELECT EXISTS (SELECT 1 FROM ' . $wpdb->posts . ' p' |
| 829 |
. ' WHERE ' . $this->live_product_predicate_sql( 'p' ) . ' LIMIT 1)' |
| 830 |
. ' AND NOT EXISTS (SELECT 1 FROM ' . $this->table_name() |
| 831 |
. ' WHERE object_type IN ' . self::OBJECT_TYPES_SQL . ' LIMIT 1)' |
| 832 |
); |
| 833 |
} |
| 834 |
|
| 835 |
/** |
| 836 |
* The readable-catalog predicate over a post alias and its parent alias — a published product, |
| 837 |
* or a variation whose parent product is published. One home for the rule. |
| 838 |
*/ |
| 839 |
private function published_product_predicate_sql( string $post_alias, string $parent_alias ): string { |
| 840 |
return "(({$post_alias}.post_type = 'product' AND {$post_alias}.post_status = 'publish')" |
| 841 |
. " OR ({$post_alias}.post_type = 'product_variation' AND {$parent_alias}.post_type = 'product'" |
| 842 |
. " AND {$parent_alias}.post_status = 'publish'))"; |
| 843 |
} |
| 844 |
|
| 845 |
/** |
| 846 |
* A LIVE product-space row over a `wp_posts` alias: a product or variation that |
| 847 |
* is not trashed/auto-draft. ONE spelling for the digest SELECT, the live-row |
| 848 |
* probes, the rebuild guard and the completion id — the scan is only sound |
| 849 |
* while every side agrees on what "live" means. |
| 850 |
*/ |
| 851 |
private function live_product_predicate_sql( string $alias ): string { |
| 852 |
return "{$alias}.post_type IN " . self::PRODUCT_POST_TYPES_SQL |
| 853 |
. " AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL; |
| 854 |
} |
| 855 |
|
| 856 |
/** |
| 857 |
* A LIVE order over the active order store's alias — `wc_orders` under HPOS, |
| 858 |
* `wp_posts` under legacy CPT — with the same one-spelling rule as |
| 859 |
* {@see live_product_predicate_sql}. |
| 860 |
*/ |
| 861 |
private function live_order_predicate_sql( string $alias ): string { |
| 862 |
if ( $this->orders_are_hpos() ) { |
| 863 |
return "{$alias}.type = 'shop_order' AND {$alias}.status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL; |
| 864 |
} |
| 865 |
|
| 866 |
return "{$alias}.post_type = 'shop_order' AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL; |
| 867 |
} |
| 868 |
|
| 869 |
/** |
| 870 |
* The joins {@see product_servable_predicate_sql} reads through: the post row |
| 871 |
* behind `$id_expr` as `catalog_post`, and its parent as `parent_product` when |
| 872 |
* it is a variation. One spelling for every side of the scan. |
| 873 |
*/ |
| 874 |
private function servable_product_join_sql( string $id_expr ): string { |
| 875 |
global $wpdb; |
| 876 |
|
| 877 |
return " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = {$id_expr}" . $this->parent_product_join_sql(); |
| 878 |
} |
| 879 |
|
| 880 |
/** |
| 881 |
* `catalog_post`'s parent as `parent_product` when it is a variation (NULL otherwise). |
| 882 |
*/ |
| 883 |
private function parent_product_join_sql(): string { |
| 884 |
global $wpdb; |
| 885 |
|
| 886 |
return " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'"; |
| 887 |
} |
| 888 |
|
| 889 |
/** |
| 890 |
* `MAX(id)` of a collection's LIVE rows under the same predicate its digest |
| 891 |
* SELECT uses — off the base table, never through a digested row (#1805). |
| 892 |
* |
| 893 |
* Customers are every `wp_users` row (#1379), so this is the primary key's end. |
| 894 |
* Orders and products carry a type/status predicate, so this is one index pass |
| 895 |
* over the live rows of that type — the honest floor, since no index ends on |
| 896 |
* the id under a status filter. Under the published product scope the servable |
| 897 |
* predicate (published, or a variation of a published parent, and not POS-hidden) |
| 898 |
* applies to the post row itself, exactly as the windowed sides apply it. |
| 899 |
* |
| 900 |
* @return array{sql: string, args: array<int, int>} |
| 901 |
*/ |
| 902 |
private function live_max_id_sql( string $collection, bool $publish ): array { |
| 903 |
global $wpdb; |
| 904 |
if ( 'customers' === $collection ) { |
| 905 |
return array( |
| 906 |
'sql' => "SELECT MAX(u.ID) FROM {$wpdb->users} u", |
| 907 |
'args' => array(), |
| 908 |
); |
| 909 |
} |
| 910 |
if ( 'orders' === $collection ) { |
| 911 |
if ( $this->orders_are_hpos() ) { |
| 912 |
$orders_table = $wpdb->prefix . 'wc_orders'; |
| 913 |
return array( |
| 914 |
'sql' => "SELECT MAX(o.id) FROM {$orders_table} o WHERE " . $this->live_order_predicate_sql( 'o' ), |
| 915 |
'args' => array(), |
| 916 |
); |
| 917 |
} |
| 918 |
return array( |
| 919 |
'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_order_predicate_sql( 'p' ), |
| 920 |
'args' => array(), |
| 921 |
); |
| 922 |
} |
| 923 |
if ( ! $publish ) { |
| 924 |
return array( |
| 925 |
'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_product_predicate_sql( 'p' ), |
| 926 |
'args' => array(), |
| 927 |
); |
| 928 |
} |
| 929 |
$scope = $this->product_servable_predicate_sql( 'catalog_post.ID', true ); |
| 930 |
return array( |
| 931 |
'sql' => "SELECT MAX(catalog_post.ID) FROM {$wpdb->posts} catalog_post" . $this->parent_product_join_sql() |
| 932 |
. ' WHERE ' . $scope['sql'], |
| 933 |
'args' => $scope['args'], |
| 934 |
); |
| 935 |
} |
| 936 |
|
| 937 |
private function product_servable_predicate_sql( string $id_expr, bool $publish ): array { |
| 938 |
$predicates = array( |
| 939 |
'catalog_post.post_type IN ' . self::PRODUCT_POST_TYPES_SQL, |
| 940 |
'catalog_post.post_status NOT IN ' . self::EXCLUDED_POST_STATUSES_SQL, |
| 941 |
); |
| 942 |
if ( $publish ) { |
| 943 |
$predicates[] = $this->published_product_predicate_sql( 'catalog_post', 'parent_product' ); |
| 944 |
} |
| 945 |
$hidden = $this->pos_hidden_product_ids(); |
| 946 |
if ( array() !== $hidden ) { |
| 947 |
$predicates[] = $id_expr . ' NOT IN (' . implode( ',', array_fill( 0, \count( $hidden ), '%d' ) ) . ')'; |
| 948 |
} |
| 949 |
return array( |
| 950 |
'sql' => implode( ' AND ', $predicates ), |
| 951 |
'args' => $hidden, |
| 952 |
); |
| 953 |
} |
| 954 |
|
| 955 |
/** |
| 956 |
* Product-space ids hidden from the POS (`online_only`), products and variations unioned — they share |
| 957 |
* the wp_posts id-space. Read through the {@see Pos_Visibility} contract, never from the option. |
| 958 |
* |
| 959 |
* @return int[] |
| 960 |
*/ |
| 961 |
private function pos_hidden_product_ids(): array { |
| 962 |
return array_values( |
| 963 |
array_unique( |
| 964 |
array_map( |
| 965 |
'intval', |
| 966 |
array_merge( |
| 967 |
$this->visibility->online_only_product_ids(), |
| 968 |
$this->visibility->online_only_variation_ids() |
| 969 |
) |
| 970 |
) |
| 971 |
) |
| 972 |
); |
| 973 |
} |
| 974 |
|
| 975 |
/** True when orders use HPOS (WooCommerce's own tables); false → legacy CPT (wp_posts). */ |
| 976 |
private function orders_are_hpos(): bool { |
| 977 |
$order_util = '\\Automattic\\WooCommerce\\Utilities\\OrderUtil'; |
| 978 |
if ( class_exists( $order_util ) && method_exists( $order_util, 'custom_orders_table_usage_is_enabled' ) ) { |
| 979 |
return (bool) call_user_func( array( $order_util, 'custom_orders_table_usage_is_enabled' ) ); |
| 980 |
} |
| 981 |
return false; // no WC / older WC → CPT |
| 982 |
} |
| 983 |
} |
| 984 |
|
