jetpack / modules / sitemaps / sitemap-librarian.php

sitemap-librarian.php in Jetpack – WP Security, Backup, Speed, & Growth 16.3-a.5, at modules/sitemaps/sitemap-librarian.php

568 lines 16.4 KB

Raw Download Zip

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	if ( ! defined( 'ABSPATH' ) ) {
14	exit( 0 );
15	}
16
17	/* Ensure sitemap constants are available. */
18	require_once __DIR__ . '/sitemap-constants.php';
19
20	/**
21	* This object handles any database interaction required
22	* for sitemap generation.
23	*
24	* @since 4.8.0
25	*/
26	class Jetpack_Sitemap_Librarian {
27
28	/**
29	* Sanitized posts table column lists, keyed by table name.
30	*
31	* Keying by table name keeps a process that switches blogs from reusing
32	* one site's column list against another site's posts table. The cache
33	* is static because the librarian is constructed fresh on every request
34	* that builds a sitemap, while the underlying schema is not.
35	*
36	* @var array
37	*/
38	private static $post_columns_cache = array();
39
40	/**
41	* Retrieve a single sitemap with given name and type.
42	* Returns null if no such sitemap exists.
43	*
44	* @access public
45	* @since 4.8.0
46	*
47	* @param string $name Name of the sitemap to be retrieved.
48	* @param string $type Type of the sitemap to be retrieved.
49	*
50	* @return array $args {
51	* @type int $id ID number of the sitemap in the database.
52	* @type string $timestamp Most recent timestamp of the resources pointed to.
53	* @type string $name Name of the sitemap in the database.
54	* @type string $type Type of the sitemap in the database.
55	* @type string $text The content of the sitemap.
56	* }
57	*/
58	public function read_sitemap_data( $name, $type ) {
59	$post_array = get_posts(
60	array(
61	'numberposts' => 1,
62	'title' => $name,
63	'post_type' => $type,
64	'post_status' => 'draft',
65	)
66	);
67
68	$the_post = array_shift( $post_array );
69
70	if ( null === $the_post ) {
71	return null;
72	} else {
73	return array(
74	'id' => $the_post->ID,
75	'timestamp' => $the_post->post_date,
76	'name' => $the_post->post_title,
77	'type' => $the_post->post_type,
78	'text' => base64_decode( $the_post->post_content ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_decode
79	);
80	}
81	}
82
83	/**
84	* Store a sitemap of given type and index in the database.
85	* Note that the timestamp is reencoded as 'Y-m-d H:i:s'.
86	*
87	* If a sitemap with that type and name does not exist, create it.
88	* If a sitemap with that type and name does exist, update it.
89	*
90	* This method uses get_current_sitemap_post_id() for efficiency,
91	* as it only retrieves the post ID, which will be typically cached in the persistent object cache.
92	* This approach avoids loading unnecessary data (like post content) into memory,
93	* unlike using read_sitemap_data() which would retrieve the full post object.
94	*
95	* @access public
96	* @since 4.8.0
97	*
98	* @param string $index Index of the sitemap to be stored.
99	* @param string $type Type of the sitemap to be stored.
100	* @param string $contents Contents of the sitemap to be stored.
101	* @param string $timestamp Timestamp of the sitemap to be stored, in 'YYYY-MM-DD hh:mm:ss' format.
102	*/
103	public function store_sitemap_data( $index, $type, $contents, $timestamp ) {
104	$name = jp_sitemap_filename( $type, $index );
105
106	$post_id = $this->get_current_sitemap_post_id( $name, $type );
107
108	if ( null === $post_id ) {
109	// Post does not exist.
110	wp_insert_post(
111	array(
112	'post_title' => $name,
113	'post_content' => base64_encode( $contents ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
114	'post_type' => $type,
115	'post_date' => gmdate( 'Y-m-d H:i:s', strtotime( $timestamp ) ),
116	)
117	);
118	} else {
119	// Post does exist.
120	wp_insert_post(
121	array(
122	'ID' => $post_id,
123	'post_title' => $name,
124	'post_content' => base64_encode( $contents ), // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
125	'post_type' => $type,
126	'post_date' => gmdate( 'Y-m-d H:i:s', strtotime( $timestamp ) ),
127	)
128	);
129	}
130	}
131
132	/**
133	* Get the current sitemap post ID.
134	*
135	* @param string $name The name of the sitemap.
136	* @param string $type The type of the sitemap.
137	* @return int\|null The post ID if it exists, null otherwise.
138	*/
139	private function get_current_sitemap_post_id( $name, $type ) {
140	$args = array(
141	'post_type' => $type,
142	'post_status' => 'draft',
143	'posts_per_page' => 1,
144	'title' => $name,
145	'fields' => 'ids',
146	);
147
148	$query = new WP_Query( $args );
149	$posts = $query->posts;
150	return is_array( $posts ) && $posts ? $posts[0] : null;
151	}
152	/**
153	* Delete a sitemap by name and type.
154	*
155	* @access public
156	* @since 4.8.0
157	*
158	* @param string $name Row name.
159	* @param string $type Row type.
160	*
161	* @return bool 'true' if a row was deleted, 'false' otherwise.
162	*/
163	public function delete_sitemap_data( $name, $type ) {
164	$the_post = $this->read_sitemap_data( $name, $type );
165
166	if ( null === $the_post ) {
167	return false;
168	} else {
169	wp_delete_post( $the_post['id'] );
170	return true;
171	}
172	}
173
174	/**
175	* Retrieve the contents of a sitemap with given name and type.
176	* If no such sitemap exists, return the empty string. Note that the
177	* returned string is run through wp_specialchars_decode.
178	*
179	* @access public
180	* @since 4.8.0
181	*
182	* @param string $name Row name.
183	* @param string $type Row type.
184	*
185	* @return string Text of the specified sitemap, or the empty string.
186	*/
187	public function get_sitemap_text( $name, $type ) {
188	$row = $this->read_sitemap_data( $name, $type );
189
190	if ( null === $row ) {
191	return '';
192	} else {
193	return $row['text'];
194	}
195	}
196
197	/**
198	* Delete numbered sitemaps named prefix-(p+1), prefix-(p+2), ...
199	* until the first nonexistent sitemap is found.
200	*
201	* @access public
202	* @since 4.8.0
203	*
204	* @param int $position Number before the first sitemap to be deleted.
205	* @param string $type Sitemap type.
206	*/
207	public function delete_numbered_sitemap_rows_after( $position, $type ) {
208	$any_left = true;
209
210	while ( true === $any_left ) {
211	++$position;
212	$name = jp_sitemap_filename( $type, $position );
213	$any_left = $this->delete_sitemap_data( $name, $type );
214	}
215	}
216
217	/**
218	* Deletes all stored sitemap data.
219	*
220	* @access public
221	* @since 4.8.0
222	*/
223	public function delete_all_stored_sitemap_data() {
224	$this->delete_sitemap_type_data( JP_MASTER_SITEMAP_TYPE );
225	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_TYPE );
226	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_INDEX_TYPE );
227	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_TYPE );
228	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_INDEX_TYPE );
229	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_TYPE );
230	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_INDEX_TYPE );
231	}
232
233	/**
234	* Deletes all sitemap data of specific type
235	*
236	* @access protected
237	* @since 5.3.0
238	*
239	* @param String $type Type of sitemap.
240	*/
241	protected function delete_sitemap_type_data( $type ) {
242	$ids = get_posts(
243	array(
244	'post_type' => $type,
245	'post_status' => 'draft',
246	'fields' => 'ids',
247	)
248	);
249
250	foreach ( $ids as $id ) {
251	wp_trash_post( $id );
252	}
253	}
254
255	/**
256	* Retrieve the timestamps of named sitemaps of a given type, keyed by filename.
257	*
258	* Looking rows up by name is what lets a caller avoid assuming the Nth row of
259	* a type is file N, which an interrupted cleanup can make false by rewriting a
260	* row and moving it in ID order. Only the named rows are read, in batches, so
261	* the result is bounded by what was asked for rather than by how many sitemap
262	* rows the site has. Names with no stored row are absent from the result.
263	*
264	* @access public
265	* @since 16.2
266	*
267	* @param string $type Type of the sitemap rows to retrieve.
268	* @param array $names Sitemap filenames to look for.
269	*
270	* @return array Map of sitemap filename to its 'YYYY-MM-DD hh:mm:ss' timestamp.
271	*/
272	public function query_sitemap_timestamps( $type, $names ) {
273	global $wpdb;
274
275	$timestamps = array();
276
277	foreach ( array_chunk( (array) $names, JP_SITEMAP_BATCH_SIZE ) as $chunk ) {
278	$placeholders = implode( ', ', array_fill( 0, count( $chunk ), '%s' ) );
279
280	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders is a generated list of %s.
281	$sql = "SELECT post_title, post_date
282	FROM $wpdb->posts
283	WHERE post_type=%s
284	AND post_status=%s
285	AND post_title IN ( $placeholders );";
286	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
287
288	$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
289	$wpdb->prepare(
290	$sql, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared right here.
291	array_merge( array( $type, 'draft' ), array_values( $chunk ) )
292	),
293	ARRAY_A
294	);
295
296	foreach ( (array) $rows as $row ) {
297	$timestamps[ $row['post_title'] ] = $row['post_date'];
298	}
299	}
300
301	return $timestamps;
302	}
303
304	/**
305	* Retrieve an array of sitemap rows (of a given type) sorted by ID.
306	*
307	* Returns the smallest $num_posts sitemap rows (measured by ID)
308	* of the given type which are larger than $from_id.
309	*
310	* @access public
311	* @since 4.8.0
312	*
313	* @param string $type Type of the sitemap rows to retrieve.
314	* @param int $from_id Greatest lower bound of retrieved sitemap post IDs.
315	* @param int $num_posts Largest number of sitemap posts to retrieve.
316	*
317	* @return array The sitemaps, as an array of associative arrays with
318	* keys ID, post_title, and post_date. The post content is
319	* deliberately excluded to keep memory usage low.
320	*/
321	public function query_sitemaps_after_id( $type, $from_id, $num_posts ) {
322	global $wpdb;
323
324	return $wpdb->get_results(
325	$wpdb->prepare(
326	"SELECT ID, post_title, post_date
327	FROM $wpdb->posts
328	WHERE post_type=%s
329	AND post_status=%s
330	AND ID>%d
331	ORDER BY ID ASC
332	LIMIT %d;",
333	$type,
334	'draft',
335	$from_id,
336	$num_posts
337	),
338	ARRAY_A
339	); // WPCS: db call ok; no-cache ok.
340	}
341
342	/**
343	* Retrieve an array of posts sorted by ID.
344	*
345	* More precisely, returns the smallest $num_posts posts
346	* (measured by ID) which are larger than $from_id.
347	*
348	* @access public
349	* @since 4.8.0
350	*
351	* @param int $from_id Greatest lower bound of retrieved post IDs.
352	* @param int $num_posts Largest number of posts to retrieve.
353	*
354	* @return array The posts.
355	*/
356	public function query_posts_after_id( $from_id, $num_posts ) {
357	global $wpdb;
358
359	// Get the list of post types to include and prepare for query.
360	$post_types = Jetpack_Options::get_option_and_ensure_autoload(
361	'jetpack_sitemap_post_types',
362	array( 'page', 'post' )
363	);
364	foreach ( (array) $post_types as $i => $post_type ) {
365	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
366	}
367	$post_types_list = implode( ',', $post_types );
368
369	$columns_list = $this->get_sanitized_post_columns( $wpdb );
370
371	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
372	return $wpdb->get_results(
373	$wpdb->prepare(
374	"SELECT $columns_list
375	FROM $wpdb->posts
376	WHERE post_status='publish'
377	AND post_type IN ($post_types_list)
378	AND ID>%d
379	ORDER BY ID ASC
380	LIMIT %d;",
381	$from_id,
382	$num_posts
383	)
384	);
385	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
386	}
387
388	/**
389	* Get the most recent timestamp among approved comments for the given post_id.
390	*
391	* @access public
392	* @since 4.8.0
393	*
394	* @param int $post_id Post identifier.
395	*
396	* @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.
397	*/
398	public function query_latest_approved_comment_time_on_post( $post_id ) {
399	global $wpdb;
400
401	return $wpdb->get_var(
402	$wpdb->prepare(
403	"SELECT MAX(comment_date_gmt)
404	FROM $wpdb->comments
405	WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_type in ( '', 'comment' )",
406	$post_id
407	)
408	);
409	}
410
411	/**
412	* Retrieve an array of image posts sorted by ID.
413	*
414	* More precisely, returns the smallest $num_posts image posts
415	* (measured by ID) which are larger than $from_id.
416	*
417	* @access public
418	* @since 4.8.0
419	*
420	* @param int $from_id Greatest lower bound of retrieved image post IDs.
421	* @param int $num_posts Largest number of image posts to retrieve.
422	*
423	* @return array The posts, without the post_content and
424	* post_content_filtered columns.
425	*/
426	public function query_images_after_id( $from_id, $num_posts ) {
427	global $wpdb;
428
429	$columns_list = $this->get_sanitized_post_columns( $wpdb );
430
431	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
432	return $wpdb->get_results(
433	$wpdb->prepare(
434	"SELECT $columns_list
435	FROM $wpdb->posts
436	WHERE post_type='attachment'
437	AND post_mime_type LIKE %s
438	AND ID>%d
439	ORDER BY ID ASC
440	LIMIT %d;",
441	'image/%',
442	$from_id,
443	$num_posts
444	)
445	);
446	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
447	}
448
449	/**
450	* Retrieve an array of video posts sorted by ID.
451	*
452	* More precisely, returns the smallest $num_posts video posts
453	* (measured by ID) which are larger than $from_id.
454	*
455	* @access public
456	* @since 4.8.0
457	*
458	* @param int $from_id Greatest lower bound of retrieved video post IDs.
459	* @param int $num_posts Largest number of video posts to retrieve.
460	*
461	* @return array The posts.
462	*/
463	public function query_videos_after_id( $from_id, $num_posts ) {
464	global $wpdb;
465
466	return $wpdb->get_results(
467	$wpdb->prepare(
468	"SELECT *
469	FROM $wpdb->posts
470	WHERE post_type='attachment'
471	AND post_mime_type LIKE %s
472	AND ID>%d
473	ORDER BY ID ASC
474	LIMIT %d;",
475	'video/%',
476	$from_id,
477	$num_posts
478	)
479	); // WPCS: db call ok; no-cache ok.
480	}
481
482	/**
483	* Retrieve an array of published posts from the last 2 days.
484	*
485	* @access public
486	* @since 4.8.0
487	*
488	* @param int $num_posts Largest number of posts to retrieve.
489	*
490	* @return array The posts.
491	*/
492	public function query_most_recent_posts( $num_posts ) {
493	global $wpdb;
494
495	$two_days_ago = gmdate( 'Y-m-d', strtotime( '-2 days' ) );
496
497	/**
498	* Filter post types to be included in news sitemap.
499	*
500	* @module sitemaps
501	*
502	* @since 3.9.0
503	*
504	* @param array $post_types Array with post types to include in news sitemap.
505	*/
506	$post_types = apply_filters(
507	'jetpack_sitemap_news_sitemap_post_types',
508	array( 'page', 'post' )
509	);
510
511	foreach ( (array) $post_types as $i => $post_type ) {
512	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
513	}
514
515	$post_types_list = implode( ',', $post_types );
516
517	$columns_list = $this->get_sanitized_post_columns( $wpdb );
518
519	// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
520	return $wpdb->get_results(
521	$wpdb->prepare(
522	"SELECT $columns_list
523	FROM $wpdb->posts
524	WHERE post_status='publish'
525	AND post_date >= '%s'
526	AND post_type IN ($post_types_list)
527	ORDER BY post_date DESC
528	LIMIT %d;",
529	$two_days_ago,
530	$num_posts
531	)
532	);
533	// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
534	}
535
536	/**
537	* Returns all columns from the posts table,
538	* except post_content and post_content_filtered.
539	*
540	* The column list is memoized in self::$post_columns_cache, since this is
541	* called once per batch while building sitemaps.
542	*
543	* A cached entry is only used when it is non-empty. SHOW COLUMNS returns
544	* no rows when the query fails, and treating that as a cache hit would
545	* leave every later query in the process with an empty column list.
546	*
547	* @param object $wpdb The WordPress database object.
548	* @return string The sanitized post columns.
549	*/
550	private function get_sanitized_post_columns( $wpdb ) {
551	$table = $wpdb->posts;
552
553	if ( empty( self::$post_columns_cache[ $table ] ) ) {
554	$columns = array_filter(
555	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
556	$wpdb->get_col( "SHOW COLUMNS FROM $wpdb->posts" ),
557	function ( $column ) {
558	return $column !== 'post_content' && $column !== 'post_content_filtered';
559	}
560	);
561
562	self::$post_columns_cache[ $table ] = implode( ',', array_map( 'esc_sql', $columns ) );
563	}
564
565	return self::$post_columns_cache[ $table ];
566	}
567	}
568