← All changes | includes/Sync/Digest_Index.php +155 -58 1.10.3 → 1.10.18 View file →

		@@ -248,10 +248,9 @@
248	248	// backstop, and CRC32 is linear so structured bulk edits correlate collisions).
249	249	. ')),1,16),16,10) AS UNSIGNED) AS crc'
250	250	. " FROM {$wpdb->posts} p"
251	251	. " LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key IN {$meta_keys_sql}"
252		- . ' WHERE p.post_type IN ' . self::PRODUCT_POST_TYPES_SQL
253		- . ' AND p.post_status NOT IN ' . self::EXCLUDED_POST_STATUSES_SQL
	252	+ . ' WHERE ' . $this->live_product_predicate_sql( 'p' )
254	253	. ( '' === $where_sql ? '' : ' AND ' . $where_sql )
255	254	. ' GROUP BY p.ID';
256	255	}
257	256
		@@ -314,9 +313,9 @@
314	313	. " COALESCE(o.total_amount,''),"
315	314	. ' COALESCE(o.customer_id,0),'
316	315	. " COALESCE(o.date_updated_gmt,''))),1,16),16,10) AS UNSIGNED) AS crc"
317	316	. " FROM {$orders_table} o"
318		- . " WHERE o.type = 'shop_order' AND o.status NOT IN ('trash','auto-draft')"
	317	+ . ' WHERE ' . $this->live_order_predicate_sql( 'o' )
319	318	. $condition;
320	319	}
321	320
322	321	$meta_keys_sql = "('" . implode( "','", self::ORDER_DIGESTED_META_KEYS ) . "')";
		@@ -328,9 +327,9 @@
328	327	. " COALESCE(p.post_modified_gmt,''),"
329	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"
330	329	. " FROM {$wpdb->posts} p"
331	330	. " LEFT JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key IN {$meta_keys_sql}"
332		- . " WHERE p.post_type = 'shop_order' AND p.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL
	331	+ . ' WHERE ' . $this->live_order_predicate_sql( 'p' )
333	332	. $condition
334	333	. ' GROUP BY p.ID';
335	334	}
336	335
		@@ -353,8 +352,13 @@
