sitemap-librarian.php — Jetpack – WP Security, Backup, Speed, & Growth 14.9.2

jetpack / modules / sitemaps / sitemap-librarian.php

jetpack / modules / sitemaps Last commit date

sitemap-librarian.php

482 lines

1	<?php // phpcs:ignore WordPress.Files.FileName.InvalidClassFileName
2	/**
3	* Sitemaps are stored in the database using a custom table. This class
4	* provides a small API for storing and retrieving sitemap data so we can
5	* avoid lots of explicit SQL juggling while building sitemaps. This file
6	* also includes the SQL used to retrieve posts and images to be included
7	* in the sitemaps.
8	*
9	* @since 4.8.0
10	* @package automattic/jetpack
11	*/
12
13	/* Ensure sitemap constants are available. */
14	require_once __DIR__ . '/sitemap-constants.php';
15
16	/**
17	* This object handles any database interaction required
18	* for sitemap generation.
19	*
20	* @since 4.8.0
21	*/
22	class Jetpack_Sitemap_Librarian {
23
24	/**
25	* Retrieve a single sitemap with given name and type.
26	* Returns null if no such sitemap exists.
27	*
28	* @access public
29	* @since 4.8.0
30	*
31	* @param string $name Name of the sitemap to be retrieved.
32	* @param string $type Type of the sitemap to be retrieved.
33	*
34	* @return array $args {
35	* @type int $id ID number of the sitemap in the database.
36	* @type string $timestamp Most recent timestamp of the resources pointed to.
37	* @type string $name Name of the sitemap in the database.
38	* @type string $type Type of the sitemap in the database.
39	* @type string $text The content of the sitemap.
40	* }
41	*/
42	public function read_sitemap_data( $name, $type ) {
43	$post_array = get_posts(
44	array(
45	'numberposts' => 1,
46	'title' => $name,
47	'post_type' => $type,
48	'post_status' => 'draft',
49	)
50	);
51
52	$the_post = array_shift( $post_array );
53
54	if ( null === $the_post ) {
55	return null;
56	} else {
57	return array(
58	'id' => $the_post->ID,
59	'timestamp' => $the_post->post_date,
60	'name' => $the_post->post_title,
61	'type' => $the_post->post_type,
62	'text' => base64_decode( $the_post->post_content ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_decode
63	);
64	}
65	}
66
67	/**
68	* Store a sitemap of given type and index in the database.
69	* Note that the timestamp is reencoded as 'Y-m-d H:i:s'.
70	*
71	* If a sitemap with that type and name does not exist, create it.
72	* If a sitemap with that type and name does exist, update it.
73	*
74	* This method uses get_current_sitemap_post_id() for efficiency,
75	* as it only retrieves the post ID, which will be typically cached in the persistent object cache.
76	* This approach avoids loading unnecessary data (like post content) into memory,
77	* unlike using read_sitemap_data() which would retrieve the full post object.
78	*
79	* @access public
80	* @since 4.8.0
81	*
82	* @param string $index Index of the sitemap to be stored.
83	* @param string $type Type of the sitemap to be stored.
84	* @param string $contents Contents of the sitemap to be stored.
85	* @param string $timestamp Timestamp of the sitemap to be stored, in 'YYYY-MM-DD hh:mm:ss' format.
86	*/
87	public function store_sitemap_data( $index, $type, $contents, $timestamp ) {
88	$name = jp_sitemap_filename( $type, $index );
89
90	$post_id = $this->get_current_sitemap_post_id( $name, $type );
91
92	if ( null === $post_id ) {
93	// Post does not exist.
94	wp_insert_post(
95	array(
96	'post_title' => $name,
97	'post_content' => base64_encode( $contents ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
98	'post_type' => $type,
99	'post_date' => gmdate( 'Y-m-d H:i:s', strtotime( $timestamp ) ),
100	)
101	);
102	} else {
103	// Post does exist.
104	wp_insert_post(
105	array(
106	'ID' => $post_id,
107	'post_title' => $name,
108	'post_content' => base64_encode( $contents ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
109	'post_type' => $type,
110	'post_date' => gmdate( 'Y-m-d H:i:s', strtotime( $timestamp ) ),
111	)
112	);
113	}
114	}
115
116	/**
117	* Get the current sitemap post ID.
118	*
119	* @param string $name The name of the sitemap.
120	* @param string $type The type of the sitemap.
121	* @return int\|null The post ID if it exists, null otherwise.
122	*/
123	private function get_current_sitemap_post_id( $name, $type ) {
124	$args = array(
125	'post_type' => $type,
126	'post_status' => 'draft',
127	'posts_per_page' => 1,
128	'title' => $name,
129	'fields' => 'ids',
130	);
131
132	$query = new WP_Query( $args );
133	return $query->posts ? $query->posts[0] : null;
134	}
135	/**
136	* Delete a sitemap by name and type.
137	*
138	* @access public
139	* @since 4.8.0
140	*
141	* @param string $name Row name.
142	* @param string $type Row type.
143	*
144	* @return bool 'true' if a row was deleted, 'false' otherwise.
145	*/
146	public function delete_sitemap_data( $name, $type ) {
147	$the_post = $this->read_sitemap_data( $name, $type );
148
149	if ( null === $the_post ) {
150	return false;
151	} else {
152	wp_delete_post( $the_post['id'] );
153	return true;
154	}
155	}
156
157	/**
158	* Retrieve the contents of a sitemap with given name and type.
159	* If no such sitemap exists, return the empty string. Note that the
160	* returned string is run through wp_specialchars_decode.
161	*
162	* @access public
163	* @since 4.8.0
164	*
165	* @param string $name Row name.
166	* @param string $type Row type.
167	*
168	* @return string Text of the specified sitemap, or the empty string.
169	*/
170	public function get_sitemap_text( $name, $type ) {
171	$row = $this->read_sitemap_data( $name, $type );
172
173	if ( null === $row ) {
174	return '';
175	} else {
176	return $row['text'];
177	}
178	}
179
180	/**
181	* Delete numbered sitemaps named prefix-(p+1), prefix-(p+2), ...
182	* until the first nonexistent sitemap is found.
183	*
184	* @access public
185	* @since 4.8.0
186	*
187	* @param int $position Number before the first sitemap to be deleted.
188	* @param string $type Sitemap type.
189	*/
190	public function delete_numbered_sitemap_rows_after( $position, $type ) {
191	$any_left = true;
192
193	while ( true === $any_left ) {
194	++$position;
195	$name = jp_sitemap_filename( $type, $position );
196	$any_left = $this->delete_sitemap_data( $name, $type );
197	}
198	}
199
200	/**
201	* Deletes all stored sitemap data.
202	*
203	* @access public
204	* @since 4.8.0
205	*/
206	public function delete_all_stored_sitemap_data() {
207	$this->delete_sitemap_type_data( JP_MASTER_SITEMAP_TYPE );
208	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_TYPE );
209	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_INDEX_TYPE );
210	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_TYPE );
211	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_INDEX_TYPE );
212	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_TYPE );
213	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_INDEX_TYPE );
214	}
215
216	/**
217	* Deletes all sitemap data of specific type
218	*
219	* @access protected
220	* @since 5.3.0
221	*
222	* @param String $type Type of sitemap.
223	*/
224	protected function delete_sitemap_type_data( $type ) {
225	$ids = get_posts(
226	array(
227	'post_type' => $type,
228	'post_status' => 'draft',
229	'fields' => 'ids',
230	)
231	);
232
233	foreach ( $ids as $id ) {
234	wp_trash_post( $id );
235	}
236	}
237
238	/**
239	* Retrieve an array of sitemap rows (of a given type) sorted by ID.
240	*
241	* Returns the smallest $num_posts sitemap rows (measured by ID)
242	* of the given type which are larger than $from_id.
243	*
244	* @access public
245	* @since 4.8.0
246	*
247	* @param string $type Type of the sitemap rows to retrieve.
248	* @param int $from_id Greatest lower bound of retrieved sitemap post IDs.
249	* @param int $num_posts Largest number of sitemap posts to retrieve.
250	*
251	* @return array The sitemaps, as an array of associative arrays.
252	*/
253	public function query_sitemaps_after_id( $type, $from_id, $num_posts ) {
254	global $wpdb;
255
256	return $wpdb->get_results(
257	$wpdb->prepare(
258	"SELECT *
259	FROM $wpdb->posts
260	WHERE post_type=%s
261	AND post_status=%s
262	AND ID>%d
263	ORDER BY ID ASC
264	LIMIT %d;",
265	$type,
266	'draft',
267	$from_id,
268	$num_posts
269	),
270	ARRAY_A
271	); // WPCS: db call ok; no-cache ok.
272	}
273
274	/**
275	* Retrieve an array of posts sorted by ID.
276	*
277	* More precisely, returns the smallest $num_posts posts
278	* (measured by ID) which are larger than $from_id.
279	*
280	* @access public
281	* @since 4.8.0
282	*
283	* @param int $from_id Greatest lower bound of retrieved post IDs.
284	* @param int $num_posts Largest number of posts to retrieve.
285	*
286	* @return array The posts.
287	*/
288	public function query_posts_after_id( $from_id, $num_posts ) {
289	global $wpdb;
290
291	// Get the list of post types to include and prepare for query.
292	$post_types = Jetpack_Options::get_option_and_ensure_autoload(
293	'jetpack_sitemap_post_types',
294	array( 'page', 'post' )
295	);
296	foreach ( (array) $post_types as $i => $post_type ) {
297	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
298	}
299	$post_types_list = implode( ',', $post_types );
300
301	$columns_list = $this->get_sanitized_post_columns( $wpdb );
302
303	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
304	return $wpdb->get_results(
305	$wpdb->prepare(
306	"SELECT $columns_list
307	FROM $wpdb->posts
308	WHERE post_status='publish'
309	AND post_type IN ($post_types_list)
310	AND ID>%d
311	ORDER BY ID ASC
312	LIMIT %d;",
313	$from_id,
314	$num_posts
315	)
316	);
317	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
318	}
319
320	/**
321	* Get the most recent timestamp among approved comments for the given post_id.
322	*
323	* @access public
324	* @since 4.8.0
325	*
326	* @param int $post_id Post identifier.
327	*
328	* @return string Timestamp in 'Y-m-d h:i:s' format (UTC) of the most recent comment on the given post, or null if no such comments exist.
329	*/
330	public function query_latest_approved_comment_time_on_post( $post_id ) {
331	global $wpdb;
332
333	return $wpdb->get_var(
334	$wpdb->prepare(
335	"SELECT MAX(comment_date_gmt)
336	FROM $wpdb->comments
337	WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_type in ( '', 'comment' )",
338	$post_id
339	)
340	);
341	}
342
343	/**
344	* Retrieve an array of image posts sorted by ID.
345	*
346	* More precisely, returns the smallest $num_posts image posts
347	* (measured by ID) which are larger than $from_id.
348	*
349	* @access public
350	* @since 4.8.0
351	*
352	* @param int $from_id Greatest lower bound of retrieved image post IDs.
353	* @param int $num_posts Largest number of image posts to retrieve.
354	*
355	* @return array The posts.
356	*/
357	public function query_images_after_id( $from_id, $num_posts ) {
358	global $wpdb;
359
360	return $wpdb->get_results(
361	$wpdb->prepare(
362	"SELECT *
363	FROM $wpdb->posts
364	WHERE post_type='attachment'
365	AND post_mime_type LIKE %s
366	AND ID>%d
367	ORDER BY ID ASC
368	LIMIT %d;",
369	'image/%',
370	$from_id,
371	$num_posts
372	)
373	); // WPCS: db call ok; no-cache ok.
374	}
375
376	/**
377	* Retrieve an array of video posts sorted by ID.
378	*
379	* More precisely, returns the smallest $num_posts video posts
380	* (measured by ID) which are larger than $from_id.
381	*
382	* @access public
383	* @since 4.8.0
384	*
385	* @param int $from_id Greatest lower bound of retrieved video post IDs.
386	* @param int $num_posts Largest number of video posts to retrieve.
387	*
388	* @return array The posts.
389	*/
390	public function query_videos_after_id( $from_id, $num_posts ) {
391	global $wpdb;
392
393	return $wpdb->get_results(
394	$wpdb->prepare(
395	"SELECT *
396	FROM $wpdb->posts
397	WHERE post_type='attachment'
398	AND post_mime_type LIKE %s
399	AND ID>%d
400	ORDER BY ID ASC
401	LIMIT %d;",
402	'video/%',
403	$from_id,
404	$num_posts
405	)
406	); // WPCS: db call ok; no-cache ok.
407	}
408
409	/**
410	* Retrieve an array of published posts from the last 2 days.
411	*
412	* @access public
413	* @since 4.8.0
414	*
415	* @param int $num_posts Largest number of posts to retrieve.
416	*
417	* @return array The posts.
418	*/
419	public function query_most_recent_posts( $num_posts ) {
420	global $wpdb;
421
422	$two_days_ago = gmdate( 'Y-m-d', strtotime( '-2 days' ) );
423
424	/**
425	* Filter post types to be included in news sitemap.
426	*
427	* @module sitemaps
428	*
429	* @since 3.9.0
430	*
431	* @param array $post_types Array with post types to include in news sitemap.
432	*/
433	$post_types = apply_filters(
434	'jetpack_sitemap_news_sitemap_post_types',
435	array( 'page', 'post' )
436	);
437
438	foreach ( (array) $post_types as $i => $post_type ) {
439	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
440	}
441
442	$post_types_list = implode( ',', $post_types );
443
444	$columns_list = $this->get_sanitized_post_columns( $wpdb );
445
446	// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
447	return $wpdb->get_results(
448	$wpdb->prepare(
449	"SELECT $columns_list
450	FROM $wpdb->posts
451	WHERE post_status='publish'
452	AND post_date >= '%s'
453	AND post_type IN ($post_types_list)
454	ORDER BY post_date DESC
455	LIMIT %d;",
456	$two_days_ago,
457	$num_posts
458	)
459	);
460	// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
461	}
462
463	/**
464	* Returns all columns from the posts table,
465	* except post_content and post_content_filtered.
466	*
467	* @param object $wpdb The WordPress database object.
468	* @return string The sanitized post columns.
469	*/
470	private function get_sanitized_post_columns( $wpdb ) {
471	$columns = array_filter(
472	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
473	$wpdb->get_col( "SHOW COLUMNS FROM $wpdb->posts" ),
474	function ( $column ) {
475	return $column !== 'post_content' && $column !== 'post_content_filtered';
476	}
477	);
478
479	return implode( ',', array_map( 'esc_sql', $columns ) );
480	}
481	}
482