TiDB CloudのAuto Embedding機能を使ってUnityで連想ゲームを作ってみた

-- こんな風にSQL内で直接テキストをベクトル化して比較できます
SELECT VEC_EMBED_COSINE_DISTANCE(embedding, 'りんご') as distance
FROM word_dictionary
WHERE word = 'みかん';

-- 単語辞書テーブル（自動エンベディング対応）
CREATE TABLE word_dictionary (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(191) NOT NULL,
    -- EMBED_TEXT()関数で自動的にテキストをベクトルに変換
    -- Cohere embed-multilingual-v3.0は1024次元のベクトルを生成
    embedding VECTOR(1024) GENERATED ALWAYS AS (
        EMBED_TEXT("tidbcloud_free/cohere/embed-multilingual-v3.0", word, '{"input_type": "search_document", "input_type@search": "search_query"}')
    ) STORED,
    UNIQUE KEY uk_word (word),
    -- Vector Indexを同時に作成してパフォーマンス向上
    VECTOR INDEX ((VEC_COSINE_DISTANCE(embedding)))
);

-- テスト用のサンプルデータ挿入
-- データ挿入時に自動的にベクトルが生成される
INSERT INTO word_dictionary (word) VALUES
('りんご'), ('みかん'), ('バナナ'), ('ぶどう'),
('犬'), ('猫'), ('鳥'), ('魚'),
('車'), ('電車'), ('飛行機'), ('自転車'),
('王様'), ('女王'), ('王子'), ('騎士'),
('学校'), ('先生'), ('生徒'), ('教室'),
('海'), ('山'), ('川'), ('森'),
('赤'), ('青'), ('黄色'), ('緑');

EMBED_TEXT("model_name", text_content[, additional_json_options])

using System;
using System.Threading.Tasks;
using UnityEngine;
using MySqlConnector;

namespace VectorMind
{
    public class TiDBConnector : MonoBehaviour
    {
        // 接続情報（本番環境ではバックエンドサーバーを経由してください）
        private const string HOST = "your-cluster.region.prod.aws.tidbcloud.com";
        private const int PORT = 4000;
        private const string USER = "your-username";
        private const string PASSWORD = "your-password";
        private const string DATABASE = "test";

        public static TiDBConnector Instance { get; private set; }
        public bool IsConnected { get; private set; } = false;

        private MySqlConnection _connection;
        private string ConnectionString =>
            $"Server={HOST};Port={PORT};User={USER};Password={PASSWORD};Database={DATABASE};SslMode=Required;";

        private void Awake()
        {
            if (Instance == null)
            {
                Instance = this;
                DontDestroyOnLoad(gameObject);
            }
            else
            {
                Destroy(gameObject);
            }
        }

        // TiDBに接続
        public async Task<bool> ConnectAsync()
        {
            try
            {
                _connection = new MySqlConnection(ConnectionString);
                await _connection.OpenAsync();
                IsConnected = true;
                Debug.Log("[TiDB] 接続成功！");
                return true;
            }
            catch (Exception ex)
            {
                Debug.LogError($"[TiDB] 接続エラー: {ex.Message}");
                return false;
            }
        }

        // ランダムなお題を取得
        public async Task<string> GetRandomWordAsync()
        {
            try
            {
                const string sql = "SELECT word FROM word_dictionary ORDER BY RAND() LIMIT 1";
                using var cmd = new MySqlCommand(sql, _connection);
                var result = await cmd.ExecuteScalarAsync();
                return result?.ToString();
            }
            catch (Exception ex)
            {
                Debug.LogError($"[TiDB] お題取得エラー: {ex.Message}");
                return null;
            }
        }

        // 2つの単語のベクトル類似度を計算（自動エンベディング使用）
        public async Task<float?> GetSimilarityScoreAsync(string targetWord, string inputWord)
        {
            // 同じ単語の場合は0点
            if (targetWord.Equals(inputWord.Trim(), StringComparison.OrdinalIgnoreCase))
            {
                return 0f;
            }

            try
            {
                // VEC_EMBED_COSINE_DISTANCEで自動的にテキストをベクトル化して比較
                const string sql = @"
                    SELECT VEC_EMBED_COSINE_DISTANCE(embedding, @input) as distance
                    FROM word_dictionary
                    WHERE word = @target";

                using var cmd = new MySqlCommand(sql, _connection);
                cmd.Parameters.AddWithValue("@target", targetWord);
                cmd.Parameters.AddWithValue("@input", inputWord.Trim());

                var result = await cmd.ExecuteScalarAsync();
                if (result != null && result != DBNull.Value)
                {
                    float distance = Convert.ToSingle(result);
                    // コサイン距離を類似度に変換（類似度 = 1 - 距離）
                    float similarity = Mathf.Max(0f, 1f - distance);
                    return similarity;
                }

                return null;
            }
            catch (Exception ex)
            {
                Debug.LogError($"[TiDB] 類似度計算エラー: {ex.Message}");
                return null;
            }
        }
    }
}

using System;
using System.Threading.Tasks;
using UnityEngine;

namespace VectorMind
{
    public enum GameState
    {
        Title, Connecting, Question, Answering, Judging, Result, GameOver
    }

