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

jetpack / modules / sitemaps / sitemap-librarian.php

jetpack / modules / sitemaps Last commit date

sitemap-librarian.php

486 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	return $query->posts ? $query->posts[0] : null;
138	}
139	/**
140	* Delete a sitemap by name and type.
141	*
142	* @access public
143	* @since 4.8.0
144	*
145	* @param string $name Row name.
146	* @param string $type Row type.
147	*
148	* @return bool 'true' if a row was deleted, 'false' otherwise.
149	*/
150	public function delete_sitemap_data( $name, $type ) {
151	$the_post = $this->read_sitemap_data( $name, $type );
152
153	if ( null === $the_post ) {
154	return false;
155	} else {
156	wp_delete_post( $the_post['id'] );
157	return true;
158	}
159	}
160
161	/**
162	* Retrieve the contents of a sitemap with given name and type.
163	* If no such sitemap exists, return the empty string. Note that the
164	* returned string is run through wp_specialchars_decode.
165	*
166	* @access public
167	* @since 4.8.0
168	*
169	* @param string $name Row name.
170	* @param string $type Row type.
171	*
172	* @return string Text of the specified sitemap, or the empty string.
173	*/
174	public function get_sitemap_text( $name, $type ) {
175	$row = $this->read_sitemap_data( $name, $type );
176
177	if ( null === $row ) {
178	return '';
179	} else {
180	return $row['text'];
181	}
182	}
183
184	/**
185	* Delete numbered sitemaps named prefix-(p+1), prefix-(p+2), ...
186	* until the first nonexistent sitemap is found.
187	*
188	* @access public
189	* @since 4.8.0
190	*
191	* @param int $position Number before the first sitemap to be deleted.
192	* @param string $type Sitemap type.
193	*/
194	public function delete_numbered_sitemap_rows_after( $position, $type ) {
195	$any_left = true;
196
197	while ( true === $any_left ) {
198	++$position;
199	$name = jp_sitemap_filename( $type, $position );
200	$any_left = $this->delete_sitemap_data( $name, $type );
201	}
202	}
203
204	/**
205	* Deletes all stored sitemap data.
206	*
207	* @access public
208	* @since 4.8.0
209	*/
210	public function delete_all_stored_sitemap_data() {
211	$this->delete_sitemap_type_data( JP_MASTER_SITEMAP_TYPE );
212	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_TYPE );
213	$this->delete_sitemap_type_data( JP_PAGE_SITEMAP_INDEX_TYPE );
214	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_TYPE );
215	$this->delete_sitemap_type_data( JP_IMAGE_SITEMAP_INDEX_TYPE );
216	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_TYPE );
217	$this->delete_sitemap_type_data( JP_VIDEO_SITEMAP_INDEX_TYPE );
218	}
219
220	/**
221	* Deletes all sitemap data of specific type
222	*
223	* @access protected
224	* @since 5.3.0
225	*
226	* @param String $type Type of sitemap.
227	*/
228	protected function delete_sitemap_type_data( $type ) {
229	$ids = get_posts(
230	array(
231	'post_type' => $type,
232	'post_status' => 'draft',
233	'fields' => 'ids',
234	)
235	);
236
237	foreach ( $ids as $id ) {
238	wp_trash_post( $id );
239	}
240	}
241
242	/**
243	* Retrieve an array of sitemap rows (of a given type) sorted by ID.
244	*
245	* Returns the smallest $num_posts sitemap rows (measured by ID)
246	* of the given type which are larger than $from_id.
247	*
248	* @access public
249	* @since 4.8.0
250	*
251	* @param string $type Type of the sitemap rows to retrieve.
252	* @param int $from_id Greatest lower bound of retrieved sitemap post IDs.
253	* @param int $num_posts Largest number of sitemap posts to retrieve.
254	*
255	* @return array The sitemaps, as an array of associative arrays.
256	*/
257	public function query_sitemaps_after_id( $type, $from_id, $num_posts ) {
258	global $wpdb;
259
260	return $wpdb->get_results(
261	$wpdb->prepare(
262	"SELECT *
263	FROM $wpdb->posts
264	WHERE post_type=%s
265	AND post_status=%s
266	AND ID>%d
267	ORDER BY ID ASC
268	LIMIT %d;",
269	$type,
270	'draft',
271	$from_id,
272	$num_posts
273	),
274	ARRAY_A
275	); // WPCS: db call ok; no-cache ok.
276	}
277
278	/**
279	* Retrieve an array of posts sorted by ID.
280	*
281	* More precisely, returns the smallest $num_posts posts
282	* (measured by ID) which are larger than $from_id.
283	*
284	* @access public
285	* @since 4.8.0
286	*
287	* @param int $from_id Greatest lower bound of retrieved post IDs.
288	* @param int $num_posts Largest number of posts to retrieve.
289	*
290	* @return array The posts.
291	*/
292	public function query_posts_after_id( $from_id, $num_posts ) {
293	global $wpdb;
294
295	// Get the list of post types to include and prepare for query.
296	$post_types = Jetpack_Options::get_option_and_ensure_autoload(
297	'jetpack_sitemap_post_types',
298	array( 'page', 'post' )
299	);
300	foreach ( (array) $post_types as $i => $post_type ) {
301	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
302	}
303	$post_types_list = implode( ',', $post_types );
304
305	$columns_list = $this->get_sanitized_post_columns( $wpdb );
306
307	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
308	return $wpdb->get_results(
309	$wpdb->prepare(
310	"SELECT $columns_list
311	FROM $wpdb->posts
312	WHERE post_status='publish'
313	AND post_type IN ($post_types_list)
314	AND ID>%d
315	ORDER BY ID ASC
316	LIMIT %d;",
317	$from_id,
318	$num_posts
319	)
320	);
321	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
322	}
323
324	/**
325	* Get the most recent timestamp among approved comments for the given post_id.
326	*
327	* @access public
328	* @since 4.8.0
329	*
330	* @param int $post_id Post identifier.
331	*
332	* @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.
333	*/
334	public function query_latest_approved_comment_time_on_post( $post_id ) {
335	global $wpdb;
336
337	return $wpdb->get_var(
338	$wpdb->prepare(
339	"SELECT MAX(comment_date_gmt)
340	FROM $wpdb->comments
341	WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_type in ( '', 'comment' )",
342	$post_id
343	)
344	);
345	}
346
347	/**
348	* Retrieve an array of image posts sorted by ID.
349	*
350	* More precisely, returns the smallest $num_posts image posts
351	* (measured by ID) which are larger than $from_id.
352	*
353	* @access public
354	* @since 4.8.0
355	*
356	* @param int $from_id Greatest lower bound of retrieved image post IDs.
357	* @param int $num_posts Largest number of image posts to retrieve.
358	*
359	* @return array The posts.
360	*/
361	public function query_images_after_id( $from_id, $num_posts ) {
362	global $wpdb;
363
364	return $wpdb->get_results(
365	$wpdb->prepare(
366	"SELECT *
367	FROM $wpdb->posts
368	WHERE post_type='attachment'
369	AND post_mime_type LIKE %s
370	AND ID>%d
371	ORDER BY ID ASC
372	LIMIT %d;",
373	'image/%',
374	$from_id,
375	$num_posts
376	)
377	); // WPCS: db call ok; no-cache ok.
378	}
379
380	/**
381	* Retrieve an array of video posts sorted by ID.
382	*
383	* More precisely, returns the smallest $num_posts video posts
384	* (measured by ID) which are larger than $from_id.
385	*
386	* @access public
387	* @since 4.8.0
388	*
389	* @param int $from_id Greatest lower bound of retrieved video post IDs.
390	* @param int $num_posts Largest number of video posts to retrieve.
391	*
392	* @return array The posts.
393	*/
394	public function query_videos_after_id( $from_id, $num_posts ) {
395	global $wpdb;
396
397	return $wpdb->get_results(
398	$wpdb->prepare(
399	"SELECT *
400	FROM $wpdb->posts
401	WHERE post_type='attachment'
402	AND post_mime_type LIKE %s
403	AND ID>%d
404	ORDER BY ID ASC
405	LIMIT %d;",
406	'video/%',
407	$from_id,
408	$num_posts
409	)
410	); // WPCS: db call ok; no-cache ok.
411	}
412
413	/**
414	* Retrieve an array of published posts from the last 2 days.
415	*
416	* @access public
417	* @since 4.8.0
418	*
419	* @param int $num_posts Largest number of posts to retrieve.
420	*
421	* @return array The posts.
422	*/
423	public function query_most_recent_posts( $num_posts ) {
424	global $wpdb;
425
426	$two_days_ago = gmdate( 'Y-m-d', strtotime( '-2 days' ) );
427
428	/**
429	* Filter post types to be included in news sitemap.
430	*
431	* @module sitemaps
432	*
433	* @since 3.9.0
434	*
435	* @param array $post_types Array with post types to include in news sitemap.
436	*/
437	$post_types = apply_filters(
438	'jetpack_sitemap_news_sitemap_post_types',
439	array( 'page', 'post' )
440	);
441
442	foreach ( (array) $post_types as $i => $post_type ) {
443	$post_types[ $i ] = $wpdb->prepare( '%s', $post_type );
444	}
445
446	$post_types_list = implode( ',', $post_types );
447
448	$columns_list = $this->get_sanitized_post_columns( $wpdb );
449
450	// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WPCS: db call ok; no-cache ok.
451	return $wpdb->get_results(
452	$wpdb->prepare(
453	"SELECT $columns_list
454	FROM $wpdb->posts
455	WHERE post_status='publish'
456	AND post_date >= '%s'
457	AND post_type IN ($post_types_list)
458	ORDER BY post_date DESC
459	LIMIT %d;",
460	$two_days_ago,
461	$num_posts
462	)
463	);
464	// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.QuotedSimplePlaceholder,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
465	}
466
467	/**
468	* Returns all columns from the posts table,
469	* except post_content and post_content_filtered.
470	*
471	* @param object $wpdb The WordPress database object.
472	* @return string The sanitized post columns.
473	*/
474	private function get_sanitized_post_columns( $wpdb ) {
475	$columns = array_filter(
476	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
477	$wpdb->get_col( "SHOW COLUMNS FROM $wpdb->posts" ),
478	function ( $column ) {
479	return $column !== 'post_content' && $column !== 'post_content_filtered';
480	}
481	);
482
483	return implode( ',', array_map( 'esc_sql', $columns ) );
484	}
485	}
486