jetpack / jetpack_vendor / automattic / jetpack-sync / src / replicastore / class-table-checksum-users.php

class-table-checksum-users.php in Jetpack – WP Security, Backup, Speed, & Growth 16.2, at jetpack_vendor/automattic/jetpack-sync/src/replicastore/class-table-checksum-users.php

190 lines 5.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Table Checksums Class.
4	*
5	* @package automattic/jetpack-sync
6	*/
7
8	namespace Automattic\Jetpack\Sync\Replicastore;
9
10	use Exception;
11
12	if ( ! defined( 'ABSPATH' ) ) {
13	exit( 0 );
14	}
15
16	/**
17	* Class to handle Table Checksums for the Users table.
18	*/
19	class Table_Checksum_Users extends Table_Checksum {
20
21	/**
22	* Returns the checksum query. All validation of fields and configurations are expected to occur prior to usage.
23	*
24	* @param int\|null $range_from The start of the range.
25	* @param int\|null $range_to The end of the range.
26	* @param array\|null $filter_values Additional filter values. Not used at the moment.
27	* @param bool $granular_result If the function should return a granular result.
28	*
29	* @return string
30	*
31	* @throws Exception Throws an exception if validation fails in the internal function calls.
32	*/
33	protected function build_checksum_query( $range_from = null, $range_to = null, $filter_values = null, $granular_result = false ) {
34	global $wpdb;
35
36	// Escape the salt.
37	$salt = $wpdb->prepare( '%s', $this->salt );
38
39	// Prepare the compound key.
40	$key_fields = array();
41
42	// Prefix the fields with the table name, to avoid clashes in queries with sub-queries (e.g. meta tables).
43	foreach ( $this->key_fields as $field ) {
44	$key_fields[] = $this->table . '.' . $field;
45	}
46
47	$key_fields = implode( ',', $key_fields );
48
49	// Prepare the checksum fields.
50	$checksum_fields = array();
51	// Prefix the fields with the table name, to avoid clashes in queries with sub-queries (e.g. meta tables).
52	foreach ( $this->checksum_fields as $field ) {
53	$checksum_fields[] = $this->table . '.' . $field;
54	}
55	// Apply latin1 conversion if enabled.
56	if ( $this->perform_text_conversion ) {
57	// Convert text fields to allow for encoding discrepancies as WP.com is latin1.
58	foreach ( $this->checksum_text_fields as $field ) {
59	$checksum_fields[] = 'CONVERT(' . $this->table . '.' . $field . ' using latin1 )';
60	}
61	} else {
62	// Conversion disabled, default to table prefixing.
63	foreach ( $this->checksum_text_fields as $field ) {
64	$checksum_fields[] = $this->table . '.' . $field;
65	}
66	}
67
68	$checksum_fields_string = implode( ',', array_merge( $checksum_fields, array( $salt ) ) );
69
70	$additional_fields = '';
71	if ( $granular_result ) {
72	// TODO uniq the fields as sometimes(most) range_index is the key and there's no need to select the same field twice.
73	$additional_fields = "
74	{$this->table}.{$this->range_field} as range_index,
75	{$key_fields},
76	";
77	}
78
79	$filter_stamenet = $this->build_filter_statement( $range_from, $range_to, $filter_values );
80
81	// usermeta join to limit on user_level.
82	$join_statement = "JOIN {$wpdb->usermeta} as um_table ON um_table.user_id = {$this->table}.ID";
83
84	$query = "
85	SELECT
86	{$additional_fields}
87	SUM(
88	CRC32(
89	CONCAT_WS( '#', {$salt}, {$checksum_fields_string} )
90	)
91	) AS checksum
92	FROM
93	{$this->table}
94	{$join_statement}
95	WHERE
96	{$filter_stamenet}
97	AND um_table.meta_key = '{$wpdb->prefix}user_level'
98	AND um_table.meta_value > 0
99	";
100
101	/**
102	* We need the GROUP BY only for compound keys.
103	*/
104	if ( $granular_result ) {
105	$query .= "
106	GROUP BY {$key_fields}
107	LIMIT 9999999
108	";
109	}
110
111	return $query;
112	}
113
114	/**
115	* Obtain the min-max values (edges) of the range.
116	*
117	* @param int\|null $range_from The start of the range.
118	* @param int\|null $range_to The end of the range.
119	* @param int\|null $limit How many values to return.
120	*
121	* @return array\|object\|void
122	* @throws Exception Throws an exception if validation fails on the internal function calls.
123	*/
124	public function get_range_edges( $range_from = null, $range_to = null, $limit = null ) {
125	global $wpdb;
126
127	$this->validate_fields( array( $this->range_field ) );
128
129	// `trim()` to make sure we don't add the statement if it's empty.
130	$filters = trim( $this->build_filter_statement( $range_from, $range_to ) );
131
132	$filter_statement = '';
133	if ( ! empty( $filters ) ) {
134	$filter_statement = "
135	JOIN {$wpdb->usermeta} as um_table ON um_table.user_id = {$this->table}.ID
136	WHERE
137	{$filters}
138	AND um_table.meta_key = '{$wpdb->prefix}user_level'
139	AND um_table.meta_value > 0
140	";
141	}
142
143	$query = "
144	SELECT
145	MIN({$this->range_field}) as min_range,
146	MAX({$this->range_field}) as max_range,
147	COUNT( {$this->range_field} ) as item_count
148	FROM
149	";
150
151	/**
152	* If `$limit` is not specified, we can directly use the table.
153	*/
154	if ( ! $limit ) {
155	$query .= "
156	{$this->table}
157	{$filter_statement}
158	";
159	} else {
160	/**
161	* If there is `$limit` specified, we can't directly use `MIN/MAX()` as they don't work with `LIMIT`.
162	* That's why we will alter the query for this case.
163	*/
164	$limit = intval( $limit );
165
166	$query .= "
167	(
168	SELECT
169	{$this->range_field}
170	FROM
171	{$this->table}
172	{$filter_statement}
173	ORDER BY
174	{$this->range_field} ASC
175	LIMIT {$limit}
176	) as ids_query
177	";
178	}
179
180	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
181	$result = $wpdb->get_row( $query, ARRAY_A );
182
183	if ( ! $result \|\| ! is_array( $result ) ) {
184	throw new Exception( 'Unable to get range edges' );
185	}
186
187	return $result;
188	}
189	}
190