353	352	* @return array<int, string>
354	353	*/
355	354	public function read_digests( string $collection, array $ids ): array {
356	355	global $wpdb;
	356	+ // Order/customer upserts are coalesced per request and land at shutdown
	357	+ // (see Integrity_Digest::$pending_digests). A read inside the same
	358	+ // request — the pull lane stamping `_rxdb_digest` after a write — must
	359	+ // see the settled digest, so land what is owed before reading.
	360	+ Integrity_Digest::flush_pending_digests();
357	361	$object_types = self::digest_object_types( $collection );
358	362	if ( array() === $object_types ) {
359	363	return array();
360	364	}
		@@ -414,15 +418,15 @@
414	418	$live_rows = isset( $row['digest']['live_rows'] ) ? (string) $row['digest']['live_rows'] : '';
415	419	// Products are the one collection whose servability is NARROWER than a live
416	420	// row: POS visibility and the readable-catalog scope both apply, so the
417	421	// richer reader owns them. Every other id-space is exactly its live row.
418		- $products = 'products' === $collection;
419		- if ( ! $products && ( '' === $live_rows || ! method_exists( $this, $live_rows ) ) ) {
	422	+ $servable = $row['digest']['servable'] ?? null;
	423	+ if ( null === $servable && ( '' === $live_rows || ! method_exists( $this, $live_rows ) ) ) {
420	424	return $ids;
421	425	}
422	426	$wpdb->last_error = '';
423		- if ( $products ) {
424		- $servable_ids = $this->servable_product_ids( $ids, true );
	427	+ if ( null !== $servable ) {
	428	+ $servable_ids = $this->$servable( $ids, true );
425	429	} else {
426	430	$predicate = (string) \call_user_func( array( $this, $live_rows ), 'requested.id' );
427	431	$requested = implode( ' UNION ALL ', array_fill( 0, \count( $ids ), 'SELECT %d AS id' ) );
428	432	$servable_ids = $wpdb->get_col(
		@@ -477,10 +481,9 @@
477	481	public function live_row_exists_sql( string $id_expr ): string {
478	482	global $wpdb;
479	483
480	484	return "EXISTS (SELECT 1 FROM {$wpdb->posts} lp WHERE lp.ID = {$id_expr}"
481		- . ' AND lp.post_type IN ' . self::PRODUCT_POST_TYPES_SQL
482		- . ' AND lp.post_status NOT IN ' . self::EXCLUDED_POST_STATUSES_SQL . ')';
	485	+ . ' AND ' . $this->live_product_predicate_sql( 'lp' ) . ')';
483	486	}
484	487
485	488	/**
486	489	* Customer analogue of {@see live_row_exists_sql}: the id still names any WordPress user (ADR 0015).
		@@ -502,12 +505,12 @@
502	505	global $wpdb;
503	506	if ( $this->orders_are_hpos() ) {
504	507	$orders_table = $wpdb->prefix . 'wc_orders';
505	508	return "EXISTS (SELECT 1 FROM {$orders_table} lo WHERE lo.id = {$id_expr}"
506		- . " AND lo.type = 'shop_order' AND lo.status NOT IN ('trash','auto-draft'))";
	509	+ . ' AND ' . $this->live_order_predicate_sql( 'lo' ) . ')';
507	510	}
508	511	return "EXISTS (SELECT 1 FROM {$wpdb->posts} lp WHERE lp.ID = {$id_expr}"
509		- . " AND lp.post_type = 'shop_order' AND lp.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL . ')';
	512	+ . ' AND ' . $this->live_order_predicate_sql( 'lp' ) . ')';
510	513	}
511	514
512	515	/**
513	516	* Stored-vs-current bucket aggregate over one id window (the integrity scan).
		@@ -533,21 +536,18 @@
533	536	global $wpdb;
534	537	$bucket_size = max( 1, (int) ( $range['bucket_size'] ?? 1 ) );
535	538	$window_start = max( 0, (int) ( $range['start'] ?? 0 ) );
536	539	$window_end = max( 0, (int) ( $range['end'] ?? 0 ) );
537		- $publish = 'products' === $collection && 'publish' === ( $filters['status'] ?? '' );
538		- $object_types = self::OBJECT_TYPES_SQL;
539		- $current_sql = $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' );
540		- $max_sql = $this->row_digest_select_sql();
541		- if ( 'customers' === $collection ) {
542		- $object_types = "('customer')";
543		- $current_sql = $this->customer_digest_select_sql( 'u.ID >= %d AND u.ID < %d' );
544		- $max_sql = $this->customer_digest_select_sql();
545		- } elseif ( 'orders' === $collection ) {
546		- $object_types = "('order')";
547		- $current_sql = $this->order_digest_select_sql( '{id} >= %d AND {id} < %d' );
548		- $max_sql = $this->order_digest_select_sql();
	540	+ $digest = Collections::row( $collection )['digest'] ?? null;
	541	+ if ( null === $digest ) {
	542	+ return array(
	543	+ 'buckets' => array(),
	544	+ 'max_id' => 0,
	545	+ );
549	546	}
	547	+ $publish = isset( Collections::row( $collection )['digest']['published_ids'] ) && 'publish' === ( $filters['status'] ?? '' );
	548	+ $object_types = "('" . implode( "','", $digest['object_types'] ) . "')";
	549	+ $current_sql = $this->{$digest['select']}( $digest['id_column'] . ' >= %d AND ' . $digest['id_column'] . ' < %d' );
550	550	$current_scope = $publish ? $this->product_servable_predicate_sql( 't.id', true ) : array(
551	551	'sql' => '',
552	552	'args' => array(),
553	553	);
		@@ -554,10 +554,10 @@
554	554	$stored_scope = $publish ? $this->product_servable_predicate_sql( 'd.object_id', true ) : array(
555	555	'sql' => '',
556	556	'args' => array(),
557	557	);
558		- $current_join = $publish ? " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = t.id LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'" : '';
559		- $stored_join = $publish ? " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = d.object_id LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'" : '';
	558	+ $current_join = $publish ? $this->servable_product_join_sql( 't.id' ) : '';
	559	+ $stored_join = $publish ? $this->servable_product_join_sql( 'd.object_id' ) : '';
560	560
561	561	// Current side: one SQL pass — per-row canonical digests aggregated
562	562	// per bucket inside the DB engine. Raw rows are digested for
563	563	// DETECTION only; hydration goes through filtered REST (ADR 0003).
		@@ -621,27 +621,18 @@
621	621	'match' => $stored_count === $current_count && $stored_digest === $current_digest,
622	622	);
623	623	}
624	624
625		- $max_query =
626		- 'SELECT GREATEST('
627		- . "COALESCE((SELECT MAX(ID) FROM {$wpdb->posts} WHERE post_type IN " . self::PRODUCT_POST_TYPES_SQL
628		- . ' AND post_status NOT IN ' . self::EXCLUDED_POST_STATUSES_SQL . '), 0),'
629		- . ' COALESCE((SELECT MAX(object_id) FROM ' . $this->table_name()
630		- . ' WHERE object_type IN ' . self::OBJECT_TYPES_SQL . '), 0))';
631		- $max_args = array();
632		- if ( 'products' !== $collection || $publish ) {
633		- $live_scope = $publish ? $this->product_servable_predicate_sql( 'live.id', true ) : array(
634		- 'sql' => '',
635		- 'args' => array(),
636		- );
637		- $live_join = $publish ? " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = live.id LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'" : '';
638		- $max_query = 'SELECT GREATEST(COALESCE((SELECT MAX(live.id) FROM (' . $max_sql . ') live' . $live_join . ( '' === $live_scope['sql'] ? '' : ' WHERE ' . $live_scope['sql'] ) . '), 0),'
639		- . ' COALESCE((SELECT MAX(d.object_id) FROM ' . $this->table_name() . ' d'
640		- . ' WHERE d.object_type IN ' . $object_types . '), 0))';
641		- $max_args = $live_scope['args'];
642		- }
643		- $max_id = (int) $wpdb->get_var( empty( $max_args ) ? $max_query : $wpdb->prepare( $max_query, ...$max_args ) );
	625	+ // Completion id: the larger of the last LIVE id under the collection's own
	626	+ // servable predicate and the last STORED id. The live side is MAX(id)
	627	+ // straight off the base table — wrapping the un-windowed per-row digest
	628	+ // SELECT as a derived table just to take its max digested the whole
	629	+ // collection on every scan page (0.4–3 s on real stores; #1805, ADR 0038).
	630	+ $live_max = $this->live_max_id_sql( $collection, $publish );
	631	+ $max_query = 'SELECT GREATEST(COALESCE((' . $live_max['sql'] . '), 0),'
	632	+ . ' COALESCE((SELECT MAX(d.object_id) FROM ' . $this->table_name() . ' d'
	633	+ . ' WHERE d.object_type IN ' . $object_types . '), 0))';
	634	+ $max_id = (int) $wpdb->get_var( empty( $live_max['args'] ) ? $max_query : $wpdb->prepare( $max_query, ...$live_max['args'] ) );
644	635
645	636	return array(
646	637	'buckets' => $buckets,
647	638	'max_id' => $max_id,
		@@ -664,8 +655,9 @@
664	655	public function bucket_drift( array $range ): array {
665	656	global $wpdb;
666	657	$range_start = max( 0, (int) ( $range['start'] ?? 0 ) );
667	658	$range_end = max( 0, (int) ( $range['end'] ?? 0 ) );
	659	+ $digest = Collections::row( 'products' )['digest'];
668	660	$table = $this->table_name();
669	661
670	662	// Same-formula invariant: the current side must digest identically to the stored side.
671	663	$this->raise_group_concat_max_len();
		@@ -673,17 +665,17 @@
673	665	$wpdb->prepare(
674	666	'SELECT cur.id AS id,'
675	667	. " CASE WHEN d.digest IS NULL THEN 'missing_stored' ELSE 'changed' END AS status,"
676	668	. ' d.digest AS stored_digest, cur.crc AS current_digest, cur.object_type AS object_type'
677		- . ' FROM (' . $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' ) . ') cur'
	669	+ . ' FROM (' . $this->{$digest['select']}( $digest['id_column'] . ' >= %d AND ' . $digest['id_column'] . ' < %d' ) . ') cur'
678	670	. " LEFT JOIN {$table} d ON d.object_id = cur.id AND d.object_type = cur.object_type"
679	671	. ' WHERE d.digest IS NULL OR d.digest <> cur.crc'
680	672	. ' UNION ALL'
681	673	. " SELECT d.object_id AS id, 'deleted' AS status, d.digest AS stored_digest, NULL AS current_digest, d.object_type AS object_type"
682	674	. " FROM {$table} d"
683		- . ' WHERE d.object_type IN ' . self::OBJECT_TYPES_SQL
	675	+ . ' WHERE d.object_type IN ' . "('" . implode( "','", $digest['object_types'] ) . "')"
684	676	. ' AND d.object_id >= %d AND d.object_id < %d'
685		- . ' AND NOT ' . $this->live_row_exists_sql( 'd.object_id' )
	677	+ . ' AND NOT ' . $this->{$digest['live_rows']}( 'd.object_id' )
686	678	. ' ORDER BY id ASC',
687	679	$range_start,
688	680	$range_end,
689	681	$range_start,
		@@ -734,15 +726,14 @@
734	726
735	727	$servable_join = '';
736	728	$servable_filter = '';
737	729	$servable_args = array();
738		- if ( 'customers' === $collection ) {
739		- $inner_sql = $this->customer_digest_select_sql( 'u.ID >= %d AND u.ID < %d' );
740		- } elseif ( 'orders' === $collection ) {
741		- // Orders bucket over their own id-space (HPOS o.id / CPT p.ID) via the {id} placeholder.
742		- $inner_sql = $this->order_digest_select_sql( '{id} >= %d AND {id} < %d' );
743		- } else {
744		- $inner_sql = $this->row_digest_select_sql( 'p.ID >= %d AND p.ID < %d' );
	730	+ $digest = Collections::row( $collection )['digest'] ?? null;
	731	+ if ( null === $digest ) {
	732	+ return array();
	733	+ }
	734	+ $inner_sql = $this->{$digest['select']}( $digest['id_column'] . ' >= %d AND ' . $digest['id_column'] . ' < %d' );
	735	+ if ( isset( $digest['servable'] ) ) {
745	736	$servable_join = " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = cur.id";
746	737	if ( 'publish' === ( $filters['status'] ?? '' ) ) {
747	738	$servable_join .= " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent"
748	739	. " AND catalog_post.post_type = 'product_variation'";
		@@ -838,11 +829,10 @@
838	829	public function needs_product_rebuild(): bool {
839	830	global $wpdb;
840	831
841	832	return (bool) $wpdb->get_var(
842		- 'SELECT EXISTS (SELECT 1 FROM ' . $wpdb->posts
843		- . ' WHERE post_type IN ' . self::PRODUCT_POST_TYPES_SQL
844		- . ' AND post_status NOT IN ' . self::EXCLUDED_POST_STATUSES_SQL . ' LIMIT 1)'
	833	+ 'SELECT EXISTS (SELECT 1 FROM ' . $wpdb->posts . ' p'
	834	+ . ' WHERE ' . $this->live_product_predicate_sql( 'p' ) . ' LIMIT 1)'
845	835	. ' AND NOT EXISTS (SELECT 1 FROM ' . $this->table_name()
846	836	. ' WHERE object_type IN ' . self::OBJECT_TYPES_SQL . ' LIMIT 1)'
847	837	);
848	838	}
		@@ -854,8 +844,115 @@
854	844	private function published_product_predicate_sql( string $post_alias, string $parent_alias ): string {
855	845	return "(({$post_alias}.post_type = 'product' AND {$post_alias}.post_status = 'publish')"
856	846	. " OR ({$post_alias}.post_type = 'product_variation' AND {$parent_alias}.post_type = 'product'"
857	847	. " AND {$parent_alias}.post_status = 'publish'))";
	848	+ }
	849	+
	850	+ /**
	851	+ * A LIVE product-space row over a `wp_posts` alias: a product or variation that
	852	+ * is not trashed/auto-draft. ONE spelling for the digest SELECT, the live-row
	853	+ * probes, the rebuild guard and the completion id — the scan is only sound
	854	+ * while every side agrees on what "live" means.
	855	+ */
	856	+ private function live_product_predicate_sql( string $alias ): string {
	857	+ return "{$alias}.post_type IN " . self::PRODUCT_POST_TYPES_SQL
	858	+ . " AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	859	+ }
	860	+
	861	+ /**
	862	+ * A LIVE order over the active order store's alias — `wc_orders` under HPOS,
	863	+ * `wp_posts` under legacy CPT — with the same one-spelling rule as
	864	+ * {@see live_product_predicate_sql}.
	865	+ */
	866	+ private function live_order_predicate_sql( string $alias ): string {
	867	+ if ( $this->orders_are_hpos() ) {
	868	+ return "{$alias}.type = 'shop_order' AND {$alias}.status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	869	+ }
	870	+
	871	+ return "{$alias}.post_type = 'shop_order' AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	872	+ }
	873	+
	874	+ /**
	875	+ * The joins {@see product_servable_predicate_sql} reads through: the post row
	876	+ * behind `$id_expr` as `catalog_post`, and its parent as `parent_product` when
	877	+ * it is a variation. One spelling for every side of the scan.
	878	+ */
	879	+ private function servable_product_join_sql( string $id_expr ): string {
	880	+ global $wpdb;
	881	+
	882	+ return " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = {$id_expr}" . $this->parent_product_join_sql();
	883	+ }
	884	+
	885	+ /**
	886	+ * `catalog_post`'s parent as `parent_product` when it is a variation (NULL otherwise).
	887	+ */
	888	+ private function parent_product_join_sql(): string {
	889	+ global $wpdb;
	890	+
	891	+ return " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'";
	892	+ }
	893	+
	894	+ /**
	895	+ * `MAX(id)` of a collection's LIVE rows under the same predicate its digest
	896	+ * SELECT uses — off the base table, never through a digested row (#1805).
	897	+ *
	898	+ * Customers are every `wp_users` row (#1379), so this is the primary key's end.
	899	+ * Orders and products carry a type/status predicate, so this is one index pass
	900	+ * over the live rows of that type — the honest floor, since no index ends on
	901	+ * the id under a status filter. Under the published product scope the servable
	902	+ * predicate (published, or a variation of a published parent, and not POS-hidden)
	903	+ * applies to the post row itself, exactly as the windowed sides apply it.
	904	+ *
	905	+ * @return array{sql: string, args: array<int, int>}
	906	+ */
	907	+ private function live_max_id_sql( string $collection, bool $publish ): array {
	908	+ $digest = Collections::row( $collection )['digest'] ?? null;
	909	+ if ( null === $digest ) {
	910	+ return array(
	911	+ 'sql' => 'SELECT NULL WHERE 1 = 0',
	912	+ 'args' => array(),
	913	+ );
	914	+ }
	915	+ return $this->{$digest['live_max']}( $publish );
	916	+ }
	917	+
	918	+ private function customer_live_max_id_sql(): array {
	919	+ global $wpdb;
	920	+ return array(
	921	+ 'sql' => "SELECT MAX(u.ID) FROM {$wpdb->users} u",
	922	+ 'args' => array(),
	923	+ );
	924	+ }
	925	+
	926	+ private function order_live_max_id_sql(): array {
	927	+ global $wpdb;
	928	+ if ( $this->orders_are_hpos() ) {
	929	+ $orders_table = $wpdb->prefix . 'wc_orders';
	930	+ return array(
	931	+ 'sql' => "SELECT MAX(o.id) FROM {$orders_table} o WHERE " . $this->live_order_predicate_sql( 'o' ),
	932	+ 'args' => array(),
	933	+ );
	934	+ }
	935	+ return array(
	936	+ 'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_order_predicate_sql( 'p' ),
	937	+ 'args' => array(),
	938	+ );
	939	+ }
	940	+
	941	+ private function product_live_max_id_sql( bool $publish ): array {
	942	+ global $wpdb;
	943	+ if ( ! $publish ) {
	944	+ return array(
	945	+ 'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_product_predicate_sql( 'p' ),
	946	+ 'args' => array(),
	947	+ );
	948	+ }
	949	+ $scope = $this->product_servable_predicate_sql( 'catalog_post.ID', true );
	950	+ return array(
	951	+ 'sql' => "SELECT MAX(catalog_post.ID) FROM {$wpdb->posts} catalog_post" . $this->parent_product_join_sql()
	952	+ . ' WHERE ' . $scope['sql'],
	953	+ 'args' => $scope['args'],
	954	+ );
858	955	}
859	956
860	957	private function product_servable_predicate_sql( string $id_expr, bool $publish ): array {
861	958	$predicates = array(