}?php /** * Database layer: MySQL-backed vector store with per-model chunk archive. * * Embeddings are unit-normalized and stored either in a native VECTOR column * (MySQL 9.0+ / MariaDB 11.7+) or as JSON text on older engines. */ if ( ! defined( 'ABSPATH' ) ) { exit; } class WPGRAG_DB { public static function table_name() { global $wpdb; return $wpdb->prefix . WPGRAG_TABLE; } public static function backup_table() { global $wpdb; return $wpdb->prefix . WPGRAG_TABLE . '_backup'; } public static function activate() { self::create_table(); add_option( 'wprag_api_key', '' ); add_option( 'wprag_embed_model', self::default_embed_model() ); add_option( 'wprag_chat_model', self::default_chat_model() ); add_option( 'wprag_chunk_size', 900 ); add_option( 'wprag_top_k', 5 ); add_option( 'wprag_min_score', 0.30 ); add_option( 'wprag_indexed_version', '0' ); update_option( 'wprag_native_vectors', self::detect_native() ); } public static function create_table() { global $wpdb; $table = self::table_name(); $charset = $wpdb->get_charset_collate(); $native = self::detect_native(); if ( $native ) { $embedding_col = '`embedding` VECTOR(' . self::vector_dim() . ') NOT NULL'; } else { $embedding_col = '`embedding` LONGTEXT NULL'; } $sql = "CREATE TABLE IF NOT EXISTS {$table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, post_id BIGINT UNSIGNED NOT NULL, chunk_index SMALLINT UNSIGNED NOT NULL DEFAULT 0, chunk_text LONGTEXT NOT NULL, {$embedding_col}, dimensions SMALLINT UNSIGNED NOT NULL DEFAULT 0, model VARCHAR(100) NOT NULL DEFAULT '', post_type VARCHAR(20) NOT NULL DEFAULT 'post', created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY post_id (post_id), KEY post_type_idx (post_type) ) {$charset};"; $wpdb->query( $sql ); $bak = self::backup_table(); $sql_b = "CREATE TABLE IF NOT EXISTS {$bak} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, model VARCHAR(100) NOT NULL DEFAULT '', post_id BIGINT UNSIGNED NOT NULL, chunk_index SMALLINT UNSIGNED NOT NULL DEFAULT 0, chunk_text LONGTEXT NOT NULL, embedding LONGTEXT NULL, dimensions SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY mpu (model, post_id, chunk_index), KEY model_idx (model) ) {$charset};"; $wpdb->query( $sql_b ); if ( empty( $wpdb->last_error ) && self::table_exists() ) { update_option( 'wprag_native_vectors', $native ); public static function maybe_create_table() { static $checked = false; if ( $checked ) { return; } $checked = true; if ( ! self::table_exists() ) { self::create_table(); return; } global $wpdb; if ( self::native_mode() ) { $actual = self::actual_column_dim(); // Dimension mismatch: flag it, never auto-drop. if ( -1 === $actual || ( $actual > 0 && $actual !== self::vector_dim() ) ) { update_option( 'wprag_dim_pending', array( 'current' => max( 0, $actual ), 'needed' => self::vector_dim(), 'model' => get_option( 'wprag_embed_model', self::default_embed_model() ), ), false ); return; } } delete_option( 'wprag_dim_pending' ); // Lazy restore for the active model when the live table has none of it. $model = get_option( 'wprag_embed_model', self::default_embed_model() ); $live = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::table_name() . ' WHERE model = %s', $model ) ); if ( 0 === $live ) { $n = self::restore_matching(); if ( $n > 0 ) { update_option( 'wprag_dim_restored_lazy', $n, false ); delete_option( 'wprag_dim_rebuilt' ); } } } public static function perform_rebuild() { self::create_table(); self::archive_chunks(); global $wpdb; $wpdb->query( 'DROP TABLE IF EXISTS ' . self::table_name() ); self::create_table(); delete_option( 'wprag_dim_pending' ); return self::restore_matching(); } public static function actual_column_dim() { global $wpdb; $type = $wpdb->get_var( $wpdb->prepare( "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'embedding'", self::table_name() ) ); if ( null === $type || '' === $type ) { return 0; } if ( preg_match( '/vector\((\d+)\)/i', $type, $m ) ) { return (int) $m[1]; } return -1; } public static function archive_chunks() { global $wpdb; $main = self::table_name(); $bak = self::backup_table(); $native = self::native_mode(); if ( ! self::table_exists() ) { return 0; } $to_text = $native ? ( 'mysql' === $native ? 'VECTOR_TO_STRING(embedding)' : 'VEC_ToText(embedding)' ) : 'embedding'; $rows = $wpdb->get_results( "SELECT model, post_id, chunk_index, chunk_text, {$to_text} AS vec_text, dimensions, created_at FROM {$main}", ARRAY_A ); $count = 0; foreach ( (array) $rows as $row ) { $vec = self::decode_vector_text( $row['vec_text'] ?? '' ); if ( empty( $vec ) ) { continue; } $ok = $wpdb->replace( $bak, array( 'model' => $row['model'], 'post_id' => (int) $row['post_id'], 'chunk_index' => (int) $row['chunk_index'], 'chunk_text' => $row['chunk_text'], 'embedding' => wp_json_encode( array_map( 'floatval', $vec ) ), 'dimensions' => count( $vec ), 'created_at' => $row['created_at'], ), array( '%s', '%d', '%d', '%s', '%s', '%d', '%s' ) ); if ( false !== $ok ) { ++$count; } } return $count; } public static function restore_matching() { global $wpdb; $bak = self::backup_table(); $main = self::table_name(); $model = get_option( 'wprag_embed_model', self::default_embed_model() ); $dim = self::vector_dim(); $native = self::native_mode(); if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $bak ) ) ) { return 0; } $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, chunk_index, chunk_text, embedding FROM {$bak} WHERE model = %s AND dimensions = %d", $model, $dim ), ARRAY_A ); $post_type_of = array(); $count = 0; foreach ( (array) $rows as $row ) { $pid = (int) $row['post_id']; if ( ! isset( $post_type_of[ $pid ] ) ) { $post_type_of[ $pid ] = get_post_type( $pid ) ?: 'post'; } $vec = json_decode( $row['embedding'], true ); if ( empty( $vec ) ) { continue; } if ( $native ) { $ok = $wpdb->query( $wpdb->prepare( "INSERT INTO {$main} (post_id, chunk_index, chunk_text, embedding, dimensions, model, post_type, created_at) VALUES (%d, %d, %s, " . self::sql_to_vector() . ", %d, %s, %s, %s)", $pid, (int) $row['chunk_index'], $row['chunk_text'], self::encode_native( $vec ), count( $vec ), $model, $post_type_of[ $pid ], current_time( 'mysql' ) ) ); $ok = ( false !== $ok && empty( $wpdb->last_error ) ); } else { $ok = $wpdb->insert( $main, array( 'post_id' => $pid, 'chunk_index' => (int) $row['chunk_index'], 'chunk_text' => $row['chunk_text'], 'embedding' => wp_json_encode( array_map( 'floatval', $vec ) ), 'dimensions' => count( $vec ), 'model' => $model, 'post_type' => $post_type_of[ $pid ], 'created_at' => current_time( 'mysql' ), ), array( '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%s' ) ); $ok = false !== $ok; } if ( $ok ) { update_post_meta( $pid, '_wprag_indexed_at', current_time( 'mysql' ) ); ++$count; } } return $count; } update_option( 'wprag_native_dim', $native ? self::vector_dim() : 0 ); return true; } return false; } public static function table_exists() { global $wpdb; return (bool) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', self::table_name() ) ); } public static function decode_vector_text( $text ) { $text = trim( (string) $text ); if ( '' === $text ) { return null; } $vec = json_decode( $text, true ); if ( is_array( $vec ) ) { return array_map( 'floatval', $vec ); } if ( 0 === strpos( $text, '[' ) ) { $parts = explode( ',', trim( $text, "[] \t\n\r\0\x0B" ) ); if ( count( $parts ) > 1 ) { return array_map( 'floatval', $parts ); } } return null; } public static function get_vector_preview( $chunk_id ) { global $wpdb; $native = self::native_mode(); $table = self::table_name(); if ( $native ) { $to_text = 'mysql' === $native ? 'VECTOR_TO_STRING(embedding)' : 'VEC_ToText(embedding)'; $text = $wpdb->get_var( $wpdb->prepare( "SELECT {$to_text} FROM {$table} WHERE id = %d", (int) $chunk_id ) ); } else { $text = $wpdb->get_var( $wpdb->prepare( 'SELECT embedding FROM ' . self::table_name() . ' WHERE id = %d', (int) $chunk_id ) ); } $vec = self::decode_vector_text( $text ); if ( ! is_array( $vec ) || empty( $vec ) ) { return array( 'error' => $native ? 'Could not decode the native vector.' : 'Stored embedding is empty or corrupt.', 'dims' => 0, 'norm' => 0, 'nonzero' => 0, 'sample' => array(), ); } $norm = 0.0; $nonzero = 0; foreach ( $vec as $v ) { $v = (float) $v; $norm += $v * $v; if ( abs( $v ) > 1e-9 ) { ++$nonzero; } } return array( 'dims' => count( $vec ), 'norm' => round( sqrt( $norm ), 4 ), 'nonzero' => $nonzero, 'sample' => array_slice( $vec, 0, 4 ), ); } public static function get_post_chunks( $post_id ) { global $wpdb; return $wpdb->get_col( $wpdb->prepare( 'SELECT chunk_text FROM ' . self::table_name() . ' WHERE post_id = %d ORDER BY chunk_index ASC', (int) $post_id ) ); } public static function delete_post( $post_id ) { global $wpdb; $wpdb->delete( self::table_name(), array( 'post_id' => (int) $post_id ), array( '%d' ) ); } public static function count_chunks() { global $wpdb; return (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . self::table_name() ); } public static function count_posts() { global $wpdb; return (int) $wpdb->get_var( 'SELECT COUNT(DISTINCT post_id) FROM ' . self::table_name() ); } public static function replace_chunks( $post_id, $chunks ) { global $wpdb; $table = self::table_name(); $native = self::native_mode(); self::delete_post( $post_id ); foreach ( $chunks as $i => $chunk ) { if ( $native ) { $wpdb->query( $wpdb->prepare( "INSERT INTO {$table} (post_id, chunk_index, chunk_text, embedding, dimensions, model, post_type, created_at) VALUES (%d, %d, %s, " . self::sql_to_vector() . ", %d, %s, %s, %s)", (int) $post_id, (int) $i, $chunk['text'], self::encode_native( $chunk['embedding'] ), (int) $chunk['dimensions'], sanitize_text_field( $chunk['model'] ), get_post_type( $post_id ), current_time( 'mysql' ) ) ); if ( ! empty( $wpdb->last_error ) ) { WPGRAG_Indexer::log_error( $post_id, new WP_Error( 'wprag_db_insert', 'Database rejected the embedding row: ' . $wpdb->last_error ) ); } continue; } $wpdb->insert( $table, array( 'post_id' => (int) $post_id, 'chunk_index' => (int) $i, 'chunk_text' => $chunk['text'], 'embedding' => wp_json_encode( $chunk['embedding'] ), 'dimensions' => (int) $chunk['dimensions'], 'model' => sanitize_text_field( $chunk['model'] ), 'post_type' => get_post_type( $post_id ), 'created_at' => current_time( 'mysql' ), ), array( '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%s' ) ); } } public static function search_native( array $qvec, $top_k ) { global $wpdb; $native = self::native_mode(); if ( ! $native ) { return false; } $table = self::table_name(); if ( 'mysql' === $native ) { $dist_expr = "VECTOR_DISTANCE('COSINE', embedding, STRING_TO_VECTOR(%s))"; $to_score = function ( $d ) { return round( 1.0 - (float) $d, 4 ); }; } else { $dist_expr = 'VEC_DISTANCE_EUCLIDEAN(embedding, VEC_FromText(%s))'; $to_score = function ( $d ) { return round( 1.0 - (float) $d * (float) $d / 2.0, 4 ); }; } $sql = "SELECT id, post_id, chunk_text, {$dist_expr} AS dist FROM {$table} WHERE dimensions = %d ORDER BY dist ASC LIMIT %d"; $rows = $wpdb->get_results( $wpdb->prepare( $sql, self::encode_native( $qvec ), count( $qvec ), $top_k * 3 ), ARRAY_A ); if ( ! is_array( $rows ) || ! empty( $wpdb->last_error ) ) { return false; } $out = array(); foreach ( $rows as $row ) { $out[] = array( 'post_id' => (int) $row['post_id'], 'chunk_text' => $row['chunk_text'], 'score' => $to_score( $row['dist'] ), ); } return $out; } public static function encode_native( array $vec ) { if ( 'mysql' === self::native_mode() ) { return wp_json_encode( array_map( 'floatval', $vec ) ); } return '[' . implode( ',', $vec ) . ']'; } public static function sql_to_vector() { return 'mysql' === self::native_mode() ? 'STRING_TO_VECTOR(%s)' : 'VEC_FromText(%s)'; } public static function detect_native() { global $wpdb; $ver = (string) $wpdb->get_var( 'SELECT VERSION()' ); if ( '' === $ver ) { return ''; } if ( false !== stripos( $ver, 'mariadb' ) ) { preg_match( '/^(\d+\.\d+)/', $ver, $m ); return ( isset( $m[1] ) && (float) $m[1] >= 11.7 ) ? 'mariadb' : ''; } preg_match( '/^(\d+)\./', $ver, $m ); return ( isset( $m[1] ) && (int) $m[1] >= 9 ) ? 'mysql' : ''; } public static function native_mode() { static $mode = null; if ( null !== $mode ) { return $mode; } $mode = (string) get_option( 'wprag_native_vectors', '' ); if ( '' === $mode ) { $mode = self::detect_native(); update_option( 'wprag_native_vectors', $mode ); } return $mode; } public static function column_dim() { return (int) get_option( 'wprag_native_dim', 0 ); public static function vector_dim() { $model = get_option( 'wprag_embed_model', self::default_embed_model() ); $dims = self::known_dims(); if ( isset( $dims[ $model ] ) ) { $dim = $dims[ $model ]; } elseif ( false !== stripos( $model, 'embedding' ) ) { $dim = 3072; } else { $dim = 768; } return (int) apply_filters( 'wprag_vector_dim', $dim, $model ); } public static function known_dims() { return array( 'gemini-embedding-001' => 3072, 'text-embedding-004' => 768, ); } public static function chat_models() { return apply_filters( 'wprag_chat_models', array( 'gemini-2.5-flash' => 'Gemini 2.5 Flash', 'gemini-2.0-flash' => 'Gemini 2.0 Flash', 'gemini-1.5-flash' => 'Gemini 1.5 Flash', ) ); } public static function embed_models() { return apply_filters( 'wprag_embed_models', array( 'gemini-embedding-001' => 'Gemini Embedding 001 (3072 dims)', 'text-embedding-004' => 'Text Embedding 004 (768 dims)', ) ); } }
Adam Scott says 'The Whisper Man' was 'really well written' (Video)

An Indo-American News website. It covers Gossips, Politics, Movies, Technolgy, and Sports News and Photo Galleries and Live Coverage of Events via Youtube. The website is established in 2015 and is owned by AGK FIRE INC.
This website uses cookies.