OGAI 使用指南 ​

CREATE EXTENSION ogai;

-- 查看当前用户的所有模型配置
SELECT model_key, model_name, model_provider, url FROM ogai.model_sources;

-- 注册新模型
INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, api_key, owner_name)
VALUES ('my_model', 'text-embedding-v3', 'Qwen', 
        'https://dashscope.aliyuncs.com/compatible-mode/v1', 
        'sk-xxx', CURRENT_USER);

-- 查看当前用户的所有向量化任务
SELECT task_name, type, src_table, src_col, method, dim, enable_bm25 
FROM ogai.vectorize_tasks;

-- 查看队列状态统计
SELECT status, COUNT(*) FROM ogai.vectorize_queue GROUP BY status;

-- 查看失败的任务
SELECT msg_id, task_id, pk_value, fail_reason, retry_count 
FROM ogai.vectorize_queue WHERE status = 'failed';

SELECT ogai_embedding('openGauss是一款开源数据库', 'my_embed_model', 1536);

SELECT ogai_generate('什么是向量数据库？', 'qwen_chat');

SELECT * FROM ogai_rerank(
    '数据库优化',
    ARRAY['索引优化', '备份策略', '查询计划'],
    'rerank_model'
) ORDER BY rerank_score DESC;

SELECT * FROM ogai_chunk('长文本...', 500, 100);

SELECT load_onnx_model('local_bge');

SELECT unload_onnx_model('local_bge');

ogai.ai_vectorize(
    p_task_name TEXT,
    p_task_type TEXT,
    p_index_type TEXT,
    p_embed_model TEXT,
    p_src_schema TEXT,
    p_src_table TEXT,
    p_src_col TEXT,
    p_primary_key TEXT,
    p_table_method TEXT,
    p_dim INTEGER,
    p_max_chunk_size INTEGER DEFAULT 1000,
    p_max_chunk_overlap INTEGER DEFAULT 200,
    p_enable_bm25 BOOLEAN DEFAULT true
) RETURNS TABLE(task_id INT, success BOOLEAN, processed_count INT, message TEXT)

-- 同步模式，append 方式
SELECT * FROM ogai.ai_vectorize(
    'simple_task', 'sync', 'cosine', 'my_embed_model',
    'public', 'articles', 'content', 'id',
    'append', 1536, 0, 0, true
);

-- 异步模式，join 方式，启用分块
SELECT * FROM ogai.ai_vectorize(
    'chunked_task', 'async', 'cosine', 'my_embed_model',
    'public', 'documents', 'content', 'id',
    'join', 1536, 500, 100, true
);

-- 基础搜索
SELECT * FROM ogai.search('my_task', '数据库优化方法');

-- 指定返回列和数量
SELECT * FROM ogai.search('my_task', '数据库优化', 'id, title, content', 5, '');

-- 设置混合搜索权重
SET ogai.hybrid_search_ratio = 0.7;

-- 执行混合搜索
SELECT * FROM ogai.hybrid_search('my_task', 'openGauss数据库优化', 'id, title', 10, '');

SELECT ogai.rag(
    'openGauss支持哪些向量索引类型？',
    'knowledge_base_task',
    'rerank_model',
    'chat_model',
    5,
    20
);

SELECT * FROM ogai.ai_unvectorize('my_task');

INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, api_key, owner_name)
VALUES ('openai_embed', 'text-embedding-ada-002', 'openai', 
        'https://api.openai.com/v1', 'sk-xxx', CURRENT_USER);

INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, api_key, owner_name)
VALUES ('qwen_embed', 'text-embedding-v3', 'Qwen',
        'https://dashscope.aliyuncs.com/compatible-mode/v1', 'sk-xxx', CURRENT_USER);

INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, owner_name)
VALUES ('ollama_embed', 'nomic-embed-text', 'ollama', 'http://localhost:11434', CURRENT_USER);

INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, owner_name)
VALUES ('onnx_bge', 'bge-small-zh', 'onnx', '/data/models/bge-small-zh.onnx', CURRENT_USER);

-- 1. 注册模型
INSERT INTO ogai.model_sources (model_key, model_name, model_provider, url, api_key, owner_name)
VALUES 
    ('embed_model', 'text-embedding-v3', 'Qwen', 
     'https://dashscope.aliyuncs.com/compatible-mode/v1', 'your-api-key', CURRENT_USER),
    ('chat_model', 'qwen-turbo', 'Qwen',
     'https://dashscope.aliyuncs.com/compatible-mode/v1', 'your-api-key', CURRENT_USER),
    ('rerank_model', 'gte-rerank', 'Qwen',
     'https://dashscope.aliyuncs.com/compatible-mode/v1', 'your-api-key', CURRENT_USER);

-- 2. 创建知识库表
CREATE TABLE knowledge_base (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT
);

-- 3. 插入数据
INSERT INTO knowledge_base (title, content, category) VALUES
('openGauss简介', 'openGauss是一款开源关系型数据库...', '产品'),
('向量索引', 'openGauss支持HNSW、IVFFlat等向量索引...', '技术');

-- 4. 创建向量化任务
SELECT * FROM ogai.ai_vectorize(
    'kb_task', 'sync', 'cosine', 'embed_model',
    'public', 'knowledge_base', 'content', 'id',
    'join', 1024, 500, 100, true
);

-- 5. 向量搜索
SELECT * FROM ogai.search('kb_task', '什么是openGauss', 'id, title', 5, '');

-- 6. 混合搜索
SET ogai.hybrid_search_ratio = 0.7;
SELECT * FROM ogai.hybrid_search('kb_task', 'openGauss向量索引', '', 5, '');

-- 7. RAG问答
SELECT ogai.rag('openGauss支持哪些索引？', 'kb_task', 'rerank_model', 'chat_model', 5, 20);

-- 8. 清理
SELECT * FROM ogai.ai_unvectorize('kb_task');