query( $wpdb->prepare( 'INSERT INTO %i (job_id, row_no, byte_offset, status, code, message, product_id, fields, diff, created_at) VALUES (%d, %d, %d, %s, %s, %s, %d, %s, %s, %s) ON DUPLICATE KEY UPDATE byte_offset = VALUES(byte_offset), message = VALUES(message), product_id = VALUES(product_id), fields = VALUES(fields), diff = VALUES(diff)', Schema::table(), $jobId, $row, $offset, $status, $code, mb_substr( $detail, 0, 255, 'UTF-8' ), // By characters (varchar 255): a cut inside a character makes wpdb refuse the row. $productId, mb_substr( $fields, 0, 190, 'UTF-8' ), // By characters (varchar 190), like the message. $diff, gmdate( 'Y-m-d H:i:s' ) ) ); return 1 === $n; // ON DUPLICATE KEY UPDATE: 1 inserted, 2 updated, 0 unchanged. } /** * Store a sentinel with JSON data. * * @param int $jobId Job. * @param string $code totals|outcome|tuner|plan. * @param array $data Data. * @return void */ public static function sentinel( int $jobId, string $code, array $data ): void { self::record( $jobId, 0, 0, self::SENTINEL, $code, '', 0, '', (string) wp_json_encode( $data ) ); } /** * Read a sentinel. * * @param int $jobId Job. * @param string $code Code. * @return array|null */ public static function getSentinel( int $jobId, string $code ): ?array { global $wpdb; $json = $wpdb->get_var( $wpdb->prepare( 'SELECT diff FROM %i WHERE job_id = %d AND row_no = 0 AND status = %s AND code = %s', Schema::table(), $jobId, self::SENTINEL, $code ) ); $d = null === $json ? null : json_decode( (string) $json, true ); return is_array( $d ) ? $d : null; } /** * Atomic counter in a sentinel row (the value lives in byte_offset). * * @param int $jobId Job. * @param string $code Counter name. * @param int $by Increment. * @return void */ public static function bump( int $jobId, string $code, int $by = 1 ): void { global $wpdb; $wpdb->query( $wpdb->prepare( 'INSERT INTO %i (job_id, row_no, byte_offset, status, code, created_at) VALUES (%d, 0, %d, %s, %s, %s) ON DUPLICATE KEY UPDATE byte_offset = byte_offset + VALUES(byte_offset)', Schema::table(), $jobId, $by, self::SENTINEL, $code, gmdate( 'Y-m-d H:i:s' ) ) ); } /** * Counter value. * * @param int $jobId Job. * @param string $code Counter name. * @return int */ public static function counter( int $jobId, string $code ): int { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT byte_offset FROM %i WHERE job_id = %d AND row_no = 0 AND status = %s AND code = %s', Schema::table(), $jobId, self::SENTINEL, $code ) ); } /** * Counts by status (sentinels excluded). * * @param int $jobId Job. * @return array */ public static function counts( int $jobId ): array { global $wpdb; $out = array(); foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE job_id = %d AND status <> %s GROUP BY status', Schema::table(), $jobId, self::SENTINEL ), ARRAY_A ) as $r ) { $out[ $r['status'] ] = (int) $r['n']; } return $out; } /** * Counts by code within a status. * * @param int $jobId Job. * @param string $status Status. * @return array */ public static function codeCounts( int $jobId, string $status ): array { global $wpdb; $out = array(); foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SELECT code, COUNT(*) AS n FROM %i WHERE job_id = %d AND status = %s GROUP BY code ORDER BY n DESC', Schema::table(), $jobId, $status ), ARRAY_A ) as $r ) { $out[ $r['code'] ] = (int) $r['n']; } return $out; } /** * Keyset page of rows. * * @param int $jobId Job. * @param array $q status (string|string[]), code, field (substring of fields), after_row, after_id, limit (max 200). * @return array {rows: array[], next: {row,id}|null} */ public static function page( int $jobId, array $q = array() ): array { global $wpdb; $limit = isset( $q['limit'] ) ? max( 1, min( 200, (int) $q['limit'] ) ) : 50; $where = 'job_id = %d AND status <> %s'; $args = array( Schema::table(), $jobId, self::SENTINEL ); if ( ! empty( $q['status'] ) ) { $statuses = array_values( array_map( 'strval', (array) $q['status'] ) ); $where .= ' AND status IN (' . implode( ', ', array_fill( 0, count( $statuses ), '%s' ) ) . ')'; $args = array_merge( $args, $statuses ); } if ( ! empty( $q['code'] ) ) { $where .= ' AND code = %s'; $args[] = (string) $q['code']; } if ( ! empty( $q['field'] ) ) { $where .= ' AND (CONCAT(",", fields, ",") LIKE %s'; $args[] = '%,' . $wpdb->esc_like( (string) $q['field'] ) . ',%'; if ( 'attributes' === $q['field'] ) { // A variation's attributes and what a variation row adds to its parent are listed one name each. foreach ( array( 'attribute:', 'parent_attribute:', 'parent_variation:' ) as $prefix ) { $where .= ' OR CONCAT(",", fields) LIKE %s'; $args[] = '%,' . $wpdb->esc_like( $prefix ) . '%'; } // The default variation is mapped from the attribute columns too (attr:N:default). $where .= ' OR CONCAT(",", fields, ",") LIKE %s'; $args[] = '%,' . $wpdb->esc_like( 'default_attributes' ) . ',%'; } $where .= ')'; } if ( ! empty( $q['after_row'] ) || ! empty( $q['after_id'] ) ) { $where .= ' AND (row_no > %d OR (row_no = %d AND id > %d))'; $args[] = (int) ( $q['after_row'] ?? 0 ); $args[] = (int) ( $q['after_row'] ?? 0 ); $args[] = (int) ( $q['after_id'] ?? 0 ); } $args[] = $limit + 1; $sql = "SELECT id, row_no, byte_offset, status, code, message, product_id, fields, diff, resolved_by FROM %i WHERE {$where} ORDER BY row_no ASC, id ASC LIMIT %d"; $rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- fixed fragments. $more = count( $rows ) > $limit; $rows = array_slice( $rows, 0, $limit ); $next = null; if ( $more && $rows ) { $last = $rows[ count( $rows ) - 1 ]; $next = array( 'row' => (int) $last['row_no'], 'id' => (int) $last['id'], ); } return array( 'rows' => $rows, 'next' => $next, ); } /** * Whether a row of a job was recorded as failed or skipped: the dry run asks whether an earlier row of the file, the * parent of a variation, will write its product in the import. * * @param int $jobId Job. * @param int $row Row number. * @return bool */ public static function notWritten( int $jobId, int $row ): bool { global $wpdb; return null !== $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM %i WHERE job_id = %d AND row_no = %d AND status IN ('failed', 'skipped') LIMIT 1", Schema::table(), $jobId, $row ) ); } /** * Failed rows of a root job, ordered by row number, after a row. * * @param int $rootId Root job. * @param int $afterRow Row number to continue after. * @param int $limit Limit. * @param string[] $codes Codes to include (empty = all failed rows). * @return array[] */ public static function failed( int $rootId, int $afterRow, int $limit, array $codes = array() ): array { global $wpdb; $where = "job_id = %d AND status = 'failed' AND row_no > %d"; $args = array( Schema::table(), $rootId, $afterRow ); if ( $codes ) { $where .= ' AND code IN (' . implode( ', ', array_fill( 0, count( $codes ), '%s' ) ) . ')'; $args = array_merge( $args, array_values( $codes ) ); } $args[] = $limit; $sql = "SELECT row_no, byte_offset, code FROM %i WHERE {$where} ORDER BY row_no ASC LIMIT %d"; return (array) $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- fixed fragments. } /** * Number of failed rows of a job. * * @param int $jobId Job. * @param string[] $codes Codes (empty = all). * @return int */ public static function failedCount( int $jobId, array $codes = array() ): int { global $wpdb; $where = "job_id = %d AND status = 'failed'"; $args = array( Schema::table(), $jobId ); if ( $codes ) { $where .= ' AND code IN (' . implode( ', ', array_fill( 0, count( $codes ), '%s' ) ) . ')'; $args = array_merge( $args, array_values( $codes ) ); } return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- fixed fragments. } /** * A retry fixed a row of the root job. * * @param int $rootId Root job. * @param int $row Row. * @param int $retryId Retry job. * @return void */ public static function resolve( int $rootId, int $row, int $retryId ): void { global $wpdb; $wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'resolved', resolved_by = %d WHERE job_id = %d AND row_no = %d AND status = 'failed'", Schema::table(), $retryId, $rootId, $row ) ); } /** * A retry failed again: update the message of the root row. * * @param int $rootId Root job. * @param int $row Row. * @param string $code Code. * @param string $detail Detail. * @return void */ public static function refail( int $rootId, int $row, string $code, string $detail ): void { global $wpdb; $table = Schema::table(); $wpdb->query( $wpdb->prepare( "UPDATE %i SET message = %s WHERE job_id = %d AND row_no = %d AND status = 'failed'", $table, mb_substr( $detail, 0, 255, 'UTF-8' ), $rootId, $row ) ); $wpdb->query( $wpdb->prepare( "UPDATE IGNORE %i SET code = %s WHERE job_id = %d AND row_no = %d AND status = 'failed'", $table, $code, $rootId, $row ) ); } /** * Forget what an interrupted attempt of a row recorded, before its replay records its own outcome (a row that works * now must not stay failed). * * @param int $jobId Job. * @param int $row Row. * @param string[] $statuses Statuses. * @return int Rows deleted. */ public static function forget( int $jobId, int $row, array $statuses = array( 'failed', 'skipped' ) ): int { global $wpdb; $in = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) ); return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE job_id = %d AND row_no = %d AND status IN ($in)", array_merge( array( Schema::table(), $jobId, $row ), array_values( $statuses ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- placeholders generated from a list. } }