prefix). * * @return string */ public static function table_name() { global $wpdb; return $wpdb->prefix . 'lltxt_versions'; } /** * Create the table via dbDelta. Idempotent. * * @return void */ public static function install_schema() { global $wpdb; $table = self::table_name(); $charset = $wpdb->get_charset_collate(); // dbDelta is strict about whitespace + must use "KEY" not "INDEX". $sql = "CREATE TABLE {$table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, route VARCHAR(64) NOT NULL, sha256 CHAR(64) NOT NULL, body LONGTEXT NOT NULL, bytes INT UNSIGNED NOT NULL DEFAULT 0, source VARCHAR(32) NOT NULL, pinned TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY route_created (route, created_at), KEY pinned (pinned) ) {$charset};"; if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } dbDelta( $sql ); } /** * Drop the table. Called from uninstall.php only. * * @return void */ /** * Intentionally left as a no-op. Mature WP plugins (Yoast, Rank Math, * WC, ryhowa's llms.txt) preserve their custom tables on uninstall so * a merchant who uninstalls + reinstalls (debugging, accidental click) * doesn't lose their version history. The Privacy tab "Delete my data" * button is the merchant-initiated DELETE path. * * @return void */ public static function drop_schema() { // no-op by design — see docblock. } /** * Insert a new version, with sha256-based dedup against the last * DEDUP_WINDOW_DAYS days for the same route. * * @param string $route Route relative path (e.g. "llms.txt"). * @param string $body File body. * @param string $source One of VALID_SOURCES. * @return int|false Inserted row id, existing row id on dedup, or false on failure. */ public static function insert( $route, $body, $source = 'plugin-generated' ) { global $wpdb; if ( ! is_string( $route ) || '' === $route ) { return false; } if ( ! is_string( $body ) ) { return false; } if ( ! in_array( $source, self::VALID_SOURCES, true ) ) { $source = 'plugin-generated'; } $table = self::table_name(); $sha = hash( 'sha256', $body ); $bytes = strlen( $body ); // Dedup: same route + sha within DEDUP_WINDOW_DAYS → return existing id. // %i is the WP 6.2+ identifier placeholder. prepare() is inlined into // the $wpdb call so PluginCheck's NotPrepared sniff can see it. // phpcs:ignore WordPress.DB.DirectDatabaseQuery $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE route = %s AND sha256 = %s AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) ORDER BY id DESC LIMIT 1', $table, $route, $sha, self::DEDUP_WINDOW_DAYS ) ); if ( $existing ) { return (int) $existing; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery $ok = $wpdb->insert( $table, array( 'route' => $route, 'sha256' => $sha, 'body' => $body, 'bytes' => $bytes, 'source' => $source, 'pinned' => 0, 'created_at' => current_time( 'mysql', true ), ), array( '%s', '%s', '%s', '%d', '%s', '%d', '%s' ) ); if ( false === $ok ) { return false; } return (int) $wpdb->insert_id; } /** * List versions newest-first, optionally filtered by route. Returns a row * shape without the (potentially-large) body field. * * @param string|null $route Optional route filter. * @param int $limit Page size (1..100). * @param int|null $cursor Last id from previous page (exclusive). * @return array{rows:array>,next_cursor:int|null} */ public static function list( $route = null, $limit = 25, $cursor = null ) { global $wpdb; $table = self::table_name(); $limit = max( 1, min( 100, (int) $limit ) ); $cursor = empty( $cursor ) ? 0 : (int) $cursor; // Static SQL variants per filter combination — every prepare() inlined // directly into get_results() so PluginCheck's NotPrepared sniff sees // it. %i identifier placeholder for the table name; %s/%d for values. if ( ! empty( $route ) && $cursor > 0 ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, route, sha256, bytes, source, pinned, created_at FROM %i WHERE route = %s AND id < %d ORDER BY id DESC LIMIT %d', $table, $route, $cursor, $limit + 1 ), ARRAY_A ); } elseif ( ! empty( $route ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, route, sha256, bytes, source, pinned, created_at FROM %i WHERE route = %s ORDER BY id DESC LIMIT %d', $table, $route, $limit + 1 ), ARRAY_A ); } elseif ( $cursor > 0 ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, route, sha256, bytes, source, pinned, created_at FROM %i WHERE id < %d ORDER BY id DESC LIMIT %d', $table, $cursor, $limit + 1 ), ARRAY_A ); } else { // phpcs:ignore WordPress.DB.DirectDatabaseQuery $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, route, sha256, bytes, source, pinned, created_at FROM %i ORDER BY id DESC LIMIT %d', $table, $limit + 1 ), ARRAY_A ); } if ( ! is_array( $rows ) ) { $rows = array(); } $next_cursor = null; if ( count( $rows ) > $limit ) { $extra = array_pop( $rows ); $next_cursor = (int) $rows[ count( $rows ) - 1 ]['id']; } return array( 'rows' => $rows, 'next_cursor' => $next_cursor ); } /** * Fetch one row by id, including body. * * @param int $id Row id. * @return array|null */ public static function get( $id ) { global $wpdb; $id = (int) $id; if ( $id <= 0 ) { return null; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table_name(), $id ), ARRAY_A ); return is_array( $row ) ? $row : null; } /** * Read just the body for a row id. * * @param int $id Row id. * @return string|null */ public static function read_body( $id ) { $row = self::get( $id ); if ( ! $row || ! isset( $row['body'] ) ) { return null; } return (string) $row['body']; } /** * Pin / unpin a row. * * @param int $id Row id. * @param bool $pinned Pin or unpin. * @return bool */ public static function pin( $id, $pinned = true ) { global $wpdb; $id = (int) $id; if ( $id <= 0 ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery $res = $wpdb->update( self::table_name(), array( 'pinned' => $pinned ? 1 : 0 ), array( 'id' => $id ), array( '%d' ), array( '%d' ) ); return false !== $res; } /** * Delete every row in the table. * * @return int Rows deleted. */ public static function delete_all() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery $count = (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', self::table_name() ) ); return $count; } /** * Delete non-pinned rows older than TTL_DAYS. Called from the daily cron. * * @return int Rows deleted. */ public static function sweep_expired() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery $count = (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY) AND pinned = 0', self::table_name(), self::TTL_DAYS ) ); return $count; } }