← All changes | includes/Sync/Digest_Index.php +158 -60 1.10.3 → 1.10.22 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	}
		@@ -848,14 +838,122 @@
848	838	}
849	839
850	840	/**
851	841	* The readable-catalog predicate over a post alias and its parent alias — a published product,
852		- * or a variation whose parent product is published. One home for the rule.
	842	+ * or a variation whose parent product is published. One home for the rule. The wcpos/v2 products
	843	+ * ID fast path ({@see \WCPOS\WooCommercePOS\API\V2\Proxy\Products_Proxy_Behavior::fast_response()}) calls it too.
853	844	*/
854		- private function published_product_predicate_sql( string $post_alias, string $parent_alias ): string {
	845	+ public static function published_product_predicate_sql( string $post_alias, string $parent_alias ): string {
855	846	return "(({$post_alias}.post_type = 'product' AND {$post_alias}.post_status = 'publish')"
856	847	. " OR ({$post_alias}.post_type = 'product_variation' AND {$parent_alias}.post_type = 'product'"
857	848	. " AND {$parent_alias}.post_status = 'publish'))";
	849	+ }
	850	+
	851	+ /**
	852	+ * A LIVE product-space row over a `wp_posts` alias: a product or variation that
	853	+ * is not trashed/auto-draft. ONE spelling for the digest SELECT, the live-row
	854	+ * probes, the rebuild guard and the completion id — the scan is only sound
	855	+ * while every side agrees on what "live" means.
	856	+ */
	857	+ private function live_product_predicate_sql( string $alias ): string {
	858	+ return "{$alias}.post_type IN " . self::PRODUCT_POST_TYPES_SQL
	859	+ . " AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	860	+ }
	861	+
	862	+ /**
	863	+ * A LIVE order over the active order store's alias — `wc_orders` under HPOS,
	864	+ * `wp_posts` under legacy CPT — with the same one-spelling rule as
	865	+ * {@see live_product_predicate_sql}.
	866	+ */
	867	+ private function live_order_predicate_sql( string $alias ): string {
	868	+ if ( $this->orders_are_hpos() ) {
	869	+ return "{$alias}.type = 'shop_order' AND {$alias}.status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	870	+ }
	871	+
	872	+ return "{$alias}.post_type = 'shop_order' AND {$alias}.post_status NOT IN " . self::EXCLUDED_POST_STATUSES_SQL;
	873	+ }
	874	+
	875	+ /**
	876	+ * The joins {@see product_servable_predicate_sql} reads through: the post row
	877	+ * behind `$id_expr` as `catalog_post`, and its parent as `parent_product` when
	878	+ * it is a variation. One spelling for every side of the scan.
	879	+ */
	880	+ private function servable_product_join_sql( string $id_expr ): string {
	881	+ global $wpdb;
	882	+
	883	+ return " INNER JOIN {$wpdb->posts} catalog_post ON catalog_post.ID = {$id_expr}" . $this->parent_product_join_sql();
	884	+ }
	885	+
	886	+ /**
	887	+ * `catalog_post`'s parent as `parent_product` when it is a variation (NULL otherwise).
	888	+ */
	889	+ private function parent_product_join_sql(): string {
	890	+ global $wpdb;
	891	+
	892	+ return " LEFT JOIN {$wpdb->posts} parent_product ON parent_product.ID = catalog_post.post_parent AND catalog_post.post_type = 'product_variation'";
	893	+ }
	894	+
	895	+ /**
	896	+ * `MAX(id)` of a collection's LIVE rows under the same predicate its digest
	897	+ * SELECT uses — off the base table, never through a digested row (#1805).
	898	+ *
	899	+ * Customers are every `wp_users` row (#1379), so this is the primary key's end.
	900	+ * Orders and products carry a type/status predicate, so this is one index pass
	901	+ * over the live rows of that type — the honest floor, since no index ends on
	902	+ * the id under a status filter. Under the published product scope the servable
	903	+ * predicate (published, or a variation of a published parent, and not POS-hidden)
	904	+ * applies to the post row itself, exactly as the windowed sides apply it.
	905	+ *
	906	+ * @return array{sql: string, args: array<int, int>}
	907	+ */
	908	+ private function live_max_id_sql( string $collection, bool $publish ): array {
	909	+ $digest = Collections::row( $collection )['digest'] ?? null;
	910	+ if ( null === $digest ) {
	911	+ return array(
	912	+ 'sql' => 'SELECT NULL WHERE 1 = 0',
	913	+ 'args' => array(),
	914	+ );
	915	+ }
	916	+ return $this->{$digest['live_max']}( $publish );
	917	+ }
	918	+
	919	+ private function customer_live_max_id_sql(): array {
	920	+ global $wpdb;
	921	+ return array(
	922	+ 'sql' => "SELECT MAX(u.ID) FROM {$wpdb->users} u",
	923	+ 'args' => array(),
	924	+ );
	925	+ }
	926	+
	927	+ private function order_live_max_id_sql(): array {
	928	+ global $wpdb;
	929	+ if ( $this->orders_are_hpos() ) {
	930	+ $orders_table = $wpdb->prefix . 'wc_orders';
	931	+ return array(
	932	+ 'sql' => "SELECT MAX(o.id) FROM {$orders_table} o WHERE " . $this->live_order_predicate_sql( 'o' ),
	933	+ 'args' => array(),
	934	+ );
	935	+ }
	936	+ return array(
	937	+ 'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_order_predicate_sql( 'p' ),
	938	+ 'args' => array(),
	939	+ );
	940	+ }
	941	+
	942	+ private function product_live_max_id_sql( bool $publish ): array {
	943	+ global $wpdb;
	944	+ if ( ! $publish ) {
	945	+ return array(
	946	+ 'sql' => "SELECT MAX(p.ID) FROM {$wpdb->posts} p WHERE " . $this->live_product_predicate_sql( 'p' ),
	947	+ 'args' => array(),
	948	+ );
	949	+ }
	950	+ $scope = $this->product_servable_predicate_sql( 'catalog_post.ID', true );
	951	+ return array(
	952	+ 'sql' => "SELECT MAX(catalog_post.ID) FROM {$wpdb->posts} catalog_post" . $this->parent_product_join_sql()
	953	+ . ' WHERE ' . $scope['sql'],
	954	+ 'args' => $scope['args'],
	955	+ );
858	956	}
859	957
860	958	private function product_servable_predicate_sql( string $id_expr, bool $publish ): array {
861	959	$predicates = array(