insert( self::jobs_table(), array( 'type' => in_array( $type, self::TYPES, true ) ? $type : 'edit', 'status' => in_array( $status, self::STATUSES, true ) ? $status : 'draft', 'cursor' => 0, 'counts' => wp_json_encode( array() ), 'settings' => wp_json_encode( $settings ), 'parent_job_id' => $parent_job_id ? absint( $parent_job_id ) : null, 'created_by' => get_current_user_id(), 'created_at' => current_time( 'mysql', true ), ), array( '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%s' ) ); return (int) $wpdb->insert_id; } /** * Get a job with decoded counts and settings. * * @param int $job_id Job ID. * @return object|null */ public static function get( $job_id ) { global $wpdb; $table = self::jobs_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $job = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, absint( $job_id ) ) ); return $job ? self::decode_job( $job ) : null; } /** * Decode JSON columns of a job row. * * @param object $job Job row. * @return object */ private static function decode_job( $job ) { $counts = json_decode( (string) $job->counts, true ); $settings = json_decode( (string) $job->settings, true ); $job->counts = is_array( $counts ) ? $counts : array(); $job->settings = is_array( $settings ) ? $settings : array(); $job->id = (int) $job->id; $job->cursor = (int) $job->cursor; $job->created_by = (int) $job->created_by; return $job; } /** * Update job columns. Arrays for counts and settings are JSON encoded. * * @param int $job_id Job ID. * @param array $data status, cursor, counts, settings, confirmed_at, started_at, finished_at. * @return bool */ public static function update( $job_id, array $data ) { global $wpdb; $fields = array(); $formats = array(); foreach ( $data as $key => $value ) { switch ( $key ) { case 'status': if ( in_array( $value, self::STATUSES, true ) ) { $fields['status'] = $value; $formats[] = '%s'; } break; case 'cursor': $fields['cursor'] = absint( $value ); $formats[] = '%d'; break; case 'counts': case 'settings': $fields[ $key ] = is_array( $value ) ? wp_json_encode( $value ) : (string) $value; $formats[] = '%s'; break; case 'confirmed_at': case 'started_at': case 'finished_at': $fields[ $key ] = $value; $formats[] = '%s'; break; } } if ( empty( $fields ) ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching return false !== $wpdb->update( self::jobs_table(), $fields, array( 'id' => absint( $job_id ) ), $formats, array( '%d' ) ); } /** * Merge values into a job's settings JSON. * * @param int $job_id Job ID. * @param array $values Values to merge. * @return void */ public static function merge_settings( $job_id, array $values ) { $job = self::get( $job_id ); if ( $job ) { self::update( $job_id, array( 'settings' => array_merge( $job->settings, $values ) ) ); } } /** * List jobs, newest first. * * @param array $args type, status, per_page, page, exclude_types. * @return array{items: array, total: int} */ public static function query( array $args = array() ) { global $wpdb; $table = self::jobs_table(); $where = array( '1=1' ); $binds = array(); $per_page = isset( $args['per_page'] ) ? min( 200, max( 1, absint( $args['per_page'] ) ) ) : 20; $page = isset( $args['page'] ) ? max( 1, absint( $args['page'] ) ) : 1; if ( ! empty( $args['type'] ) ) { $types = array_values( array_intersect( (array) $args['type'], self::TYPES ) ); $where[] = 'type IN (' . implode( ',', array_fill( 0, max( 1, count( $types ) ), '%s' ) ) . ')'; $binds = array_merge( $binds, empty( $types ) ? array( '' ) : $types ); } if ( ! empty( $args['status'] ) ) { $statuses = array_values( array_intersect( (array) $args['status'], self::STATUSES ) ); $where[] = 'status IN (' . implode( ',', array_fill( 0, max( 1, count( $statuses ) ), '%s' ) ) . ')'; $binds = array_merge( $binds, empty( $statuses ) ? array( '' ) : $statuses ); } $where_sql = implode( ' AND ', $where ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- The WHERE clause is built from fixed SQL fragments; every value is bound by prepare(). $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where_sql}", array_merge( array( $table ), $binds ) ) ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The WHERE clause is built from fixed SQL fragments; every value is bound by prepare() as one array. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where_sql} ORDER BY id DESC LIMIT %d OFFSET %d", array_merge( array( $table ), $binds, array( $per_page, ( $page - 1 ) * $per_page ) ) ) ); // phpcs:enable return array( 'items' => array_map( array( __CLASS__, 'decode_job' ), (array) $rows ), 'total' => $total, ); } /** * Insert a job item. * * @param int $job_id Job ID. * @param array $data Item columns; message may be an array (JSON encoded). * @return int Item ID. */ public static function add_item( $job_id, array $data ) { global $wpdb; $row = array( 'job_id' => absint( $job_id ), 'post_id' => absint( $data['post_id'] ), 'kind' => isset( $data['kind'] ) && 'descendant' === $data['kind'] ? 'descendant' : 'direct', 'post_type' => isset( $data['post_type'] ) ? sanitize_key( $data['post_type'] ) : '', 'parent_id' => isset( $data['parent_id'] ) ? absint( $data['parent_id'] ) : 0, 'old_post_name' => isset( $data['old_post_name'] ) ? (string) $data['old_post_name'] : '', 'requested_post_name' => isset( $data['requested_post_name'] ) ? (string) $data['requested_post_name'] : '', 'old_permalink' => isset( $data['old_permalink'] ) ? (string) $data['old_permalink'] : '', 'expected_permalink' => isset( $data['expected_permalink'] ) ? (string) $data['expected_permalink'] : '', 'validation_state' => isset( $data['validation_state'] ) ? sanitize_key( $data['validation_state'] ) : 'pending', 'result' => isset( $data['result'] ) ? sanitize_key( $data['result'] ) : 'pending', 'message' => isset( $data['message'] ) ? ( is_array( $data['message'] ) ? wp_json_encode( $data['message'] ) : (string) $data['message'] ) : wp_json_encode( array() ), 'sort_order' => isset( $data['sort_order'] ) ? absint( $data['sort_order'] ) : 0, 'updated_at' => current_time( 'mysql', true ), ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->insert( self::items_table(), $row, array( '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s' ) ); return (int) $wpdb->insert_id; } /** * Update a job item. message and redirect_ids arrays are JSON encoded. * * @param int $item_id Item ID. * @param array $data Columns. * @return bool */ public static function update_item( $item_id, array $data ) { global $wpdb; $allowed = array( 'old_post_name', 'requested_post_name', 'actual_post_name', 'old_permalink', 'expected_permalink', 'actual_permalink', 'hash_before', 'hash_after', 'validation_state', 'result', 'message', 'redirect_ids', 'sort_order', 'post_type', 'parent_id' ); $fields = array(); $formats = array(); foreach ( $data as $key => $value ) { if ( ! in_array( $key, $allowed, true ) ) { continue; } if ( in_array( $key, array( 'message', 'redirect_ids' ), true ) && is_array( $value ) ) { $value = wp_json_encode( $value ); } $fields[ $key ] = $value; $formats[] = in_array( $key, array( 'sort_order', 'parent_id' ), true ) ? '%d' : '%s'; } $fields['updated_at'] = current_time( 'mysql', true ); $formats[] = '%s'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching return false !== $wpdb->update( self::items_table(), $fields, array( 'id' => absint( $item_id ) ), $formats, array( '%d' ) ); } /** * Merge values into an item's message JSON. * * @param object $item Item (decoded). * @param array $values Values. * @param array $extra Other columns to update at the same time. * @return void */ public static function merge_item_message( $item, array $values, array $extra = array() ) { $item->message = array_merge( $item->message, $values ); self::update_item( $item->id, array_merge( $extra, array( 'message' => $item->message ) ) ); } /** * Delete a draft item (draft jobs only; applied history is never deleted). * * @param int $item_id Item ID. * @return void */ public static function delete_draft_item( $item_id ) { global $wpdb; $items = self::items_table(); $jobs = self::jobs_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( $wpdb->prepare( "DELETE i FROM %i i JOIN %i j ON j.id = i.job_id WHERE i.id = %d AND j.status IN ('draft','validated')", $items, $jobs, absint( $item_id ) ) ); } /** * Decode an item row. * * @param object $item Item row. * @return object */ public static function decode_item( $item ) { $message = json_decode( (string) $item->message, true ); $redirect_ids = json_decode( (string) $item->redirect_ids, true ); $item->message = is_array( $message ) ? $message : array( 'text' => (string) $item->message ); $item->redirect_ids = is_array( $redirect_ids ) ? array_map( 'intval', $redirect_ids ) : array(); $item->id = (int) $item->id; $item->post_id = (int) $item->post_id; $item->job_id = (int) $item->job_id; return $item; } /** * Get one item. * * @param int $item_id Item ID. * @return object|null */ public static function get_item( $item_id ) { global $wpdb; $table = self::items_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $item = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, absint( $item_id ) ) ); return $item ? self::decode_item( $item ) : null; } /** * Find a job's direct item for a post. * * @param int $job_id Job ID. * @param int $post_id Post ID. * @return object|null */ public static function find_item( $job_id, $post_id ) { global $wpdb; $table = self::items_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $item = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE job_id = %d AND post_id = %d AND kind = 'direct' LIMIT 1", $table, absint( $job_id ), absint( $post_id ) ) ); return $item ? self::decode_item( $item ) : null; } /** * Get a job's items. * * @param int $job_id Job ID. * @param array $args kind, result (string|array), validation_state, ids, per_page, page, limit. * @return array */ public static function get_items( $job_id, array $args = array() ) { global $wpdb; $table = self::items_table(); $where = array( 'job_id = %d' ); $binds = array( absint( $job_id ) ); if ( ! empty( $args['kind'] ) ) { $where[] = 'kind = %s'; $binds[] = $args['kind']; } if ( ! empty( $args['result'] ) ) { $results = (array) $args['result']; $where[] = 'result IN (' . implode( ',', array_fill( 0, count( $results ), '%s' ) ) . ')'; $binds = array_merge( $binds, $results ); } if ( ! empty( $args['validation_state'] ) ) { $states = (array) $args['validation_state']; $where[] = 'validation_state IN (' . implode( ',', array_fill( 0, count( $states ), '%s' ) ) . ')'; $binds = array_merge( $binds, $states ); } if ( ! empty( $args['ids'] ) ) { $ids = array_map( 'absint', (array) $args['ids'] ); $where[] = 'id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')'; $binds = array_merge( $binds, $ids ); } $sql = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY sort_order ASC, id ASC'; $binds = array_merge( array( $table ), $binds ); if ( ! empty( $args['limit'] ) ) { $sql .= ' LIMIT %d OFFSET %d'; $binds[] = absint( $args['limit'] ); $binds[] = isset( $args['offset'] ) ? absint( $args['offset'] ) : 0; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Placeholders are built to match $binds; every value is bound by prepare(). $rows = $wpdb->get_results( $wpdb->prepare( $sql, $binds ) ); return array_map( array( __CLASS__, 'decode_item' ), (array) $rows ); } /** * Count a job's items grouped by a column. * * @param int $job_id Job ID. * @param string $column result, validation_state or kind. * @return array */ public static function count_by( $job_id, $column ) { global $wpdb; if ( ! in_array( $column, array( 'result', 'validation_state', 'kind' ), true ) ) { return array(); } $table = self::items_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT %i AS k, COUNT(*) AS n FROM %i WHERE job_id = %d GROUP BY %i', $column, $table, absint( $job_id ), $column ) ); $counts = array(); foreach ( (array) $rows as $row ) { $counts[ $row->k ] = (int) $row->n; } return $counts; } /** * Count items of a job. * * @param int $job_id Job ID. * @return int */ public static function count_items( $job_id ) { return array_sum( self::count_by( $job_id, 'kind' ) ); } /** * Find in-job duplicates: same new slug, post type and parent among active direct items. * * @param int $job_id Job ID. * @return array Keys "post_type|parent_id|slug". */ public static function duplicate_keys( $job_id ) { global $wpdb; $table = self::items_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_type, parent_id, requested_post_name FROM %i WHERE job_id = %d AND kind = 'direct' AND result NOT IN ('excluded','unchanged') GROUP BY post_type, parent_id, requested_post_name HAVING COUNT(*) > 1", $table, absint( $job_id ) ) ); $keys = array(); foreach ( (array) $rows as $row ) { $keys[ self::duplicate_key( $row->post_type, $row->parent_id, $row->requested_post_name ) ] = true; } return $keys; } /** * Duplicate grouping key. Flat post types ignore the parent. * * @param string $post_type Post type. * @param int $parent_id Parent ID. * @param string $slug Sanitized slug. * @return string */ public static function duplicate_key( $post_type, $parent_id, $slug ) { return $post_type . '|' . ( is_post_type_hierarchical( $post_type ) ? (int) $parent_id : 0 ) . '|' . $slug; } /** * Acquire the runner lock for a job. Expired locks are taken over. * * @param int $job_id Job ID. * @param int $ttl Lock lifetime in seconds. * @return bool */ public static function acquire_lock( $job_id, $ttl = 120 ) { $key = 'slugh_job_lock_' . absint( $job_id ); if ( add_option( $key, time() + $ttl, '', false ) ) { return true; } $expires = (int) get_option( $key, 0 ); if ( $expires < time() ) { delete_option( $key ); return add_option( $key, time() + $ttl, '', false ); } return false; } /** * Release the runner lock. * * @param int $job_id Job ID. * @return void */ public static function release_lock( $job_id ) { delete_option( 'slugh_job_lock_' . absint( $job_id ) ); } }