← All changes | modules/sitemaps/sitemap-librarian.php +567 -0 16.2-beta → 16.3 View file →

		@@ -1,0 +1,567 @@
	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	+}