    public enum ResultRank
    {
        Perfect,    // 0.80以上
        Great,      // 0.65〜0.79
        Good,       // 0.50〜0.64
        Bad         // 0.50未満
    }

    public class GameManager : MonoBehaviour
    {
        private const int TOTAL_QUESTIONS = 5;
        private const float PERFECT_THRESHOLD = 0.80f;
        private const float GREAT_THRESHOLD = 0.65f;
        private const float GOOD_THRESHOLD = 0.50f;

        public static GameManager Instance { get; private set; }
        public GameState CurrentState { get; private set; }
        public int CurrentQuestionNumber { get; private set; }
        public int TotalScore { get; private set; }
        public string CurrentQuestion { get; private set; }

        // イベント
        public event Action<string> OnQuestionReady;
        public event Action<int, ResultRank, string> OnAnswerJudged;
        public event Action<int, string> OnGameOver;

        // ゲーム開始
        public async void StartGame()
        {
            CurrentQuestionNumber = 0;
            TotalScore = 0;

            // DB接続
            bool connected = await TiDBConnector.Instance.ConnectAsync();
            if (!connected) return;

            await NextQuestion();
        }

        // 次の問題
        private async Task NextQuestion()
        {
            CurrentQuestionNumber++;
            if (CurrentQuestionNumber > TOTAL_QUESTIONS)
            {
                EndGame();
                return;
            }

            // ランダムな単語を取得
            string word = await TiDBConnector.Instance.GetRandomWordAsync();
            CurrentQuestion = word;
            OnQuestionReady?.Invoke(word);
        }

        // 回答を提出
        public async void SubmitAnswer(string inputWord)
        {
            // 類似度を取得（自動エンベディング使用）
            float? similarity = await TiDBConnector.Instance
                .GetSimilarityScoreAsync(CurrentQuestion, inputWord);

            if (similarity == null) return;

            // スコア計算
            int score = Mathf.RoundToInt(similarity.Value * 100);
            ResultRank rank = GetResultRank(similarity.Value);
            string message = GetResultMessage(rank);

            TotalScore += score;
            OnAnswerJudged?.Invoke(score, rank, message);

            await Task.Delay(2000);
            await NextQuestion();
        }

        private ResultRank GetResultRank(float similarity)
        {
            if (similarity >= PERFECT_THRESHOLD) return ResultRank.Perfect;
            if (similarity >= GREAT_THRESHOLD) return ResultRank.Great;
            if (similarity >= GOOD_THRESHOLD) return ResultRank.Good;
            return ResultRank.Bad;
        }

        private string GetResultMessage(ResultRank rank)
        {
            return rank switch
            {
                ResultRank.Perfect => "心、通じてる！",
                ResultRank.Great => "いい線いってる！",
                ResultRank.Good => "まあまあかな",
                ResultRank.Bad => "遠すぎる...",
                _ => ""
            };
        }

        private void EndGame()
        {
            string rankLetter = GetFinalRank(TotalScore);
            OnGameOver?.Invoke(TotalScore, rankLetter);
        }

        private string GetFinalRank(int totalScore)
        {
            float percentage = totalScore / 500f; // 500点満点
            if (percentage >= 0.80f) return "S";
            if (percentage >= 0.65f) return "A";
            if (percentage >= 0.50f) return "B";
            return "C";
        }
    }
}

using UnityEngine;
using UnityEngine.UI;
using TMPro;

namespace VectorMind
{
    public class UIManager : MonoBehaviour
    {
        [Header("パネル")]
        [SerializeField] private GameObject titlePanel;
        [SerializeField] private GameObject gamePanel;
        [SerializeField] private GameObject resultPanel;

        [Header("ゲーム画面")]
        [SerializeField] private TextMeshProUGUI questionNumberText;
        [SerializeField] private TextMeshProUGUI totalScoreText;
        [SerializeField] private TextMeshProUGUI questionWordText;
        [SerializeField] private TMP_InputField answerInputField;
        [SerializeField] private Button submitButton;
        [SerializeField] private TextMeshProUGUI feedbackText;

        [Header("リザルト画面")]
        [SerializeField] private TextMeshProUGUI finalScoreText;
        [SerializeField] private TextMeshProUGUI finalRankText;

        private void Start()
        {
            // イベント登録
            GameManager.Instance.OnQuestionReady += HandleQuestionReady;
            GameManager.Instance.OnAnswerJudged += HandleAnswerJudged;
            GameManager.Instance.OnGameOver += HandleGameOver;

            submitButton.onClick.AddListener(() =>
                GameManager.Instance.SubmitAnswer(answerInputField.text));
        }

        private void HandleQuestionReady(string word)
        {
            questionWordText.text = word;
            questionNumberText.text = $"{GameManager.Instance.CurrentQuestionNumber} / 5";
            answerInputField.text = "";
            answerInputField.ActivateInputField();
        }

        private void HandleAnswerJudged(int score, ResultRank rank, string message)
        {
            feedbackText.text = $"{rank}!\n{message}\n+{score}点";
            totalScoreText.text = $"スコア: {GameManager.Instance.TotalScore}";
        }

        private void HandleGameOver(int totalScore, string rankLetter)
        {
            gamePanel.SetActive(false);
            resultPanel.SetActive(true);
            finalScoreText.text = $"{totalScore} 点";
            finalRankText.text = rankLetter;
        }
    }
}