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

jetpack / modules / sitemaps / sitemap-librarian.php

jetpack / modules / sitemaps Last commit date

sitemap-librarian.php

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