a AND meta_id <= b`, gaps skipped), reading the `_product_id` / * `_variation_id` rows of each range and keeping those that point at a product or variation of * the batches being cleaned: the range's distinct product IDs are looked up by `post_id` (marker * in the batches, type product or variation), so the cost follows the lines read, never how many * test objects the site holds; each ID is looked up once per call. Their * items → orders that still exist (posts / HPOS) → the orders' marker and signature, as before: * a line in an existing order that isn't a signed test order is a real order, whatever Analytics * imported; a line whose order is gone is an orphan. A variation's lines also carry its parent in * `_product_id`, so the parent is found too. * * Each range is bounded (50,000 meta rows by default, `apwc_test_data_scan_rows`) and the walk * stops at the step's time budget, so a step stays bounded at any store size; the whole walk costs * about one pass over item meta per clean-up. Its state lives in the run state (`verify`): * `cursor` (last meta ID read), `real` (products in real orders), `over` (products with more than * Marker::LINE_ROWS lines: kept as unverified), `lines` (line counts of the others) and * `ranges` / `left` (progress: ranges read, ranges estimated to go). * * Per step the walk may overshoot the budget by one range (~15–35 ms at 50,000 rows), plus the * OVERLAP re-read (1,000 rows) of a catch-up; the saved-orders re-read only starts with time left. * * Rows added after the walk (new orders, items added to old ones) have higher meta IDs: before * each products chunk the walk catches up to the current highest ID, and re-reads the last * OVERLAP rows below its cursor (a row whose insert committed after a higher one was read). With * HPOS tables (authoritative, or kept in sync with posts) it then re-reads the lines of every * order saved since the walk started (`date_updated_gmt`, indexed; MARGIN seconds of overlap), * so a line changed in place (same meta row, e.g. a REST edit of `line_items[].product_id`) is * seen too; more than CHANGED_MAX such orders since the last look and the chunk's products stay * as unverified. With plain posts storage (no sync) `post_modified_gmt` has no index, so that * re-read isn't done (known limitation). * * @phpstan-type VerifyState array{cursor: int, since: int, checked: int, ranges: int, left: int, real: list, over: list, lines: array} * * @since 1.0.0 */ final class RealOrders { /** * Default meta rows per range. */ const ROWS = 50000; /** * Meta rows below the cursor read again on every catch-up. */ const OVERLAP = 1000; /** * Seconds of overlap when re-reading orders saved since the last look (clock skew, saves that * committed late). */ const MARGIN = 120; /** * Most orders saved since the last look that are re-read before a products chunk; beyond that * the chunk's products stay as unverified. */ const CHANGED_MAX = 5000; /** * CHANGED_MAX in use (lowered by tests only). * * @internal * @var int */ private static $changed_max = self::CHANGED_MAX; /** * The walk's state, with defaults for anything missing or broken: `cursor` (last meta ID * read), `since` (when the walk started), `checked` (when saved orders were last re-read), * `ranges` / `left` (progress), `real`, `over`, `lines`. * * @since 1.0.0 * @param mixed $verify Stored state. * @return array * @phpstan-return VerifyState */ public static function normalize( $verify ): array { $verify = is_array( $verify ) ? $verify : array(); $lines = array(); foreach ( (array) ( $verify['lines'] ?? array() ) as $id => $count ) { if ( (int) $id > 0 ) { $lines[ (int) $id ] = max( 0, (int) $count ); } } return array( 'cursor' => max( 0, (int) ( $verify['cursor'] ?? 0 ) ), 'since' => max( 0, (int) ( $verify['since'] ?? 0 ) ), 'checked' => max( 0, (int) ( $verify['checked'] ?? 0 ) ), 'ranges' => max( 0, (int) ( $verify['ranges'] ?? 0 ) ), 'left' => max( 0, (int) ( $verify['left'] ?? 0 ) ), 'real' => array_values( array_unique( array_filter( array_map( 'intval', (array) ( $verify['real'] ?? array() ) ) ) ) ), 'over' => array_values( array_unique( array_filter( array_map( 'intval', (array) ( $verify['over'] ?? array() ) ) ) ) ), 'lines' => $lines, ); } /** * Meta rows per range. * * @since 1.0.0 * @return int */ public static function rows(): int { /** * Filter how many order item meta rows (by ID) the clean-up's real-order check reads per * range (100–500,000). Each range is one indexed query; the walk stops at the step's time. * * @since 1.0.0 * @param int $rows Rows (default 50,000). */ $rows = apply_filters( 'apwc_test_data_scan_rows', self::ROWS ); return is_numeric( $rows ) ? max( 100, min( 500000, (int) $rows ) ) : self::ROWS; } /** * Walk on from the cursor up to the highest meta ID now, within the step's budget; with * `$recheck` (before a products chunk), then re-read the orders saved since the last look. * Leaves `ranges` (ranges read so far) and `left` (ranges estimated to go, from the highest * meta ID; 0 when caught up) in the state for the progress bar. * * @since 1.0.0 * @param mixed $verify The walk's state (normalize()). * @param string[] $batches Batches being cleaned. * @param Budget $budget Step budget (each range counts as an item). * @param bool $recheck Re-read orders saved since the walk started (HPOS tables). * @return array{verify: array, done: bool, ok: bool} `done` when the walk * reached the highest meta ID (and, with `$recheck`, re-read saved orders); `ok` false * when too many orders were saved to re-read (keep the chunk's products as unverified). * @phpstan-return array{verify: VerifyState, done: bool, ok: bool} */ public static function walk( $verify, array $batches, Budget $budget, bool $recheck = false ): array { global $wpdb; $verify = self::normalize( $verify ); $batches = array_values( array_filter( array_map( 'strval', $batches ), array( Marker::class, 'valid_batch' ) ) ); $table = $wpdb->prefix . 'woocommerce_order_itemmeta'; if ( 0 === $verify['cursor'] && 0 === $verify['since'] ) { $verify['since'] = time(); // Orders saved from now on are re-read (recheck()). } // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- live end of the walk. $hi = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(meta_id) FROM %i', $table ) ); $rows = self::rows(); $known = array(); // Candidate product ID => ours, for this call. if ( $verify['cursor'] > 0 ) { // Rows just below the cursor again: an insert that committed after a higher one was read. $verify = self::scan( $verify, max( 0, $verify['cursor'] - self::OVERLAP ), $verify['cursor'], $batches, $known, false ); } while ( $verify['cursor'] < $hi ) { if ( ! $budget->has_time() ) { $verify['left'] = self::estimate( $hi, $verify['cursor'], $rows ); return array( 'verify' => $verify, 'done' => false, 'ok' => true, ); } $upper = min( $verify['cursor'] + $rows, $hi ); $verify = self::scan( $verify, $verify['cursor'], $upper, $batches, $known, true ); // Jump over a gap in the IDs: just below the next existing one. // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- one primary-key lookup. $next = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(meta_id) FROM %i WHERE meta_id > %d', $table, $upper ) ); $verify['cursor'] = null === $next ? max( $upper, $hi ) : max( $upper, (int) $next - 1 ); ++$verify['ranges']; $budget->spend(); } $verify['left'] = 0; $ok = true; if ( $recheck ) { if ( ! $budget->has_time() ) { $verify['left'] = 1; // The re-read comes first thing next step. return array( 'verify' => $verify, 'done' => false, 'ok' => true, ); } $result = self::recheck( $verify, $batches, $known ); $verify = $result['verify']; $ok = $result['ok']; } return array( 'verify' => $verify, 'done' => true, 'ok' => $ok, ); } /** * Ranges still to read, estimated from the IDs left (gaps are skipped, so it can drop faster). * * @param int $hi Highest meta ID. * @param int $cursor Cursor. * @param int $rows Rows per range. * @return int */ private static function estimate( int $hi, int $cursor, int $rows ): int { return max( 1, (int) ceil( max( 0, $hi - $cursor ) / max( 1, $rows ) ) ); } /** * The orders table whose indexed `date_updated_gmt` tells which orders were saved: HPOS's * while it's authoritative, or while posts are authoritative with compatibility sync on (the * sync copies every saved order there at once). '' with plain posts storage. * * @since 1.0.0 * @return string */ public static function changed_orders_table(): string { if ( class_exists( OrderUtil::class ) && Orders::hpos() ) { return OrderUtil::get_table_for_orders(); } if ( ! function_exists( 'wc_get_container' ) || ! class_exists( DataSynchronizer::class ) || ! class_exists( OrdersTableDataStore::class ) ) { return ''; } try { $sync = wc_get_container()->get( DataSynchronizer::class ); } catch ( \Throwable $e ) { return ''; } return $sync->data_sync_is_enabled() && $sync->get_table_exists() ? OrdersTableDataStore::get_orders_table_name() : ''; } /** * Re-read the lines of orders saved since the last look (from the walk's start, with MARGIN * seconds of overlap), so a line whose product was changed in place is seen. One indexed * range on `date_updated_gmt`, then the orders' lines by `order_id` (1,000 per query). Not * done with plain posts storage (`post_modified_gmt` has no index). * * @param array $verify State. * @phpstan-param VerifyState $verify * @param string[] $batches Batches being cleaned. * @param array $known Candidates already looked up in this call. * @return array{verify: array, ok: bool} * @phpstan-return array{verify: VerifyState, ok: bool} */ private static function recheck( array $verify, array $batches, array &$known ): array { global $wpdb; $table = self::changed_orders_table(); if ( '' === $table || ! $batches ) { return array( 'verify' => $verify, 'ok' => true, ); } $now = time(); $from = max( $verify['since'], $verify['checked'] ) - self::MARGIN; // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- indexed range; one %d per ID. $orders = array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE date_updated_gmt >= %s LIMIT %d', $table, gmdate( 'Y-m-d H:i:s', $from ), self::$changed_max + 1 ) ) ); if ( count( $orders ) > self::$changed_max ) { return array( 'verify' => $verify, 'ok' => false, // Too many to re-read: `checked` stays, the chunk stays. ); } $real = array_fill_keys( $verify['real'], true ); $lines = array(); foreach ( array_chunk( $orders, 1000 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); $lines = array_merge( $lines, (array) $wpdb->get_results( $wpdb->prepare( "SELECT im.order_item_id, im.meta_value FROM %i oi INNER JOIN %i im ON im.order_item_id = oi.order_item_id AND im.meta_key IN ('_product_id', '_variation_id') WHERE oi.order_id IN ({$in})", array_merge( array( $wpdb->prefix . 'woocommerce_order_items', $wpdb->prefix . 'woocommerce_order_itemmeta' ), $chunk ) ) ) ); } // phpcs:enable $product_of = array(); $mine = self::ours( self::candidates( $lines, $real ), $batches, $known ); foreach ( $lines as $row ) { $product = self::product( (string) $row->meta_value ); if ( $product && isset( $mine[ $product ] ) && ! isset( $real[ $product ] ) ) { $product_of[ (int) $row->order_item_id ][] = $product; } } foreach ( self::resolve( $product_of ) as $product ) { $real[ $product ] = true; unset( $verify['lines'][ $product ] ); } $verify['real'] = array_map( 'intval', array_keys( $real ) ); $verify['checked'] = $now; return array( 'verify' => $verify, 'ok' => true, ); } /** * A product ID from a stored `_product_id` / `_variation_id` value, compared as text like the * stored value: '12' only, never ' 12', '012' or '12abc'. * * @param string $value Stored value. * @return int 0 when not a product ID. */ private static function product( string $value ): int { return '' !== $value && ctype_digit( $value ) && '0' !== $value[0] ? (int) $value : 0; } /** * Distinct product IDs of these lines, leaving out those already settled. * * @param object[] $lines Rows with `meta_value`. * @param array $skip Product IDs to leave out (keys). * @return int[] */ private static function candidates( array $lines, array $skip ): array { $out = array(); foreach ( $lines as $row ) { $product = self::product( (string) ( $row->meta_value ?? '' ) ); if ( $product && ! isset( $skip[ $product ] ) ) { $out[ $product ] = $product; } } return array_values( $out ); } /** * Which of these IDs are products or variations of the batches being cleaned: one query per * 1,000 candidates by `post_id` (so the cost follows the lines read, not how many test objects * the site has), each ID looked up once per call. * * @since 1.0.0 * @param int[] $candidates IDs found on order lines. * @param string[] $batches Batches being cleaned. * @param array $known ID => ours, filled in as IDs are looked up. * @return array Ours among the candidates. */ public static function ours( array $candidates, array $batches, array &$known ): array { global $wpdb; $new = array(); foreach ( $candidates as $id ) { if ( ! isset( $known[ (int) $id ] ) ) { $new[] = (int) $id; } } if ( $new && $batches ) { $marks = implode( ',', array_fill( 0, count( $batches ), '%s' ) ); foreach ( array_chunk( $new, 1000 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one placeholder per ID and batch. $found = (array) $wpdb->get_col( $wpdb->prepare( "SELECT pm.post_id FROM %i pm INNER JOIN %i p ON p.ID = pm.post_id WHERE pm.post_id IN ({$in}) AND pm.meta_key = %s AND pm.meta_value IN ({$marks}) AND p.post_type IN ('product', 'product_variation')", array_merge( array( $wpdb->postmeta, $wpdb->posts ), $chunk, array( Marker::META ), $batches ) ) ); foreach ( $chunk as $id ) { $known[ $id ] = false; } foreach ( $found as $id ) { $known[ (int) $id ] = true; } } } $out = array(); foreach ( $candidates as $id ) { if ( ! empty( $known[ (int) $id ] ) ) { $out[ (int) $id ] = true; } } return $out; } /** * Read one range (`lo < meta_id <= hi`) and record what it holds. * * @param array $verify State. * @phpstan-param VerifyState $verify * @param int $lo Exclusive start. * @param int $hi Inclusive end. * @param string[] $batches Batches being cleaned. * @param array $known Candidates already looked up in this call. * @param bool $count Count the lines (false when re-reading rows already counted). * @return array * @phpstan-return VerifyState */ private static function scan( array $verify, int $lo, int $hi, array $batches, array &$known, bool $count ): array { global $wpdb; if ( $hi <= $lo || ! $batches ) { return $verify; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- a bounded primary-key range (the meta_key index holds meta_id too). $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT order_item_id, meta_value FROM %i WHERE meta_id > %d AND meta_id <= %d AND meta_key IN ('_product_id', '_variation_id')", $wpdb->prefix . 'woocommerce_order_itemmeta', $lo, $hi ) ); $real = array_fill_keys( $verify['real'], true ); $over = array_fill_keys( $verify['over'], true ); $mine = self::ours( self::candidates( $rows, $real + $over ), $batches, $known ); $product_of = array(); foreach ( $rows as $row ) { $product = self::product( (string) $row->meta_value ); if ( ! $product || ! isset( $mine[ $product ] ) || isset( $real[ $product ] ) || isset( $over[ $product ] ) ) { continue; } if ( $count ) { $verify['lines'][ $product ] = ( $verify['lines'][ $product ] ?? 0 ) + 1; if ( $verify['lines'][ $product ] > Marker::LINE_ROWS ) { $over[ $product ] = true; // Too many lines to vouch for: kept as unverified. unset( $verify['lines'][ $product ] ); continue; } } $product_of[ (int) $row->order_item_id ][] = $product; } unset( $rows ); foreach ( self::resolve( $product_of ) as $product ) { $real[ $product ] = true; unset( $verify['lines'][ $product ] ); } $verify['real'] = array_map( 'intval', array_keys( $real ) ); $verify['over'] = array_map( 'intval', array_keys( $over ) ); return $verify; } /** * Which products of these lines are in a real order: the items' orders by primary key, which * of those still exist, and their markers and signatures (1,000 IDs per query). * * @since 1.0.0 * @param array $product_of Order item ID => product IDs on it. * @return int[] Products found in real orders. */ public static function resolve( array $product_of ): array { global $wpdb; if ( ! $product_of ) { return array(); } $items = $wpdb->prefix . 'woocommerce_order_items'; $order_of = array(); // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one %d per ID. foreach ( array_chunk( array_keys( $product_of ), 1000 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT order_item_id, order_id FROM %i WHERE order_item_id IN ({$in})", array_merge( array( $items ), $chunk ) ) ); foreach ( $rows as $row ) { $order_of[ (int) $row->order_item_id ] = (int) $row->order_id; } } $orders = array_values( array_unique( array_filter( $order_of ) ) ); // Orders that still exist (HPOS table and/or posts): a line whose order is gone is an // orphan (Database clean-up's business), not a real order. $exists = array(); $tables = array( array( $wpdb->posts, 'ID' ) ); if ( class_exists( OrderUtil::class ) && Orders::hpos() ) { $tables[] = array( OrderUtil::get_table_for_orders(), 'id' ); } foreach ( array_chunk( $orders, 1000 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); foreach ( $tables as $table ) { $rows = $wpdb->get_col( $wpdb->prepare( "SELECT %i FROM %i WHERE %i IN ({$in})", array_merge( array( $table[1], $table[0], $table[1] ), $chunk ) ) ); foreach ( $rows as $found_id ) { $exists[ (int) $found_id ] = true; } } } list( $meta, $meta_col ) = Orders::hpos() && class_exists( OrderUtil::class ) ? array( OrderUtil::get_table_for_order_meta(), 'order_id' ) : array( $wpdb->postmeta, 'post_id' ); $tags = array(); foreach ( array_chunk( array_keys( $exists ), 1000 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT %i AS id, meta_key, meta_value FROM %i WHERE meta_key IN (%s, %s) AND %i IN ({$in})", array_merge( array( $meta_col, $meta, Marker::META, Marker::SIG, $meta_col ), $chunk ) ) ); foreach ( $rows as $row ) { $tags[ (int) $row->id ][ (string) $row->meta_key ] = (string) $row->meta_value; } } // phpcs:enable $found = array(); foreach ( $product_of as $item_id => $products ) { $order = $order_of[ $item_id ] ?? 0; if ( ! $order || ! isset( $exists[ $order ] ) ) { continue; // Orphaned line: its order no longer exists. } $tag = $tags[ $order ] ?? array(); // An item whose order is unmarked, or not signed as a test order, is real. if ( ! Marker::valid_signature( 'order', $order, (string) ( $tag[ Marker::META ] ?? '' ), $tag[ Marker::SIG ] ?? '' ) ) { foreach ( $products as $product ) { $found[ $product ] = $product; } } } return array_values( $found ); } }